咨询：如何编写SQL统计不同user_id发送消息的平均数量

原SQL的核心错误 #

• 子句顺序错误：GROUP BY不能放在JOIN之前，SQL的正确执行顺序为FROM→JOIN→WHERE→GROUP BY
• 聚合函数语法错误：count avg (distinct c.ticket_id)是无效写法，同时混用两个聚合函数且格式不符合SQL规范
• 分组字段错误：需求是按user_id分组统计用户消息数据，原按ticket_id分组完全偏离需求
• 左连接过滤逻辑错误：WHERE t.created_at会过滤掉tickets表无匹配的记录，将左连接强制转为内连接，若需保留左连接特性需调整条件位置

正确SQL写法 #

场景1：计算所有用户的平均消息发送量

先统计每个用户的总消息数，再求这些数值的平均值，完全匹配“不同user_id发送消息的平均数量”的核心需求：

SELECT AVG(user_comment_count) AS avg_comments_per_user
FROM (
    SELECT u.user_id, COUNT(c.comment_id) AS user_comment_count
    FROM "comments" c
    LEFT OUTER JOIN tickets t ON c.ticket_id = t.ticket_id
    LEFT OUTER JOIN "users" u ON u.user_id = t.requester
    WHERE t.created_at BETWEEN '2022-10-01' AND '2022-10-31'
    GROUP BY u.user_id
) AS user_comment_stats;

场景2：计算每个用户在其工单中的平均消息数

若需求是统计单个用户每工单的平均消息量，可使用以下写法：

SELECT u.user_id, 
       COUNT(c.comment_id)::FLOAT / COUNT(DISTINCT t.ticket_id) AS avg_comments_per_ticket_per_user
FROM "comments" c
LEFT OUTER JOIN tickets t ON c.ticket_id = t.ticket_id
LEFT OUTER JOIN "users" u ON u.user_id = t.requester
WHERE t.created_at BETWEEN '2022-10-01' AND '2022-10-31'
GROUP BY u.user_id;

保留左连接特性的写法

如果需要保留comments表中无匹配工单的记录（即使工单不在指定时间范围内），需将时间条件移至tickets的连接条件中：

SELECT AVG(user_comment_count) AS avg_comments_per_user
FROM (
    SELECT u.user_id, COUNT(c.comment_id) AS user_comment_count
    FROM "comments" c
    LEFT OUTER JOIN tickets t 
        ON c.ticket_id = t.ticket_id
        AND t.created_at BETWEEN '2022-10-01' AND '2022-10-31'
    LEFT OUTER JOIN "users" u ON u.user_id = t.requester
    GROUP BY u.user_id
) AS user_comment_stats;

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