基于60分钟时间间隔的SQL数据排名及Speed排序需求

原始数据 #

需求说明 #

对每条记录，以自身Time为起点，筛选同Name下该时间往后60分钟内的所有记录，按Speed降序排名，得到每条记录的排名值rnk。

预期输出 #

实现方案（SQL示例） #

这种动态时间区间的排名可以通过自连接匹配区间记录，再结合排名函数实现：

WITH raw_data AS (
    SELECT 
        '2023-01-10 14:11:15.000' AS Time, 'X' AS Name, 111 AS Speed UNION ALL
    SELECT '2023-01-10 16:49:35.000', 'X', 112 UNION ALL
    SELECT '2023-01-10 16:51:35.000', 'X', 110 UNION ALL
    SELECT '2023-01-10 17:34:55.000', 'X', 115 UNION ALL
    SELECT '2023-01-10 17:35:55.000', 'X', 111 UNION ALL
    SELECT '2023-01-10 17:36:55.000', 'X', 117 UNION ALL
    SELECT '2023-01-10 17:37:21.000', 'X', 116
)
SELECT 
    r.rnk,
    d.Time,
    d.Name,
    CASE 
        WHEN COUNT(d2.Time) OVER (PARTITION BY d.Time) = 1 
        THEN CONCAT(d.Speed, '  (该时间后60分钟内无其他记录)') 
        ELSE CAST(d.Speed AS CHAR) 
    END AS Speed
FROM raw_data d
-- 匹配同Name下当前时间往后60分钟内的所有记录
LEFT JOIN raw_data d2 
    ON d.Name = d2.Name 
    AND d2.Time BETWEEN d.Time AND DATE_ADD(d.Time, INTERVAL 60 MINUTE)
-- 计算每条基准时间对应的区间排名
LEFT JOIN (
    SELECT 
        d_inner.Time AS base_time,
        d2_inner.Time,
        RANK() OVER (PARTITION BY d_inner.Time ORDER BY d2_inner.Speed DESC) AS rnk
    FROM raw_data d_inner
    LEFT JOIN raw_data d2_inner 
        ON d_inner.Name = d2_inner.Name 
        AND d2_inner.Time BETWEEN d_inner.Time AND DATE_ADD(d_inner.Time, INTERVAL 60 MINUTE)
) r ON d.Time = r.base_time AND d.Time = r.Time
GROUP BY d.Time, d.Name, d.Speed, r.rnk
ORDER BY d.Time;

逻辑拆解 #

1. 区间匹配：通过自连接，将每条记录与同Name、时间在自身起始时间后60分钟内的所有记录关联，确定排名的范围。
2. 分区排名：以每条记录的起始时间为分区键，对区间内的Speed做降序排名，得到rnk。
3. 备注补充：对区间内仅自身一条记录的情况，添加对应的说明文本。

内容的提问来源于stack exchange，提问作者אופיר אוחיון