sql - 计算表中行之间的百分比差异

Question

我有一个表格，我想在其中计算一组帐号、 yield 和年份的行之间的百分比差异。我正在使用 SQL 2008

之前的例子

AccountNbr    Benefit   Year Month Value  Diff
------------  -------   ---- ----- -----  ----
123           A         2014 11    10   
123           A         2014 12    20
123           A         2015 1     10   
123           A         2015 2     20
123           A         2015 3     40
123           B         2015 1     50
123           B         2015 2     100
123           B         2015 3     150

示例 AFTER(我希望看到的)

AccountNbr    Benefit   Year Month Value  Diff
------------  -------   ---- ----- -----  ----
123           A         2014 11    10     0
123           A         2014 12    20     100
123           A         2015 1     10     -50
123           A         2015 2     20     100
123           A         2015 3     40     100   
123           B         2015 1     50     0
123           B         2015 2     90     80
123           B         2015 3     40     -55

Answer 1

您可以使用 ROW_NUMBER() 来识别每个 AccountNbr, Benefit 分区中的前一行。然后您可以 LEFT JOIN 与前一行并计算百分比差异(如果这毕竟是您真正想要的!):

;WITH CTE AS (
   SELECT AccountNbr, Benefit, Year, Month, Value,
          ROW_NUMBER() OVER (PARTITION BY AccountNbr, Benefit 
                             ORDER BY Year, Month) AS rn
   FROM mytable
 )
 SELECT c1.AccountNbr, c1.Benefit, c1.Year, c1.Month, c1.Value,             
        CAST(COALESCE (((c1.Value - c2.Value) * 1.0 / c2.Value) * 100, 0) AS INT) AS Diff
 FROM CTE AS c1
 LEFT JOIN CTE AS c2 
 ON c1.AccountNbr = c2.AccountNbr AND c1.Benefit = c2.Benefit AND c1.rn = c2.rn + 1

SQL Fiddle Demo