postgresql - 对每 N 个值进行分组

Question

我在 PostgreSQL 中有一个这样的表。我想根据 ID(这是主键)对每 16 条记录执行均值和最大值等聚合函数。例如，我必须计算前 16 条记录和后 16 条记录的平均值，依此类推。

+-----+-------------
| ID  |  rainfall  |
+-----+----------- |
|  1  |  110.2     |
|  2  |  56.6      |
|  3  |  65.6      |
|  4  |  75.9      |
+-----+------------

Answer 1

想到的第一种方法是使用 row_number() 来注释表格，然后按 16 行的 block 分组。

SELECT min(id) as first_id, max(id) AS last_id, avg(rainfall) AS avg_this_16
FROM (
  SELECT id, rainfall, row_number() OVER (order by id) AS n
  FROM the_table
) x(id,rainfall,n)
GROUP BY n/16
ORDER BY n/16;

请注意，这不一定包括最后一组的 16 个样本。

或者，您可以使用 avg() 作为窗口函数来计算运行平均值:

SELECT id, avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING)
FROM the_table;

...可能用行号注释并选择你想要的:

SELECT id AS greatest_id_in_group, avg_last_16_inclusive FROM (
  SELECT
    id, 
    avg(rainfall) OVER (ORDER BY id ROWS 15 PRECEDING) AS avg_last_16_inclusive,
    row_number() OVER (ORDER BY id) AS n
  FROM the_table
) x WHERE n % 16 = 0;

这将忽略最后的 n<16 个样本，不会为它们返回一行。

请注意，我假设不保证 ID 是连续的。如果它们是无间隙的，您可以group by id/16 并避免窗口函数。