如何使带PARTITION的COUNT窗口函数显示分组缺失的0值？

窗口函数确实没法直接生成你需要的0值行——因为它只能基于结果集中已存在的行来计算分区统计，没法凭空造出不存在的状态分组。要实现每个日期+小时分组下都显示三种状态（包括计数为0的），我们需要换个思路：先构造出所有可能的分组组合，再和实际统计结果做左连接来补0。

具体步骤 #

1. 提取所有唯一的日期+小时分组：先从你的journal表中获取所有出现过的日期和小时的组合，确保我们只处理数据中存在的时间片段。
2. 定义所有需要的状态列表：明确列出你要的三个状态：autoprocessed、manually processed、other state。
3. 生成所有可能的组合：把日期小时分组和状态列表做交叉连接（CROSS JOIN），这样每个时间分组下都会有三个状态的行，不管有没有对应数据。
4. 统计实际的状态计数：对原表按日期小时+状态分组，统计每个组的唯一doc_id数量。
5. 左连接补0：把交叉连接的组合和统计结果左连接，用COALESCE把没有匹配的计数转换成0。

完整SQL示例（适用于支持CTE的数据库：PostgreSQL、MySQL 8+、SQL Server等） #

WITH date_hour_groups AS (
    -- 获取所有唯一的日期+小时组合
    SELECT DISTINCT
        DATE(date) AS date_part,
        EXTRACT(HOUR FROM time) AS hour_part
    FROM journal
),
all_states AS (
    -- 定义所有需要展示的状态
    SELECT 'autoprocessed' AS state UNION ALL
    SELECT 'manually processed' AS state UNION ALL
    SELECT 'other state' AS state
),
state_counts AS (
    -- 统计每个日期+小时+状态的实际文档数
    SELECT
        DATE(date) AS date_part,
        EXTRACT(HOUR FROM time) AS hour_part,
        CASE
            WHEN status_id = 5 AND flag = 1 THEN 'autoprocessed'
            WHEN status_id = 5 AND flag = 0 THEN 'manually processed'
            ELSE 'other state'
        END AS state,
        COUNT(DISTINCT doc_id) AS doc_count
    FROM journal
    GROUP BY date_part, hour_part, state
)
-- 组合所有可能的分组+状态，左连接统计结果补0
SELECT
    TO_CHAR(dhg.date_part, 'DD.MM.YY') AS date,
    CONCAT(dhg.hour_part, ':00:00') AS time,
    as_.state,
    COALESCE(sc.doc_count, 0) AS doc_count
FROM date_hour_groups dhg
CROSS JOIN all_states as_
LEFT JOIN state_counts sc
    ON dhg.date_part = sc.date_part
    AND dhg.hour_part = sc.hour_part
    AND as_.state = sc.state
ORDER BY dhg.date_part, dhg.hour_part, as_.state;

为什么原来的窗口函数方法不行？ #

你的原查询用了COUNT() OVER (PARTITION BY ...)窗口函数，它的作用是在已有的行上计算分组统计值。如果某个日期+小时分组下没有manually processed的记录，结果集中就不会有对应的行，窗口函数自然没法为这个不存在的行生成0值。而我们上面的方法是先主动构造出所有需要的行，再填充数据，这样就能确保每个状态都被展示，包括计数为0的情况。

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