Query SQL di BigQuery e Google Analytics - domanda espansa

Question

Sto cercando di espandere la mia domanda con risposta qui . quindi, dati i dati:

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

E il risultato attuale come di seguito:

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

Vorrei approfondire il risultato dato per riflettere su quei casi in cui il target è uguale a zero. Potrei anche annotare quei casi con il conteggio del numero di eventi indipendentemente dalle date di controllo?

Quindi il nuovo risultato previsto sarebbe il seguente:

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

Sono particolarmente interessato a session_id = 2 in cui si verificano numerosi eventi, senza che venga visitato url_target. Infine, session_id = 3 è anche un altro caso in cui non sono sicuro di come gestirlo! Poiché ha un evento (event2), ma è stato fatto dopo aver visitato url_target. Forse dovrei denotarlo come target = 2, come un caso speciale. Ma, se questo è difficile con SQL, lo scarterei dal risultato e lo manterrei come zero, come la tabella dei risultati prevista sopra.

Grazie mille in anticipo per eventuali contributi.

Answer 1

Da quello che descrivi, vuoi la logica condizionale. Questo dovrebbe funzionare:

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

Il simbolo target_hit_timestampè NULLse non è presente un URL di destinazione.