Блог

Я пытаюсь создать матрицу из таблицы, которая импортируется из данных Google Analytics в BigQuery. В таблице представлены обращения к веб-сайту, которые содержат идентификаторы сеансов наряду с некоторыми свойствами, такими как URL, временная метка и т. Д. Кроме того, существуют некоторые метаданные, основанные на определяемых пользователем действиях, которые мы называем событиями. Ниже приведен пример таблицы.

 session_id  hit_timestamp   url event_category
1           11:12:23        url134      event1
1           11:14:23        url2234     event2
1           11:16:23        url_target  null
2           03:12:11        url2344     event1
2           03:14:11        url43245    event2
3           09:10:11        url5533     event2
3           09:09:11        url_target  null
4           08:08:08        url64356    event2
4           08:09:08        url56456    event2
4           08:10:08        url_target  null
  

Предполагаемый результат должен быть чем-то вроде приведенной ниже таблицы.

 session_id  event1  event2  target
1           1       1       1
2           0       0       0
3           0       0       0
4           0       2       1
  

Обратите внимание, что любое событие, не приводящее к url_target, должно быть обозначено нулями, включая цель. Это означает, что запрос должен просматривать временную метку, чтобы проверить, что за любыми событиями следует url_target, просматривая их временную метку. Например, за event2 не последовало «url_target», поэтому мы обозначаем его нулями. Тот же случай в session_id 3, поскольку за событием 2 не последовал url_target, обратите внимание на временную метку url_target, которая была до события 2, а не после него. Следовательно, обозначается нулями.

Я был бы признателен за любую помощь в построении SQL-запроса для создания этой матрицы. Я смог сгруппировать только по session_id, а затем выполнить подсчет событий с помощью «count», но не смог найти запрос write SQL для сопоставления с отметкой времени и проверки других полей.

Используйте подзапрос для вычисления первого (или последнего) целевого времени. Затем используйте countif() и агрегируйте:

 select session_id,
       countif(target_hit_timestamp > hit_timestamp and category = 'event1') as event1,
       countif(target_hit_timestamp > hit_timestamp and category = 'event2') as event2,
       countif(url like '%target') as target
from (select t.*,
             min(case when url like '%target' then hit_timestamp end) over (partition by session_id) as target_hit_timestamp
      from t
     ) t
group by session_id
  

Рассмотрим:

 select session_id,
    countif(cnt_url_target > 0 and event_category = 'event1') event1,
    countif(cnt_url_target > 0 and event_category = 'event2') event2,
    countif(url = 'url_target') target
from (
    select t.*,
        countif(url = 'url_target') over(partition by session_id order by hit_timestamp desc) cnt_url_target
    from mytable t
) t
group by session_id