如何用Oracle SQL筛选无中断连续时间序列的设备数据？

需求说明 #

需要筛选满足以下条件的条目：

• 当SENDTIME_NOW=10时
• SENDTIME从起始值（00h）到当前时间（10h）无中断连续
• 连续记录次数至少为5次

原始数据 #

INSP_EQP    SENDTIME    SENDTIME_NOW
Item A      00h             10
Item A      01h             10
Item A      02h             10
Item A      03h             10
Item A      04h             10
Item A      05h             10
Item A      06h             10
Item A      07h             10
Item A      09h             10
Item A      10h             10
Item B      00h             10
Item B      01h             10
Item B      02h             10
Item B      03h             10
Item B      04h             10
Item B      05h             10
Item B      06h             10
Item B      07h             10
Item B      08h             10
Item B      09h             10
Item B      10h             10

期望结果 #

INSP_EQP    SENDTIME    SENDTIME_NOW
Item B      00h             10
Item B      01h             10
Item B      02h             10
Item B      03h             10
Item B      04h             10
Item B      05h             10
Item B      06h             10
Item B      07h             10
Item B      08h             10
Item B      09h             10
Item B      10h             10

解决方案：Oracle SQL实现 #

完全可以通过Oracle SQL的窗口函数和分组统计完成需求，无需额外使用其他语言。以下是具体实现代码：

WITH processed_data AS (
    SELECT 
        INSP_EQP,
        SENDTIME,
        SENDTIME_NOW,
        -- 提取SENDTIME中的数字部分并转换为整数，方便连续性计算
        TO_NUMBER(REGEXP_SUBSTR(SENDTIME, '\d+')) AS sendtime_num
    FROM your_table
    WHERE SENDTIME_NOW = 10
),
grouped_data AS (
    SELECT 
        *,
        -- 生成分组标识：连续的sendtime_num会得到相同的grp值，中断处grp会变化
        sendtime_num - ROW_NUMBER() OVER (PARTITION BY INSP_EQP ORDER BY sendtime_num) AS grp
    FROM processed_data
),
valid_items AS (
    SELECT 
        INSP_EQP
    FROM grouped_data
    GROUP BY INSP_EQP, grp
    -- 筛选覆盖0到10完整区间、且记录数≥5的设备
    HAVING MIN(sendtime_num) = 0 
        AND MAX(sendtime_num) = 10
        AND COUNT(*) >= 5
)
SELECT 
    t.INSP_EQP,
    t.SENDTIME,
    t.SENDTIME_NOW
FROM your_table t
JOIN valid_items vi ON t.INSP_EQP = vi.INSP_EQP
WHERE t.SENDTIME_NOW = 10
ORDER BY t.INSP_EQP, t.SENDTIME;

代码逻辑说明 #

1. processed_data：过滤出SENDTIME_NOW=10的记录，同时将带h后缀的SENDTIME转换为整数，便于后续连续性判断。
2. grouped_data：使用ROW_NUMBER()窗口函数按设备分组排序，通过sendtime_num - 行号生成分组标识，连续时间的记录会拥有相同的grp值，中断的记录会生成新的grp。
3. valid_items：按设备和分组标识聚合，筛选出时间覆盖从0到10、且记录数≥5的设备。
4. 最后关联原表，得到符合条件的所有原始记录。

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