Zapytanie SQL w BigQuery i Google Analytics

Question

Próbuję zbudować macierz z tabeli zaimportowanej z danych Google Analytics do BigQuery. Tabela przedstawia działania w witrynie zawierające identyfikatory sesji wraz z niektórymi właściwościami, takimi jak adres URL, sygnatura czasowa itp. Istnieją również metadane oparte na działaniach zdefiniowanych przez użytkownika, które nazywamy zdarzeniami. Poniżej znajduje się przykład tabeli.

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

Zamierzony wynik powinien być podobny do poniższej tabeli.

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

Zwróć uwagę, że żadne zdarzenie, które nie prowadzi do url_target, powinno być oznaczone zerami, łącznie z celem. Oznacza to, że zapytanie powinno zaglądać do sygnatury czasowej, aby sprawdzić, czy po jakimkolwiek zdarzeniu następuje url_target, sprawdzając ich sygnaturę czasową. Na przykład po zdarzeniu 2 nie wystąpiło „url_target”, dlatego oznaczamy je jako zera. Ten sam przypadek w session_id 3, ponieważ po zdarzeniu event2 nie nastąpił url_target, zwróć uwagę na znacznik czasu url_target, który był przed zdarzeniem event2, a nie po nim. Stąd oznaczane jako zera.

Byłbym wdzięczny za wszelką pomoc w tworzeniu zapytania SQL w celu utworzenia tej macierzy. Mogłem grupować tylko według session_id, a następnie zliczać zdarzenia przy użyciu „count”, ale nie byłem w stanie znaleźć zapytania SQL do zapisu, które pasowałoby do znacznika czasu i sprawdzenia innych pól.

Answer 1

Użyj podzapytania, aby obliczyć pierwszy (lub ostatni) czas docelowy. Następnie użyj countif()i agregacja:

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

Answer 2

Rozważać:

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