如何使用SQL计算MTD、LMTD及上月总额LM的累计求和

完全可以通过SQL实现你需要的三个指标计算，以下是具体实现思路和可运行代码，适配你提供的整数格式Date字段： #

核心逻辑说明 #

• 先将整数格式的Date字段转换为日期类型，提取日序、当前年月、上月年月三个关键维度
• 预先计算上月各分类的日累计值，和主表按「分类+日序」关联得到LMTD
• 预先计算上月各分类的整月汇总值，按「分类」关联得到LM
• 原有MTD的逻辑可以直接复用，只需要对齐日期转换规则即可

实现代码 #

WITH 
-- 基础数据预处理：转换日期格式，提取所需维度
base_data AS (
    SELECT 
        Category,
        Date AS origin_date,
        Amount,
        TO_DATE(CAST(Date AS VARCHAR), 'YYYYMMDD') AS dt,
        EXTRACT(DAY FROM TO_DATE(CAST(Date AS VARCHAR), 'YYYYMMDD')) AS day_num,
        TO_CHAR(TO_DATE(CAST(Date AS VARCHAR), 'YYYYMMDD'), 'YYYY-MM') AS current_month,
        TO_CHAR(ADD_MONTHS(TO_DATE(CAST(Date AS VARCHAR), 'YYYYMMDD'), -1), 'YYYY-MM') AS last_month
    FROM your_table_name
),
-- 计算上月各分类的日累计值（用于关联得到LMTD）
last_month_daily_accum AS (
    SELECT 
        Category,
        EXTRACT(DAY FROM dt) AS day_num,
        SUM(Amount) OVER(PARTITION BY Category, last_month ORDER BY dt DESC) AS lm_accum
    FROM base_data
    WHERE TO_CHAR(dt, 'YYYY-MM') = last_month
),
-- 计算上月各分类的整月总额（用于关联得到LM）
last_month_total AS (
    SELECT 
        Category,
        SUM(Amount) AS lm_total
    FROM base_data
    WHERE TO_CHAR(dt, 'YYYY-MM') = last_month
    GROUP BY Category
)
-- 主查询关联得到所有指标
SELECT 
    t1.Category,
    t1.origin_date AS Date,
    t1.Amount,
    -- 保留原有MTD计算逻辑，和你之前的实现逻辑对齐
    SUM(t1.Amount) OVER(PARTITION BY t1.Category, t1.current_month ORDER BY t1.dt DESC) AS MTD,
    -- 无匹配值时返回0，可根据业务需求自行调整
    COALESCE(t2.lm_accum, 0) AS LMTD,
    t3.lm_total AS LM
FROM base_data t1
LEFT JOIN last_month_daily_accum t2 
    ON t1.Category = t2.Category AND t1.day_num = t2.day_num
LEFT JOIN last_month_total t3
    ON t1.Category = t3.Category
ORDER BY t1.Category, t1.origin_date DESC

边界情况说明 #

如果遇到当月日序超过上月最大天数的情况（比如3月31日对应2月没有31日），可以在关联逻辑处新增判断，取上月最后一天的累计值即可，符合业务统计的通用规则。

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