不同行数值相减：基于TEMP表生成ACT_M表的SQL实现问题

问题分析与解决方案 #

你的报错核心是LAG函数必须配合OVER子句使用，原代码中LAG(Amount) AS PrvDay缺少OVER子句导致语法错误。同时现有代码未按Dept、Type分组，也未处理月度归零的业务逻辑，无法满足需求。

以下是修正后的完整实现：

-- 计算每日实际发生额并插入ACT_M表
INSERT INTO ACT_M (Date, Dept, Type, Daily_Amount)
SELECT
    Date,
    Dept,
    Type,
    -- 区分月初与非月初的计算逻辑
    CASE
        -- 当月初时，直接取Amount（因月初归零，此值即为当日实际发生额）
        WHEN DATE_TRUNC('month', Date) = Date THEN Amount
        -- 非月初时，用当日累计值减去同组前一日累计值
        ELSE Amount - LAG(Amount) OVER (
            PARTITION BY Dept, Type, DATE_TRUNC('month', Date)
            ORDER BY Date
        )
    END AS Daily_Amount
FROM {temp}
ORDER BY Date, Dept, Type;

关键细节说明 #

• PARTITION BY分组：按Dept、Type和所属月份分组，确保LAG只取同部门、同类型、同月份内的前一日数据，避免跨月取到上月末的累计值（适配每月初归零的规则）
• 日期函数适配：DATE_TRUNC('month', Date)用于提取日期所属月份，不同数据库语法略有差异：
  • MySQL：DATE_FORMAT(Date, '%Y-%m-01')
  • SQL Server：DATEADD(month, DATEDIFF(month, 0, Date), 0)
  • Oracle：TRUNC(Date, 'MM')
• 负值兼容：直接通过减法计算，负值场景会自然保留，无需额外处理

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