Google BigQuery中替代循环的高效分组统计use_time_sec小计方案求助

需求说明 #

需要针对数据表实现：仅展示event_name为login的记录，按event_datetime（该login的时间）、event_name、user_id、system_id分组，统计该登录会话内所有操作的use_time_sec小计。由于数据量达数百万且持续增长，需避免低效的循环方案。

示例输入表 #

with sample_input as (
    select '12/01/2023 14:27:59' as event_datetime, 'login' as event_name,'1' as user_id, 'X' as system_id, 0 as use_time_sec 
    union all
    select '12/01/2023 14:28:05', 'screen 1',   '1',    'X',    2
    union all
    select '12/01/2023 14:28:05', 'screen 2',   '1',    'X',    5
    union all
    select '12/01/2023 14:28:17', 'screen 1',   '1',    'X',    3
    union all
    select '12/01/2023 14:28:23', 'logout', '1',    '', 0
    union all
    select '12/01/2023 14:28:23', 'login',  '2',    'Y',    0
    union all
    select '12/01/2023 14:28:23', 'screen 1',   '2',    'Y',    10
    union all
    select '12/01/2023 14:28:24', 'screen 2',   '2',    'Y',    100
    union all
    select '12/01/2023 14:28:29', 'login',  '1',    'X',    0
    union all
    select '12/01/2023 14:28:29', 'screen 1',   '1',    'X',    500
    union all
    select '12/01/2023 14:28:29', 'logout', '1',    '', 0
)

高效解决方案SQL #

with sample_input as (
    select '12/01/2023 14:27:59' as event_datetime, 'login' as event_name,'1' as user_id, 'X' as system_id, 0 as use_time_sec 
    union all
    select '12/01/2023 14:28:05', 'screen 1',   '1',    'X',    2
    union all
    select '12/01/2023 14:28:05', 'screen 2',   '1',    'X',    5
    union all
    select '12/01/2023 14:28:17', 'screen 1',   '1',    'X',    3
    union all
    select '12/01/2023 14:28:23', 'logout', '1',    '', 0
    union all
    select '12/01/2023 14:28:23', 'login',  '2',    'Y',    0
    union all
    select '12/01/2023 14:28:23', 'screen 1',   '2',    'Y',    10
    union all
    select '12/01/2023 14:28:24', 'screen 2',   '2',    'Y',    100
    union all
    select '12/01/2023 14:28:29', 'login',  '1',    'X',    0
    union all
    select '12/01/2023 14:28:29', 'screen 1',   '1',    'X',    500
    union all
    select '12/01/2023 14:28:29', 'logout', '1',    '', 0
),
-- 为每个用户的会话标记唯一ID
session_marked as (
    select 
        *,
        SUM(CASE WHEN event_name = 'login' THEN 1 ELSE 0 END) OVER (
            PARTITION BY user_id 
            ORDER BY PARSE_DATETIME('%m/%d/%Y %H:%M:%S', event_datetime) 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) as session_id
    from sample_input
),
-- 计算每个会话的总使用时长
session_total_time as (
    select 
        user_id,
        session_id,
        SUM(use_time_sec) as total_use_time_sec
    from session_marked
    group by user_id, session_id
),
-- 关联回对应的login记录，保留所需字段
login_session_summary as (
    select
        sm.event_datetime,
        sm.event_name,
        sm.user_id,
        sm.system_id,
        st.total_use_time_sec
    from session_marked sm
    join session_total_time st 
        on sm.user_id = st.user_id 
        and sm.session_id = st.session_id
    where sm.event_name = 'login'
)
select * from login_session_summary

方案解释 #

1. 会话标记：通过窗口函数按用户分组、时间排序，每次遇到login事件就为会话ID加1，将同一会话内的所有操作归为同一个session_id。
2. 会话时长统计：按用户和会话ID分组，求和该会话内所有操作的use_time_sec。
3. 关联登录记录：将会话总时长关联到该会话的起始login记录，最后筛选出login类型的记录，得到需求结果。

该方案基于BigQuery的分布式计算能力，全程使用批量处理，避免循环的逐行操作，能高效处理百万级甚至更大规模的数据。

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