Блог

У меня есть таблица событий, которая имеет:

• идентификатор пользователя
• имя_события
• event_time

Существуют имена событий типов: 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
 

Как я могу этого добиться?
Спасибо!

Вы можете сделать это в BigQuery с помощью last_value() :

Предположительно, электронное письмо отправляется во время собрания, если самое последнее событие «собрания» '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'
 

Это похоже на какую-то проблему с пробелами и островами, где остров — это собрание, и вы хотите, чтобы электронные письма принадлежали островам.

Как мы определяем остров? Предполагая, что начало и конец собрания чередуются правильно, мы можем просто сравнить количество запусков и завершений для каждого пользователя. Если начал больше, чем заканчивается, то собрание продолжается. Используя эту логику, вы можете получить все электронные письма, которые были отправлены во время собрания, следующим образом:

 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 * во внешнем запросе.