SQL如何基于公共假日为员工连续休假记录正确分组

基础样例数据 #

员工休假表Emp_Vacation样例数据如下：

Emp_id   Vacation_Start_Date    Vacation_End_Date Public_Hday
1234         06/01/2022            06/07/2022       null
1234         06/08/2022            06/14/2022       null
1234         06/15/2022            06/19/2022       06/17/2022
1234         06/20/2022            06/23/2022       null
1234         06/24/2022            06/28/2022       null
1234         06/29/2022            07/02/2022       06/30/2022
1234         07/03/2022            07/07/2022       null
1234         07/08/2022            07/12/2022       null
1234         07/13/2022            07/17/2022       07/15/2022
1234         07/18/2022            07/22/2022       null

分组需求 #

已知所有休假记录时间连续，需要根据休假区间内的公共假日对记录分组，预期输出如下：

Emp_id   Vacation_Start_Date    Vacation_End_Date  Public_Hday    Group
    1234         06/01/2022            06/07/2022       null           0
    1234         06/08/2022            06/14/2022       null           0
    1234         06/15/2022            06/19/2022       06/17/2022     1
    1234         06/20/2022            06/23/2022       null           1 
    1234         06/24/2022            06/28/2022       null           1
    1234         06/29/2022            07/02/2022       06/30/2022     2
    1234         07/03/2022            07/07/2022       null           2
    1234         07/08/2022            07/12/2022       null           2
    1234         07/13/2022            07/17/2022       07/15/2022     3
    1234         07/18/2022            07/22/2022       null           3

原有写法问题 #

已尝试的SQL如下：

Select *, dense_rank() over (partition by Emp_id order by Public_Hday) - 1 AS Group from Emp_Vacation;

该写法的问题是：dense_rank按Public_Hday排序时，所有null值会被归为同一排序秩，无法区分不同公共假日区间前后的空值记录，只有Public_Hday非空的记录能返回正确组号。

正确实现方案 #

分组逻辑本质是：按休假时间顺序排列后，每遇到1条带公共假日的记录，组号加1，公共假日之后直到下一个公共假日出现前的所有记录，和当前公共假日记录同组，第一条公共假日之前的记录归为组0。
利用COUNT聚合窗口函数会自动忽略null值的特性，统计截止到当前行（按休假开始时间排序），该员工累计出现的非空公共假日数量，即可直接得到目标组号，SQL写法如下：

SELECT 
    *,
    COUNT(Public_Hday) OVER (
        PARTITION BY Emp_id 
        ORDER BY Vacation_Start_Date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS `Group`
FROM Emp_Vacation;

注：显式指定窗口帧范围是为了兼容不同数据库的默认窗口规则，避免结果异常。

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