如何用SQL从时间序列数据中筛选首班次后6小时的次高峰

我现在用时间序列数据和SQL检测班次开始时间，现有代码能识别第一班次的开始时间，但会漏掉第二班次。需求是：在第一班次开始时间（对应number_items的首个最大值）之后的6小时内，筛选出第二个最大值对应的时间，也就是第二班次的开始时间。

原代码：

QUALIFY row_number() over (partition by t8.Process_ID, t8.Store order by t8.number_items DESC ) = 1 

背景说明 #

班次开始时，每位员工会领用一件物品，所以这个阶段的number_items（物品使用量）会处于高位。目前通过筛选number_items的首个最大值，可以得到第一班次的开始时间，但无法捕获第二班次。

数据迭代步骤 #

迭代1：处理后得到的两个月数据

迭代2：聚合后的统计数据

举个例子：store1的第一班次开始于6:15，第二班次开始于14:15，班次结束时间逻辑同理。

解决方案 #

要同时捕获第一和第二班次，需要先确定每个门店、每个日期的第一班次时间，再筛选出该时间后6小时内的次大number_items对应的时间，用窗口函数结合子查询实现：

WITH ranked_shifts AS (
    SELECT 
        Process_ID,
        Store,
        Date,
        Start_time,
        number_items,
        -- 按物品量降序、时间升序排名，确保最大值的最早时间为第一班次
        ROW_NUMBER() OVER (PARTITION BY Process_ID, Store, Date ORDER BY number_items DESC, Start_time ASC) AS shift_rank,
        -- 获取当前组的第一班次开始时间
        FIRST_VALUE(Start_time) OVER (PARTITION BY Process_ID, Store, Date ORDER BY number_items DESC, Start_time ASC) AS first_shift_start
    FROM your_table_name -- 替换为实际表名
),
first_shift_info AS (
    SELECT 
        Process_ID,
        Store,
        Date,
        first_shift_start
    FROM ranked_shifts
    WHERE shift_rank = 1
)
SELECT 
    rs.Process_ID,
    rs.Store,
    rs.Date,
    CASE 
        WHEN rs.shift_rank = 1 THEN '第一班次'
        WHEN rs.shift_rank = 2 THEN '第二班次'
    END AS shift_type,
    rs.Start_time AS shift_start
FROM ranked_shifts rs
JOIN first_shift_info fs 
    ON rs.Process_ID = fs.Process_ID 
    AND rs.Store = fs.Store 
    AND rs.Date = fs.Date
WHERE 
    rs.shift_rank <= 2
    -- 第二班次需在第一班次开始后6小时内
    AND (rs.shift_rank = 1 OR TIMEDIFF(rs.Start_time, fs.first_shift_start) <= '6:00:00')
ORDER BY rs.Process_ID, rs.Store, rs.Date, rs.shift_rank;

代码说明 #

1. ranked_shifts CTE：给每个门店、每天的记录按物品使用量降序、时间升序排名，同时用FIRST_VALUE提取第一班次的开始时间。
2. first_shift_info CTE：单独保存每个门店每天的第一班次基础信息，方便后续关联筛选。
3. 主查询：关联两个CTE，筛选排名前2的记录，且第二班次必须满足在第一班次开始后6小时内的条件，最后输出班次类型和对应的开始时间。

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