SQL Server 查询计算差异。每月指数

Question

我有一个这样的表:

Id    Machine        Year     Month       SumProductionPerMonth
==    =======        ====     ======      =============
1     Prod1          2016      1           1500
2     Prod1          2016      2           1800
3     Prod1          2016      3           1900
4     Prod2          2016      1           456
5     Prod2          2016      2           789
6     Prod2          2016      3           922

我得到这样的数据

select table.Id, table.Machine, table.Year, 
table.Month, SUM(index) as SumProductionPerMonth...

我想计算每个月 x 每台机器的 SumProductionPerMonth 的差异:

( SumProductionPerMonth Month N - SumProductionPerMonth Month N-1) 

，并得到如下结果:

Id    Machine        Year     Month       SumProductionPerMonth    Delta
==    =======        ====     ======      =============            ====
1     Prod1          2016      1           1500                     0
2     Prod1          2016      2           1800                     300
3     Prod1          2016      3           1900                     100
4     Prod2          2016      1           456                      0
5     Prod2          2016      2           789                      333
6     Prod2          2016      3           922                      133

如何进行 SQL Server 查询以获得此结果？

Answer 1

只需使用“窗口聚合函数”，LAG:

COALESCE(SumProductionPerMonth 
          - LAG(SumProductionPerMonth) -- previous month's value
            OVER (PARTITION BY machine
            ORDER BY year, month), 0)

编辑:

在 SQL Server 2012 之前，您可以使用 ROW_NUMBER 加入:

WITH cte AS 
 (
   SELECT *
     ,ROW_NUMBER() 
      OVER (PARTITION BY machine ORDER BY YEAR ,MONTH) AS rn
   FROM SourceT
 )
SELECT t1.*
  ,COALESCE(t1.SumProductionPerMonth - prev.SumProductionPerMonth, 0) 
FROM cte AS t1 LEFT JOIN cte AS prev
  ON prev.Machine = t1.Machine
 AND prev.rn = t1.rn -1