如何在SQL中按ID分区实现带历史值的动态表透视？

假设我有如下表格：

我希望对该表进行转换，使得每个id对应的每行数据都将前5个历史value值作为列，转换后的目标表格如下：

请问是否存在一种优雅的动态方法来生成这个目标表格？

可以用Python的pandas库实现完全动态的转换，核心逻辑是按id分组后批量生成指定数量的历史滞后列，步骤如下：

1. 数据预处理（确保顺序正确） #

首先将日期转为时间格式，并按id和日期排序，保证历史值的顺序符合时间逻辑：

import pandas as pd

# 构造原始数据
df = pd.DataFrame({
    '日期': ['01/01/2022', '02/01/2022', '03/01/2022', '04/01/2022', '01/01/2022', '01/01/2022', '02/01/2022'],
    'id': [1, 1, 1, 1, 2, 4, 4],
    'value': [5, 2, 0, 9, 10, 4, 9]
})

# 转换日期格式并排序
df['日期'] = pd.to_datetime(df['日期'], format='%d/%m/%Y')
df = df.sort_values(['id', '日期']).reset_index(drop=True)

2. 动态生成历史滞后列 #

通过定义需要保留的历史值数量（此处为5），批量生成滞后列并合并到原数据：

# 定义需要生成的历史值列数量
n_lags = 5

# 按id分组，批量生成1到n_lags期的滞后列
lag_columns = pd.concat(
    [df.groupby('id')['value'].shift(i) for i in range(1, n_lags+1)],
    axis=1
)

# 重命名列名
lag_columns.columns = [f'value{i}' for i in range(1, n_lags+1)]

# 合并原始数据与滞后列，保留目标字段
result = pd.concat([df[['id', 'value']], lag_columns], axis=1)

# 将空值替换为None，匹配目标表格显示
result = result.where(pd.notnull(result), None)

方案优势 #

• 完全动态：只需修改n_lags参数，即可生成任意数量的历史值列，无需硬编码每一列
• 逻辑高效：利用pandas的分组与向量操作，避免逐行循环的低效写法
• 扩展性强：可直接适配更大规模的数据集，或调整分组、排序规则

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