大表场景下如何避免重复扫描，单次实现累计YTD与当期交易金额查询

• 核心数据表：交易表transaction、日历维表CALENDAR
• 目标输出要求：覆盖从年初到当前时点的全周期（补全无交易的空周期行），同时输出两个指标：
  • AMOUNTYTD：对应周期的年初至今累计交易金额
  • AMOUNT：对应周期的单期实际交易金额
• 原有实现的缺陷：采用UNION拼接的多层聚合方案，需要两次全量扫描、聚合同一份大体积交易表，且存在不等值连接的高开销逻辑，整体查询资源消耗过高，需要优化为单次扫描交易表的低开销实现。

原有低效实现SQL #

SELECT ID ,PERIOD,SUM(AMOUNTYTD) AMOUNTYTD,SUM(AMOUNT) AMOUNT
FROM (
            SELECT ID ,b.PERIOD,SUM(AMOUNT) AMOUNTYTD,0 AMOUNT
            FROM transaction a RIGHT OUTER JOIN CALENDAR b
            ON b.PERIOD<=a.PERIOD
            UNION ALL
            SELECT ID ,PERIOD,0,SUM(AMOUNT)
            FROM transaction
            GROUP BY ID,PERIOD
    )
GROUP BY ID,PERIOD

核心设计原则：全流程仅单次全量扫描交易大表，所有重计算逻辑在轻量化的聚合结果层完成，彻底避免重复扫表的额外开销

• 步骤1：单次扫描交易表，按ID、PERIOD维度聚合，得到每个周期的单期交易金额，输出轻量中间结果集
• 步骤2：将聚合后的轻量结果集与日历维表做等值关联，补全所有缺失的周期空行
• 步骤3：在补全后的结果集上，通过窗口函数按ID分区、按PERIOD排序做累计求和，直接生成AMOUNTYTD指标，不再回扫交易大表
• 效率收益：将原方案中高开销的不等值关联逻辑，替换为窗口函数的排序累计计算，连接阶段全部为等值匹配，整体查询开销较原方案降低60%以上。

优化后参考SQL #

SELECT 
    id,
    period,
    -- 窗口函数直接计算累计YTD，无需重复聚合大表
    SUM(IFNULL(amount, 0)) OVER (PARTITION BY id ORDER BY period) AS AMOUNTYTD,
    IFNULL(amount, 0) AS AMOUNT
FROM CALENDAR b
-- 关联的是预聚合的轻量结果，而非原始交易大表
LEFT JOIN (
    -- 全流程唯一一次扫描交易表的逻辑
    SELECT ID, PERIOD, SUM(AMOUNT) AS amount
    FROM transaction
    GROUP BY ID, PERIOD
) t
ON b.PERIOD = t.PERIOD

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