我想使用SQL Server计算一列中的百分比变化

Question

如果其他地方已经解决了这个问题，请原谅，我检查过了，但不能为我工作。我对SQL Server不是很熟悉。

我想使用SQL Server计算我的表中1月至12月之间的指数百分比，表示为01、02、03。我已经使用Excel在Pct_Change列中添加了预期内容。见以下示例：

M   Year    Indices  Pct_Change
01  2017    190.51  
02  2017    188.99   -0.8
03  2017    190.06    0.6
04  2017    194.24    2.2
05  2017    196.83    1.3
06  2017    196.30   -0.3
07  2017    191.09   -2.7
08  2017    190.42   -0.3
09  2017    194.02    1.9
10  2017    201.20    3.7
11  2017    200.98   -0.1
12  2017    194.43   -3.3
01  2018    197.23    1.4
02  2018    198.20    0.5
03  2018    194.60   -1.8

任何帮助都将不胜感激。

Answer 1

由于缺乏预期结果，这只是一个猜测，但也许。。。

WITH VTE AS(
SELECT *
FROM (VALUES(01,2017,190.51),  
(02,2017,188.99),  
(03,2017,190.06),  
(04,2017,194.24),  
(05,2017,196.83),  
(06,2017,196.30),  
(07,2017,191.09),  
(08,2017,190.42),  
(09,2017,194.02),  
(10,2017,201.20),  
(11,2017,200.98),  
(12,2017,194.43),  
(01,2018,197.23),  
(02,2018,198.20),  
(03,2018,194.60)) V(M, [Year], Indices))
SELECT *,
(Indices / LAG(Indices) OVER (ORDER BY [Year] ASC, M ASC)) -1 AS Pct_change
FROM VTE;

编辑：不幸的是，OP正在使用2008R2。就我个人而言，我强烈建议考虑升级您的版本。SQL Server 2008(R2(现在只剩下不到一年的扩展支持时间了。之后，您将不会收到任何更新，包括安全更新。如果你需要符合GDPR，那么你必须改变。

无论如何，您可以在SQLServer2008R2中通过对同一个表使用LEFT JOIN来实现这一点：

WITH VTE AS(
SELECT *
FROM (VALUES(01,2017,190.51),  
(02,2017,188.99),  
(03,2017,190.06),  
(04,2017,194.24),  
(05,2017,196.83),  
(06,2017,196.30),  
(07,2017,191.09),  
(08,2017,190.42),  
(09,2017,194.02),  
(10,2017,201.20),  
(11,2017,200.98),  
(12,2017,194.43),  
(01,2018,197.23),  
(02,2018,198.20),  
(03,2018,194.60)) V(M, [Year], Indices)),
--The solution. note that you would need your WITH on the next line
--Mine isn't, as I used a CTE to create the sample data.
RNs AS(
SELECT *,
ROW_NUMBER() OVER (ORDER BY [Year] ASC, M ASC) AS RN
FROM VTE)
SELECT R1.M,
R1.[Year],
R1.Indices,,
(R1.Indices / R2.Indices) -1 AS Pct_change
FROM RNs R1
LEFT JOIN RNs R2 ON R1.RN = R2.RN + 1;