如何在SQL中筛选组内最小recency行operation非0的分组

原始数据表：

需求：按zone_id和subzone_id分组，仅保留组内recency最小的行对应的operation不等于0的整个分组。示例中分组（p13,01）因最小recency对应的operation为0被剔除，最终仅保留（801,01）的所有行。

方法一：窗口函数+子查询筛选合法分组 #

WITH group_validity AS (
    SELECT 
        zone_id,
        subzone_id,
        -- 取组内最小recency对应的operation值
        FIRST_VALUE(operation) OVER (PARTITION BY zone_id, subzone_id ORDER BY recency) AS min_recency_op
    FROM your_table
    -- 每个分组只取一行判断即可
    QUALIFY ROW_NUMBER() OVER (PARTITION BY zone_id, subzone_id ORDER BY recency) = 1
), valid_groups AS (
    SELECT zone_id, subzone_id
    FROM group_validity
    WHERE min_recency_op != 0
)
SELECT t.*
FROM your_table t
JOIN valid_groups v ON t.zone_id = v.zone_id AND t.subzone_id = v.subzone_id;

方法二：单窗口函数直接筛选 #

WITH tagged_data AS (
    SELECT 
        *,
        -- 标记组内最小recency对应的operation
        FIRST_VALUE(operation) OVER (PARTITION BY zone_id, subzone_id ORDER BY recency) AS group_valid_op
    FROM your_table
)
SELECT zone_id, subzone_id, recency, operation
FROM tagged_data
WHERE group_valid_op != 0;

两种方法核心逻辑一致：先获取每个分组最小recency对应的operation值，再筛选出该值不为0的分组，保留分组内所有行。

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