查找分组中未遵循价格递减序列的SQL记录

现有数据表如下：

需要筛选出按Date分组后，Hierarchy从1到6过程中价格未保持递减的记录。例如18-10-2022组的ID4，19-10-2022组的ID9、10、11。

使用窗口函数LAG()追踪每组内的价格变化，标记并筛选不符合递减规则的记录：

WITH grouped_data AS (
    SELECT 
        *,
        -- 获取同组内上一个Hierarchy对应的价格
        LAG(Price) OVER (PARTITION BY Date ORDER BY Hierarchy) AS prev_price,
        -- 累计标记分组内是否出现过价格上涨的情况
        SUM(CASE WHEN Price > LAG(Price) OVER (PARTITION BY Date ORDER BY Hierarchy) THEN 1 ELSE 0 END)
            OVER (PARTITION BY Date ORDER BY Hierarchy ROWS UNBOUNDED PRECEDING) AS has_break
    FROM your_table
    WHERE Hierarchy BETWEEN 1 AND 6
)
SELECT ID, Date, `Rate Type`, Price, Hierarchy
FROM grouped_data
-- 筛选自身打破递减，或之前已出现打破的记录
WHERE has_break > 0;

输出结果 #

执行后将得到目标记录：

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