SQL实现客户活动数据的Kohorts透视（Pivot）转换需求

需求分析 #

需要将每个客户的月度活动数据，转换为以每个月份为行，展示当前及后续4个月（若存在）活动状态的透视表，同时保留原表中因未注册产生的NULL值。

解决方案 #

根据不同数据库类型，提供两种实现方式：

1. SQL Server（使用PIVOT语法）

WITH client_months AS (
    SELECT 
        month,
        id_client,
        month_number AS current_month_num,
        k
    FROM 
        your_table
    CROSS JOIN 
        (VALUES (1), (2), (3), (4), (5)) AS nums(k)
),
activity_mapping AS (
    SELECT 
        cm.month,
        cm.id_client,
        cm.k,
        t.activity
    FROM 
        client_months cm
    LEFT JOIN 
        your_table t ON cm.id_client = t.id_client 
                     AND t.month_number = cm.current_month_num + cm.k - 1
)
SELECT 
    month,
    id_client,
    [1] AS month_number_1,
    [2] AS month_number_2,
    [3] AS month_number_3,
    [4] AS month_number_4,
    [5] AS month_number_5
FROM 
    activity_mapping
PIVOT (
    MAX(activity)
    FOR k IN ([1], [2], [3], [4], [5])
) AS pivot_result
ORDER BY 
    month;

2. MySQL（使用条件聚合替代PIVOT）

WITH client_months AS (
    SELECT 
        month,
        id_client,
        month_number AS current_month_num,
        k
    FROM 
        your_table
    CROSS JOIN 
        (SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS nums
),
activity_mapping AS (
    SELECT 
        cm.month,
        cm.id_client,
        cm.k,
        t.activity
    FROM 
        client_months cm
    LEFT JOIN 
        your_table t ON cm.id_client = t.id_client 
                     AND t.month_number = cm.current_month_num + cm.k - 1
)
SELECT 
    month,
    id_client,
    MAX(CASE WHEN k=1 THEN activity END) AS month_number_1,
    MAX(CASE WHEN k=2 THEN activity END) AS month_number_2,
    MAX(CASE WHEN k=3 THEN activity END) AS month_number_3,
    MAX(CASE WHEN k=4 THEN activity END) AS month_number_4,
    MAX(CASE WHEN k=5 THEN activity END) AS month_number_5
FROM 
    activity_mapping
GROUP BY 
    month, id_client
ORDER BY 
    month;

关键逻辑说明 #

• CTE client_months：为每个客户的每个月份生成1-5的索引值k，用于对应后续透视列。
• CTE activity_mapping：通过左连接原表，匹配当前月份current_month_num + k -1对应的活动状态，不存在的月份自动返回NULL，保留未注册的原始NULL值。
• 透视转换：SQL Server用PIVOT语法直接转换，MySQL用条件聚合实现相同的列转行效果。

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