如何在BigQuery中为客户标签变化获取正确起止日期并生成宽表

我有一张记录客户购买数据的表，结构及示例数据如下：

原SQL语句（无法区分同一客户重复标签阶段）：

SELECT
customer_id,
label,
COUNT(DISTINCT(purchase_id)) as orders_count,
SUM(price) as total_spent,
min(date) as first_date,
max(date) as last_date,
DATE_DIFF(max(date), min(date), DAY) as days
FROM
TABLE
WHERE
date > '2022-01-01'
GROUP BY
customer_id,
label

原结果存在的问题：同一客户（如customer_id=2）标签从A→G→A时，会将两次A的记录合并，无法区分独立的标签阶段，导致起止日期、统计金额等数据错误。

一、区分重复标签阶段的长表解决方案 #

核心思路是通过窗口函数标记客户的连续标签分组，将同一客户的相同连续标签划分为独立阶段，再按阶段统计指标。

步骤1：生成标签阶段分组标识 #

使用LAG()窗口函数对比当前记录与上一条记录的标签，生成阶段编号：

WITH customer_label_stages AS (
    SELECT
        customer_id,
        label,
        date,
        purchase_id,
        price,
        -- 当当前标签与上一条不同时，阶段编号+1，否则沿用之前的编号
        SUM(CASE WHEN label != LAG(label) OVER (PARTITION BY customer_id ORDER BY date) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY customer_id ORDER BY date) AS stage_id
    FROM TABLE
    WHERE date > '2022-01-01'
)

步骤2：按客户+阶段分组统计 #

基于阶段编号，统计每个标签阶段的核心指标：

SELECT
    customer_id,
    label,
    stage_id,
    COUNT(DISTINCT purchase_id) AS orders_count,
    SUM(price) AS total_spent,
    MIN(date) AS first_date,
    MAX(date) AS last_date,
    DATE_DIFF(MAX(date), MIN(date), DAY) AS days
FROM customer_label_stages
GROUP BY customer_id, label, stage_id
ORDER BY customer_id, stage_id;

长表结果示例 #

二、转换为宽表格式的解决方案 #

宽表转换可通过条件聚合实现静态列生成，若需动态适配阶段数量，可结合数据库的动态SQL功能。

静态宽表实现（适用于阶段数较少的场景） #

基于长表统计结果，用条件聚合将每个阶段的指标转为独立列：

WITH customer_label_stages AS (
    SELECT
        customer_id,
        label,
        date,
        purchase_id,
        price,
        SUM(CASE WHEN label != LAG(label) OVER (PARTITION BY customer_id ORDER BY date) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY customer_id ORDER BY date) AS stage_id
    FROM TABLE
    WHERE date > '2022-01-01'
),
stage_summary AS (
    SELECT
        customer_id,
        label,
        stage_id,
        COUNT(DISTINCT purchase_id) AS orders_count,
        SUM(price) AS total_spent,
        MIN(date) AS first_date,
        MAX(date) AS last_date,
        DATE_DIFF(MAX(date), MIN(date), DAY) AS days
    FROM customer_label_stages
    GROUP BY customer_id, label, stage_id
)
SELECT
    customer_id,
    -- 第一阶段
    MAX(CASE WHEN stage_id = 0 THEN label END) AS first_label,
    MAX(CASE WHEN stage_id = 0 THEN first_date END) AS first_first_date,
    MAX(CASE WHEN stage_id = 0 THEN last_date END) AS first_last_date,
    MAX(CASE WHEN stage_id = 0 THEN total_spent END) AS first_total_spent,
    MAX(CASE WHEN stage_id = 0 THEN days END) AS first_days,
    -- 第二阶段
    MAX(CASE WHEN stage_id = 1 THEN label END) AS next_label,
    MAX(CASE WHEN stage_id = 1 THEN first_date END) AS next_first_date,
    MAX(CASE WHEN stage_id = 1 THEN last_date END) AS next_last_date,
    MAX(CASE WHEN stage_id = 1 THEN total_spent END) AS next_total_spent,
    MAX(CASE WHEN stage_id = 1 THEN days END) AS next_days,
    -- 第三阶段（可按需扩展更多阶段）
    MAX(CASE WHEN stage_id = 2 THEN label END) AS next_label_2,
    MAX(CASE WHEN stage_id = 2 THEN first_date END) AS next_first_date_2,
    MAX(CASE WHEN stage_id = 2 THEN last_date END) AS next_last_date_2,
    MAX(CASE WHEN stage_id = 2 THEN total_spent END) AS next_total_spent_2,
    MAX(CASE WHEN stage_id = 2 THEN days END) AS next_days_2
FROM stage_summary
GROUP BY customer_id
ORDER BY customer_id;

动态宽表实现思路（适用于阶段数不固定的场景） #

若需根据实际数据自动生成列，需结合数据库的动态SQL功能：

1. 查询所有客户的最大阶段数，确定需生成的列数。
2. 拼接动态SQL语句，自动生成对应阶段的条件聚合列。
3. 执行动态SQL得到结果。

以BigQuery为例，动态SQL实现示例：

DECLARE max_stages INT64;
SET max_stages = (
    SELECT MAX(stage_count)
    FROM (
        SELECT customer_id, COUNT(DISTINCT stage_id) AS stage_count
        FROM (
            SELECT
                customer_id,
                SUM(CASE WHEN label != LAG(label) OVER (PARTITION BY customer_id ORDER BY date) THEN 1 ELSE 0 END) 
                    OVER (PARTITION BY customer_id ORDER BY date) AS stage_id
            FROM TABLE
            WHERE date > '2022-01-01'
        )
        GROUP BY customer_id
    )
);

EXECUTE IMMEDIATE FORMAT("""
WITH customer_label_stages AS (
    SELECT
        customer_id,
        label,
        date,
        purchase_id,
        price,
        SUM(CASE WHEN label != LAG(label) OVER (PARTITION BY customer_id ORDER BY date) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY customer_id ORDER BY date) AS stage_id
    FROM TABLE
    WHERE date > '2022-01-01'
),
stage_summary AS (
    SELECT
        customer_id,
        label,
        stage_id,
        COUNT(DISTINCT purchase_id) AS orders_count,
        SUM(price) AS total_spent,
        MIN(date) AS first_date,
        MAX(date) AS last_date,
        DATE_DIFF(MAX(date), MIN(date), DAY) AS days
    FROM customer_label_stages
    GROUP BY customer_id, label, stage_id
)
SELECT
    customer_id,
    %s
FROM stage_summary
GROUP BY customer_id
ORDER BY customer_id;
""", 
-- 拼接各阶段的列
STRING_AGG(
    IF(stage_id = 0, 
        "MAX(CASE WHEN stage_id = 0 THEN label END) AS first_label,\nMAX(CASE WHEN stage_id = 0 THEN first_date END) AS first_first_date,\nMAX(CASE WHEN stage_id = 0 THEN last_date END) AS first_last_date,\nMAX(CASE WHEN stage_id = 0 THEN total_spent END) AS first_total_spent,\nMAX(CASE WHEN stage_id = 0 THEN days END) AS first_days",
        FORMAT("MAX(CASE WHEN stage_id = %d THEN label END) AS next_label_%d,\nMAX(CASE WHEN stage_id = %d THEN first_date END) AS next_first_date_%d,\nMAX(CASE WHEN stage_id = %d THEN last_date END) AS next_last_date_%d,\nMAX(CASE WHEN stage_id = %d THEN total_spent END) AS next_total_spent_%d,\nMAX(CASE WHEN stage_id = %d THEN days END) AS next_days_%d", stage_id, stage_id-1, stage_id, stage_id-1, stage_id, stage_id-1, stage_id, stage_id-1, stage_id, stage_id-1)
    ),
    ",\n"
)
FROM UNNEST(GENERATE_ARRAY(0, max_stages-1)) AS stage_id
);

宽表结果示例 #

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