PostgreSQL按小时分组时如何显示无数据小时的0值结果？

我完全懂你的需求——当某个时间段（比如4点到5点）没有符合条件的数据时，你希望结果里仍然保留这个小时的行，并且count和heures字段显示为0，而不是直接跳过该行。

要实现这个效果，核心思路是先生成完整的0-23小时序列，再将你的原始查询结果与这个序列做左连接，这样就能确保每个小时都出现在最终结果中。

修改后的查询语句 #

WITH all_hours AS (
    -- 生成0到23的所有小时数，确保每个小时都有对应的行
    SELECT generate_series(0, 23) AS hour
),
original_data AS (
    -- 保留你原始查询的所有过滤和聚合逻辑
    SELECT 
        extract(hour from "public"."v_mdmeddialogues"."datecreation")::integer AS "datecreation", 
        "public"."v_mdmeddialogues"."refspecialty" AS "refspecialty", 
        "Refspecialty"."label" AS "label", 
        COALESCE(count(distinct "public"."v_mdmeddialogues"."iddialogue"), 0) AS "count", 
        COALESCE(sum("public"."v_mdmeddialogues"."timespent"), 0) AS "heures" 
    FROM "public"."v_mdmeddialogues" 
    LEFT JOIN "public"."v_mdiddoctors" "Refuser" ON "public"."v_mdmeddialogues"."refuser" = "Refuser"."iduser" 
    LEFT JOIN "public"."v_mdparamspecialties" "Refspecialty" ON "public"."v_mdmeddialogues"."refspecialty" = "Refspecialty"."idspecialty" 
    WHERE 
        date_trunc('month', "public"."v_mdmeddialogues"."datecreation") 
            BETWEEN date_trunc('month', now() - INTERVAL '3 month') 
            AND date_trunc('month', now())
        AND "Refspecialty"."label" = 'Dermatologie' 
        AND "public"."v_mdmeddialogues"."codedialogueaction" IN ('RESPONSE', 'TRANSFER')
        AND extract('dow' FROM "public"."v_mdmeddialogues"."datecreation") IN (0, 6) 
    GROUP BY 
        extract(hour from "public"."v_mdmeddialogues"."datecreation")::integer, 
        "public"."v_mdmeddialogues"."refspecialty", 
        "Refspecialty"."label"
)
-- 将完整小时序列与原始数据左连接，补全缺失的小时行
SELECT 
    ah.hour AS "datecreation",
    COALESCE(od.refspecialty, '你的皮肤科ID') AS "refspecialty", -- 替换为实际的皮肤科refspecialty值
    COALESCE(od.label, 'Dermatologie') AS "label",
    COALESCE(od.count, 0) AS "count",
    COALESCE(od.heures, 0) AS "heures"
FROM all_hours ah
LEFT JOIN original_data od ON ah.hour = od.datecreation
ORDER BY ah.hour ASC, od.refspecialty ASC, od.label ASC;

关键说明 #

1. all_hours CTE：用generate_series(0,23)生成一天中所有的小时数，这是确保每个小时都能出现在结果里的基础。
2. original_data CTE：保留你原来的查询逻辑，只做必要的聚合和过滤，得到有数据的小时结果。这里我简化了重复的CAST操作（因为你的datecreation本身就是timestamp类型），让代码更简洁。
3. 左连接与补全：通过左连接将完整小时序列和原始数据关联，没有匹配到数据的小时，用COALESCE把count和heures设为0；同时可以固定refspecialty和label的值，让结果格式更统一。
4. 额外优化：原查询中的LEFT JOIN "public"."v_mdiddoctors"没有用到该表的任何字段，你可以考虑删除这部分，提升查询效率。

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