SQL技术咨询：提取15分钟粒度表中连续4段求和的峰值小时及关联列

首先修正原SQL中的语法错误（LEAD函数的参数括号写法有误），以下提供两种实用方案来获取最大值对应的关联列：

方法一：窗口函数筛选法 #

先计算每个时段的连续4个15分钟求和结果，同时给每个item的求和值按降序排名，最终取排名第一的记录，即可拿到对应的关联列：

WITH hourly_sums AS (
    SELECT
        item,
        time,
        -- 计算连续4个15分钟时段的总和，修正LEAD函数语法
        result + LEAD(result, 1, 0) OVER (PARTITION BY item ORDER BY time)
        + LEAD(result, 2, 0) OVER (PARTITION BY item ORDER BY time)
        + LEAD(result, 3, 0) OVER (PARTITION BY item ORDER BY time) AS sum_4_periods,
        -- 替换为你实际需要的关联列，示例为col1、col2
        col1,
        col2
    FROM db
    WHERE time >= current_date - 2 AND time < current_date - 1
        AND item LIKE 'item1'
),
ranked_sums AS (
    SELECT
        *,
        -- 按求和值降序排名，最大值排第1
        RANK() OVER (PARTITION BY item ORDER BY sum_4_periods DESC) AS rnk
    FROM hourly_sums
)
SELECT
    item,
    time,
    sum_4_periods AS max_sum,
    col1,
    col2
FROM ranked_sums
WHERE rnk = 1;

方法二：子查询关联法 #

先算出每个item的最大求和值，再关联回原求和结果表，匹配出对应记录的关联列：

WITH hourly_sums AS (
    SELECT
        item,
        time,
        result + LEAD(result, 1, 0) OVER (PARTITION BY item ORDER BY time)
        + LEAD(result, 2, 0) OVER (PARTITION BY item ORDER BY time)
        + LEAD(result, 3, 0) OVER (PARTITION BY item ORDER BY time) AS sum_4_periods,
        col1,
        col2 -- 你的关联列
    FROM db
    WHERE time >= current_date - 2 AND time < current_date - 1
        AND item LIKE 'item1'
),
max_sums AS (
    SELECT
        item,
        MAX(sum_4_periods) AS max_sum
    FROM hourly_sums
    GROUP BY item
)
SELECT
    hs.item,
    hs.time,
    ms.max_sum,
    hs.col1,
    hs.col2
FROM hourly_sums hs
JOIN max_sums ms ON hs.item = ms.item AND hs.sum_4_periods = ms.max_sum;

关键提示 #

• 原SQL中的ORDER BY item,time建议改为PARTITION BY item ORDER BY time，确保每个item独立计算连续时段求和，避免跨item干扰。
• 如果存在多个时段求和值等于最大值，两种方法都会返回所有对应记录；若只需取单条，可将RANK()替换为ROW_NUMBER()。

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