Python/SQL长表转宽表并生成全数据组合的高效实现方案

需求概述 #

需要将长格式的客户-月份-问题数据转换为宽格式，生成每个客户在各月份下所有可能的问题组合，最终统计不同组合模式的客户数量。

输入数据：

CUSTOMER    MONTH   ISSUE
1   M1  ABC
1   M1  DEF
1   M2  ABC
1   M3  QRS
2   M1  PQR
2   M2  PQR
2   M2  ABC
2   M3  DEF

期望输出（宽表组合）：

CUSTOMER    M1  M2  M3
1   ABC ABC QRS
1   DEF ABC QRS
2   PQR PQR DEF
2   PQR ABC DEF

现有问题：SQL自连接会因数据量庞大导致性能极差；常规Pivot（Python/SQL）无法处理同一客户-月份下的重复Issue，Python中会抛出ValueError: Index contains duplicate entries, cannot reshape。

Python 高效解决方案 #

核心思路是先按客户分组聚合各月份的Issue列表，再通过笛卡尔积生成所有组合，避免直接Pivot的重复索引问题。

代码实现 #

import pandas as pd
from itertools import product

# 读取输入数据
df = pd.DataFrame({
    "CUSTOMER": [1,1,1,1,2,2,2,2],
    "MONTH": ["M1","M1","M2","M3","M1","M2","M2","M3"],
    "ISSUE": ["ABC","DEF","ABC","QRS","PQR","PQR","ABC","DEF"]
})

# 1. 按客户+月份分组，聚合Issue为列表
grouped = df.groupby(["CUSTOMER", "MONTH"])["ISSUE"].apply(list).unstack()

# 2. 对每个客户生成各月份Issue的笛卡尔积
result = []
for idx, row in grouped.iterrows():
    # 过滤掉空值（如果有客户缺失某个月份数据）
    valid_lists = [lst for lst in row.values if isinstance(lst, list)]
    # 生成所有组合
    for combo in product(*valid_lists):
        result.append({
            "CUSTOMER": idx,
            "M1": combo[0],
            "M2": combo[1],
            "M3": combo[2]
        })

# 3. 转换为DataFrame
wide_df = pd.DataFrame(result)

# 4. 统计不同模式的客户数量（按M1-M3组合分组，计数不同客户）
pattern_count = wide_df.groupby(["M1", "M2", "M3"])["CUSTOMER"].nunique().reset_index(name="CUSTOMER_COUNT")

print("宽表组合结果：")
print(wide_df)
print("\n模式统计结果：")
print(pattern_count)

优势 #

• 仅在分组后的小范围数据上生成笛卡尔积，避免全量数据的交叉计算，性能远优于SQL自连接
• 天然处理同一月份的重复Issue，无需额外去重

SQL 高效解决方案（以PostgreSQL为例） #

利用数组聚合+unnest函数实现笛卡尔积，避免多次自连接的性能损耗。

代码实现 #

-- 1. 按客户分组，聚合各月份的Issue为数组
WITH customer_month_issues AS (
    SELECT
        CUSTOMER,
        ARRAY_AGG(DISTINCT ISSUE) FILTER (WHERE MONTH = 'M1') AS m1_issues,
        ARRAY_AGG(DISTINCT ISSUE) FILTER (WHERE MONTH = 'M2') AS m2_issues,
        ARRAY_AGG(DISTINCT ISSUE) FILTER (WHERE MONTH = 'M3') AS m3_issues
    FROM your_table
    GROUP BY CUSTOMER
),
-- 2. 生成每个客户的所有组合
customer_combinations AS (
    SELECT
        CUSTOMER,
        m1 AS M1,
        m2 AS M2,
        m3 AS M3
    FROM customer_month_issues,
         UNNEST(m1_issues) AS m1,
         UNNEST(m2_issues) AS m2,
         UNNEST(m3_issues) AS m3
)
-- 3. 统计不同模式的客户数量
SELECT
    M1,
    M2,
    M3,
    COUNT(DISTINCT CUSTOMER) AS CUSTOMER_COUNT
FROM customer_combinations
GROUP BY M1, M2, M3;

优势 #

• 用数组聚合减少中间数据量，UNNEST+交叉连接比多次自连接更高效
• 支持大表场景，数据库引擎会优化数组和嵌套循环的执行计划

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