SQL Server中对不连续学期编号分组并计算学年字段

原始数据 #

Term_order, name
1,Summer
2,Fall
3,Spring
4,Fall
5,Fall
6,Spring
7,Summer
8,Spring

需求说明 #

需要生成Year列，规则如下：

• 学年周期为 Summer → Fall → Spring，以Summer开头、Spring结尾；
• 若某学年无Summer，首个出现的Fall直接作为该学年起始；
• 连续无后续Spring的Fall，每个单独Fall视为独立学年（如第4、5行的Fall分别对应学年2、3）

期望输出 #

Term_order, name, year
1, Summer, 1
2, Fall, 1
3, Spring, 1
4, Fall, 2（注：学年2没有Summer，但Fall为首个学期，故标记为学年2）
5, Fall, 3
6, Spring, 3
7, Summer, 4
8, Spring, 4

之前尝试的无效方法 #

将Summer、Fall、Spring映射为1、2、3，基于term_num和term_nm创建排名，但因缺乏明确的学年分组逻辑，无法得到正确结果。

解决方案 #

SQL实现 #

核心是识别所有学年起始点，再通过累积计数生成Year：

WITH term_markers AS (
    SELECT 
        Term_order,
        name,
        CASE 
            -- Summer直接标记为新学年起始
            WHEN name = 'Summer' THEN 1
            -- 无前驱行的Fall，或前驱是Spring的Fall，标记为新学年起始
            WHEN name = 'Fall' AND (
                LAG(name) OVER (ORDER BY Term_order) IS NULL 
                OR LAG(name) OVER (ORDER BY Term_order) = 'Spring'
            ) THEN 1
            -- 连续Fall且后续不是Spring的，视为独立学年起始
            WHEN name = 'Fall' 
                 AND LAG(name) OVER (ORDER BY Term_order) = 'Fall'
                 AND LEAD(name) OVER (ORDER BY Term_order) != 'Spring' THEN 1
            ELSE 0
        END AS is_new_year
    FROM your_table_name
)
SELECT 
    Term_order,
    name,
    SUM(is_new_year) OVER (ORDER BY Term_order) AS year
FROM term_markers
ORDER BY Term_order;

Python Pandas实现 #

import pandas as pd

# 构造数据（或从文件读取）
df = pd.DataFrame({
    'Term_order': [1,2,3,4,5,6,7,8],
    'name': ['Summer','Fall','Spring','Fall','Fall','Spring','Summer','Spring']
})

# 标记学年起始点
df['is_new_year'] = 0
# Summer作为起始
df.loc[df['name'] == 'Summer', 'is_new_year'] = 1
# 无前驱或前驱是Spring的Fall作为起始
df.loc[(df['name'] == 'Fall') & (df['name'].shift(1).isna() | (df['name'].shift(1) == 'Spring')), 'is_new_year'] = 1
# 连续Fall且后续非Spring的，视为独立学年
df.loc[(df['name'] == 'Fall') & (df['name'].shift(1) == 'Fall') & (df['name'].shift(-1) != 'Spring'), 'is_new_year'] = 1

# 累积求和得到Year列
df['year'] = df['is_new_year'].cumsum()

# 展示结果
print(df[['Term_order', 'name', 'year']])

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