SQL中按超1小时时间间隔统计number有效计数的查询方法

现有表结构及示例数据如下：

需求：统计每个number的有效计数，规则为同number的两条记录dt_start间隔超过1小时则计入统计，示例数据的预期结果为2。

解决思路与代码实现 #

核心逻辑是用窗口函数LAG()获取同number分组内上一条记录的时间，计算时间差后筛选符合条件的记录，最终统计有效数量。以下是不同数据库的实现代码：

SQL Server

WITH numbered_records AS (
    SELECT 
        number,
        dt_start,
        -- 按number分组、dt_start排序，获取上一条记录的时间
        LAG(dt_start) OVER (PARTITION BY number ORDER BY dt_start) AS prev_dt
    FROM your_table_name
)
SELECT 
    number,
    -- 第一条记录默认算1，加上间隔超1小时的记录数
    1 + COUNT(CASE WHEN DATEDIFF(MINUTE, prev_dt, dt_start) > 60 THEN 1 END) AS valid_count
FROM numbered_records
GROUP BY number;

MySQL

WITH numbered_records AS (
    SELECT 
        number,
        dt_start,
        LAG(dt_start) OVER (PARTITION BY number ORDER BY dt_start) AS prev_dt
    FROM your_table_name
)
SELECT 
    number,
    1 + COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, prev_dt, dt_start) > 60 THEN 1 END) AS valid_count
FROM numbered_records
GROUP BY number;

PostgreSQL

WITH numbered_records AS (
    SELECT 
        number,
        dt_start,
        LAG(dt_start) OVER (PARTITION BY number ORDER BY dt_start) AS prev_dt
    FROM your_table_name
)
SELECT 
    number,
    1 + COUNT(CASE WHEN EXTRACT(EPOCH FROM (dt_start - prev_dt)) / 60 > 60 THEN 1 END) AS valid_count
FROM numbered_records
GROUP BY number;

逻辑说明 #

1. 利用LAG()窗口函数按number分组、时间排序，拿到每条记录对应的上一条记录时间prev_dt
2. 计算当前记录与prev_dt的时间差，筛选出间隔超过60分钟的记录
3. 初始计数为1（第一条记录本身默认有效），加上符合条件的记录数，得到最终有效计数

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