如何基于不同日期范围SUM字段并返回上年同期聚合结果

核心原则：不要用自然日偏移计算同期，因为会计日历存在年度偏移，必须基于日历表自带的会计年度/期/周维度做匹配，否则会出现周度错位。

前置依赖确认 #

你的calendar表需要满足以下结构（如果所有门店共用统一会计日历，可去掉store_id字段）：

• cal_date：日期值，和交易表的交易日期一一对应
• store_id：门店ID（多门店独立会计日历场景必备）
• acc_year：日期对应的会计年度
• acc_term：日期对应的会计期
• acc_week：日期对应的会计周

实现步骤 #

1. 先聚合得到各门店各会计周期的交易总金额 #

用CTE先做基础聚合，避免后续关联时重复计算：

WITH period_agg AS (
    SELECT
        c.store_id,
        c.acc_year,
        c.acc_term,
        c.acc_week,
        SUM(t.trade_amt) AS current_period_sum
    FROM transactions t
    INNER JOIN calendar c 
        ON t.trade_date = c.cal_date
        -- 统一会计日历可删掉下面这行门店关联条件
        AND t.store_id = c.store_id
    GROUP BY
        c.store_id,
        c.acc_year,
        c.acc_term,
        c.acc_week
)

2. 匹配上一年度同期值 #

两种实现方式按需选择：

方式1：LAG窗口函数（推荐，性能更好代码更简洁）

直接按门店、会计期、会计周分组，按年度排序取上一行的聚合值即可：

SELECT
    store_id,
    acc_year,
    acc_term,
    acc_week,
    current_period_sum,
    LAG(current_period_sum, 1) OVER (
        PARTITION BY store_id, acc_term, acc_week 
        ORDER BY acc_year
    ) AS last_year_same_period_sum
FROM period_agg
ORDER BY store_id, acc_year DESC, acc_term, acc_week;

方式2：LEFT JOIN自关联（逻辑透明，方便扩展多期对比）

通过关联条件匹配同门店、同会计期、同会计周、年度差1的记录：

SELECT
    curr.store_id,
    curr.acc_year,
    curr.acc_term,
    curr.acc_week,
    curr.current_period_sum,
    prev.current_period_sum AS last_year_same_period_sum
FROM period_agg curr
LEFT JOIN period_agg prev
    ON curr.store_id = prev.store_id
    AND curr.acc_term = prev.acc_term
    AND curr.acc_week = prev.acc_week
    AND curr.acc_year = prev.acc_year + 1
ORDER BY curr.store_id, curr.acc_year DESC, curr.acc_term, curr.acc_week;

特殊场景说明 #

• 如果遇到某会计年度存在53周的特殊情况，上述逻辑会自动因为上年不存在同周记录返回NULL，不需要额外写规则过滤，符合常规业务统计逻辑
• 不要用DATEADD(year,-1, 交易日期)这类自然日偏移逻辑找同期，会计日历跨年度偏移时会直接导致周/期匹配错位，统计结果失真
• 如果存在多套会计日历（比如不同区域用不同财年规则），只需要在PARTITION BY或者JOIN条件中加上日历标识字段即可

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