如何用LAG函数计算数据库每日增长量？SQL异常排查

问题描述 #

尝试用LAG函数计算数据库每日增长量，逻辑是当日UsedSpaceMB减去前一日的UsedSpaceMB，存入Difference_Previous_Day列。例如2021-11-21的UsedSpaceMB为2161706MB，前一日为2158844MB，预期差值为2862MB，但实际计算结果不符。

原SQL语句：

USE DATABASEGrowth
--Working on grouping by month and calculating the difference
SELECT DBName AS DatabaseName,
       Date AS CollectedDate,
       MONTH(Date) AS Month,
       DAY(Date) AS Day,
       [UsedSpace(Mb)] AS UsedSpaceMB,
       --CAST(SUM(CAST([Usedspace(Mb)] AS FLOAT)) / 1024 / 1024 AS DECIMAL(10, 3)) AS [Database Used Space GB],
       --LAG([Usedspace(Mb)]) AS previous_day,
       [Usedspace(Mb)] - LAG([Usedspace(Mb)],1,0)
            --OVER (ORDER BY Date) AS difference_previous_day 
        OVER (ORDER BY Date) AS difference_previous_day 

        
FROM DATABASEGrowth
GROUP BY DBName,
         MONTH(Date),
         Date,
         [Usedspace(Mb)],
         DAY(Date)
ORDER BY DatabaseName,
         Date DESC,
         DAY(Date),
         MONTH(Date)

问题分析与修正 #

• 未按数据库分区：LAG函数的OVER子句仅按Date排序，若存在多个数据库，会跨库取前一条记录的值，导致差值计算错误。必须添加PARTITION BY DBName，确保每个数据库单独计算每日差值。
• 排序方向错误：原语句ORDER BY Date DESC让数据按日期倒序排列，LAG函数会取后一天的记录值而非前一天，逻辑完全反转。需将OVER子句内的排序改为ORDER BY Date ASC，最终结果的倒序展示只需在末尾ORDER BY设置。
• 多余的GROUP BY：当前查询无聚合函数，GROUP BY冗余，若同一天同库有重复记录还会导致数据异常，直接去掉即可。

修正后的SQL #

USE DATABASEGrowth
SELECT 
    DBName AS DatabaseName,
    Date AS CollectedDate,
    MONTH(Date) AS Month,
    DAY(Date) AS Day,
    [UsedSpace(Mb)] AS UsedSpaceMB,
    -- 按数据库分区，按日期正序取前一天的值
    [UsedSpace(Mb)] - LAG([UsedSpace(Mb)], 1, 0) OVER (PARTITION BY DBName ORDER BY Date ASC) AS Difference_Previous_Day
FROM DATABASEGrowth
ORDER BY 
    DatabaseName,
    Date DESC; -- 最终结果按数据库、日期倒序展示

验证逻辑 #

修正后，每个数据库的记录单独按日期正序排列，LAG函数会准确取到当前数据库前一日的UsedSpaceMB，差值计算逻辑正确。比如2021-11-21的记录会对应取到2021-11-20的数值，计算出正确的2862MB差值。

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