求BigQuery SQL方案：统计各Campaign前用户接收活动的次数分组计数

原始数据表 #

Campaign_id User_id Campaign_date
C_1         1      2020-01-01    
C_1         2      2020-01-01
C_1         3      2020-01-01
C_1         4      2020-01-01
C_1         5      2020-01-01
C_2         2      2020-02-01
C_2         7      2020-02-01
C_2         8      2020-02-01
C_2         3      2020-02-01
C_2         5      2020-02-01
C_2         11     2020-02-01 
C_3         7      2020-03-01
C_3         2      2020-03-01
C_3         12     2020-03-01
C_3         4      2020-03-01
C_4         1      2020-04-01
C_4         2      2020-04-01
C_4         4      2020-04-01
C_4         15     2020-04-01  
C_4         7      2020-04-01

期望结果表 #

Campaign_id sent_2_times sent_3_times sent_4_times
C_1             0           0           0
C_2             3           0           0
C_3             4           1           0
C_4             4           2           1

需求说明 #

• 统计每个Campaign_id对应的、在该活动日期之前（含当日），累计接收过2次、3次、4次活动的唯一用户数量
• 细节：
  • C_1为首个活动，所有用户均为首次参与，因此各统计列均为0
  • C_2中，用户2、3、5累计参与2次活动，对应sent_2_times为3
  • C_3中，用户3、4、5、7累计参与2次，用户2累计参与3次，因此sent_2_times为4，sent_3_times为1

BigQuery SQL实现 #

WITH user_campaign_counts AS (
    -- 计算每个用户在当前活动日期及之前累计参与的不同活动数
    SELECT 
        t1.Campaign_id,
        t1.User_id,
        COUNT(DISTINCT t2.Campaign_id) AS total_campaigns
    FROM 
        `your_project.your_dataset.your_table` t1
    LEFT JOIN 
        `your_project.your_dataset.your_table` t2
        ON t1.User_id = t2.User_id
        AND t2.Campaign_date <= t1.Campaign_date
    GROUP BY 
        t1.Campaign_id, t1.User_id
),
campaign_user_distribution AS (
    -- 按活动分组，统计各累计参与次数的用户数
    SELECT 
        Campaign_id,
        SUM(CASE WHEN total_campaigns = 2 THEN 1 ELSE 0 END) AS sent_2_times,
        SUM(CASE WHEN total_campaigns = 3 THEN 1 ELSE 0 END) AS sent_3_times,
        SUM(CASE WHEN total_campaigns = 4 THEN 1 ELSE 0 END) AS sent_4_times
    FROM 
        user_campaign_counts
    GROUP BY 
        Campaign_id
)
-- 确保所有活动都出现在结果中，空值替换为0
SELECT 
    original.Campaign_id,
    COALESCE(dist.sent_2_times, 0) AS sent_2_times,
    COALESCE(dist.sent_3_times, 0) AS sent_3_times,
    COALESCE(dist.sent_4_times, 0) AS sent_4_times
FROM 
    (SELECT DISTINCT Campaign_id FROM `your_project.your_dataset.your_table`) original
LEFT JOIN 
    campaign_user_distribution dist
    ON original.Campaign_id = dist.Campaign_id
ORDER BY 
    original.Campaign_id;

代码逻辑说明 #

1. user_campaign_counts：通过自连接关联同一用户的历史活动记录，计算每个用户在当前活动日期及之前参与的不同活动总数，用COUNT(DISTINCT)避免同一活动内重复记录的干扰。
2. campaign_user_distribution：按活动分组，使用CASE语句统计各累计参与次数的用户数量。
3. 最终左连接原始活动列表，确保所有活动都被包含，并用COALESCE将NULL值替换为0，保证结果格式统一。

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