Truy vấn SQL của BigQuery và Google Analytics - câu hỏi mở rộng

Question

Tôi đang cố gắng mở rộng câu hỏi đã trả lời của tôi ở đây . vì vậy, với dữ liệu:

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

Và kết quả hiện tại như sau:

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

Tôi muốn mở rộng kết quả đã cho để phản ánh những trường hợp mà mục tiêu bằng 0. Tôi cũng có thể chú thích những trường hợp đó với số lượng sự kiện đếm được bất kể ngày kiểm tra không?

Vì vậy, kết quả dự kiến ​​mới sẽ như sau:

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

Tôi đặc biệt quan tâm đến session_id = 2 nơi có số lượng sự kiện đang diễn ra mà không có url_target nào được truy cập. Cuối cùng, session_id = 3 cũng là một trường hợp khác mà tôi không biết phải xử lý như thế nào! Vì nó có một sự kiện (event2), nhưng nó đã được thực hiện sau khi truy cập url_target. Có lẽ tôi nên biểu thị nó là target = 2, vì đây là một trường hợp đặc biệt. Nhưng, nếu điều này là khó với SQL, thì tôi sẽ loại bỏ nó khỏi kết quả và giữ nó ở dạng số không, như bảng kết quả dự kiến ​​ở trên.

Cảm ơn rất nhiều trước cho bất kỳ đóng góp.

Answer 1

Từ những gì bạn mô tả, bạn muốn logic có điều kiện. Điều này sẽ hoạt động:

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

Các target_hit_timestamplà NULLnếu không có URL mục tiêu.