PostgreSQL/TimescaleDB如何按分区与时间窗口计算分位数？

现有一张results表，结构与数据如下：

需求：对表中每一行，按user_id和attr_id分区，计算当前行之前、且时间在10天窗口内的分位数。目前可通过支持partial模式的函数计算标准差，示例代码如下：

SELECT stddev(value) OVER (PARTITION BY user_id, attr_id ORDER BY timestamp ASC RANGE BETWEEN '10 days'::interval PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) AS stddev_efficiency FROM results;

请问在PostgreSQL/TimescaleDB中，是否有方法实现符合上述要求的分位数计算？

在PostgreSQL和TimescaleDB中，可以通过以下几种方式实现滑动时间窗口内的分位数计算：

1. 关联查询模拟滑动窗口 #

PostgreSQL内置的percentile_cont（连续分位数）和percentile_disc（离散分位数）函数不支持直接在滑动窗口的OVER子句中使用，但可以通过关联查询模拟逻辑：

SELECT 
    r.*,
    percentile_cont(0.5) WITHIN GROUP (ORDER BY r_prev.value) AS median_10d,
    percentile_cont(0.9) WITHIN GROUP (ORDER BY r_prev.value) AS p90_10d
FROM results r
LEFT JOIN results r_prev 
    ON r.user_id = r_prev.user_id
    AND r.attr_id = r_prev.attr_id
    AND r_prev.timestamp >= r.timestamp - INTERVAL '10 days'
    AND r_prev.timestamp < r.timestamp
GROUP BY r.result_id, r.attr_id, r.user_id, r.value, r.timestamp;

核心逻辑是关联当前行与同分区内、时间在10天前到当前行之前的所有数据，再对这些数据的value计算分位数。

2. TimescaleDB时间桶+滑动窗口（批量场景） #

如果是基于固定时间桶的批量分析，可以结合TimescaleDB的time_bucket优化性能：

WITH bucketed_data AS (
    SELECT
        user_id,
        attr_id,
        time_bucket('1 day', timestamp) AS bucket,
        percentile_cont(0.5) WITHIN GROUP (ORDER BY value) AS bucket_median
    FROM results
    GROUP BY user_id, attr_id, bucket
)
SELECT
    *,
    percentile_cont(0.5) OVER (
        PARTITION BY user_id, attr_id 
        ORDER BY bucket 
        RANGE BETWEEN INTERVAL '10 days' PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
    ) AS sliding_10d_median
FROM bucketed_data;

这种方式先按时间桶聚合数据，再计算滑动窗口分位数，性能比逐行关联更优。

3. 自定义聚合函数（进阶优化） #

如果追求极致性能，可以基于PostgreSQL的聚合函数框架，编写支持滑动窗口的自定义分位数聚合函数（用PL/pgSQL或C语言实现），实现增量式分位数计算。但该方案需要具备开发能力。

性能优化提示 #

逐行关联的方式在数据量大时可能存在性能瓶颈，建议创建(user_id, attr_id, timestamp)的复合索引，提升关联查询的效率。

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