按同组列条件筛选多行航段数据的SQL实现咨询

需要对航班航段原始数据集做行级筛选，保留原始航段粒度，暂不做分组聚合，筛选规则如下：

• 以acid、index、date三个字段作为航班分组标识
• 若分组内任意一条记录满足us_air=1，则该分组下所有航段记录全部保留
• 若分组内所有记录us_air均为0，则该分组整组剔除

你当前使用的SQL存在逻辑缺陷：三个IN子查询是分别单独判断三个字段是否出现在含us_air=1的记录中，没有绑定三个字段的同组匹配关系，会把字段值分别命中、但实际不属于同一有效分组的错误数据保留下来，导致筛选结果不准。

以下两种写法均为SQL通用语法，兼容绝大多数主流数据库（Spark SQL、Hive、MySQL 8.0+、PostgreSQL等），筛选结果和你给出的预期完全一致。

方案1：窗口函数实现（推荐，性能最优） #

仅需全表扫描一次，通过窗口函数直接给每行打上所属分组是否符合保留条件的标记，再做过滤即可，不会改变原始行粒度：

CREATE TABLE table2 AS
SELECT acid, `index`, date, segment, us_air
FROM (
    SELECT
        *,
        MAX(us_air) OVER (PARTITION BY acid, `index`, date) AS group_valid_flag
    FROM table1
) t
WHERE group_valid_flag = 1;

注意：index是多数数据库的保留关键字，引用时需要用反引号包裹转义，避免语法报错。

方案2：子查询关联实现（兼容老版本数据库） #

如果使用的数据库不支持窗口函数，可以先通过聚合查询捞出所有符合保留条件的分组标识，再和原表做三字段等值关联，关联命中的行全部保留：

CREATE TABLE table2 AS
SELECT t1.*
FROM table1 t1
INNER JOIN (
    SELECT acid, `index`, date
    FROM table1
    GROUP BY acid, `index`, date
    HAVING MAX(us_air) = 1
) valid_group
ON t1.acid = valid_group.acid
    AND t1.`index` = valid_group.`index`
    AND t1.date = valid_group.date;

用你提供的样例数据运行上述语句：

• xyz/123/2020-10-01组存在us_air=1的记录，组内3条航段（含us_air=0的segment=2航段）全部保留
• abc/456/2020-10-02组所有记录us_air=0，整组2条航段全部剔除
• def/789/2020-10-03组所有记录us_air=1，组内2条航段全部保留
  最终输出和你给出的预期结果完全匹配。

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