如何在PostgreSQL中计算滚动相关性？

问题说明 #

现有数据集如下：

计算整体X与Y的相关性可直接用：

SELECT CORR(X,Y) FROM some_table;

但需要计算滚动窗口的相关性：

• X=1对应仅包含X=1、2、3的所有数据的相关性
• X=2对应仅包含X=2、3、4的所有数据的相关性

期望输出：

实现SQL #

可以通过定义窗口范围的CTE结合关联查询来实现：

WITH window_defs AS (
    -- 生成每个目标X对应的窗口X范围：target_x到target_x+2
    SELECT 
        x AS target_x,
        x + 2 AS max_x
    FROM (SELECT DISTINCT x FROM some_table) t
    -- 过滤掉窗口上限超过最大X值的情况（比如X=3的话，3+2=5>4，不需要）
    WHERE x + 2 <= (SELECT MAX(x) FROM some_table)
)
SELECT 
    w.target_x AS X,
    -- 计算相关性并保留两位小数
    ROUND(CORR(t.x, t.y), 2) AS "CORR(X,Y)"
FROM window_defs w
-- 关联原表，筛选出窗口范围内的数据
JOIN some_table t ON t.x BETWEEN w.target_x AND w.max_x
-- 按目标X分组计算
GROUP BY w.target_x
ORDER BY w.target_x;

逻辑说明 #

1. window_defs 子查询先提取所有唯一的X值，确定每个目标X对应的窗口上限（当前X+2），同时过滤掉窗口超出数据集最大X的情况；
2. 将窗口定义与原表关联，获取每个窗口内的所有数据；
3. 按目标X分组，调用CORR()函数计算相关性，并用ROUND()保留两位小数得到最终结果。

内容的提问来源于stack exchange，提问作者Dirk