Oracle SQL从累计数据计算单日数据的最优实现方案咨询

嘿，作为SQL新手遇到这种累计转单日的需求确实有点懵，不过别担心，Oracle的窗口函数刚好能完美解决这个问题，而且效率还很高！

我先给你理清楚核心逻辑：咱们需要把同一周内的数据分组，周首日的数值就是当日实际值，之后每天的累计值减去前一天的累计值（或者首日的数值）就是当日发生额。这里用LAG窗口函数是最优选择，因为它能直接获取分组内上一行的数据，不用做复杂的自连接。

下面是具体的SQL代码，我会一步步给你解释：

SELECT
    Center,
    Entity,
    record_date AS "Date", -- 用双引号保留关键字列名，或者你可以把表的Date字段改名避免冲突
    Year,
    Period,
    -- 计算单日Bonus
    CASE
        -- 周首日没有上一行数据，LAG返回NULL，直接取原Bonus值
        WHEN LAG(Bonus) OVER (
            PARTITION BY Center, Entity, Year, Period, TRUNC(record_date, 'IW') 
            ORDER BY record_date
        ) IS NULL THEN Bonus
        -- 非首日用当前累计值减去上一行的累计值
        ELSE Bonus - LAG(Bonus) OVER (
            PARTITION BY Center, Entity, Year, Period, TRUNC(record_date, 'IW') 
            ORDER BY record_date
        )
    END AS Daily_Bonus,
    -- 同理计算单日Incentive
    CASE
        WHEN LAG(Incentive) OVER (
            PARTITION BY Center, Entity, Year, Period, TRUNC(record_date, 'IW') 
            ORDER BY record_date
        ) IS NULL THEN Incentive
        ELSE Incentive - LAG(Incentive) OVER (
            PARTITION BY Center, Entity, Year, Period, TRUNC(record_date, 'IW') 
            ORDER BY record_date
        )
    END AS Daily_Incentive
FROM
    your_table -- 替换成你的实际表名
ORDER BY
    Center, Entity, Year, Period, record_date;

关键部分解释： #

1. PARTITION BY子句：
   我们按Center、Entity、Year、Period这些维度分组，再加上TRUNC(record_date, 'IW')把日期截断到ISO周的周一（也就是周首日），这样同一维度、同一周的数据会被分到一组里。如果你的周首日是周日，把'IW'改成'W'就行，Oracle会自动识别周日为周起始。

2. LAG函数：
   这个函数的作用是在分组内按record_date排序后，获取当前行的上一行对应的Bonus或Incentive值。周首日是组内的第一行，没有上一行，所以返回NULL。

3. CASE判断：

   • 当LAG返回NULL时，说明是周首日，直接用原字段值作为当日数据；
   • 否则用当前累计值减去上一行的累计值，得到当日实际发生的金额，完全符合你要的Day2=Day2累计值-Day1值的逻辑。

额外小贴士： #

• 如果你的表中Date字段是字符串类型，记得先转成日期类型（比如TO_DATE(record_date, 'YYYY-MM-DD')）再使用TRUNC函数。
• 窗口函数的效率比自连接（比如自己连自己找前一天的数据）高很多，数据量大的时候优势特别明显，因为它只需要扫描一次表就完成计算。

拿你给的示例数据测试的话，结果会完全符合预期：2017-12-31的Daily_Bonus是1200，2018-01-01的Daily_Bonus是1500-1200=300，Incentive同理。

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