对多个窗口函数重用同一个移动窗口

Question

我在Postgres数据库中有一个数字表，我正在尝试计算每行前3行和后3行的范围(最大-最小值(。给定一个测试表：

CREATE TABLE test_table AS (
SELECT
    generate_series(1,100) id,
    random()*generate_series(1,100) val
);

这可以通过以下查询完成：

SELECT
    id,
    MAX(val) OVER (
        ORDER BY id
        ROWS
            BETWEEN 3 PRECEDING
            AND 3 FOLLOWING
    ) - 
    MIN(val) OVER (
        ORDER BY id
        ROWS
            BETWEEN 3 PRECEDING
            AND 3 FOLLOWING
    ) val_range
FROM test_table;

但OVER条款是重复的。有没有办法改进查询并删除重复项？重复会影响性能吗？如果我想计算每个 id 的其他聚合，我应该如何处理移动窗口？

我想出了以下查询来重用窗口，但我不确定这是否是最佳方法。

WITH
windowed AS (
SELECT
    id,
    UNNEST(ARRAY_AGG(val) OVER (
        ORDER BY id
        ROWS
            BETWEEN 3 PRECEDING
            AND 3 FOLLOWING
    )) val
FROM test_table
)
SELECT
    id,
    MAX(val)-MIN(val) val_range,
    AVG(val) val_mean,
    STDDEV(val) val_stddev
FROM windowed
GROUP BY id
ORDER BY id;

Answer 1

您可以使用 WINDOW 子句来缩短代码：

SELECT id
     , MAX(val) OVER w - 
       MIN(val) OVER w AS val_range
FROM   test_table
WINDOW w AS (ORDER BY id ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING);

但这几乎不会影响性能。Postgres 也会重复使用窗口框架，如果你也反复拼写出来。手册：

当使用多个窗口函数时，所有窗口函数具有语法等效的PARTITION BY和ORDER BY子句在他们的窗口中，定义保证在单个中进行评估传递数据。

相关：

• 如何在窗口函数中使用环数据结构
• PostgreSQL中一行中窗口函数的第一个和最后一个值