如何使用TimescaleDB time_bucket获取percentile(x)值对应的时间戳

解决方案 #

你需要先在每个时间桶内对指标值排序后，定位到P50对应的实际行，再取出该行的时间戳，以下是两种可行实现：

方案1：窗口函数排名后取中（推荐，单遍扫描性能更好） #

WITH ranked_data AS (
    SELECT 
        time_bucket('120 sec',timestamp_utc) as interval_size,
        timestamp_utc,
        int_val,
        -- 每个时间桶内按int_val排序编号
        ROW_NUMBER() OVER (PARTITION BY time_bucket('120 sec',timestamp_utc) ORDER BY int_val, timestamp_utc) as rn,
        -- 计算每个时间桶的总行数
        COUNT(*) OVER (PARTITION BY time_bucket('120 sec',timestamp_utc)) as total_cnt
    FROM timeseries.raw
    WHERE timestamp_utc > NOW() - INTERVAL '10 min'
    AND tag_id = 59560544877390423
)
SELECT 
    interval_size,
    MIN(int_val) as minVal,
    MIN(timestamp_utc) FILTER (WHERE rn = 1) as minTime,
    MAX(int_val) as maxVal,
    MAX(timestamp_utc) FILTER (WHERE rn = total_cnt) as maxTime,
    -- 取P50对应行的值和时间戳，偶数行时默认取靠前的匹配项，要取靠后的可以把ceil改成floor
    MAX(int_val) FILTER (WHERE rn = CEIL(total_cnt * 0.5)) as medianVal,
    MAX(timestamp_utc) FILTER (WHERE rn = CEIL(total_cnt * 0.5)) as medianTime
FROM ranked_data
GROUP BY interval_size
ORDER BY interval_size DESC

如果存在多个相同P50数值的行，上述写法会返回排序后第一个出现的P50对应的时间戳，你可以调整ORDER BY的规则调整匹配优先级。

方案2：关联匹配已计算的P50值 #

如果你需要保留原有查询的percentile_disc逻辑，也可以用子查询先算出每个桶的P50值，再关联回原表取对应时间戳：

WITH bucket_stats AS (
    SELECT 
        time_bucket('120 sec',timestamp_utc) as interval_size,
        first(timestamp_utc,int_val) as minTime,
        min(int_val) as minVal,
        last(timestamp_utc,int_val) as maxTime,
        max(int_val) as maxVal,
        percentile_disc(0.5) within group (order by int_val) as medianVal
    FROM timeseries.raw
    WHERE timestamp_utc > NOW() - INTERVAL '10 min'
    AND tag_id = 59560544877390423
    GROUP BY interval_size
)
SELECT 
    bs.*,
    MIN(r.timestamp_utc) as medianTime -- 多个相同P50值时取最早出现的时间
FROM bucket_stats bs
JOIN timeseries.raw r 
    ON time_bucket('120 sec', r.timestamp_utc) = bs.interval_size
    AND r.int_val = bs.medianVal
    AND r.timestamp_utc > NOW() - INTERVAL '10 min'
    AND r.tag_id = 59560544877390423
GROUP BY bs.interval_size, bs.minTime, bs.minVal, bs.maxTime, bs.maxVal, bs.medianVal
ORDER BY bs.interval_size DESC

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