如何为学生各学期主修专业生成顺序排名：转回原专业计为新排名

问题背景 #

现有学生专业学期数据集，包含StudID（学生ID）、Major（主修专业）、Term（学期）字段。需要实现：当学生转回曾经主修过的专业时，该专业需被视为新的主修阶段，并按变更顺序分配排名。

示例输入 #

StudID   Major       Term
12345    History     202101
12345    History     202102
12345    Management  202201
12345    History     202202

期望输出 #

StudID   Major       Term    MajorNumber
12345    History     202101  1
12345    History     202102  1
12345    Management  202201  2
12345    History     202202  3

原有方案的问题 #

之前尝试通过提取每个专业的最早学期，再按学生ID内的专业首次出现时间排名，这种方法会将第二次出现的History仍标记为1，无法满足“转回旧专业算新阶段”的需求——我们需要的是按专业变更的先后顺序排名，而非专业首次出现的时间。

解决方案 #

核心思路是：识别每个学生的专业连续阶段，当专业发生变更时（包括转回旧专业），视为新阶段，然后对阶段进行累计计数。

SQL实现（标准SQL） #

WITH stage_markers AS (
    SELECT 
        StudID,
        Major,
        Term,
        -- 对比当前学期与上一学期的专业，不同则标记为新阶段
        CASE 
            WHEN LAG(Major) OVER (PARTITION BY StudID ORDER BY Term) != Major 
            THEN 1 
            ELSE 0 
        END AS is_new_stage
    FROM student_majors
)
SELECT 
    StudID,
    Major,
    Term,
    -- 累计新阶段标记，加1得到最终排名（第一个阶段从1开始）
    SUM(is_new_stage) OVER (PARTITION BY StudID ORDER BY Term) + 1 AS MajorNumber
FROM stage_markers
ORDER BY StudID, Term;

Pandas实现 #

import pandas as pd

# 加载数据（示例）
df = pd.DataFrame({
    'StudID': [12345, 12345, 12345, 12345],
    'Major': ['History', 'History', 'Management', 'History'],
    'Term': [202101, 202102, 202201, 202202]
})

# 按学生ID和学期排序
df = df.sort_values(['StudID', 'Term'])

# 标记专业变更的行
df['is_new_stage'] = df.groupby('StudID')['Major'].apply(lambda x: x != x.shift()).astype(int)

# 计算阶段排名
df['MajorNumber'] = df.groupby('StudID')['is_new_stage'].cumsum() + 1

# 输出结果
print(df[['StudID', 'Major', 'Term', 'MajorNumber']])

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