编写SQL查询筛选价格递增序列中ID低于历史最小值的记录

实现思路 #

先按价格升序排列所有数据集，逐行计算两个前置判定指标：

• 当前行之前所有记录的最高价格，用于校验「价格高于历史所有价格」的规则
• 当前行之前所有记录的最小ID，用于校验「当前ID低于历史所有ID最小值」的规则
  排在首位的记录无前置数据，默认满足两项判定条件。

实现代码 #

WITH sorted_base AS (
    SELECT
        id,
        price,
        -- 取当前行之前所有记录的最高价格
        MAX(price) OVER (
            ORDER BY price 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_highest_price,
        -- 取当前行之前所有记录的最小ID
        MIN(id) OVER (
            ORDER BY price 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS prev_min_id
    FROM 你的业务表名
)
SELECT id, price
FROM sorted_base
WHERE
    -- 满足价格规则：要么是首行，要么当前价格高于此前所有最高值
    (prev_highest_price IS NULL OR price > prev_highest_price)
    -- 满足ID规则：要么是首行，要么当前ID小于此前所有最小ID
    AND (prev_min_id IS NULL OR id < prev_min_id)
ORDER BY price;

注意：需将代码中的「你的业务表名」替换为实际存储id、price数据的表名后再运行

结果验证 #

使用你提供的测试数据集运行上述SQL，返回结果与预期目标完全匹配。

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