如何在BigQuery中将30天范围内的记录进行迭代分组

需求说明 #

需要将订单记录按以下逻辑分组：

• 以某条订单的起始日期为基准，所有在该日期30天内的记录归为同一组，组的起始日期即为该基准日期
• 若记录超出当前组的30天范围，则该记录的日期作为新组的起始日期，重复上述分组逻辑

输入数据示例 #

ORD_ID  ORD_DT
O1      9/10/2022
O1      9/25/2022
O1      9/29/2022
O1      10/15/2022
O1      10/30/2022
O1      11/10/2022
O1      11/18/2022
O1      11/29/2022
O1      12/20/2022

预期输出 #

ORD_ID  ORD_START_DT    ORD_DT
O1      9/10/2022   9/10/2022
O1      9/10/2022   9/25/2022
O1      9/10/2022   9/29/2022
O1      10/15/2022  10/15/2022
O1      10/15/2022  10/30/2022
O1      10/15/2022  11/10/2022
O1      11/18/2022  11/18/2022
O1      11/18/2022  11/29/2022
O1      11/18/2022  12/20/2022

输入数据的SQL语句 #

WITH  Order_Detail AS 
(
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-25') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-29')  AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-10-15')  AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-10-30')  AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-11-10')  AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-11-18') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-11-29') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-12-20') AS ORD_DT
)
SELECT ORD_ID, ORD_DT
FROM ORDER_DETAIL

解决方案SQL #

通过窗口函数标记新组起始点，再提取每组的基准日期，实现滑动分组：

WITH Order_Detail AS (
    SELECT 'O1' AS ORD_ID, DATE('2022-09-10') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-09-25') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-09-29') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-10-15') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-10-30') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-11-10') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-11-18') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-11-29') AS ORD_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID, DATE('2022-12-20') AS ORD_DT
),
Group_Markers AS (
    SELECT 
        ORD_ID,
        ORD_DT,
        -- 标记新组：第一条记录或与当前组基准日期差超30天的记录，视为新组起点
        SUM(CASE 
            WHEN LAG(ORD_DT) OVER (PARTITION BY ORD_ID ORDER BY ORD_DT) IS NULL THEN 1
            WHEN ORD_DT > DATE_ADD(FIRST_VALUE(ORD_DT) OVER (PARTITION BY ORD_ID ORDER BY ORD_DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), INTERVAL 30 DAY) THEN 1
            ELSE 0
        END) OVER (PARTITION BY ORD_ID ORDER BY ORD_DT) AS GROUP_ID
    FROM Order_Detail
)
SELECT 
    ORD_ID,
    FIRST_VALUE(ORD_DT) OVER (PARTITION BY ORD_ID, GROUP_ID ORDER BY ORD_DT) AS ORD_START_DT,
    ORD_DT
FROM Group_Markers
ORDER BY ORD_ID, ORD_DT;

逻辑说明 #

1. Group_Markers CTE：利用LAG()和FIRST_VALUE()窗口函数，判断每条记录是否需要开启新组：
   • 第一条记录直接标记为新组
   • 若当前记录与当前已形成组的基准日期差超过30天，标记为新组
2. 最终查询：通过FIRST_VALUE()在每个分组内提取最早日期作为组的起始日期，得到预期分组结果

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