基于双表多条件计算比率：CTE/CASE WHEN实现方案咨询

现有表格 #

Table A（航班数据） #

Table B（气象数据） #

需求说明 #

1. 先筛选出平均降水量（precip）>0.2的温度（temp）分组，仅保留这些分组的所有行
2. 对每个符合条件的temp，计算满足flight='oneway'且airport包含"CA"的行数占该temp总行数的比率，最终转换为整数

基于CTE的SQL实现 #

WITH valid_temps AS (
    -- 第一步：筛选出平均precip>0.2的temp集合
    SELECT temp
    FROM TableB
    GROUP BY temp
    HAVING AVG(precip) > 0.2
),
combined_data AS (
    -- 第二步：关联航班与气象表，仅保留符合条件的temp数据
    SELECT
        b.temp,
        a.flight,
        a.airport
    FROM TableA a
    INNER JOIN TableB b ON a.date = b.date
    INNER JOIN valid_temps vt ON b.temp = vt.temp
)
-- 第三步：计算每个temp的目标比率并转整数
SELECT
    temp,
    -- 按"符合条件行数/总行数*100"取整，可根据需求调整取整逻辑
    CAST(
        (SUM(CASE WHEN flight = 'oneway' AND airport LIKE '%CA%' THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
        AS INTEGER
    ) AS ratio
FROM combined_data
GROUP BY temp;

逻辑说明 #

• 第一步CTE（valid_temps）：先从气象表单独计算每个temp的平均降水量，筛选出符合条件的temp，避免后续关联大量无效数据，提升大表处理效率
• 第二步CTE（combined_data）：通过date关联两张表，并仅保留第一步筛选出的有效temp数据，确保后续计算的数据集准确
• 最终计算：用CASE WHEN标记符合条件的行，通过SUM统计符合条件的行数，除以该temp的总行数得到比率，乘以100后转整数

针对之前错误的修正 #

之前按date关联后直接按temp分组筛选平均precip<0.2的组，错误在于关联后的数据集会改变precip的分组计算逻辑，导致平均precip的统计结果失真。先单独从气象表筛选有效temp，再关联航班表，能确保分组统计的准确性，同时减少大表关联的数据量。

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