使用WITH和UNION关联双表统计冷暖日航班数及天数

表结构与示例数据 #

Table A（约10万行，存储航班日期、航班类型、机场信息） #

Table B（约60行，存储日期、气温、降水量） #

需求说明 #

• 定义：气温(temp)低于40的日期为冷天，其余为暖天
• 统计目标：分别计算冷天、暖天的总航班数（Num_of_flight）及对应天数（Num_of_days）
• 注意：实际数据中有10个匹配日期需纳入统计，要求用WITH和UNION实现，期望结果格式如下：

期望结果 #

解决方案（使用WITH和UNION） #

WITH day_type AS (
    SELECT
        b.date,
        CASE WHEN b.temp < 40 THEN 'cold day' ELSE 'warm day' END AS day_category,
        COUNT(a.flight) AS flight_count
    FROM Table_B b
    JOIN Table_A a ON b.date = a.date
    GROUP BY b.date, day_category
)
SELECT
    'cold day' AS Days,
    SUM(flight_count) AS Num_of_flight,
    COUNT(*) AS Num_of_days
FROM day_type
WHERE day_category = 'cold day'
UNION ALL
SELECT
    'warm day' AS Days,
    SUM(flight_count) AS Num_of_flight,
    COUNT(*) AS Num_of_days
FROM day_type
WHERE day_category = 'warm day';

代码说明 #

1. CTE day_type：关联两张表，按日期和天类型分组，计算每个日期的航班数量
2. UNION ALL：分别统计冷天、暖天的总航班数（对每日航班数求和）和天数（统计符合条件的日期数量），最终合并两组统计结果

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