使用滞后窗口函数找到正确的分区

Question

我每天都有针对不同行业的不同公司的时刻表，并与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  |
+------------+---------+----------+---+------------+

我需要变量的每日变化，例如，在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的变化也是“日内”。

我希望它只需要在“分区按”-子句中做一个小小的修改，但是我真的不知道如何去做。你有什么能帮我的主意吗？

Answer 1

我想你也希望公司在那里：

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;