如何将SQL查询结果行转列以生成Cohort用户留存图表？

问题背景 #

现有如下SQL查询语句，用于计算用户分周期的交易数据：

SELECT first_period, period, sum(num) trans_num
FROM (SELECT (DATEDIFF(created_at, '2022-12-10') DIV 6) period, 
             user_id,
             count(1) num,
             MIN(MIN(DATEDIFF(created_at, '2022-12-10') DIV 6)) OVER (PARTITION BY user_id) as first_period
      FROM pos_transactions
      WHERE DATE(created_at) >= '2022-12-10' 
      GROUP BY user_id, DATEDIFF(created_at, '2022-12-10') DIV 6
     ) u
GROUP BY first_period, period
ORDER BY first_period, period

该查询返回三列结构的行式数据：first_period（用户首次交易所在周期）、period（交易所在周期）、trans_num（该周期的交易用户数）。

现在需要将结果重构为用户留存Cohort图表所需的行列转换格式，目标格式如下：

+--------------+------+------+------+------+
| first_period |  0   |  1   |  2   |   3  |
+--------------+------+------+------+------+
| 0            | 6230 | 2469 | 2846 | 1713 |
| 1            |      | 2589 | 742  | 375  |
| 2            |      |      | 3034 | 397  |
| 3            |      |      |      | 1207 |
+--------------+------+------+------+------+

解决方案 #

由于MySQL无原生PIVOT函数，可通过CASE表达式结合聚合函数实现行转列，具体SQL如下：

SELECT 
    first_period,
    SUM(CASE WHEN period = 0 THEN trans_num ELSE NULL END) AS `0`,
    SUM(CASE WHEN period = 1 THEN trans_num ELSE NULL END) AS `1`,
    SUM(CASE WHEN period = 2 THEN trans_num ELSE NULL END) AS `2`,
    SUM(CASE WHEN period = 3 THEN trans_num ELSE NULL END) AS `3`
FROM (
    -- 原查询逻辑保留，用于生成基础的周期交易数据
    SELECT first_period, period, sum(num) trans_num
    FROM (
        SELECT 
            (DATEDIFF(created_at, '2022-12-10') DIV 6) period, 
            user_id,
            count(1) num,
            MIN(MIN(DATEDIFF(created_at, '2022-12-10') DIV 6)) OVER (PARTITION BY user_id) as first_period
        FROM pos_transactions
        WHERE DATE(created_at) >= '2022-12-10' 
        GROUP BY user_id, DATEDIFF(created_at, '2022-12-10') DIV 6
    ) u
    GROUP BY first_period, period
) t
GROUP BY first_period
ORDER BY first_period;

逻辑说明 #

1. 内层保留原查询逻辑，先计算每个用户在各周期的交易数据，以及用户的首次交易周期first_period；
2. 外层通过CASE表达式匹配每个目标周期列：当period等于列对应数值时取出trans_num，否则返回NULL；
3. 用SUM聚合CASE结果，同时按first_period分组，最终得到行列转换后的结构；
4. 列名用反引号`包裹，避免与SQL关键字冲突。

若后续出现更多周期（如4、5），只需在SELECT中新增对应语句：SUM(CASE WHEN period = N THEN trans_num ELSE NULL END) AS N``即可。

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