SQL按日期分组取最大消息收发比及对应用户结果错误排查

逻辑错误根因 #

你写的SQL不符合SQL标准的GROUP BY语义要求：当你按ds.date分组时，SELECT子句中仅允许出现分组键（即date字段）和聚合函数（即max(ratio)），你额外选择的ds.user_name不属于上述两类，除了关闭严格模式的旧版MySQL外，绝大多数SQL方言会直接抛出语法错误。
即使在允许这种非法写法的MySQL环境中，数据库也不会自动匹配与最大ratio对应的用户，只会从当前日期分组的所有用户里随机取一个返回，这就是你得到的比值正确但用户错误的核心原因。
另外你内层子查询的group by a.user_name, a.date是多余操作，假设messages表单条记录的粒度就是单用户单日的统计数据，不需要额外分组即可直接计算比值。

注意：如果存在message_received为0的记录，需要提前加过滤条件避免出现除以0的报错

方案1：窗口函数实现（推荐，兼容MySQL8+、PostgreSQL、Hive、Spark SQL等绝大多数主流SQL引擎） #

用ROW_NUMBER()按日期分组后对比值倒序排序，取每个分组排序第一的记录即可：

SELECT date, user_name, ratio
FROM (
    SELECT 
        user_name,
        date,
        message_sent / message_received AS ratio,
        ROW_NUMBER() OVER (PARTITION BY date ORDER BY message_sent / message_received DESC) AS rn
    FROM messages
) t
WHERE rn = 1;

如果需要处理同一天多个用户比值相同且均为最大值的场景，把ROW_NUMBER()替换为RANK()即可返回所有符合条件的用户。

方案2：子查询关联实现（兼容不支持窗口函数的老版本SQL引擎） #

SELECT 
    a.user_name,
    a.date,
    a.message_sent / a.message_received AS ratio
FROM messages a
WHERE (a.date, a.message_sent / a.message_received) IN (
    SELECT date, MAX(message_sent / message_received)
    FROM messages
    GROUP BY date
);

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