Oracle中按时间排序为速度<5的连续记录范围分配唯一标识

需求说明 #

现有Oracle表存储GPS点位及速度数据（忽略位置信息），原始数据如下：

ID   TIME                 SPEED   
--------------------------------- 
 1   2024-01-01 09:00:02     0
 3   2024-01-01 09:00:03     0     
 6   2024-01-01 09:00:04     2
 8   2024-01-01 09:00:09    11
14   2024-01-01 09:00:10    15
11   2024-01-01 09:00:22    22
12   2024-01-01 09:00:28     4
10   2024-01-01 09:00:32     0
15   2024-01-01 09:00:33     2
16   2024-01-01 09:00:34     8
17   2024-01-01 09:00:35    12
18   2024-01-01 09:00:38     3

需按TIME字段排序，实现两个需求：

1. 为所有SPEED < 5的连续记录范围分配唯一标识RANGE_NO，非该区间的记录标识为NULL或0
2. 为所有连续的区间（无论速度是否满足条件）分配唯一递增标识ALT_RANGE_NO

期望输出如下：

ID   TIME                 SPEED   RANGE_NO     ALT_RANGE_NO
--------------------------------------------------------------- 
 1   2024-01-01 09:00:02     0    1                   1
 3   2024-01-01 09:00:03     0    1                   1
 6   2024-01-01 09:00:04     2    1                   1 
 8   2024-01-01 09:00:09    11    (null or 0)         2
14   2024-01-01 09:00:10    15    (null or 0)         2 
11   2024-01-01 09:00:22    22    (null or 0)         2 
12   2024-01-01 09:00:28     4    2                   3
10   2024-01-01 09:00:32     0    2                   3
15   2024-01-01 09:00:33     2    2                   3 
16   2024-01-01 09:00:34     8    (null or 0)         4
17   2024-01-01 09:00:35    12    (null or 0)         4
18   2024-01-01 09:00:38     3    3                   5

实现方案 #

核心思路是利用**分析函数SUM() OVER()**结合条件判断，识别连续区间的起始点，通过累计求和生成分组编号，无需依赖LEAD()/LAG()的复杂边界判断，单查询+WITH子句即可完成。

完整SQL代码 #

WITH base_data AS (
    SELECT 
        t.*,
        -- 标记当前行是否是SPEED<5区间的起始点
        CASE 
            WHEN SPEED < 5 AND (LAG(SPEED) OVER (ORDER BY TIME) >=5 OR LAG(SPEED) OVER (ORDER BY TIME) IS NULL)
            THEN 1 
            ELSE 0 
        END AS is_low_speed_start,
        -- 标记当前行是否是任意区间的起始点（用于ALT_RANGE_NO）
        CASE 
            WHEN (LAG(CASE WHEN SPEED <5 THEN 1 ELSE 0 END) OVER (ORDER BY TIME) IS NULL)
                 OR (LAG(CASE WHEN SPEED <5 THEN 1 ELSE 0 END) OVER (ORDER BY TIME) != CASE WHEN SPEED <5 THEN 1 ELSE 0 END)
            THEN 1 
            ELSE 0 
        END AS is_any_range_start
    FROM your_table t
)
SELECT 
    ID,
    TIME,
    SPEED,
    -- 生成RANGE_NO：仅对SPEED<5的连续组分配编号，否则为NULL
    CASE WHEN SPEED <5 THEN SUM(is_low_speed_start) OVER (ORDER BY TIME) ELSE NULL END AS RANGE_NO,
    -- 生成ALT_RANGE_NO：所有连续区间的递增编号
    SUM(is_any_range_start) OVER (ORDER BY TIME) AS ALT_RANGE_NO
FROM base_data
ORDER BY TIME;

代码解释 #

1. base_data CTE：
   • is_low_speed_start：判断当前行是否是SPEED<5连续区间的起始行（前一行速度≥5或为第一行），是则标记为1，否则0。
   • is_any_range_start：判断当前行是否是任意连续区间的起始行（前一行的速度分类（<5/≥5）与当前行不同，或为第一行），是则标记为1，否则0。
2. 主查询：
   • RANGE_NO：对SPEED<5的行，累计求和is_low_speed_start得到连续组的唯一编号；非满足条件的行返回NULL。
   • ALT_RANGE_NO：累计求和is_any_range_start，得到所有连续区间的递增编号，无论速度是否满足条件。

替代写法（无WITH子句） #

如果不需要CTE，也可以将逻辑直接嵌入子查询：

SELECT 
    ID,
    TIME,
    SPEED,
    CASE WHEN SPEED <5 THEN SUM(is_low_speed_start) OVER (ORDER BY TIME) ELSE NULL END AS RANGE_NO,
    SUM(is_any_range_start) OVER (ORDER BY TIME) AS ALT_RANGE_NO
FROM (
    SELECT 
        t.*,
        CASE 
            WHEN SPEED < 5 AND (LAG(SPEED) OVER (ORDER BY TIME) >=5 OR LAG(SPEED) OVER (ORDER BY TIME) IS NULL)
            THEN 1 
            ELSE 0 
        END AS is_low_speed_start,
        CASE 
            WHEN (LAG(CASE WHEN SPEED <5 THEN 1 ELSE 0 END) OVER (ORDER BY TIME) IS NULL)
                 OR (LAG(CASE WHEN SPEED <5 THEN 1 ELSE 0 END) OVER (ORDER BY TIME) != CASE WHEN SPEED <5 THEN 1 ELSE 0 END)
            THEN 1 
            ELSE 0 
        END AS is_any_range_start
    FROM your_table t
) sub
ORDER BY TIME;

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