如何基于月份动态生成列名？DB2数据透视方案咨询

我在DB2数据库中有一张表，结构与数据如下：

我希望得到按category、year、month组合的月度支出列，格式如下：

我可以使用SQL和Python，目前想到的方法是用CASE WHEN语句，但硬编码的条件在每次运行（需要覆盖过去24个月数据的临时任务）时都要手动更新，代码如下：

SELECT      CUSTOMER_ID,
            SUM(CASE WHEN CATEGORY = 'A' AND YEAR = '2025' AND MONTH = '06' THEN SPEND ELSE 0 END) AS A_SPEND_2025_12,
            SUM(CASE WHEN CATEGORY = 'A' AND YEAR = '2026' AND MONTH = '05' THEN SPEND ELSE 0 END) AS A_SPEND_2026_05,
            SUM(CASE WHEN CATEGORY = 'A' AND YEAR = '2026' AND MONTH = '06' THEN SPEND ELSE 0 END) AS A_SPEND_2026_06,
            SUM(CASE WHEN CATEGORY = 'B' AND YEAR = '2026' AND MONTH = '06' THEN SPEND ELSE 0 END) AS B_SPEND_2026_06 
FROM        MY_TABLE        
GROUP BY    CUSTOMER_ID

请问实现该需求的最佳方式是什么？

方案一：DB2动态SQL生成（纯SQL方式） #

利用DB2的动态SQL能力，自动生成过去24个月的CASE WHEN语句，避免硬编码。

步骤1：生成动态列定义 #

先查询过去24个月内所有唯一的category-year-month组合，并生成对应的CASE WHEN片段：

SELECT DISTINCT 
    CONCAT(CATEGORY, '_spend_', YEAR, '_', MONTH) AS col_name,
    CONCAT('SUM(CASE WHEN CATEGORY = ''', CATEGORY, ''' AND YEAR = ''', YEAR, ''' AND MONTH = ''', MONTH, ''' THEN SPEND ELSE 0 END) AS ', CONCAT(CATEGORY, '_spend_', YEAR, '_', MONTH)) AS case_stmt
FROM MY_TABLE
WHERE DATE >= CURRENT_DATE - 24 MONTHS

步骤2：拼接完整动态SQL #

用LISTAGG函数将所有CASE WHEN片段拼接成完整的查询语句：

WITH dynamic_cols AS (
    SELECT DISTINCT 
        CONCAT('SUM(CASE WHEN CATEGORY = ''', CATEGORY, ''' AND YEAR = ''', YEAR, ''' AND MONTH = ''', MONTH, ''' THEN SPEND ELSE 0 END) AS ', CONCAT(CATEGORY, '_spend_', YEAR, '_', MONTH)) AS case_stmt
    FROM MY_TABLE
    WHERE DATE >= CURRENT_DATE - 24 MONTHS
)
SELECT 'SELECT CUSTOMER_ID, ' || LISTAGG(case_stmt, ', ') || ' FROM MY_TABLE WHERE DATE >= CURRENT_DATE - 24 MONTHS GROUP BY CUSTOMER_ID' AS dynamic_sql
FROM dynamic_cols

执行该查询会得到可直接运行的完整SQL，自动适配过去24个月的所有组合。

方案二：Python辅助实现 #

如果更习惯用Python，可以选择生成动态SQL或直接用Pandas处理数据。

方式A：Python生成动态SQL并执行 #

import ibm_db_dbi
import pandas as pd

# 连接DB2数据库（替换为你的数据库信息）
conn = ibm_db_dbi.connect(
    "DATABASE=your_db;HOSTNAME=your_host;PORT=your_port;PROTOCOL=TCPIP;UID=your_user;PWD=your_pwd;",
    "", ""
)
cursor = conn.cursor()

# 查询过去24个月的唯一category-year-month组合
cursor.execute("""
    SELECT DISTINCT CATEGORY, YEAR, MONTH
    FROM MY_TABLE
    WHERE DATE >= CURRENT_DATE - 24 MONTHS
""")
cols = cursor.fetchall()

# 生成CASE WHEN语句片段
case_stmts = []
for cat, yr, mn in cols:
    col_name = f"{cat}_spend_{yr}_{mn}"
    stmt = f"SUM(CASE WHEN CATEGORY = '{cat}' AND YEAR = '{yr}' AND MONTH = '{mn}' THEN SPEND ELSE 0 END) AS {col_name}"
    case_stmts.append(stmt)

# 拼接并执行完整SQL
final_sql = f"""
SELECT CUSTOMER_ID, {', '.join(case_stmts)}
FROM MY_TABLE
WHERE DATE >= CURRENT_DATE - 24 MONTHS
GROUP BY CUSTOMER_ID
"""
result_df = pd.read_sql(final_sql, conn)
print(result_df)

# 关闭连接
conn.close()

方式B：Pandas透视表直接处理数据 #

如果数据量不大，可直接拉取数据后用透视表转换格式：

import ibm_db_dbi
import pandas as pd

# 连接数据库（替换为你的信息）
conn = ibm_db_dbi.connect(
    "DATABASE=your_db;HOSTNAME=your_host;PORT=your_port;PROTOCOL=TCPIP;UID=your_user;PWD=your_pwd;",
    "", ""
)

# 拉取过去24个月的数据
raw_df = pd.read_sql("""
    SELECT customer_id, category, year, month, spend
    FROM MY_TABLE
    WHERE DATE >= CURRENT_DATE - 24 MONTHS
""", conn)

# 生成目标列名
raw_df['col_name'] = raw_df['category'] + '_spend_' + raw_df['year'] + '_' + raw_df['month']

# 生成透视表
pivot_df = raw_df.pivot_table(
    index='customer_id',
    columns='col_name',
    values='spend',
    aggfunc='sum',
    fill_value=0
).reset_index()

print(pivot_df)
conn.close()

方案选择建议 #

• 数据量较大时优先用DB2动态SQL，避免大量数据传输，效率更高。
• 需要后续分析或更熟悉Python时，用Pandas透视表更灵活。

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