如何在MySQL中为会话数据生成循环会话序号与年份标识

假设你的会话数据表结构如下（以user_sessions为例）：

CREATE TABLE user_sessions (
    user_id INT,
    session_date DATE,
    PRIMARY KEY (user_id, session_date)
);

先插入测试数据验证：

-- 用户1：2022-01-15开始，8条季度记录
INSERT INTO user_sessions VALUES
(1, '2022-01-15'), (1, '2022-04-15'), (1, '2022-07-15'), (1, '2022-10-15'),
(1, '2023-01-15'), (1, '2023-04-15'), (1, '2023-07-15'), (1, '2023-10-15');

-- 用户2：2021-10-15开始，8条季度记录
INSERT INTO user_sessions VALUES
(2, '2021-10-15'), (2, '2022-01-15'), (2, '2022-04-15'), (2, '2022-07-15'),
(2, '2022-10-15'), (2, '2023-01-15'), (2, '2023-04-15'), (2, '2023-07-15');

核心实现（MySQL 8.0+ 推荐方案） #

利用窗口函数ROW_NUMBER()先按用户分组、日期排序生成行号，再通过数学计算得到循环的会话序号和年份标识：

SELECT
    user_id,
    session_date,
    CONCAT('session', MOD(row_num - 1, 4) + 1) AS session_seq,
    CEIL(row_num / 4) AS year_label
FROM (
    SELECT
        user_id,
        session_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_date) AS row_num
    FROM user_sessions
) AS ranked_sessions;

逻辑解释 #

• 行号生成：ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY session_date) 为每个用户的会话记录按日期顺序分配唯一行号，确保排序正确。
• 循环会话序号：MOD(row_num - 1, 4) + 1 实现1-4的循环：
  • 行号1 → (1-1)%4+1=1 → session1
  • 行号4 → (4-1)%4+1=4 → session4
  • 行号5 → (5-1)%4+1=1 → session1，以此类推。
• 年份标识：CEIL(row_num / 4) 每4条记录递增1：
  • 行号1-4 → CEIL(1/4)=1、CEIL(4/4)=1 → year 1
  • 行号5-8 → CEIL(5/4)=2、CEIL(8/4)=2 → year 2，以此类推。

兼容MySQL 5.x版本的方案（无窗口函数） #

如果你的MySQL版本低于8.0，可使用用户变量实现分组排序和行号计算：

SELECT
    user_id,
    session_date,
    CONCAT('session', MOD(row_num - 1, 4) + 1) AS session_seq,
    CEIL(row_num / 4) AS year_label
FROM (
    SELECT
        user_id,
        session_date,
        @row_num := IF(@current_user = user_id, @row_num + 1, 1) AS row_num,
        @current_user := user_id
    FROM user_sessions,
         (SELECT @current_user := NULL, @row_num := 0) AS init_vars
    ORDER BY user_id, session_date
) AS ranked_sessions;

逻辑解释 #

• 初始化变量@current_user（跟踪当前用户）和@row_num（行号计数器）。
• 按user_id和session_date排序后，若当前用户与上一条相同，行号+1；否则重置为1。
• 后续的会话序号和年份标识计算逻辑与8.0+版本一致。

执行上述查询后，即可得到符合需求的结果，无论用户从哪个季度开始，都能正确生成循环的会话序号和递增的年份标识。

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