我每天都有来自不同行业的不同公司的时间序列,并使用PostgreSQL。我从一个例子开始解释我的问题。我拥有的是这个:
+------------+---------+-------------+----+
| day | company | industry | v |
+------------+---------+-------------+----+
| 2012-01-12 | A | consumer | 2 |
| 2012-01-12 | B | consumer | 2 |
| 2012-01-12 | C | health | 4 |
| 2012-01-12 | D | health | 4 |
| 2012-01-13 | A | consumer | 5 |
| 2012-01-13 | B | consumer | 5 |
| 2012-01-13 | C | health | 7 |
| 2012-01-13 | D | health | 7 |
| 2012-01-16 | A | consumer | 8 |
| 2012-01-16 | B | consumer | 8 |
| 2012-01-16 | C | health | 3 |
| 2012-01-16 | D | health | 3 |
+------------+---------+-------------+----+
来自不同行业的不同公司,有些值 v 是各行业的每日平均值。我需要的是这个:
+------------+---------+----------+---+------------+
| day | company | industry | v | delta_v |
+------------+---------+----------+---+------------+
| 2012-01-12 | A | consumer | 2 | NULL |
| 2012-01-12 | B | consumer | 2 | NULL |
| 2012-01-12 | C | health | 4 | NULL |
| 2012-01-12 | D | health | 4 | NULL |
| 2012-01-13 | A | consumer | 5 | 1.5 |
| 2012-01-13 | B | consumer | 5 | 1.5 |
| 2012-01-13 | C | health | 7 | 0.75 |
| 2012-01-13 | D | health | 7 | 0.75 |
| 2012-01-16 | A | consumer | 8 | 0.6 |
| 2012-01-16 | B | consumer | 8 | 0.6 |
| 2012-01-16 | C | health | 3 | -0.571428 |
| 2012-01-16 | D | health | 3 | -0.571428 |
+------------+---------+----------+---+------------+
我需要变量 v 的每日更改。例如,2012-01-12 行业"消费者"的 v 平均值为 2,2012-01-13 为 5。因此,增长为 (5-2)/2 = 1.5。
我试过这个:
SELECT *
, (v - LAG(v) OVER (PARTITION BY industry ORDER BY day) )
/ LAG (v) OVER (PARTITION BY industry ORDER BY day) AS delta_v
FROM mytable
ORDER BY day, industry
问题是,如果一天内有多个来自同一行业的公司,它也会"在日内"计算值 v 的变化。
我希望它只需要在"PARTITION BY" - 子句中进行一个小的更正,但我真的不知道该怎么做。你有什么想法可以帮助我吗?
我想你也希望公司在那里:
SELECT t.*,
((v - LAG(v) OVER (PARTITION BY industry, company ORDER BY day) )
/ LAG (v) OVER (PARTITION BY industry, company ORDER BY day)
) AS delta_v
FROM mytable t
ORDER BY day, industry;
我不确定 Postgres 是否真的计算了两次lag()
,但这更容易维护:
SELECT t.*,
(v / LAG(v) OVER (PARTITION BY industry, company ORDER BY day) ) - 1
) AS delta_v
FROM mytable t
ORDER BY day, industry;