动态生成 SQL 列名实现年份自动透视表-Python教程-PHP中文网

动态生成 SQL 列名实现年份自动透视表

本文介绍如何在 Python 中动态构建 SQL 查询语句，将数据库中按年份存储的分数数据（如 name, year, score）自动转为宽表格式（每列为年份），并支持未来新增年份无需手动修改 SQL，最终导出为结构清晰的 Excel 表格。

本文介绍如何在 python 中动态构建 sql 查询语句，将数据库中按年份存储的分数数据（如 `name`, `year`, `score`）自动转为宽表格式（每列为年份），并支持未来新增年份无需手动修改 sql，最终导出为结构清晰的 excel 表格。

在处理时间序列类业务数据（如用户年度评分、销售统计等）时，常需将“长表”（name, year, score）转换为“宽表”（name, 2020, 2021, ..., TOTAL），尤其当年份范围随时间增长（如从 2016 持续扩展至 2024+）时，硬编码 CASE WHEN 语句不仅冗长易错，更违背可维护性原则。

解决方案的核心是：分离逻辑与结构——先动态获取数据库中所有有效年份，再基于该元数据自动生成符合当前数据范围的 PIVOT 式 SQL 查询。

以下为完整实现流程（使用标准 sqlite3 + pandas，适配 MySQL/PostgreSQL 仅需微调连接方式）：

import sqlite3
import pandas as pd

# 假设已建立数据库连接
conn = sqlite3.connect("scores.db")
cursor = conn.cursor()

# 步骤 1：动态获取全部年份（升序确保列顺序稳定）
cursor.execute("SELECT DISTINCT year FROM scores ORDER BY year")
years = cursor.fetchall()  # 返回 [(2020,), (2021,), ...]

if not years:
    raise ValueError("No year data found in table 'scores'")

# 步骤 2：构建动态 SELECT 子句
year_columns = [
    f"MAX(CASE WHEN year = {y[0]} THEN score END) AS '{y[0]}'"
    for y in years
]
year_sum_terms = [
    f"COALESCE(MAX(CASE WHEN year = {y[0]} THEN score END), 0)"
    for y in years
]

# 组装最终 SQL（含 TOTAL 列）
sql = f"""
SELECT 
    name,
    {', '.join(year_columns)},
    {' + '.join(year_sum_terms)} AS total
FROM scores
GROUP BY name
ORDER BY name;
"""

# 步骤 3：执行查询并加载为 DataFrame
cursor.execute(sql)
result = cursor.fetchall()

# 构建列名列表：['name', '2020', '2021', ..., 'total']
columns = ['name'] + [str(y[0]) for y in years] + ['total']
df = pd.DataFrame(result, columns=columns)

# 步骤 4：导出 Excel（需安装 openpyxl）
df.to_excel("annual_scores_pivot.xlsx", index=False)
print("✅ Excel 文件已生成：annual_scores_pivot.xlsx")

✅ 关键优势说明：

零人工维护：每年新增记录后，下次运行脚本自动识别新 year 并扩展列；
安全防错：使用 COALESCE(..., 0) 确保缺失年份显示为 0（非 NULL），保障 TOTAL 计算正确；
兼容性强：SQL 片段基于标准 ANSI SQL，适用于 SQLite、MySQL、PostgreSQL 等主流数据库（注意：SQL Server 推荐用 PIVOT，但此方案仍通用）；
可扩展友好：如需添加 AVG、COUNT 等聚合指标，只需在 SELECT 中追加对应动态表达式。