如何在Redshift SQL中统计到下一个更高值前的≤值数量？

你的需求是对每个分组（国家+产品）内按月份升序排列的行，统计从当前行开始到第一个出现更高stock值之前，所有stock≤当前值的月份数量。当前查询的问题在于，它统计了所有后续stock≤当前值的行，而非截止到第一个更高值的范围。

解决方案思路 #

1. 为每个分组内的行按月份生成递增行号（rn），同时计算每个分组的最大行号（max_rn），用于处理后续无更高stock的场景。
2. 对每一行，找到后续第一个stock大于当前值的行的最小行号（first_higher_rn）。
3. 根据first_higher_rn是否存在，计算free_duration：
   • 若存在：用first_higher_rn减去当前行号再减1（排除第一个更高值的行本身）。
   • 若不存在（后续无更高stock）：用分组最大行号减去当前行号，统计到最后一行的数量。

修正后的SQL代码 #

WITH ranking AS (
    SELECT 
        month,
        country,
        product,
        stock,
        -- 按分组生成按月份升序的行号
        ROW_NUMBER() OVER (PARTITION BY country, product ORDER BY month) AS rn,
        -- 计算每个分组的最大行号
        MAX(ROW_NUMBER() OVER (PARTITION BY country, product ORDER BY month)) 
            OVER (PARTITION BY country, product) AS max_rn
    FROM data_table
),
first_higher_rn_cte AS (
    SELECT 
        r1.*,
        -- 找到后续第一个stock大于当前值的最小行号
        (SELECT MIN(r2.rn)
         FROM ranking r2
         WHERE r2.country = r1.country
           AND r2.product = r1.product
           AND r2.rn > r1.rn
           AND r2.stock > r1.stock) AS first_higher_rn
    FROM ranking r1
)
SELECT 
    month,
    country,
    product,
    stock,
    CASE
        -- 存在后续更高stock时，计算到该行之前的数量
        WHEN first_higher_rn IS NOT NULL THEN first_higher_rn - rn - 1
        -- 无后续更高stock时，计算到分组最后一行的数量
        ELSE max_rn - rn
    END AS free_duration
FROM first_higher_rn_cte
ORDER BY country, product, month;

示例验证 #

针对你提供的测试数据：

• 2010年7月（行号1）：第一个更高stock的行号为7，计算得7-1-1=5，符合预期结果。
• 2010年12月（行号6）：第一个更高stock的行号为7，计算得7-6-1=0，符合预期结果。
• 2011年1月（行号7）：后续无更高stock，分组最大行号为9，计算得9-7=2，符合预期结果。
• 2011年3月（行号9）：无后续行，计算得9-9=0，符合预期结果。

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