SQL实现：按zone_id和subzone_id分组保留operation=0最近出现后的行

需求说明 #

按zone_id和subzone_id分组，仅保留每组内最近一次operation值为0之后的行。其中recency列表示数据新旧程度：值越小越新，值越大越旧，“之后”指记录序列中该0值行之后的更旧数据。

示例数据集 #

期望结果 #

解决方案 #

使用窗口函数标记每组内最后一次出现operation=0的recency值，再筛选出该值之后的行：

WITH grouped_data AS (
    SELECT 
        *,
        -- 计算每组内最后一次operation=0的recency，无0则设为-1
        MAX(CASE WHEN operation = 0 THEN recency ELSE -1 END) OVER (PARTITION BY zone_id, subzone_id) AS last_zero_recency
    FROM your_table
)
SELECT zone_id, subzone_id, recency, operation
FROM grouped_data
-- 保留recency大于最后一次0值行的recency的记录
WHERE recency > last_zero_recency;

逻辑解释 #

1. 标记最后一次0值行：通过CASE语句将非0操作的recency转为-1，再用MAX()窗口函数在每组内取最大值，得到该组中最后一次（recency最大，即最旧的）0值行的recency；若组内无0值行，最大值为-1。
2. 筛选目标行：保留recency大于该标记值的行，即为最后一次0值行之后的所有记录。

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