PostgreSQL分组计算平均时长与95分位数技术咨询

核心逻辑拆解 #

• 按指定字段（支持多字段组合）分组
• 每组时长 = 组内最后一条记录的start_time - 组内第一条记录的start_time
• 基于各组时长，计算平均值和95分位数

基础解决方案代码 #

先通过聚合获取每组首尾时间差，再统计目标指标：

WITH group_durations AS (
    SELECT
        group_id, -- 替换为你的实际分组字段，多分组用逗号分隔（如group_id, type）
        MAX(start_time) - MIN(start_time) AS duration
    FROM your_table -- 替换为你的表名
    GROUP BY group_id
)
SELECT
    AVG(duration) AS avg_duration,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration) AS percentile_95_duration
FROM group_durations;

关键细节说明 #

• 多分组场景：只需在GROUP BY和SELECT子句中同时添加所有分组字段即可
• MAX(start_time)和MIN(start_time)分别取组内最晚、最早的开始时间，差值即为组的总时长
• 若需要离散型95分位数，可将PERCENTILE_CONT替换为PERCENTILE_DISC

多分组场景示例 #

假设表包含group_id、category两个分组字段，示例SQL：

WITH group_durations AS (
    SELECT
        group_id,
        category,
        MAX(start_time) - MIN(start_time) AS duration
    FROM your_table
    GROUP BY group_id, category
)
SELECT
    group_id,
    category,
    duration,
    AVG(duration) OVER (PARTITION BY group_id) AS avg_duration_per_group,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY group_id) AS p95_per_group
FROM group_durations;

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