MySQL按日-小时分组统计分钟行数：各小时计数相同问题

嘿，这个问题我熟！你的查询之所以每个小时都显示100%在线，核心原因是没把stub表的所有小时都完整关联进来——当前的查询只统计了有activity记录的小时，那些玩家离线、没有activity条目的小时根本没出现在结果里，反而可能因为关联逻辑的问题，把有数据的小时数值重复填充到了所有行里。

核心思路 #

我们需要用**左连接（LEFT JOIN）**把stub表（包含0-23小时）和activity表关联，确保stub里的24个小时都会被保留；同时处理没有activity记录的情况，把在线分钟数设为0，这样就能得到真实的在线百分比。

调整后的查询示例 #

假设你的stub表名为hour_stub（字段为hour，值0-23），activity表包含player、log_day、log_hour、log_minute字段，以下是针对单玩家单日期的查询：

SELECT
    a.player AS Player,
    a.log_day AS Day,
    s.hour AS Hour,
    -- 没有记录时用0填充在线分钟数
    COALESCE(COUNT(DISTINCT a.log_minute), 0) AS Minutes,
    -- 计算在线百分比，保留两位小数
    ROUND((COALESCE(COUNT(DISTINCT a.log_minute), 0) / 60) * 100, 2) AS `% online`
FROM hour_stub s
-- 左连接保证stub的所有小时都被保留
LEFT JOIN activity a
    ON s.hour = a.log_hour
    AND a.log_day = 27 -- 替换为你要查询的日期，或改为参数
    AND a.player = 'player1' -- 替换为目标玩家，或去掉此条件后分组
GROUP BY a.player, a.log_day, s.hour
ORDER BY a.player, a.log_day, s.hour;

如果需要查询所有玩家的所有日期，可以用CROSS JOIN生成玩家-日期的全组合，再关联小时stub：

SELECT
    pd.player AS Player,
    pd.log_day AS Day,
    s.hour AS Hour,
    COALESCE(COUNT(DISTINCT a.log_minute), 0) AS Minutes,
    ROUND((COALESCE(COUNT(DISTINCT a.log_minute), 0) / 60) * 100, 2) AS `% online`
FROM hour_stub s
-- 生成所有玩家-日期的唯一组合
CROSS JOIN (SELECT DISTINCT player, log_day FROM activity) pd
LEFT JOIN activity a
    ON s.hour = a.log_hour
    AND a.player = pd.player
    AND a.log_day = pd.log_day
GROUP BY pd.player, pd.log_day, s.hour
ORDER BY pd.player, pd.log_day, s.hour;

关键细节解释 #

• LEFT JOIN的作用：强制保留stub表的所有24小时记录，哪怕对应小时没有玩家的activity数据，这是解决问题的核心。
• COALESCE函数：把COUNT返回的NULL（无activity记录时）转换成0，确保离线小时的分钟数显示为0，百分比为0%。
• COUNT(DISTINCT a.log_minute)：因为activity表每分钟一条在线记录，统计不同的分钟数能准确得到该小时的在线时长（避免重复记录干扰）。
• 分组逻辑：必须把stub表的hour字段加入分组，确保每个小时单独统计，不会出现数值重复填充的问题。

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