如何按时间窗口（分钟/小时）计算数据百分位数

数据集 #

以下是测试用的毫秒级时间戳与数值数据集：

DECLARE @Table TABLE (TimeInMillis BIGINT, value BigInt);
INSERT INTO @Table (TimeInMillis, value) VALUES
(1680195900000, 8 ), (1680195923000, 5 ), (1680195924000, 7 ),
(1680195927000, 12), (1680195962000, 8 ), (1680195963000, 9 ),
(1680195964000, 15), (1680196025000, 9 ), (1680196028000, 8 ),
(1680196029000, 15);

需求说明 #

• 将数据按分钟粒度分组汇总，计算每个分钟窗口内数值的90分位、99分位
• 后续需支持扩展为小时粒度的相同百分位计算

期望输出 #

优化后的实现方案 #

你提供的候选代码可简化为直接使用聚合函数形式的PERCENTILE_CONT，避免重复计算，同时支持灵活切换时间粒度：

分钟粒度计算 #

SELECT 
    (TimeInMillis - (TimeInMillis % 60000)) AS TimeInMillis,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY value) AS Percentile90,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY value) AS Percentile99,
    COUNT(*) AS Cnt
FROM @Table
GROUP BY (TimeInMillis - (TimeInMillis % 60000))
ORDER BY TimeInMillis;

小时粒度扩展 #

仅需修改时间窗口的取模逻辑（1小时=3600000毫秒）：

SELECT 
    (TimeInMillis - (TimeInMillis % 3600000)) AS TimeInMillis,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY value) AS Percentile90,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY value) AS Percentile99,
    COUNT(*) AS Cnt
FROM @Table
GROUP BY (TimeInMillis - (TimeInMillis % 3600000))
ORDER BY TimeInMillis;

关键说明 #

• PERCENTILE_CONT：采用连续插值法计算百分位数，与你期望的非整数输出结果完全匹配
• 若需要取数据中实际存在的离散值作为百分位数，可替换为PERCENTILE_DISC函数

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