如何按Emp_Id、时间范围及最长时长对访问记录分组并排名？

需求明确 #

• 一级分组：按Emp_Id聚合所有访问记录
• 子组划分：同一Emp_Id下，时间区间存在重叠/包含关系的记录归为同一个子组（示例中9:00-11:00、13:00-14:00为两个独立子组）
• 子组排名：每个子组内按Duration从高到低生成连续的排名值

SQL实现代码 #

以下以支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server）为例：

WITH subgroup_cte AS (
    SELECT 
        cv_id,
        Emp_Id,
        revin,
        revout,
        Duration,
        -- 生成子组ID：同一Emp_Id下，时间不重叠则开启新子组
        SUM(CASE WHEN revin <= LAG(revout) OVER(PARTITION BY Emp_Id ORDER BY revin) THEN 0 ELSE 1 END) 
            OVER(PARTITION BY Emp_Id ORDER BY revin) AS subgroup_id
    FROM access_data
)
SELECT 
    cv_id,
    Emp_Id,
    revin,
    revout,
    Duration,
    subgroup_id,
    -- 子组内按Duration降序生成连续唯一排名（相同Duration获不同排名）
    ROW_NUMBER() OVER(PARTITION BY Emp_Id, subgroup_id ORDER BY Duration DESC) AS rank_num,
    -- 若需相同Duration共享同一排名，替换为：
    -- DENSE_RANK() OVER(PARTITION BY Emp_Id, subgroup_id ORDER BY Duration DESC) AS rank_num
FROM subgroup_cte
ORDER BY Emp_Id, subgroup_id, rank_num;

逻辑拆解 #

1. 子组识别：

   • 用LAG(revout) OVER(...)获取当前记录的前一条同Emp_Id记录的离开时间
   • 通过CASE判断当前记录的进入时间是否晚于前一条的离开时间：是则标记为新子组（1），否则归为当前子组（0）
   • 用SUM() OVER()累加标记值，生成唯一的subgroup_id，同一子组的ID完全相同

2. 排名生成：

   • ROW_NUMBER()：严格按时长降序生成连续唯一排名，适合需区分相同时长记录的场景
   • DENSE_RANK()：相同时长的记录共享同一排名，排名值连续无跳号，适合无需区分相同时长的场景

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