MySQL如何按每月第N个周几分组统计过去一年各月会话量

现有存储客户会话到访时间的sessions表，需要制作图表对比过去一年的会话总量。原有统计逻辑为按每月固定日历日期统计各月会话量，现在需要调整为按「每月第N个周几」维度统计，实现不同月份相同的「第N个周几」数据归为同一行，比如对比每个月第一个周一、第二个周六的会话量。

SELECT
  DAY(`SessionDate`) as month_day, 
  SUM(if(MONTH(SessionDate) =  1, 1, 0))  AS Jan,
  SUM(if(MONTH(SessionDate) =  2, 1, 0))  AS Feb,
  SUM(if(MONTH(SessionDate) =  3, 1, 0))  AS Mar,
  SUM(if(MONTH(SessionDate) =  4, 1, 0))  AS Apr,
  SUM(if(MONTH(SessionDate) =  5, 1, 0))  AS May,
  SUM(if(MONTH(SessionDate) =  6, 1, 0))  AS Jun,
  SUM(if(MONTH(SessionDate) =  7, 1, 0))  AS Jul,
  SUM(if(MONTH(SessionDate) =  8, 1, 0))  AS Aug,
  SUM(if(MONTH(SessionDate) =  9, 1, 0))  AS Sep,
  SUM(if(MONTH(SessionDate) = 10, 1, 0))  AS 'Oct',
  SUM(if(MONTH(SessionDate) = 11, 1, 0))  AS 'Nov',
  SUM(if(MONTH(SessionDate) = 12, 1, 0))  AS 'Dec'
FROM sessions
WHERE
`SessionDate` >= NOW()-Interval 12 MONTH
GROUP BY DAY(`SessionDate`)
ORDER BY DAY(SessionDate)

-----------------------------...
|weekday  | Jan | Feb | Mar |...
-----------------------------...
| 1st Mon |     |     |     |...
| 1st Tue |     |  2  |     |...
| 1st Wed |     |  3  |  1  |...
| 1st Thu |     |  4  |  0  |...
| 1st Fri |  1  |  4  |  1  |...
| 1st Sat |  2  |  3  |  1  |...
| 1st Sun |  0  |  1  |  2  |...
| 2nd Mon |  0  |  0  |  3  |...
| 2nd Tue |  4  |  2  |  4  |...
|...      |... |... |... |...

核心逻辑是给每条会话的到访日期计算两个属性：该日期属于当月的第几个周（周序）、该日期是周几，按两个属性分组统计即可实现需求，无需在PHP层做额外对齐处理。
以下为MySQL环境下的改写SQL：

SELECT
  -- 拼接最终显示的维度名称
  CONCAT(
    -- 计算周序：1-7号为第1周，8-14为第2周，以此类推
    (DAY(`SessionDate`) - 1) DIV 7 + 1,
    -- 拼接序数后缀
    CASE (DAY(`SessionDate`) - 1) DIV 7 + 1
      WHEN 1 THEN 'st '
      WHEN 2 THEN 'nd '
      WHEN 3 THEN 'rd '
      ELSE 'th '
    END,
    -- 映射周几名称，WEEKDAY返回值0=周一、6=周日
    ELT(WEEKDAY(`SessionDate`)+1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun')
  ) AS `weekday`,
  -- 各月统计逻辑保持不变
  SUM(IF(MONTH(`SessionDate`) = 1, 1, 0)) AS Jan,
  SUM(IF(MONTH(`SessionDate`) = 2, 1, 0)) AS Feb,
  SUM(IF(MONTH(`SessionDate`) = 3, 1, 0)) AS Mar,
  SUM(IF(MONTH(`SessionDate`) = 4, 1, 0)) AS Apr,
  SUM(IF(MONTH(`SessionDate`) = 5, 1, 0)) AS May,
  SUM(IF(MONTH(`SessionDate`) = 6, 1, 0)) AS Jun,
  SUM(IF(MONTH(`SessionDate`) = 7, 1, 0)) AS Jul,
  SUM(IF(MONTH(`SessionDate`) = 8, 1, 0)) AS Aug,
  SUM(IF(MONTH(`SessionDate`) = 9, 1, 0)) AS Sep,
  SUM(IF(MONTH(`SessionDate`) = 10, 1, 0)) AS Oct,
  SUM(IF(MONTH(`SessionDate`) = 11, 1, 0)) AS Nov,
  SUM(IF(MONTH(`SessionDate`) = 12, 1, 0)) AS `Dec`
FROM sessions
WHERE `SessionDate` >= NOW() - INTERVAL 12 MONTH
-- 按周序、周几分组
GROUP BY 
  (DAY(`SessionDate`) - 1) DIV 7 + 1,
  WEEKDAY(`SessionDate`)
-- 先按周序升序，再按周几升序，符合预期输出顺序
ORDER BY
  (DAY(`SessionDate`) - 1) DIV 7 + 1 ASC,
  WEEKDAY(`SessionDate`) ASC

注意事项 #

• 若统计的12个月跨年度，可在WHERE条件中补充年份限制，或把月份统计字段调整为带年份的格式，避免不同年份的同月份数据合并
• 若你使用的数据库周几函数返回规则和MySQL不一致，调整周几映射逻辑即可
• 部分月份不存在第5个周几的情况，对应统计值会自动返回0，无需额外处理

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