如何使用BigQuery SQL将周留存表转换为同期群留存矩阵

你需要的留存矩阵本质是将留存数据按「同期群创建周」为行、「创建后第N周」为列做行转列处理，以下是可直接复用的实现代码：

前置假设 #

你的源表（示例命名为your_project.your_dataset.retention_base）包含字段：

• cohort_creation_week：同期群的创建周，即最终矩阵行维度weekXX对应的数字
• active_week：用户活跃周，和创建周的差值即为创建后第N周
• retention_rate：对应周的留存率数值

实现方式1：手动行转列（固定周数场景，最稳定） #

SELECT
  CONCAT('week', cohort_creation_week) AS week,
  100 AS week0, -- week0固定为100%
  MAX(IF(active_week - cohort_creation_week = 1, retention_rate, NULL)) AS week1,
  MAX(IF(active_week - cohort_creation_week = 2, retention_rate, NULL)) AS week2,
  MAX(IF(active_week - cohort_creation_week = 3, retention_rate, NULL)) AS week3,
  MAX(IF(active_week - cohort_creation_week = 4, retention_rate, NULL)) AS week4,
  MAX(IF(active_week - cohort_creation_week = 5, retention_rate, NULL)) AS week5,
  MAX(IF(active_week - cohort_creation_week = 6, retention_rate, NULL)) AS week6
FROM `your_project.your_dataset.retention_base`
GROUP BY cohort_creation_week
ORDER BY cohort_creation_week

实现方式2：用BigQuery原生PIVOT语法 #

SELECT
  CONCAT('week', cohort_creation_week) AS week,
  100 AS week0,
  week1, week2, week3, week4, week5, week6
FROM (
  SELECT
    cohort_creation_week,
    retention_rate,
    CONCAT('week', active_week - cohort_creation_week) AS week_col
  FROM `your_project.your_dataset.retention_base`
)
PIVOT (
  MAX(retention_rate) 
  FOR week_col IN ('week1', 'week2', 'week3', 'week4', 'week5', 'week6')
)
ORDER BY cohort_creation_week

实现方式3：动态SQL（周数不固定的场景） #

如果留存周数是动态增长的，不想每次手动修改列名，可以用动态SQL自动生成：

DECLARE week_columns STRING;

-- 自动获取所有需要生成的周列名
SET week_columns = (
  SELECT STRING_AGG(
    DISTINCT CONCAT('"week', active_week - cohort_creation_week, '"')
    ORDER BY CONCAT('"week', active_week - cohort_creation_week, '"')
  )
  FROM `your_project.your_dataset.retention_base`
  WHERE active_week > cohort_creation_week
);

-- 执行动态拼接的SQL
EXECUTE IMMEDIATE FORMAT("""
SELECT
  CONCAT('week', cohort_creation_week) AS week,
  100 AS week0,
  * EXCEPT(cohort_creation_week)
FROM (
  SELECT
    cohort_creation_week,
    retention_rate,
    CONCAT('week', active_week - cohort_creation_week) AS week_col
  FROM `your_project.your_dataset.retention_base`
)
PIVOT (
  MAX(retention_rate) FOR week_col IN (%s)
)
ORDER BY cohort_creation_week
""", week_columns);

适配你给出的示例数据的说明 #

你贴出的示例数据所有行的num_users_in_cohort都是4604，属于单个同期群的多周留存数据，如果要生成你示例的矩阵结构，只需要把每行的creation_week当作虚拟的同期群创建周，调整nth_week的计算逻辑为现有行排序的差值即可，也可以直接把现有留存值按顺序填充到对应列。

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