循环的替代方案

Question

我有一个表，如下所示，我需要显示use_time_sec列按event_datetime, event_name(仅显示login)， user_id和system_id分组的小计。

示例输入表

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',
)
select * from sample_input

的示例输出我可以遍历表以获得我想要的输出。但这不是最有效的解决方案，因为表中有数百万条记录，并且每天都在增长。

如果有人能提供比我更好的解决方案，我将不胜感激。

注:数据在google BigQuery中。

感谢

Answer 1

这就是所谓的缺口和孤岛问题。我们正在尝试识别用户会话的孤岛。我们需要做一个查询，它为我们提供了一些识别会话的方法。这很大程度上依赖于窗口函数。

一种方法是计算每个用户的登录次数。

select
*,
sum(1)
filter(where event_name = 'login')
over(partition by user_id order by event_time)
as session_num
from events
order by event_time

将保留每个user_id的计数。每当它看到一个用户登录时，它将添加到计数中。

<表类>event_timeevent_typeuser_iduse_time_secsession_numtbody><<tr>1000登录1011001事情1311001登录21011002注销1711005注销22011100登录1521101注销1102