如何在聚合中包括下一组中的第一行

Question

我在Postgres中有一个包含类别和值的表。我想执行聚合，例如每个类别的avg(值(，但在聚合中包括下一个类别的第一行。

样品表：

id  category  value
-------------------
1   1         5.4
2   1         2.1
3   2         1.0
4   2         2.6
5   2         0.3
6   3         4.4
7   3         3.8

id是一个主键并提供一个订单。类别按顺序分组，并且是连续的
创建这样一个中间表是可以接受的(但不是必须的(，它复制了相邻的行：

id  category  value
-------------------
1   1         5.4
2   1         2.1
3   1         1.0  <-- new row
4   2         1.0
5   2         2.6
6   2         0.3
7   2         4.4  <-- new row
8   3         4.4
9   3         3.8

然后做：

select category, avg(value) group by category from sample_table

如何使用SQL语句实现这一点？

我怀疑这可以通过窗口函数和一些复杂的框架子句(如GROUPS(来实现，但我不知道如何实现。(请参见https://www.postgresql.org/docs/12/sql-expressions.html#SYNTAX-窗口函数(

Answer 1

您确认类别编号稳步增加1，没有缺口
对于简单的情况，这里有一个简单的方法：

SELECT category, avg(value)
FROM  (
SELECT category, value
FROM   tbl
UNION ALL
(  -- parentheses required
SELECT DISTINCT ON (category)
category - 1, value
FROM   tbl
WHERE  category > (SELECT min(category) FROM tbl)  -- eliminate corner case
ORDER  BY category, id
)   
) sub
GROUP  BY 1
ORDER  BY 1;

UNION ALL之后的第二个术语添加了您建议的行：我取每组的第一行，然后从类别中减去1。

角落案例：添加一个min(category) - 1的新类别。可以很容易地消除。。。

---

任何类别的通用解决方案(只要定义了顺序(：

SELECT category, avg(value)
FROM  (
SELECT category, value
FROM   tbl
UNION ALL
SELECT lag(category) OVER (ORDER BY category), value
FROM  (
SELECT DISTINCT ON (category)
category, value
FROM   tbl
ORDER  BY category, id
) unicat
) sub
WHERE  category IS NOT NULL  -- eliminate corner case
GROUP  BY 1
ORDER  BY 1;

使用窗口函数lag()将每个组的第一个值添加到前一个类别。

关于DISTINCT ON:

• 选择每个GROUP BY组中的第一行