基于近4个月购买品类的月度用户标签SQL/BigQuery实现问询

问题 #

我有一张多用户、多品类日度采购数据表，结构及数据如下：

需要生成如下格式的目标表：

标签生成规则 #

基于用户**过去4个日历月（含当月）**的采购品类quantity占比生成标签：

• 若某品类占比为明显多数（单个品类占比>40%，其余均为小占比），单独标注该品类
• 若2个品类占比均>40%，标注双标签（用-连接）
• 若3个品类占比接近（各约30%），标注三标签（用-连接）
• 仅保留用户有采购记录的月份，跳过无采购的空白月份

我已梳理大致步骤，但不清楚BigQuery SQL的具体实现逻辑，请求完整实现方案。

BigQuery SQL实现方案 #

整体思路 #

1. 按月聚合用户各品类的采购总量，提取用户有采购记录的所有月份
2. 对每个月份，关联过去4个月（含当月）的品类采购数据并求和
3. 计算每个品类在过去4个月内的采购占比
4. 根据占比规则筛选符合条件的品类，生成对应的标签

完整SQL代码 #

WITH monthly_type_totals AS (
    -- 按月聚合用户各品类采购总量，转换为月起始日期格式
    SELECT
        user,
        type,
        DATE_TRUNC(purchase_date, MONTH) AS month,
        SUM(quantity) AS total_quantity
    FROM
        `your-project.your-dataset.purchase_table` -- 替换为你的实际表路径
    GROUP BY
        user, type, DATE_TRUNC(purchase_date, MONTH)
),
user_months AS (
    -- 提取用户所有有采购记录的月份，去重避免重复计算
    SELECT DISTINCT
        user,
        month
    FROM
        monthly_type_totals
),
four_month_window AS (
    -- 关联每个目标月份过去4个月的品类采购数据，求和得到周期内总量
    SELECT
        um.user,
        um.month AS target_month,
        mtt.type,
        SUM(mtt.total_quantity) AS four_month_quantity
    FROM
        user_months um
    LEFT JOIN
        monthly_type_totals mtt
        ON um.user = mtt.user
        AND mtt.month BETWEEN DATE_SUB(um.month, INTERVAL 3 MONTH) AND um.month
    GROUP BY
        um.user, um.month, mtt.type
    HAVING
        four_month_quantity IS NOT NULL -- 过滤无采购的品类
),
category_ratios AS (
    -- 计算每个品类在过去4个月内的采购占比
    SELECT
        user,
        target_month,
        type,
        four_month_quantity,
        SUM(four_month_quantity) OVER (PARTITION BY user, target_month) AS total_four_month,
        ROUND(four_month_quantity / SUM(four_month_quantity) OVER (PARTITION BY user, target_month), 2) AS ratio
    FROM
        four_month_window
),
ranked_categories AS (
    -- 按占比降序给品类排名，方便规则判断
    SELECT
        *,
        RANK() OVER (PARTITION BY user, target_month ORDER BY ratio DESC) AS rank
    FROM
        category_ratios
),
label_generation AS (
    -- 根据规则生成对应标签
    SELECT
        user,
        target_month AS month,
        CASE
            -- 单个品类占比>40%，其余均≤40%：单标签
            WHEN MAX(CASE WHEN rank = 1 THEN ratio END) > 0.4 AND MAX(CASE WHEN rank = 2 THEN ratio END) <= 0.4 THEN
                STRING_AGG(CASE WHEN rank = 1 THEN type END, '-' ORDER BY rank)
            -- 前两个品类占比均>40%：双标签
            WHEN MAX(CASE WHEN rank = 1 THEN ratio END) > 0.4 AND MAX(CASE WHEN rank = 2 THEN ratio END) > 0.4 THEN
                STRING_AGG(type, '-' ORDER BY rank LIMIT 2)
            -- 前三个品类占比在25%-35%区间（接近30%）：三标签
            WHEN MAX(CASE WHEN rank = 1 THEN ratio END) BETWEEN 0.25 AND 0.35
                AND MAX(CASE WHEN rank = 2 THEN ratio END) BETWEEN 0.25 AND 0.35
                AND MAX(CASE WHEN rank = 3 THEN ratio END) BETWEEN 0.25 AND 0.35 THEN
                STRING_AGG(type, '-' ORDER BY rank LIMIT 3)
            -- 其他情况默认取占比最高的品类
            ELSE
                STRING_AGG(CASE WHEN rank = 1 THEN type END, '-' ORDER BY rank)
        END AS label
    FROM
        ranked_categories
    GROUP BY
        user, target_month
)
-- 输出目标表结构
SELECT
    user,
    label,
    month
FROM
    label_generation
ORDER BY
    user, month;

代码说明 #

• monthly_type_totals：将日度数据聚合为月维度的品类采购总量
• user_months：提取用户有采购的所有月份，跳过无采购的空白月份
• four_month_window：拉取每个目标月份过去4个月的品类采购数据并求和
• category_ratios：计算品类在周期内的采购占比
• ranked_categories：按占比排序品类，便于规则判断
• label_generation：根据预设规则生成单/双/三标签

注意事项 #

1. 替换代码中的your-project.your-dataset.purchase_table为你的实际表路径
2. 三标签的占比区间（25%-35%）可根据业务需求调整
3. 若需要包含无采购的空白月份，可修改user_months部分，生成用户采购周期内的连续月份序列

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