按周统计开放记录的ROLLING TOTAL实现咨询（起始周为5/1）

现有开放记录数据集如下：

需要以5/1/2023为第1周起点，按周展示开放记录的滚动累计数（ROLLING TOTAL），预期输出如下：

以下以SQL为例，分步骤实现需求：

1. 自定义周区间计算 #

由于默认周函数（如DATE_TRUNC）的起始日不符合要求，需手动计算每条记录所属周数：

• 计算创建日期与5/1/2023的天数差：DATEDIFF(day, '2023-05-01', BDC_CREATE_DATE)
• 通过整数除法得到周数：FLOOR(天数差/7) + 1，确保5/1-5/7为第1周，5/8-5/14为第2周，以此类推。

2. 按周统计新增记录数 #

先分组统计每周新增的开放记录数量，同时计算每周的起止日期：

WITH weekly_new AS (
    SELECT
        FLOOR(DATEDIFF(day, '2023-05-01', BDC_CREATE_DATE)/7) + 1 AS week_num,
        COUNT(BDC_ID) AS new_count,
        DATEADD(day, (week_num - 1)*7, '2023-05-01') AS week_start,
        DATEADD(day, (week_num - 1)*7 + 6, '2023-05-01') AS week_end
    FROM your_table
    GROUP BY week_num, week_start, week_end
)

3. 计算滚动累计值 #

使用窗口函数SUM() OVER(ORDER BY week_num)，计算从第1周到当前周的累计记录数，同时格式化周显示文本：

SELECT
    CONCAT(week_num, ' (', FORMAT(week_start, 'M/d'), '-', FORMAT(week_end, 'M/d')) AS WEEK,
    SUM(new_count) OVER(ORDER BY week_num) AS ROLLING_TOTAL
FROM weekly_new
ORDER BY week_num;

4. 补全空周（可选） #

如果存在无新增记录的周，需生成连续周序列避免结果缺失，可通过递归CTE实现：

WITH max_week AS (
    SELECT MAX(FLOOR(DATEDIFF(day, '2023-05-01', BDC_CREATE_DATE)/7) + 1) AS max_num
    FROM your_table
),
week_series AS (
    SELECT 1 AS week_num
    UNION ALL
    SELECT week_num + 1
    FROM week_series, max_week
    WHERE week_num < max_num
),
weekly_new AS (
    SELECT
        ws.week_num,
        COALESCE(COUNT(t.BDC_ID), 0) AS new_count,
        DATEADD(day, (ws.week_num - 1)*7, '2023-05-01') AS week_start,
        DATEADD(day, (ws.week_num - 1)*7 + 6, '2023-05-01') AS week_end
    FROM week_series ws
    LEFT JOIN your_table t ON FLOOR(DATEDIFF(day, '2023-05-01', t.BDC_CREATE_DATE)/7) + 1 = ws.week_num
    GROUP BY ws.week_num, week_start, week_end
)
SELECT
    CONCAT(week_num, ' (', FORMAT(week_start, 'M/d'), '-', FORMAT(week_end, 'M/d')) AS WEEK,
    SUM(new_count) OVER(ORDER BY week_num) AS ROLLING_TOTAL
FROM weekly_new
ORDER BY week_num;

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