如何在MySQL中按月份和ID分组求first_hour与last_hour的众数？

核心思路 #

先对month+id分组后的first_hour/last_hour统计出现频率，再通过排序规则确定最终众数——频率最高的优先；若多个值频率相同，按指定备选规则（如取最值、最早出现值等）筛选。

通用SQL实现（以MySQL为例） #

以下代码同时处理first_hour和last_hour的众数计算，默认备选规则为频率相同时取最小的hour值，可根据需求调整排序逻辑。

WITH first_hour_stats AS (
    -- 统计每个分组内first_hour的出现频率，并按规则排序
    SELECT 
        month,
        id,
        first_hour,
        COUNT(*) AS occur_count,
        ROW_NUMBER() OVER (
            PARTITION BY month, id 
            ORDER BY COUNT(*) DESC, first_hour ASC -- 先按频率降序，再按hour升序（备选规则）
        ) AS rank_num
    FROM your_table
    GROUP BY month, id, first_hour
),
last_hour_stats AS (
    -- 同理统计last_hour的频率和排序
    SELECT 
        month,
        id,
        last_hour,
        COUNT(*) AS occur_count,
        ROW_NUMBER() OVER (
            PARTITION BY month, id 
            ORDER BY COUNT(*) DESC, last_hour ASC
        ) AS rank_num
    FROM your_table
    GROUP BY month, id, last_hour
)
-- 合并两个字段的众数结果
SELECT 
    f.month,
    f.id,
    f.first_hour AS first_hour_mode,
    l.last_hour AS last_hour_mode
FROM first_hour_stats f
INNER JOIN last_hour_stats l 
    ON f.month = l.month AND f.id = l.id
WHERE f.rank_num = 1 AND l.rank_num = 1;

备选规则调整示例 #

如果多众数备选方案不是取最小值，可修改ORDER BY子句：

1. 取最大的hour值：将first_hour ASC改为first_hour DESC
2. 取最早出现的hour值：需要先记录每个hour在分组内的首次出现时间，再加入排序逻辑：

-- 调整first_hour_stats的CTE，加入首次出现时间
WITH first_hour_first_occur AS (
    SELECT 
        month,
        id,
        first_hour,
        MIN(your_timestamp_column) AS first_occur_time -- 替换为你表中的时间字段
    FROM your_table
    GROUP BY month, id, first_hour
),
first_hour_stats AS (
    SELECT 
        fo.month,
        fo.id,
        fo.first_hour,
        COUNT(t.first_hour) AS occur_count,
        ROW_NUMBER() OVER (
            PARTITION BY fo.month, fo.id 
            ORDER BY COUNT(t.first_hour) DESC, fo.first_occur_time ASC
        ) AS rank_num
    FROM first_hour_first_occur fo
    JOIN your_table t 
        ON fo.month = t.month AND fo.id = t.id AND fo.first_hour = t.first_hour
    GROUP BY fo.month, fo.id, fo.first_hour, fo.first_occur_time
)
-- 后续查询逻辑不变

其他数据库适配提示 #

• PostgreSQL：可直接使用MODE() WITHIN GROUP (ORDER BY first_hour)获取众数，但默认返回第一个遇到的众数；若要自定义规则，仍需用窗口函数实现。
• SQL Server：逻辑与MySQL一致，窗口函数语法通用，仅需保证CTE支持（SQL Server 2008及以上支持）。

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