如何将按天分组的近3个月注册用户SQL查询改为按周分组？

修改后的SQL查询 #

SELECT 
  -- 生成每周的周一作为分组标识，适配折线图展示
  STR_TO_DATE(CONCAT(YEAR(user_registered), '-', WEEK(user_registered), '-1'), '%X-%V-%w') AS week_start,
  COUNT(DISTINCT wp_users.id) AS subscriber_count
FROM wp_users
WHERE EXISTS (
  SELECT 1 
  FROM wp_usermeta 
  WHERE wp_usermeta.user_id = wp_users.id 
    AND wp_usermeta.meta_value LIKE '%subscriber%'
)
AND user_registered >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
AND user_registered < NOW()
GROUP BY week_start
ORDER BY week_start;

关键修改说明 #

• 按周分组逻辑：用STR_TO_DATE结合年、周数生成每周的起始日期（周一），折线图的X轴能显示清晰的时间节点，比单纯的年周字符串更直观。如果只需要年周格式（如2024-32），可以替换为DATE_FORMAT(user_registered, '%Y-%u') AS year_week，同时GROUP BY对应字段即可。
• 避免重复计数：原JOIN写法可能因wp_usermeta存在多条匹配记录导致用户被重复统计，改用COUNT(DISTINCT wp_users.id)或EXISTS子查询过滤订阅用户，确保每个用户仅被统计一次。
• 条件优化：直接用user_registered >= DATE_SUB(...)代替DATE(user_registered) > ...，减少函数调用，提升查询效率，同时逻辑等价。

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