PostgreSQL视图查询结果未按user_id分组的问题求助

问题背景 #

现有tickets和ticket_messages两张表，创建了report_users视图用于报表统计。执行查询时，尽管GROUP BY子句包含user_id，但同一user_id仍出现多行数据（如user_1在2024-11-12有两行total tickets分别为1和2），需实现按user_id合并行的需求。

原因分析 #

1. 分组维度过多：当前查询的GROUP BY子句包含date（日粒度）、user_id、project_id、channel_id、category_id。如果同一user_id在同一天内存在不同category_id或channel_id的记录，会被拆分为不同行（当前结果未显示这两个字段，但大概率是此原因）。
2. 视图JOIN逻辑问题：report_users使用FULL OUTER JOIN关联tickets和ticket_messages的子查询，若两边的关联条件（date、project_id、user_id、category_id、channel_id）不完全匹配，会生成同一user_id的多条不匹配行，导致后续分组无法合并。

解决方案 #

方案1：按user_id+日期合并（忽略category/channel维度） #

如果报表不需要按category_id和channel_id细分，直接修改查询的分组字段，去掉这两个维度：

SELECT
    users.name AS "user",
    users.email AS user_email,
    projects.name AS project,
    -- 若需显示channel，可通过聚合函数取该用户当日的代表值，比如MAX
    (SELECT MAX(ch.title) FROM channels ch JOIN report_users ru_inner ON ch.id = ru_inner.channel_id WHERE ru_inner.user_id = rows.user_id AND date_trunc('day', ru_inner.date) = rows.date) AS channel,
    rows.*
FROM (
    SELECT
        to_char(date_trunc('day', ru.date), 'YYYY-MM-DD') AS date,
        ru.user_id,
        ru.project_id,
        SUM(COALESCE(ru.total_tickets, 0)) AS total_tickets
    FROM report_users ru
    WHERE
        ru.project_id = :project_id
        AND date_trunc('day', ru.date) BETWEEN to_date('2024-11-12', 'YYYY-MM-DD') AND to_date('2024-11-12', 'YYYY-MM-DD')
    GROUP BY
        date,
        ru.user_id,
        ru.project_id
) AS rows
LEFT OUTER JOIN projects ON projects.id = rows.project_id
LEFT OUTER JOIN users ON users.id = rows.user_id
ORDER BY
    date,
    "user",
    project

方案2：保留细分维度但修复分组问题 #

如果需要保留category_id和channel_id维度，需先修复视图的JOIN逻辑，避免生成冗余行：

优化后的report_users视图

用UNION ALL合并两个子查询后再聚合，替代FULL OUTER JOIN：

CREATE VIEW report_users AS
SELECT
    date,
    project_id,
    user_id,
    category_id,
    channel_id,
    SUM(total_tickets) AS total_tickets,
    SUM(total_messages) AS total_messages
FROM (
    -- 工单统计
    SELECT
        date_trunc('hour', t.created_at) AS date,
        t.project_id,
        t.assignee_id AS user_id,
        t.category_id,
        t.channel_id,
        COUNT(t.*) AS total_tickets,
        0 AS total_messages
    FROM tickets t
    GROUP BY
        date,
        t.project_id,
        t.assignee_id,
        t.category_id,
        t.channel_id
    
    UNION ALL
    
    -- 工单消息统计
    SELECT
        date_trunc('hour', m.created_at) AS date,
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id,
        0 AS total_tickets,
        COUNT(m.*) AS total_messages
    FROM ticket_messages m
    JOIN tickets t ON t.id = m.ticket_id
    WHERE m.user_id IS NOT NULL
    GROUP BY
        date,
        t.project_id,
        m.user_id,
        t.category_id,
        t.channel_id
) AS combined
GROUP BY
    date,
    project_id,
    user_id,
    category_id,
    channel_id;

修改查询的SUM逻辑

处理视图中可能存在的null值：

SELECT
    users.name AS "user",
    users.email AS user_email,
    projects.name AS project,
    channels.title AS channel,
    rows.*
FROM (
    SELECT
        to_char(date_trunc('day', ru.date), 'YYYY-MM-DD') AS date,
        to_char(date_trunc('hour', ru.date), 'YYYY-MM-DD HH24:MI:SS') AS date_hms,
        ru.user_id,
        ru.project_id,
        ru.category_id,
        ru.channel_id,
        SUM(COALESCE(ru.total_tickets, 0)) AS total_tickets
    FROM report_users ru
    WHERE
        ru.project_id = :project_id
        AND date_trunc('day', ru.date) BETWEEN to_date('2024-11-12', 'YYYY-MM-DD') AND to_date('2024-11-12', 'YYYY-MM-DD')
    GROUP BY
        date,
        date_hms,
        ru.user_id,
        ru.project_id,
        ru.channel_id,
        ru.category_id
) AS rows
LEFT OUTER JOIN projects ON projects.id = rows.project_id
LEFT OUTER JOIN channels ON channels.id = rows.channel_id
LEFT OUTER JOIN users ON users.id = rows.user_id
ORDER BY
    date,
    "user",
    project,
    channel

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