在BigQuery中基于条件从现有列派生subscription_time列

现有数据表结构及数据 #

说明 #

• event_time实际为毫秒数，此处已简化；

需求 #

为每条记录添加subscription_time派生列，让每个renewal、churn事件对应其之前最近的subscribe事件的event_time，最终表格如下：

目标结果表 #

最终目的是统计各subscription_time对应的renewal、churn等事件数量。

实现方案 #

可以通过窗口函数实现需求，核心逻辑是为每条记录匹配最近的前置subscribe事件时间，以下是通用SQL示例：

1. 生成subscription_time列

SELECT 
    user_id AS user,
    event_name,
    event_time,
    -- 取当前行及之前所有行中最近的subscribe时间
    MAX(CASE WHEN event_name = 'subscribe' THEN event_time END) 
        OVER (PARTITION BY user_id ORDER BY event_time) AS subscription_time
FROM your_table_name

这种方式利用MAX()窗口函数的特性，因为subscribe时间递增，所以会自动保留最近的订阅时间，兼容性在多数数据库中表现更好。

2. 统计各订阅周期的事件数量

基于上面的结果，进一步聚合统计：

WITH subscription_events AS (
    SELECT 
        user_id,
        event_name,
        MAX(CASE WHEN event_name = 'subscribe' THEN event_time END) 
            OVER (PARTITION BY user_id ORDER BY event_time) AS subscription_time
    FROM your_table_name
)
SELECT 
    subscription_time,
    COUNT(CASE WHEN event_name = 'renewal' THEN 1 END) AS renewal_count,
    COUNT(CASE WHEN event_name = 'churn' THEN 1 END) AS churn_count,
    COUNT(CASE WHEN event_name = 'subscribe' THEN 1 END) AS subscribe_count
FROM subscription_events
GROUP BY subscription_time
ORDER BY subscription_time;

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