如何正确设置Round列？基于工作日规则的SQL查询修正需求

数据集 #

Round列核心规则 #

• 工作日为周一至周六
• 周日为非工作日
• 节假日同样为非工作日
• 仅当连续两个日期之间的工作日间隙（忽略周日和节假日）超过1天时，Round值才变更

节假日表 #

原SQL查询 #

WITH UniqueEntries AS (
    SELECT DISTINCT
        [CC Receiver],
        [Date]
    FROM
        combined_output_zpay_view_harvesting
),
RankedEntries AS (
    SELECT 
        [CC Receiver],
        [Date],
        ROW_NUMBER() OVER (PARTITION BY [CC Receiver] ORDER BY [Date]) AS RowNum
    FROM 
        UniqueEntries
),
-- Step 1: Calculate the number of working days between consecutive dates
WorkingDayDifference AS (
    SELECT
        r1.[CC Receiver],
        r1.[Date],
        r2.[Date] AS NextDate,
        DATEDIFF(DAY, r1.[Date], r2.[Date]) - 
        (
            SELECT COUNT(*) 
            FROM master.dbo.spt_values v
            WHERE v.type = 'P' 
            AND DATEADD(DAY, v.number, r1.[Date]) < r2.[Date]
            AND DATENAME(WEEKDAY, DATEADD(DAY, v.number, r1.[Date])) NOT IN ('Sunday') 
            AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.HolidayDate = DATEADD(DAY, v.number, r1.[Date]))
        ) AS WorkingDaysDiff
    FROM 
        RankedEntries r1
    LEFT JOIN RankedEntries r2
        ON r1.[CC Receiver] = r2.[CC Receiver] 
        AND r1.RowNum + 1 = r2.RowNum
),
-- Step 2: Identify when a new round starts
Rounds AS (
    SELECT
        wd.[CC Receiver],
        wd.[Date],
        CASE
            WHEN wd.WorkingDaysDiff > 1 THEN 1
            ELSE 0
        END AS IsNewRound
    FROM 
        WorkingDayDifference wd
    WHERE wd.NextDate IS NOT NULL
),
-- Step 3: Accumulate the round number
FinalRounds AS (
    SELECT
        [CC Receiver],
        [Date],
        SUM(IsNewRound) OVER (PARTITION BY [CC Receiver] ORDER BY [Date]) + 1 AS Round
    FROM 
        Rounds
)
SELECT
    [CC Receiver],
    [Date],
    Round
FROM
    FinalRounds
WHERE [CC Receiver] = 'DIAB21C054'
ORDER BY
    [CC Receiver], [Date];

错误结果 #

正确预期结果 #

另一错误案例 #

错误原因：2月10日为周六，2月12日为周一，周日为非工作日，两者的工作日间隙仅为1天，应属于同一Round。

修正方案 #

错误根源 #

原SQL的WorkingDaysDiff计算逻辑完全颠倒：它用总天数减去工作日数量，得到的是非工作日数量，而非我们需要的两个日期之间的工作日间隙。正确逻辑是计算连续记录日期之间的工作日天数（忽略周日和节假日），若该天数超过1则开启新Round。

修正后的SQL #

WITH UniqueEntries AS (
    SELECT DISTINCT
        [CC Receiver],
        [Date]
    FROM
        combined_output_zpay_view_harvesting
),
RankedEntries AS (
    SELECT 
        [CC Receiver],
        [Date],
        ROW_NUMBER() OVER (PARTITION BY [CC Receiver] ORDER BY [Date]) AS RowNum
    FROM 
        UniqueEntries
),
-- 计算连续日期之间的工作日间隙：从当前日期次日到下一个日期的工作日数量
WorkingDayGap AS (
    SELECT
        r1.[CC Receiver],
        r1.[Date],
        r2.[Date] AS NextDate,
        (SELECT COUNT(*) 
         FROM master.dbo.spt_values v
         WHERE v.type = 'P' 
         AND DATEADD(DAY, v.number, DATEADD(DAY, 1, r1.[Date])) <= r2.[Date]
         AND DATENAME(WEEKDAY, DATEADD(DAY, v.number, DATEADD(DAY, 1, r1.[Date]))) != 'Sunday'
         AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.HolidayDate = DATEADD(DAY, v.number, DATEADD(DAY, 1, r1.[Date])))
        ) AS WorkingDaysGap
    FROM RankedEntries r1
    LEFT JOIN RankedEntries r2
        ON r1.[CC Receiver] = r2.[CC Receiver] 
        AND r1.RowNum + 1 = r2.RowNum
),
-- 判断是否开启新Round
RoundFlags AS (
    SELECT
        [CC Receiver],
        [Date],
        CASE
            WHEN WorkingDaysGap > 1 THEN 1
            ELSE 0
        END AS IsNewRound
    FROM WorkingDayGap
),
-- 累计Round编号
FinalRounds AS (
    SELECT
        [CC Receiver],
        [Date],
        SUM(IsNewRound) OVER (PARTITION BY [CC Receiver] ORDER BY [Date]) + 1 AS Round
    FROM RoundFlags
)
SELECT
    [CC Receiver],
    [Date],
    Round
FROM FinalRounds
WHERE [CC Receiver] = 'DIAB21C054'
ORDER BY [CC Receiver], [Date];

高效写法（SQL Server 2022+） #

若使用SQL Server 2022及以上版本，可替换master.dbo.spt_values为更清晰的GENERATE_SERIES：

-- 替换WorkingDayGap中的子查询
(SELECT COUNT(*) 
 FROM GENERATE_SERIES(1, DATEDIFF(DAY, r1.[Date], r2.[Date])) AS s(n)
 CROSS APPLY (SELECT DATEADD(DAY, s.n, r1.[Date]) AS GapDate) AS gd
 WHERE DATENAME(WEEKDAY, gd.GapDate) != 'Sunday'
 AND NOT EXISTS (SELECT 1 FROM holidays h WHERE h.HolidayDate = gd.GapDate)
) AS WorkingDaysGap

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