基于SQL时间线的查询

Question

我有一个事件表，其中包含：

• user_id
• 事件名称
• 事件时间

有以下类型的事件名称：meeting_started、meeting_ended、email_sent

我想创建一个查询，统计会议期间发送电子邮件的次数。

更新：我正在使用Google BigQuery。

示例查询：

SELECT
event_name,
count(distinct user_id) users,
FROM
events_table WHERE
and event_name IN ('meeting_started', 'meeting_ended')
group by 1

我怎样才能做到这一点？谢谢

Answer 1

您可以使用last_value():在BigQuery中执行此操作

假设在会议期间发送电子邮件；会议；事件为'meeting_started'。因此，您可以通过获取每个事件的最新会议事件，然后进行过滤来解决此问题：

select et.*
from (select et.*,
last_value(case when event_name in ('meeting_started', 'meeting_ended') then event_name end) ignore nulls) over
(partition by user_id order by event_time) as last_meeting_event
from events_table et
) et
where event_name = 'email_sent' and last_meeting_event = 'meeting_started'

Answer 2

这读起来像是某种缺口和孤岛问题，孤岛是一个会议，你想要属于孤岛的电子邮件。

我们如何定义一个岛屿？假设会议的开始和结束正确地交错，我们可以在每个用户的基础上比较开始和结束的次数。如果开始次数多于结束次数，则会议正在进行中。使用此逻辑，您可以获得会议期间发送的所有电子邮件，如下所示：

select *
from (
select e.*,
countif(event_name = 'meeting_started') over(partition by user_id order by event_time) as cnt_started,
countif(event_name = 'meeting_ended'  ) over(partition by user_id order by event_time) as cnt_ended
from events_table e
) e
where event_name = 'email_sent' and cnt_started > cnt_ended

现在还不清楚你想从这里去哪里。如果您想要计算此类电子邮件的数量，只需在外部查询中使用select count(*)而不是select *即可。