MySQL使用GROUP BY结合JSON_ARRAYAGG实现用户周维度访问统计聚合查询

实现思路 #

通过两层分组聚合+MySQL JSON函数即可实现需求：

1. 内层先按user+week分组，统计每个用户单周的访问指标
2. 外层按user分组，统计用户全周期的总指标，同时将每周的统计结果拼接为要求的JSON数组格式

完整SQL语句 #

SELECT
  user,
  SUM(week_visit_1) AS visit_1,
  SUM(week_total) AS visit_total,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'week', week,
      'visit_1', week_visit_1,
      'visit_total', week_total
    ) 
    ORDER BY week ASC -- 保证周维度按升序排列，符合常规阅读习惯
  ) AS weekly_history
FROM (
  -- 内层子查询：按用户+周粒度统计单周指标
  SELECT 
    user,
    week,
    COUNT(CASE WHEN visit = 1 THEN 1 END) AS week_visit_1,
    COUNT(*) AS week_total
  FROM table1
  GROUP BY user, week
) AS user_week_stat
GROUP BY user;

补充说明 #

• 该方案适配MySQL 5.7及以上版本（该版本开始正式支持JSON_OBJECT、JSON_ARRAYAGG等JSON处理函数）
• 如果你使用的是不支持JSON函数的低版本MySQL，可以改用GROUP_CONCAT手动拼接字符串实现JSON数组结构，注意做好特殊字符转义即可

内容的提问来源于stack exchange，提问作者Александр Грешников