使用SQL计算基于类别的价格变化百分比

Question

我正在用SQL编写一个查询，但还没弄清楚。。。

我的桌子是这样的：

Category Price Date
Cat1       20   2019-04
Cat2       12   2019-04
Cat3        5   2019-04
Cat1       23   2020-04
Cat2       17   2020-04
Cat3        8   2020-04

我想要一张显示以下内容的表格：

Cat  Pct_change Period
Cat 1   0.15     2019-2020
Cat 2   0.41       "

等等。

我可以按类别获得这个类别，但我有大约100个类别，无法手动完成。如果能同时看到这两种价格，那也太好了。我不允许(不能(生成新的表，只保存数据来连接单独的表。。。

谢谢！！

Answer 1

使用LEAD()窗口函数获取每个类别下一个日期的价格和日期：

SELECT Category,
ROUND(1.0 * next_price / Price - 1, 2) Pct_change,
SUBSTR(Date, 1, 4) || '-' || SUBSTR(next_date, 1, 4) Period
FROM (
SELECT *,
LEAD(Price) OVER (PARTITION BY Category ORDER BY Date) next_price,
LEAD(Date) OVER (PARTITION BY Category ORDER BY Date) next_date
FROM tablename
)
WHERE next_date IS NOT NULL

请参阅演示
结果：

019-20202019-2020年第2类>

类别	Pct_change	周期
Cat1	0.15
Cat3	0.6	2019-2020

您可以使用first_value():

select distinct category, min(date), max(date),
(-1 + first_value(price) over (partition by category order by date desc) /
first_value(price) over (partition by category order by date asc)
) as percent_change
from t;