SQL实现：统计指定时间区间内指定列的去重值出现次数

现有打卡数据集包含arrivedid（打卡人ID）、clockin（打卡时间，字符串格式）两个字段，需要为每条记录统计其打卡时间之前指定时间区间内的去重打卡ID数量：

• 以单条记录的打卡时间为基准，拆分前1小时为两个连续30分钟窗口
• 分别统计每个窗口内出现的不重复arrivedid数量

示例数据集如下：

核心逻辑为表自关联+条件聚合，通过时间范围匹配关联出所有落在统计窗口内的记录，再按窗口条件做去重计数即可，以下以MySQL 8.0+版本为例给出实现代码：

WITH base_data AS (
    SELECT
        arrivedid,
        -- 字符串时间转datetime类型，不同数据库替换对应语法
        STR_TO_DATE(clockin, '%Y-%m-%dT%H:%i') AS clock_time
    FROM attendance -- 替换为实际表名
)
SELECT
    t1.arrivedid AS record_id,
    t1.clock_time AS record_clockin,
    -- 统计 [基准时间-30分钟, 基准时间) 窗口去重ID
    COUNT(DISTINCT CASE
        WHEN t2.clock_time >= DATE_SUB(t1.clock_time, INTERVAL 30 MINUTE)
            AND t2.clock_time < t1.clock_time
        THEN t2.arrivedid
    END) AS cnt_last_30min,
    -- 统计 [基准时间-60分钟, 基准时间-30分钟) 窗口去重ID
    COUNT(DISTINCT CASE
        WHEN t2.clock_time >= DATE_SUB(t1.clock_time, INTERVAL 60 MINUTE)
            AND t2.clock_time < DATE_SUB(t1.clock_time, INTERVAL 30 MINUTE)
        THEN t2.arrivedid
    END) AS cnt_30min_to_60min
FROM base_data t1
LEFT JOIN base_data t2
    ON t2.clock_time >= DATE_SUB(t1.clock_time, INTERVAL 60 MINUTE)
    AND t2.clock_time < t1.clock_time
GROUP BY t1.arrivedid, t1.clock_time;

注意事项 #

• 上述代码时间窗口采用左闭右开规则，避免临界时间点的记录被重复统计；如果需要将当前记录本身计入统计，将所有判断条件里的< t1.clock_time改为<= t1.clock_time即可。
• 其他数据库适配只需要替换时间计算函数：
  • PostgreSQL：用clock_time - INTERVAL '30 minutes'替换DATE_SUB相关写法
  • SQL Server：用DATEADD(minute, -30, clock_time)替换DATE_SUB相关写法
• 数据量较大时，建议给clockin字段建立索引，能大幅提升自关联查询的性能。
• 如果需要扩展更多统计窗口，只需要新增对应CASE WHEN的统计字段，同时调整JOIN条件里的时间下限覆盖所有窗口范围即可。

按照示例数据运行上述代码，输出结果如下：

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