sql - 按小时间隔计数

Question

我在 postgresql 中编写了一个 SQL 查询，它运行良好，它让我得到每个员工每小时完成的工作量

SELECT COUNT(work_done) AS count, EXTRACT(hour from start_time) AS hour
FROM c_call
WHERE  start_time >= '2018-10-13 00:00:00'
GROUP BY employee_id;

如果某个员工每隔一个小时都处于事件状态，但当一个小时没有员工的数据时，它就会被忽略，这是完美的。如果员工在那个时间不工作，如何让它工作，以便结果包含每个时间间隔的一行，并且值字段设置为零。

Answer 1

您可以使用 generate_series 生成小时序列功能:

SELECT * FROM generate_series(0, 23) AS foo(bar)

然后用它来填补小时空缺:

WITH employee_call AS (
    SELECT 
        COUNT(work_done) AS count, 
        EXTRACT(hour from start_time) AS hour_fraction
    FROM 
        c_call
    WHERE  
        start_time >= '2018-10-13 00:00:00'
    GROUP BY 
        employee_id
), hour_series (hour_fraction) AS (
    SELECT generate_series(0, 23)
)
SELECT 
    COALESCE(c.count, 0) AS count, 
    COALESCE(c.hour_fraction, h.hour_fraction) AS hour_fraction 
FROM 
    hour_series h 
    LEFT JOIN employee_call c ON (c.hour_fraction = h.hour_fraction)