Zapytanie SQL w BigQuery i Google Analytics - pytanie rozszerzone

Question

Staram się rozwijać na moim odpowiedział na pytanie tutaj . więc biorąc pod uwagę dane:

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

A aktualny wynik jak poniżej:

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

Chciałbym rozwinąć podany wynik, aby zastanowić się nad przypadkami, w których cel jest równy zero. Czy byłbym w stanie również dodać adnotacje do tych przypadków z liczbą zdarzeń niezależnie od sprawdzania dat?

Tak więc nowy zamierzony wynik wyglądałby jak poniżej:

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

Szczególnie interesuje mnie sesja session_id = 2, w której zachodzi pewna liczba wydarzeń, bez odwiedzania adresu url_target. Wreszcie session_id = 3 to także kolejny przypadek, w którym nie jestem pewien, jak sobie z tym poradzić! Ponieważ ma jedno zdarzenie (event2), ale zostało zrobione po wizycie w url_target. Może powinienem oznaczyć to jako target = 2, jako przypadek specjalny. Ale jeśli jest to trudne z SQL, odrzuciłbym go z wyniku i zachował jako zera, zgodnie z zamierzoną tabelą wyników powyżej.

Z góry dziękuję za wszelkie wkłady.

Answer 1

Z tego, co opisujesz, chcesz logiki warunkowej. To powinno działać:

select session_id,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) and category = 'event1') as event1,
       countif((target_hit_timestamp > hit_timestamp or target_hit_timestamp is null) > 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

target_hit_timestampJest NULLjeśli nie ma docelowego adresu URL.