如何用SQL全连接计算生日一个月内发帖用户占总用户数的比例

是否需要使用full join？ #

不需要。本次统计的分母是用户信息表的总用户数，仅需以用户信息表为左表，左连接网站发帖表即可满足需求。使用full join反而会引入发帖表中user_id不存在于用户表的脏数据，干扰统计结果，完全没有必要。

实现逻辑 #

1. 先处理日期格式转换，将字符串格式的生日、发帖日期转换为日期类型
2. 对每个用户的每一条发帖，判断是否落在用户生日前后30天范围内（这里兼顾跨年场景：比如生日1月5日，前一个月包含上一年12月5日之后的日期，只要日期间隔绝对值≤30或者≥335（即365-30）都符合要求）
3. 统计有至少1条符合要求发帖的用户数，除以总用户数得到目标比例

参考SQL（MySQL语法） #

SELECT 
    -- 符合要求的用户数/总用户数，得到目标占比
    COUNT(DISTINCT CASE WHEN 
        ABS(DATEDIFF(
            -- 将生日替换为发帖当年的日期，方便计算间隔
            STR_TO_DATE(CONCAT(YEAR(STR_TO_DATE(p.post_date, '%m/%d/%Y')), DATE_FORMAT(STR_TO_DATE(up.birthday, '%m/%d/%Y'), '-%m-%d')), '%Y-%m-%d'),
            STR_TO_DATE(p.post_date, '%m/%d/%Y')
        )) <= 30 
        OR 
        ABS(DATEDIFF(
            STR_TO_DATE(CONCAT(YEAR(STR_TO_DATE(p.post_date, '%m/%d/%Y')), DATE_FORMAT(STR_TO_DATE(up.birthday, '%m/%d/%Y'), '-%m-%d')), '%Y-%m-%d'),
            STR_TO_DATE(p.post_date, '%m/%d/%Y')
        )) >= 335
    THEN up.user_id END) / COUNT(DISTINCT up.user_id) AS target_ratio
FROM user_profile up
LEFT JOIN post p ON up.user_id = p.user_id

示例数据计算结果 #

你提供的示例中：

• 总用户数为3
• 仅用户12有符合要求的发帖记录
• 最终计算结果为 1/3 ≈ 0.333

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