如何在SQL中从多表计算年初至今（YTD）聚合值？

核心需求：通过关联JOURNAL、DATE、COMPANY、DEPARTMENT、ACCOUNT_TYPE、ACCOUNT六张表，将AMOUNT字段聚合为年初至今（YTD）数值以替代原期间值，修正当前错误的计算逻辑。

常见错误原因 #

• 未基于DATE表的年度维度约束累计范围，导致跨年度错误累计
• 聚合时未按核心维度（公司、部门、科目、年度）分组，造成跨维度的错误求和
• 误用普通聚合函数而非窗口函数/子查询，导致无法实现逐期累计

修正后的SQL代码（窗口函数方案，推荐） #

假设各表关联外键如下：

• JOURNAL.DATE_ID = DATE.DATE_ID
• JOURNAL.COMPANY_ID = COMPANY.COMPANY_ID
• JOURNAL.DEPT_ID = DEPARTMENT.DEPT_ID
• JOURNAL.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID
• ACCOUNT.ACCOUNT_TYPE_ID = ACCOUNT_TYPE.ACCOUNT_TYPE_ID

SELECT
    C.COMPANY_NAME,
    D.DEPT_NAME,
    AT.ACCOUNT_TYPE_NAME,
    ACC.ACCOUNT_NAME,
    DT.CALENDAR_YEAR,
    DT.CALENDAR_MONTH,
    -- 计算维度内的年初至今累计金额
    SUM(J.AMOUNT) OVER (
        PARTITION BY C.COMPANY_ID, D.DEPT_ID, ACC.ACCOUNT_ID, DT.CALENDAR_YEAR
        ORDER BY DT.CALENDAR_MONTH
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS YTD_AMOUNT,
    -- 可选保留原期间金额用于对比
    J.AMOUNT AS PERIOD_AMOUNT
FROM
    JOURNAL J
JOIN DATE DT ON J.DATE_ID = DT.DATE_ID
JOIN COMPANY C ON J.COMPANY_ID = C.COMPANY_ID
JOIN DEPARTMENT D ON J.DEPT_ID = D.DEPT_ID
JOIN ACCOUNT ACC ON J.ACCOUNT_ID = ACC.ACCOUNT_ID
JOIN ACCOUNT_TYPE AT ON ACC.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
ORDER BY
    C.COMPANY_NAME,
    D.DEPT_NAME,
    AT.ACCOUNT_TYPE_NAME,
    ACC.ACCOUNT_NAME,
    DT.CALENDAR_YEAR,
    DT.CALENDAR_MONTH;

关键修正说明 #

1. 分区维度精准性：PARTITION BY锁定了公司、部门、科目、年度四个核心维度，确保每个维度组内独立计算YTD，避免跨维度的错误累计。
2. 累计范围明确：ORDER BY DT.CALENDAR_MONTH保证按月份顺序累加，ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW明确限定累计范围为当年1月至当前记录月份。
3. 表关联完整性：使用内连接确保仅保留所有表中存在匹配关系的数据，避免笛卡尔积导致的重复计算。

兼容方案（子查询实现，适用于不支持窗口函数的数据库） #

SELECT
    C.COMPANY_NAME,
    D.DEPT_NAME,
    AT.ACCOUNT_TYPE_NAME,
    ACC.ACCOUNT_NAME,
    DT.CALENDAR_YEAR,
    DT.CALENDAR_MONTH,
    (
        SELECT SUM(J2.AMOUNT)
        FROM JOURNAL J2
        JOIN DATE DT2 ON J2.DATE_ID = DT2.DATE_ID
        WHERE
            J2.COMPANY_ID = J.COMPANY_ID
            AND J2.DEPT_ID = J.DEPT_ID
            AND J2.ACCOUNT_ID = J.ACCOUNT_ID
            AND DT2.CALENDAR_YEAR = DT.CALENDAR_YEAR
            AND DT2.CALENDAR_MONTH <= DT.CALENDAR_MONTH
    ) AS YTD_AMOUNT,
    J.AMOUNT AS PERIOD_AMOUNT
FROM
    JOURNAL J
JOIN DATE DT ON J.DATE_ID = DT.DATE_ID
JOIN COMPANY C ON J.COMPANY_ID = C.COMPANY_ID
JOIN DEPARTMENT D ON J.DEPT_ID = D.DEPT_ID
JOIN ACCOUNT ACC ON J.ACCOUNT_ID = ACC.ACCOUNT_ID
JOIN ACCOUNT_TYPE AT ON ACC.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
ORDER BY
    C.COMPANY_NAME,
    D.DEPT_NAME,
    AT.ACCOUNT_TYPE_NAME,
    ACC.ACCOUNT_NAME,
    DT.CALENDAR_YEAR,
    DT.CALENDAR_MONTH;

验证要点 #

• 确认每个维度组内，当年第一个月的YTD值与当月期间值完全相等
• 检查后续月份的YTD值等于当前月之前所有月份期间值的累加和
• 验证跨年度的数据不会被错误计入其他年度的累计中

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