Postgres指定时间段多数据查询及子组平均值计算SQL求助

Postgres查询语句实现方案 #

假设你的日志表名为server_logs，核心字段如下：

• log_time：日志记录时间（timestamp类型）
• branch：服务器分支（取值1/2）
• sub_branch：子分支（取值aa/bb/cc）
• metric_value：日志待统计的指标数值（请替换为实际字段名）

完整查询语句

WITH time_range AS (
    -- 替换为实际查询的起止时间
    SELECT '2024-01-01 00:00:00'::TIMESTAMP AS start_time,
           '2024-01-01 00:00:02'::TIMESTAMP AS end_time
),
detail_data AS (
    SELECT
        log_time,
        MAX(CASE WHEN branch = 1 AND sub_branch = 'aa' THEN metric_value END) AS branch1_aa,
        MAX(CASE WHEN branch = 1 AND sub_branch = 'bb' THEN metric_value END) AS branch1_bb,
        MAX(CASE WHEN branch = 1 AND sub_branch = 'cc' THEN metric_value END) AS branch1_cc,
        MAX(CASE WHEN branch = 2 AND sub_branch = 'aa' THEN metric_value END) AS branch2_aa,
        MAX(CASE WHEN branch = 2 AND sub_branch = 'bb' THEN metric_value END) AS branch2_bb,
        MAX(CASE WHEN branch = 2 AND sub_branch = 'cc' THEN metric_value END) AS branch2_cc
    FROM server_logs, time_range
    WHERE log_time BETWEEN time_range.start_time AND time_range.end_time
    GROUP BY log_time
    ORDER BY log_time
),
avg_data AS (
    SELECT
        '时段平均值' AS log_time,
        AVG(CASE WHEN branch = 1 AND sub_branch = 'aa' THEN metric_value END) AS branch1_aa,
        AVG(CASE WHEN branch = 1 AND sub_branch = 'bb' THEN metric_value END) AS branch1_bb,
        AVG(CASE WHEN branch = 1 AND sub_branch = 'cc' THEN metric_value END) AS branch1_cc,
        AVG(CASE WHEN branch = 2 AND sub_branch = 'aa' THEN metric_value END) AS branch2_aa,
        AVG(CASE WHEN branch = 2 AND sub_branch = 'bb' THEN metric_value END) AS branch2_bb,
        AVG(CASE WHEN branch = 2 AND sub_branch = 'cc' THEN metric_value END) AS branch2_cc
    FROM server_logs, time_range
    WHERE log_time BETWEEN time_range.start_time AND time_range.end_time
)
SELECT * FROM detail_data
UNION ALL
SELECT * FROM avg_data;

注意事项

• 请将代码中的server_logs替换为实际表名，metric_value替换为实际要统计的指标字段
• 若同一时间点同一分支+子分支存在多条记录，可根据业务需求将detail_data中的MAX聚合函数替换为SUM/AVG
• 若不需要明细数据仅需时段平均值，直接保留avg_data的查询逻辑即可

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