PostgreSQL查询优化：如何补全员工每小时工作统计的0值缺失行？

阿华AIGC实验室

2026-5-20

如何让PostgreSQL查询补全无数据的小时区间并显示count为0？

这个问题很常见，本质上是如何在分组统计时补全无数据的维度值，我来一步步帮你解决：

首先得先纠正你原查询里的一个问题：你的SELECT语句里包含了EXTRACT(hour from start_time)这个非聚合字段，但GROUP BY里只有employee_id，这在PostgreSQL的默认配置下会直接报错（违反only_full_group_by规则），因为数据库不知道该取哪个小时的值。所以原查询首先要调整为按employee_id和hour一起分组，这才是正确的统计逻辑。

接下来解决核心问题：补全无数据的小时区间。我们需要先生成当天所有的小时（0-23），再和需要统计的员工做全量组合，最后左连接你的统计数据，这样缺失的小时就会显示出来，再用COALESCE把空值转为0。

完整的SQL语句如下：

WITH hours AS (
    -- 生成0到23的所有小时
    SELECT generate_series(0, 23) AS hour
),
employees AS (
    -- 获取当天有工作记录的所有员工（如果要统计所有员工，建议用员工表代替这里的distinct）
    SELECT DISTINCT employee_id 
    FROM c_call 
    WHERE start_time >= '2018-10-13 00:00:00' 
      AND start_time < '2018-10-14 00:00:00'
),
employee_hours AS (
    -- 交叉连接得到每个员工和每个小时的全量组合
    SELECT e.employee_id, h.hour
    FROM employees e
    CROSS JOIN hours h
),
work_stats AS (
    -- 原统计逻辑，修正GROUP BY
    SELECT 
        employee_id,
        EXTRACT(hour from start_time)::INT AS hour,
        COUNT(work_done) AS count
    FROM c_call 
    WHERE start_time >= '2018-10-13 00:00:00' 
      AND start_time < '2018-10-14 00:00:00'
    GROUP BY employee_id, EXTRACT(hour from start_time)
)
-- 左连接全量组合和统计数据，用COALESCE把null转为0
SELECT 
    eh.employee_id,
    eh.hour,
    COALESCE(ws.count, 0) AS count
FROM employee_hours eh
LEFT JOIN work_stats ws 
    ON eh.employee_id = ws.employee_id 
    AND eh.hour = ws.hour
ORDER BY eh.employee_id, eh.hour;

关键部分解释：

hours CTE：用generate_series生成0到23的整数，代表一天的所有小时区间。
employees CTE：提取当天有工作记录的员工ID，如果你的业务需要统计所有在职员工（哪怕当天没有任何工作记录），建议直接查询你的员工表（比如SELECT employee_id FROM employees），而不是从c_call里取distinct。
employee_hours CTE：通过交叉连接（CROSS JOIN）得到每个员工和每个小时的组合，这一步是补全缺失维度的核心。
work_stats CTE：修正后的原统计逻辑，确保按employee_id和hour分组，得到有数据的员工-小时的工作量。
最终查询：左连接全量组合和统计数据，用COALESCE把没有匹配到的count转为0，最后按员工和小时排序，结果就完整了。