本文介绍如何在 python 中动态构建 sql 查询语句，将数据库中按年份存储的分数数据自动转为宽表格式（如 2020、2021…列），并计算每行总计，彻底避免硬编码年份，适配未来新增年份。
本文介绍如何在 python 中动态构建 sql 查询语句，将数据库中按年份存储的分数数据自动转为宽表格式（如 2020、2021…列），并计算每行总计，彻底避免硬编码年份，适配未来新增年份。
在处理时间序列类业务数据（如用户年度评分、销售记录等）时，常需将“长表”（name, year, score）转换为“宽表”（name, 2020, 2021, ..., TOTAL）以满足报表或 Excel 导出需求。若年份范围随业务增长持续扩展（例如从 2016 年起逐年新增），手动维护 CASE WHEN 语句不仅低效，还极易出错。解决方案是：在 Python 层动态查询年份元数据，并据此生成标准 SQL Pivot 查询。
✅ 核心思路
-
先查年份维度：执行
SELECT DISTINCT year FROM scores ORDER BY year获取当前所有有效年份； -
动态拼接 SELECT 子句：为每个年份生成
MAX(CASE WHEN year = ? THEN score END) AS '202X'； -
动态构建 TOTAL 表达式：用
COALESCE(..., 0)防空值，再求和； - 执行并加载为 DataFrame：便于后续导出 Excel 或进一步分析。
? 完整可运行示例代码
import sqlite3 # 或 pymysql/psycopg2 等对应数据库驱动
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：动态生成各年份列 + TOTAL 列
year_columns = [
f"MAX(CASE WHEN year = {y[0]} THEN score END) AS '{y[0]}'"
for y in years
]
total_expr = " + ".join([
f"COALESCE(MAX(CASE WHEN year = {y[0]} THEN score END), 0)"
for y in years
])
# 步骤3：组装最终 SQL
sql = f"""
SELECT
name,
{', '.join(year_columns)},
{total_expr} AS total
FROM scores
GROUP BY name
ORDER BY name;
"""
# 步骤4：执行查询并转为 DataFrame
cursor.execute(sql)
rows = cursor.fetchall()
column_names = ["name"] + [str(y[0]) for y in years] + ["total"]
df = pd.DataFrame(rows, columns=column_names)
# ✅ 输出验证
print(df)
# name 2020 2021 2022 2023 2024 total
# 0 Alex 14 1 13 13 10 51
# 1 Andi 40 60 55 55 49 259
# ✅ 导出 Excel（需安装 openpyxl）
df.to_excel("annual_scores_pivot.xlsx", index=False)
⚠️ 注意事项与最佳实践
-
SQL 注入防护：本方案中
year来自数据库DISTINCT查询（非用户输入），因此直接拼接是安全的；若年份来源不可信，请改用参数化占位符（但多数数据库不支持?用于列别名，故仍推荐元数据可信前提下的字符串拼接）。 -
NULL 处理：使用
COALESCE(..., 0)确保缺失年份计为 0，避免NULL + 数字 = NULL导致总计异常。 -
性能优化：对
year字段建立索引（CREATE INDEX idx_scores_year ON scores(year);）可显著提升DISTINCT和GROUP BY效率。 -
扩展性增强：如需支持多指标（如
score,count,avg_time），可封装为函数，传入指标字段名列表，批量生成各列逻辑。 -
兼容性提示：上述 SQL 使用标准 ANSI SQL 的
CASE WHEN，兼容 SQLite、MySQL、PostgreSQL、SQL Server；若用 Oracle，可替换为DECODE，但动态生成逻辑不变。
通过该方法，无论明年新增 2025 还是回溯补录 2016 数据，只需重新运行脚本，即可全自动适配最新年份结构——真正实现“一次开发，永久生效”的健壮数据管道。
