如何统计两日期间特定周几航班数并按日统计航班总量？

问题背景 #

现有航班时刻表数据表结构及示例如下：

• frequency字段代表航班执行的周几，1=周一，2=周二，以此类推，字段中的数字即为执行航班的周几，其他符号为占位符。

最终需求：生成一张按日期统计特定国家出发航班总量的表格。

现有思路存在逻辑错误，且代码无法运行，主要问题包括：

• 无法在CTE中直接引用原表的start_date和end_date生成日期数组
• 无法直接在CROSS JOIN中引用刚生成的数组字段frequencyNEW
• 分组逻辑错误，数组字段无法直接参与分组统计

正确解决方案 #

核心思路 #

1. 拆分frequency字段，提取出所有航班执行的周几数字；
2. 为每个航班生成其start_date到end_date范围内的所有日期，并将日期转换为对应的周几数字；
3. 匹配航班执行的周几和日期的周几，筛选出实际有航班的日期；
4. 按日期和出发国家分组，统计每日的航班总量。

BigQuery实现代码 #

WITH flight_schedules AS (
  -- 拆分frequency字段，提取执行的周几，并保留原表其他字段
  SELECT
    id,
    departure_country,
    arrival_country,
    -- 提取frequency中的所有数字，转为整数类型的周几
    CAST(weekday AS INT64) AS flight_weekday,
    start_date,
    end_date
  FROM flights_table,
    UNNEST(REGEXP_EXTRACT_ALL(frequency, r'[0-9]')) AS weekday
),
date_range AS (
  -- 为每个航班生成起止日期内的所有日期，并计算日期对应的周几
  SELECT
    fs.id,
    fs.departure_country,
    fs.flight_weekday,
    date_list AS flight_date,
    -- 将日期转为周几数字（1=周一，7=周日）
    CAST(FORMAT_DATE('%u', date_list) AS INT64) AS date_weekday
  FROM flight_schedules fs,
    UNNEST(GENERATE_DATE_ARRAY(fs.start_date, fs.end_date)) AS date_list
),
valid_flights AS (
  -- 筛选出日期周几与航班执行周几匹配的记录
  SELECT
    departure_country,
    flight_date
  FROM date_range
  WHERE flight_weekday = date_weekday
)
-- 按日期和出发国家统计每日航班总量
SELECT
  flight_date,
  departure_country,
  COUNT(*) AS total_flights
FROM valid_flights
-- 可添加WHERE条件筛选特定国家，比如 WHERE departure_country = 'Germany'
GROUP BY flight_date, departure_country
ORDER BY flight_date, departure_country;

代码说明 #

• flight_schedules：拆分frequency字段，将每个航班的执行周几拆成单独的行，方便后续关联日期。
• date_range：为每个航班的每个执行周几生成所有日期，并计算每个日期对应的周几。
• valid_flights：匹配航班执行周几和日期周几，得到实际有航班的日期记录。
• 最终统计：按日期和出发国家分组，得到每日的航班总量，可根据需求添加国家筛选条件。

内容的提问来源于stack exchange，提问作者cavierx