MySQL如何按小时粒度分组用户通知并合并同content_type内容？

实现结论 #

该需求不需要重构表结构，可以直接在MySQL 8.0及以上版本中通过窗口函数+聚合查询实现，仅在高并发、大数据量场景下建议配套优化索引或存储结构。

规则对齐（基于你提供的样例） #

你的合并逻辑可拆解为：

• 触发合并的分组维度：同一接收人user_id下，记录的date_added落在同一自然小时窗口内、且content_type值相同
• 合并边界：每个分组内按记录id（或时间正序）排序后，仅最早的2条记录参与合并；分组内第3条及之后的所有记录，不参与合并流程，直接作为独立行输出
• 合并后的字段取值规则：
  • content字段：将参与合并的2条记录的原值用分号拼接
  • 其余字段（id、sender_id、text、date_added、locale等）：取分组内最新一条记录的对应值

实现步骤 #

1. 给每条记录打组内序号
   用窗口函数按分组维度做分区，组内按id升序生成排序序号，同时统计每个分组的总记录数，为后续拆分做准备：

   WITH grouped_record AS (
     SELECT 
       *,
       -- 组内按ID升序的排序号
       ROW_NUMBER() OVER (
         PARTITION BY user_id, content_type, DATE_FORMAT(date_added, '%Y-%m-%d %H:00:00')
         ORDER BY id ASC
       ) AS row_rank,
       -- 统计当前组的总记录数
       COUNT(*) OVER (
         PARTITION BY user_id, content_type, DATE_FORMAT(date_added, '%Y-%m-%d %H:00:00')
       ) AS group_total
     FROM notification_table
   )
   

2. 拆分两部分数据做合并输出

   • 第一部分：处理每个分组的前2条记录，如果组内总记录数≥2，就把前2条的content做拼接，取最新记录的所有字段；如果组内只有1条，直接输出即可
   • 第二部分：处理组内排序号≥3的记录，不做任何聚合，直接和第一部分结果拼接
     完整的查询逻辑参考：

   -- 前置配置：放宽GROUP_CONCAT的默认长度限制，避免长内容被截断
   SET SESSION group_concat_max_len = 102400;
   
   WITH grouped_record AS (
     SELECT 
       *,
       ROW_NUMBER() OVER (
         PARTITION BY user_id, content_type, DATE_FORMAT(date_added, '%Y-%m-%d %H:00:00')
         ORDER BY id ASC
       ) AS row_rank
     FROM notification_table
   )
   -- 第一部分：处理组内前2条的合并逻辑
   SELECT
     MAX(id) AS id,
     MAX(sender_id) AS sender_id,
     MAX(user_id) AS user_id,
     MAX(type) AS type,
     -- 拼接2条记录的content，用分号分隔
     GROUP_CONCAT(content SEPARATOR ';') AS content,
     MAX(content_type) AS content_type,
     -- 取最新记录的text值
     SUBSTRING_INDEX(GROUP_CONCAT(text ORDER BY date_added DESC SEPARATOR '|||'), '|||', 1) AS text,
     -- 取组内最新的时间
     MAX(date_added) AS date_added,
     MAX(locale) AS locale
   FROM grouped_record
   WHERE row_rank <=2
   GROUP BY user_id, content_type, DATE_FORMAT(date_added, '%Y-%m-%d %H:00:00')
   
   UNION ALL
   
   -- 第二部分：直接输出组内第3条及之后的所有独立记录
   SELECT
     id, sender_id, user_id, type, content, content_type, text, date_added, locale
   FROM grouped_record
   WHERE row_rank >=3
   ORDER BY date_added DESC;
   

如果你的MySQL版本低于8.0，没有窗口函数支持，就需要通过自关联的方式给每条记录计算组内排序号，写法更繁琐，性能也会更低，建议升级到8.0以上版本实现。

优化与表结构调整建议 #

• 基础优化：如果查询是高频执行的，在(user_id, content_type, date_added, id)字段上建立联合索引，避免全表扫描带来的性能损耗。
• 结构调整场景：如果合并后的通知需要持久化存储（而非查询时临时拼接），更建议在原表增加merged_group_id字段，给参与合并的记录标记同一个组ID，查询时直接按组聚合即可，不需要每次运行都做实时窗口计算，适合大数据量下的长期使用。

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