Snowflake中如何筛选含指定值分组的所有行记录

你的需求既可以用分区窗口函数实现，也可以用兼容性更强的分组子查询实现，两种方案都能精准满足「保留含US记录的PET分组下全部行、整体排除无US记录的PET分组」的过滤规则，具体写法如下：

方案1：分区窗口函数实现 #

按PET字段分区，统计每个PET分组内COUNTRY值为US的记录数量，只要计数大于0就保留该分组所有行，写法如下：

SELECT ID, PET, COUNTRY
FROM (
    SELECT
        *,
        SUM(CASE WHEN COUNTRY = 'US' THEN 1 ELSE 0 END) OVER (PARTITION BY PET) AS us_flag
    FROM A
) temp
WHERE us_flag > 0;

逻辑说明：窗口函数会把同一个PET值的所有记录划到同一个分区，分区内只要存在1条COUNTRY='US'的记录，该分区下所有行的us_flag值都会等于US记录的条数（你的场景下每个PET固定2条记录，有US的组us_flag值为1，无US的组值为0），外层直接过滤us_flag>0即可拿到符合要求的结果。

方案2：IN子查询实现（全SQL版本兼容） #

不需要窗口函数支持，先查出所有包含US记录的PET值集合，再回表筛选对应PET的全部行即可，写法更简洁：

SELECT ID, PET, COUNTRY
FROM A
WHERE PET IN (
    SELECT PET
    FROM A
    WHERE COUNTRY = 'US'
    GROUP BY PET
);

针对你给出的样例数据，上述两种写法都会排除无US记录的SNAKE、HORSE分组，完整返回DOG、CAT、PIG三个分组下的所有记录，和预期结果完全一致。

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