求可查找任意时段内活跃于多个部门的用户的SQL查询语句

需求：从用户部门活跃表中，找出任意时间点同时活跃于多个部门的用户。比如示例中的U1需要被返回，因为其在2021-04-01至2021-12-31期间同时活跃于D1和D2两个部门。

示例数据表 #

SQL查询语句 #

方法一：自连接判断时间重叠 #

SELECT DISTINCT a.UserId
FROM user_department_active a
JOIN user_department_active b
  ON a.UserId = b.UserId
  AND a.Department != b.Department
  AND a.ActiveFromDate <= b.ActiveToDate
  AND a.ActiveToDate >= b.ActiveFromDate;

逻辑说明

• 自连接同一张表，关联条件为同一用户、不同部门
• 通过a.ActiveFromDate <= b.ActiveToDate AND a.ActiveToDate >= b.ActiveFromDate判断两个部门的活跃时间段存在重叠
• 用DISTINCT去重，避免同一用户被多次返回

方法二：窗口函数统计重叠时段的部门数 #

SELECT DISTINCT UserId
FROM (
    SELECT 
        UserId,
        COUNT(DISTINCT Department) OVER (
            PARTITION BY UserId
            ORDER BY ActiveFromDate
            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS dept_count
    FROM user_department_active
    WHERE EXISTS (
        SELECT 1
        FROM user_department_active b
        WHERE b.UserId = user_department_active.UserId
          AND b.Department != user_department_active.Department
          AND b.ActiveFromDate <= user_department_active.ActiveToDate
          AND b.ActiveToDate >= user_department_active.ActiveFromDate
    )
) t
WHERE dept_count >= 2;

逻辑说明

• 子查询中通过窗口函数统计每个用户在重叠时段内的活跃部门数量
• 外层筛选出部门数≥2的用户，确保存在同时活跃的情况

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