BigQuery中基于is_order=True的区间累计求和实现问题

需求说明 #

需要计算用户两次订单之间的耗时总和，仅在is_order为TRUE的行展示累计结果，累计范围是上一个订单行之后到当前订单行的所有行（若为第一个订单则从第一行开始）。

示例数据 #

user  | open_time   | spend_time_sec | is_order   | cumsum
001   | 2022-01-01  | 320            | FALSE      | 
001   | 2022-01-02  | 60             | TRUE       | 380
001   | 2022-01-04  | 100            | TRUE       | 100
001   | 2022-01-06  | 20             | FALSE      |
001   | 2022-01-08  | 60             | TRUE       | 80

解决方案 #

核心思路是先为每个订单区间（上一个订单到当前订单的所有行）标记分组ID，再在分组内进行累计求和，仅在订单行展示结果。

方法1：使用CTE分步实现 #

WITH grouped_data AS (
    SELECT
        user,
        open_time,
        spend_time_sec,
        is_order,
        -- 标记当前行所属的订单区间分组ID
        SUM(CASE WHEN is_order THEN 1 ELSE 0 END) OVER(
            PARTITION BY user 
            ORDER BY open_time 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS group_id
    FROM your_table
)
SELECT
    user,
    open_time,
    spend_time_sec,
    is_order,
    -- 仅在订单行展示分组内的累计求和结果
    CASE WHEN is_order THEN 
        SUM(spend_time_sec) OVER(PARTITION BY user, group_id ORDER BY open_time)
    ELSE NULL END AS cumsum
FROM grouped_data
ORDER BY user, open_time;

方法2：嵌套窗口函数直接实现 #

SELECT
    user,
    open_time,
    spend_time_sec,
    is_order,
    CASE WHEN is_order THEN 
        SUM(spend_time_sec) OVER(
            PARTITION BY user, 
                SUM(CASE WHEN is_order THEN 1 ELSE 0 END) OVER(
                    PARTITION BY user 
                    ORDER BY open_time 
                    ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                )
            ORDER BY open_time
        )
    ELSE NULL END AS cumsum
FROM your_table
ORDER BY user, open_time;

逻辑说明 #

1. 分组ID生成：通过窗口函数SUM(CASE WHEN is_order THEN 1 ELSE 0 END)统计当前行之前的所有订单数量，以此作为分组ID。这样每个订单行和它与上一个订单之间的非订单行会被分到同一组。
2. 分组累计求和：按用户和分组ID进行窗口累计求和，仅在is_order=TRUE的行展示结果，非订单行留空。

原问题中UNBOUNDED PRECEDING的窗口函数会累加所有历史行，无法区分订单区间，因此需要通过分组的方式限定累计范围。

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