SQL查询实现2019年1月用户评论数直方图统计（含零评论用户）

统计2019年1月每位用户的评论数量，统计范围需覆盖从未发表过评论的用户，最终结果用于制作评论数分布直方图。

用户表 #

评论表（代码中对应Undostres表） #

原有实现绕了不必要的弯路，存在多处逻辑和语法错误：

• 不需要通过user_id+1推算缺失用户ID，用户表已经存储全量用户信息，直接关联即可
• 列重命名语句语法错误，计算列应在SELECT阶段直接指定别名，无需建表后再修改列名
• 向原始评论表插入空评论数据的操作会污染原始表，会导致后续统计结果出错
• 缺少2019年1月的时间过滤条件，给出的样例数据中所有评论均为2018年产生，按需求条件所有用户评论数应为0

用LEFT JOIN关联全量用户表和符合时间条件的评论数据，没有评论的用户会自动被统计为0条，不需要建临时表、不需要修改原始数据，单条SQL即可输出结果：

SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(c.user_id) AS comment_cnt
FROM users u -- 左表取全量用户，保证所有用户都被纳入统计
LEFT JOIN comments c
    ON u.id = c.user_id
    -- 关联阶段直接加时间过滤，不要把条件写在WHERE中，否则会过滤掉无评论用户
    AND c.Date >= '2019-01-01 00:00:00'
    AND c.Date < '2019-02-01 00:00:00'
GROUP BY u.id, u.name;

逻辑说明 #

• 左连接特性会保留左表（全量用户）的所有记录，右表（评论）匹配不到的字段会返回NULL
• COUNT(c.user_id)只会统计非NULL的记录，没有匹配到评论的用户计数自动为0，无需额外补0
• 时间条件写在ON子句而非WHERE子句：如果写在WHERE中，左连接生成的NULL记录会因为不满足时间条件被过滤，导致丢失无评论的用户
• 针对给出的样例数据，执行后会返回4个用户，所有人的comment_cnt均为0，符合实际数据情况

如果不需要单用户明细，要直接得到评论数对应的用户分布（x轴为评论数，y轴为对应用户数），可以在上述结果基础上再做一层聚合：

SELECT
    comment_cnt,
    COUNT(user_id) AS user_num
FROM (
    SELECT 
        u.id AS user_id,
        COUNT(c.user_id) AS comment_cnt
    FROM users u
    LEFT JOIN comments c
        ON u.id = c.user_id
        AND c.Date >= '2019-01-01 00:00:00'
        AND c.Date < '2019-02-01 00:00:00'
    GROUP BY u.id
) t
GROUP BY comment_cnt
ORDER BY comment_cnt;

注意：不要为了统计修改原始业务表数据，所有计算逻辑通过查询完成，避免破坏原始数据。

内容的提问来源于stack exchange，提问作者Sofía Contreras