sql - SQL 查询中的自定义组和平均值

Question

我有一个关于 SQL 查询的问题，我可以使用 Postgres 做以下情况吗:

1- 如果我有数百万行，每行都有两列“日期和值”

2- 我想对每 1000 行进行分组并返回“Date and avgValue(1000 rows)”

我的数据集示例:

Date | Value
2010 | 4
2011 | 2
2012 | 6
2013 | 8
2014 | 6
2015 | 4
2016 | 4
2017 | 3
2018 | 5
....

如果我想通过计算值列的平均值来对每三行进行分组，那么输出应该是这样的:

Date      | Value
2010-2012 |  (4+2+6)/3
2013-2015 |  (8+6+4)/3
2016-2018 |  (4+3+5)/3

谢谢大家

Answer 1

如果我没理解错的话，您想按日期分组，并在每个日期内任意分组为每批 1000 行，然后计算该组中的平均值。

SELECT 
    date,
    rn,
    avg(value)
FROM
(
    SELECT 
        date,
        value,
        row_number() OVER(partition by date) / 1000 as rn
    FROM 
        schema.table
) a
GROUP BY date,rn

这里的关键是 row_number() 位，它创建一个按行递增的变量。我们使用 OVER(按日期分区)，这意味着计数器将为每个日期值重新开始。

然后我们将其除以 1000，这样每个日期的 1000 行的每个 block 对于 rn 将具有相同的值。然后我们按日期和 rn 分组，以获得所需的输出。

此查询返回的是每个日期每 1000 行中的一行，以及该 block 的平均值。