如何以周一为起始日按周计算此前所有周的累计金额

核心逻辑拆分为三个步骤：

• 将每条交易记录映射到所属自然周的周一（周起始日），按周聚合得到单周总金额
• 生成从首个有交易的周一开始，到最后一个交易周的下一个周一为止的连续周一序列，保证无交易的空周也能正常输出
• 通过窗口函数做错位累计，每个周一仅统计严格早于自身的周的金额总和，天然排除当前周，首行无历史数据时取0

PostgreSQL 实现 #

PostgreSQL 原生支持周截断与序列生成，写法最简洁：

WITH weekly_sum AS (
    SELECT
        DATE_TRUNC('week', "Date")::DATE AS week_start,
        SUM(amount) AS week_amount
    FROM your_table
    GROUP BY week_start
),
all_mondays AS (
    SELECT generate_series(
        (SELECT MIN(week_start) FROM weekly_sum),
        (SELECT MAX(week_start) + INTERVAL '1 week' FROM weekly_sum),
        INTERVAL '1 week'
    )::DATE AS week_start
)
SELECT
    am.week_start AS "Date",
    COALESCE(
        SUM(ws.week_amount) OVER (
            ORDER BY am.week_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS amount
FROM all_mondays am
LEFT JOIN weekly_sum ws ON am.week_start = ws.week_start
ORDER BY am.week_start;

注意：将your_table替换为实际业务表名。

逻辑说明 #

• PostgreSQL 默认以周一为一周起始，DATE_TRUNC('week', 日期)可直接得到对应周的周一日期
• generate_series按7天间隔生成连续日期，自动补全无交易的空周
• 窗口帧ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING限定只统计当前行之前的所有金额，符合「不含当前周」的要求，空值通过COALESCE转为0

MySQL 8.0+ 实现 #

MySQL 没有内置序列生成函数，通过递归CTE生成连续周一：

WITH RECURSIVE weekly_sum AS (
    SELECT
        DATE(`Date`) - INTERVAL WEEKDAY(`Date`) DAY AS week_start,
        SUM(amount) AS week_amount
    FROM your_table
    GROUP BY week_start
),
all_mondays AS (
    SELECT MIN(week_start) AS week_start FROM weekly_sum
    UNION ALL
    SELECT week_start + INTERVAL 1 WEEK
    FROM all_mondays
    WHERE week_start < (SELECT MAX(week_start) + INTERVAL 1 WEEK FROM weekly_sum)
)
SELECT
    am.week_start AS `Date`,
    COALESCE(
        SUM(ws.week_amount) OVER (
            ORDER BY am.week_start
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ),
        0
    ) AS amount
FROM all_mondays am
LEFT JOIN weekly_sum ws ON am.week_start = ws.week_start
ORDER BY am.week_start;

注意：将your_table替换为实际业务表名。

逻辑说明 #

• WEEKDAY()返回日期对应的周序号（周一为0），日期减去对应天数即可得到当周周一
• 递归CTE逐周生成日期，自动补全空交易周
• 累计规则与PostgreSQL版本完全一致

代入题目示例数据运行后，输出结果与预期完全匹配：

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