基于Customer Id递归计算30天滚动最小日期的BigQuery问题

问题翻译 #

报错原文：A subquery containing a recursive reference may not use an analytic function at []
中文翻译：包含递归引用的子查询不能使用分析函数

替代解决方案（不用递归CTE） #

递归CTE确实没法和分析函数混用，换用窗口函数+累加分组的方式就能实现需求，步骤如下：

1. 先按用户ID和事件日期排序，计算当前记录与同用户上一条记录的日期差
2. 标记出那些和前一个窗口起始日期间隔超过30天的记录，作为新窗口的起点
3. 累加这些标记得到每个用户的窗口ID，再基于窗口ID计算最小日期和窗口名称

BigQuery代码示例 #

假设你的业务表包含Customer_Id（用户ID）、Event_Date（业务发生日期）以及其他业务字段，代码如下：

WITH sorted_data AS (
    SELECT
        Customer_Id,
        Event_Date,
        -- 计算当前记录与同用户上一条记录的日期差
        DATE_DIFF(Event_Date, LAG(Event_Date) OVER (PARTITION BY Customer_Id ORDER BY Event_Date), DAY) AS days_since_last
    FROM `your_project.your_dataset.your_table`
),
window_markers AS (
    SELECT
        *,
        -- 标记新窗口起点：第一条记录 或者 和上一条间隔超过30天
        CASE
            WHEN days_since_last IS NULL OR days_since_last > 30 THEN 1
            ELSE 0
        END AS is_new_window
    FROM sorted_data
),
window_groups AS (
    SELECT
        *,
        -- 累加标记得到每个用户的窗口ID
        SUM(is_new_window) OVER (PARTITION BY Customer_Id ORDER BY Event_Date) AS window_id
    FROM window_markers
)
SELECT
    *,
    -- 30天滚动窗口的最小日期
    MIN(Event_Date) OVER (PARTITION BY Customer_Id, window_id) AS Window_30,
    -- 窗口名称，格式可以自定义
    CONCAT('Window_', Customer_Id, '_', window_id) AS Window_Name
FROM window_groups
ORDER BY Customer_Id, Event_Date;

代码说明 #

• sorted_data：对每个用户的记录按日期排序，用LAG函数获取上一条记录的日期，计算间隔天数
• window_markers：判断哪些记录是新窗口的起点，第一条记录或者和上一条间隔超30天的标记为1
• window_groups：通过累加标记值，给每个用户的不同窗口分配唯一ID
• 最后一步：按用户+窗口ID取最小日期作为Window_30，生成自定义格式的Window_Name

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