如何使用BigQuery SQL获取下一组订单起始日期

现有如下结构的订单详情表：

Order Id    Order_start_date
O1          9/10/2022
O1          9/10/2022
O1          9/12/2023
O1          9/12/2023
O1          9/14/2023
O1          9/14/2023

需要新增Next_Order_start_date字段，代表当前订单日期组的下一组起始日期，期望结果如下：

Order_id    Order_start_date    Next_Order_start_date
O1          9/10/2022           9/12/2023
O1          9/10/2022           9/12/2023
O1          9/12/2023           9/14/2023
O1          9/12/2023           9/14/2023
O1          9/14/2023           Null
O1          9/14/2023           Null

创建该表的BigQuery SQL语句如下：

WITH  ORDER_DETAIL AS 
(
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
)
SELECT ORD_ID, ORDER_START_DT
FROM ORDER_DETAIL

如何使用BigQuery SQL实现获取Next_order_start_dt字段？

可以通过窗口函数结合分组去重的方式实现，核心思路是先识别每个订单下的唯一日期组，再为每个组匹配后续的日期。

方法1：先分组去重再关联（逻辑清晰版） #

先提取每个订单的唯一日期，用LEAD()获取下一组日期，再关联回原始表为每条记录匹配对应值：

WITH  ORDER_DETAIL AS 
(
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
),
DATE_GROUPS AS (
    SELECT 
        ORD_ID,
        ORDER_START_DT,
        -- 按订单分组、日期排序，获取下一组的起始日期
        LEAD(ORDER_START_DT) OVER (PARTITION BY ORD_ID ORDER BY ORDER_START_DT) AS NEXT_ORDER_START_DT
    FROM (
        -- 提取唯一的订单-日期组合
        SELECT DISTINCT ORD_ID, ORDER_START_DT
        FROM ORDER_DETAIL
    )
)
SELECT 
    od.ORD_ID,
    od.ORDER_START_DT,
    dg.NEXT_ORDER_START_DT
FROM ORDER_DETAIL od
JOIN DATE_GROUPS dg 
    ON od.ORD_ID = dg.ORD_ID 
    AND od.ORDER_START_DT = dg.ORDER_START_DT
ORDER BY od.ORDER_START_DT;

方法2：嵌套窗口函数（代码简洁版） #

利用DENSE_RANK()为每个日期组分配排名，再基于排名用LEAD()获取对应下一个日期：

WITH  ORDER_DETAIL AS 
(
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-10') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-12')  AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
    UNION ALL 
    SELECT 'O1' AS ORD_ID , DATE('2022-09-14') AS ORDER_START_DT
)
SELECT 
    ORD_ID,
    ORDER_START_DT,
    -- 基于日期组的排名，获取下一组的起始日期
    LEAD(ORDER_START_DT) OVER (
        PARTITION BY ORD_ID 
        ORDER BY DENSE_RANK() OVER (PARTITION BY ORD_ID ORDER BY ORDER_START_DT)
    ) AS NEXT_ORDER_START_DT
FROM ORDER_DETAIL;

逻辑说明 #

• LEAD()窗口函数用于获取当前行之后指定偏移量的行的值，这里默认偏移1，即下一个日期组的日期。
• 方法1通过先去重减少计算量，再关联回原始表，适合数据量较大的场景；方法2直接在原始表上嵌套窗口函数，代码更紧凑。

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