计算每组从最后一行到第一行的百分比变化

Question

我有一个如下所示的数据集：

date    name    value
0   2018-10 A   10
1   2018-09 A   12
2   2018-10 B   33
3   2018-09 B   45
4   2018-10 C   55
5   2018-09 C   66
6   2018-10 D   0
7   2018-09 D   0

我需要计算在group by name变量之后从2018-09到2018-10的百分比变化。

因此，我需要一个如下所示的数据集：

    date    name    value   pctChangFromLastMonth
0   2018-10 A       10      xx
1   2018-10 B       33      xx
2   2018-10 C       55      xx
3   2018-10 D       0       xx

有人知道我如何做到这一点吗？

Answer 1

你可以这样做：

select a.*, 100.0(a.value - b.value)/b.value as pctChangFromLastMonth
from tbl a 
inner join tbl b on a.name = b.name and a.date > b.date

Answer 2

一种可能的方法是使用窗口函数：

表：

CREATE TABLE Data (
   [date] varchar(7),   
   [name] varchar(1),    
   [value] int
)
INSERT INTO Data
   ([date], [name], [value])
VALUES
   ('2018-10', 'A', 10),
   ('2018-09', 'A', 12),
   ('2018-10', 'B', 33),
   ('2018-09', 'B', 45),
   ('2018-10', 'C', 55),
   ('2018-09', 'C', 66),
   ('2018-10', 'D', 0),
   ('2018-09', 'D', 0)

声明：

SELECT
   [date],
   [name], 
   [value],
   CASE
      WHEN [prevvalue] = 0 THEN 0.0
      ELSE 100.0 * ([value] - [prevvalue]) / [prevvalue]
   END AS pctChangFromLastMonth,
   CASE
      WHEN [firstvalue] = 0 THEN 0.0
      ELSE 100.0 * ([value] - [firstvalue]) / [firstvalue]
   END AS pctChangFromFirstMonth
FROM (   
   SELECT
      [date],
      [name],
      [value],
      LAG([value]) OVER (PARTITION BY [name] ORDER BY [date]) AS [prevvalue],
      FIRST_VALUE([value]) OVER (PARTITION BY [name] ORDER BY [date]) AS [firstvalue],
      ROW_NUMBER() OVER (PARTITION BY [name] ORDER BY [date] DESC) AS [rn]
   FROM Data
) t
WHERE t.[rn] = 1

结果：

----------------------------------------------------------------------
date    name    value   pctChangFromLastMonth   pctChangFromFirstMonth
----------------------------------------------------------------------
2018-10 A       10           -16.666666666666         -16.666666666666
2018-10 B       33           -26.666666666666         -26.666666666666
2018-10 C       55           -16.666666666666         -16.666666666666
2018-10 D       0              0.000000000000           0.000000000000