如何按工作日小时维度统计平均任务完成量

要实现一周内每个小时时段（如7-8点）的周一至周五平均任务完成量统计，需要对初始SQL做以下调整：

核心思路 #

1. 过滤出仅包含周一至周五的任务记录
2. 按天+小时分组，计算每个工作日各小时的任务总量
3. 对同一小时的单日任务总量取平均值，得到该时段的日均任务数

优化后的SQL代码 #

WITH daily_hourly_counts AS (
    SELECT
        EXTRACT(HOUR FROM tasks) AS hour_of_day,
        COUNT(*) AS task_count
    FROM tasks
    -- 筛选周一至周五（DOW=1为周一，DOW=5为周五），并限制最近一周的数据
    WHERE EXTRACT(DOW FROM tasks) BETWEEN 1 AND 5
        AND tasks >= CURRENT_DATE - INTERVAL '1 week'
    -- 按天和小时分组，得到单日单小时的任务数
    GROUP BY date_trunc('day', tasks), hour_of_day
)
SELECT
    -- 格式化小时为AM/PM格式
    CASE 
        WHEN hour_of_day < 12 THEN CONCAT(hour_of_day, 'AM')
        WHEN hour_of_day = 12 THEN '12PM'
        ELSE CONCAT(hour_of_day - 12, 'PM')
    END AS hour_period,
    -- 计算该小时时段的平均任务数，保留两位小数
    ROUND(AVG(task_count), 2) AS avg_tasks
FROM daily_hourly_counts
-- 按小时分组取平均
GROUP BY hour_of_day
-- 按小时顺序排序
ORDER BY hour_of_day;

关键说明 #

• EXTRACT(DOW FROM tasks)：提取日期对应的星期几（PostgreSQL中0为周日，1-5对应周一至周五）
• date_trunc('day', tasks)：将时间截断到天级别，确保每个小时的统计是单日的任务量
• CTE daily_hourly_counts：先计算单日单小时的任务数，再基于此计算跨工作日的平均值，避免直接按小时分组导致的错误统计（比如将不同天的同一小时数据直接合并而非取平均）

示例输出 #

hour_period | avg_tasks
------------|----------
7AM         | 15.20
8AM         | 22.50
9AM         | 18.75
10AM        | 25.00
...

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