如何使用递归CTE处理时间序列：按周统计拒绝原因

原表（Declines） #

注：declineReason的可选值可能增加，不限于当前展示的3种。

期望结果 #

已编写的CTE代码 #

WITH date_loop AS (
            SELECT
                CAST('2022-08-01' AS DATE) AS WeekEnd,   
                CAST('2022-08-02' AS DATE) AS BeforeDate,
                CAST('2022-07-25' AS DATE) AS AfterDate 
        UNION ALL
            SELECT
                DATEADD(ww, 1, WeekEnd),
                DATEADD(ww, 1, BeforeDate),
                DATEADD(ww, 1, AfterDate)
            FROM date_loop
            WHERE BeforeDate < DATEADD(ww, -1, GETDATE())
    ),

CountOfDDeclineByReasonAllTime AS (
    SELECT
        count(*) AS totalDeclinesPerReasonCode
    FROM
        Declines
    GROUP BY
        declineReason), 

完整解决方案 #

要实现期望的结果，核心是确保每个周末与所有拒绝原因的组合都被展示，即使该周没有对应原因的记录（显示为0）。以下是调整后的完整代码：

WITH date_loop AS (
    -- 调整日期字段命名，明确周起始和结束
    SELECT
        CAST('2022-08-01' AS DATE) AS WeekEnd,   
        CAST('2022-07-25' AS DATE) AS WeekStart -- 当周起始日期
    UNION ALL
    SELECT
        DATEADD(ww, 1, WeekEnd),
        DATEADD(ww, 1, WeekStart)
    FROM date_loop
    -- 可以根据需要调整终止条件，这里保留原逻辑或指定固定范围
    WHERE WeekEnd < DATEADD(ww, -1, GETDATE())
),
-- 获取所有唯一的拒绝原因，自动兼容新增值
AllDeclineReasons AS (
    SELECT DISTINCT declineReason
    FROM Declines
),
-- 生成每个周末与所有拒绝原因的全量组合
WeekReasonPairs AS (
    SELECT
        dl.WeekEnd,
        adr.declineReason
    FROM date_loop dl
    CROSS JOIN AllDeclineReasons adr
),
-- 按周和原因统计实际拒绝次数
WeeklyCounts AS (
    SELECT
        dl.WeekEnd,
        d.declineReason,
        COUNT(d.accountId) AS 每周拒绝原因次数
    FROM date_loop dl
    LEFT JOIN Declines d 
        ON CAST(d.日期 AS DATE) BETWEEN dl.WeekStart AND dl.WeekEnd
    GROUP BY dl.WeekEnd, d.declineReason
)
-- 关联全量组合与统计结果，无数据则填充0
SELECT
    wrp.WeekEnd,
    wrp.declineReason,
    ISNULL(wc.每周拒绝原因次数, 0) AS 每周拒绝原因次数
FROM WeekReasonPairs wrp
LEFT JOIN WeeklyCounts wc
    ON wrp.WeekEnd = wc.WeekEnd 
    AND wrp.declineReason = wc.declineReason
-- 过滤出需要的周末日期（示例中的10/10/22和17/10/22）
WHERE wrp.WeekEnd IN (CAST('2022-10-10' AS DATE), CAST('2022-10-17' AS DATE))
ORDER BY wrp.WeekEnd, wrp.declineReason;

关键步骤说明： #

1. AllDeclineReasons：通过DISTINCT获取所有拒绝原因，确保新增原因无需修改代码即可被包含。
2. WeekReasonPairs：使用CROSS JOIN生成每个周末与所有拒绝原因的笛卡尔积，保证每个组合都存在。
3. WeeklyCounts：左连接Declines表，按周和原因统计实际拒绝次数，无数据时会返回NULL。
4. 最终查询：再次左连接全量组合与统计结果，用ISNULL将NULL转为0，实现零次记录的展示。

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