Truy vấn SQL của BigQuery và Google Analytics

Question

Tôi đang cố gắng tạo một ma trận ngoài bảng được nhập từ dữ liệu Google Analytics vào BigQuery. Bảng đại diện cho các lần truy cập trên một trang web có chứa session_ID cùng với một số thuộc tính như url, dấu thời gian, v.v. Ngoài ra, có một số siêu dữ liệu dựa trên các hành động do người dùng xác định mà chúng tôi gọi là sự kiện. Dưới đây là một ví dụ của bảng.

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

Kết quả dự kiến ​​sẽ giống như bảng dưới đây.

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

Lưu ý rằng bất kỳ sự kiện nào không dẫn đến url_target phải được biểu thị là số không bao gồm mục tiêu. Điều này có nghĩa là truy vấn phải xem xét dấu thời gian để kiểm tra xem có bất kỳ sự kiện nào được theo sau bởi url_target hay không bằng cách xem xét dấu thời gian của chúng. Ví dụ: sự kiện2 không được theo sau bởi "url_target", đó là lý do tại sao chúng tôi biểu thị nó là số không. Trường hợp tương tự trong session_id 3, vì event2 không được theo sau bởi url_target, hãy lưu ý dấu thời gian của url_target nằm trước event2, không phải sau nó. Do đó được biểu thị là số không.

Tôi đánh giá cao bất kỳ sự trợ giúp nào trong việc xây dựng truy vấn SQL để tạo ra ma trận đó. Tôi chỉ có thể nhóm theo session_id và sau đó thực hiện đếm các sự kiện bằng cách sử dụng "count", nhưng không thể tìm thấy truy vấn SQL ghi để khớp với dấu thời gian và kiểm tra các trường khác.

Answer 1

Sử dụng truy vấn con để tính thời gian mục tiêu đầu tiên (hoặc cuối cùng). Sau đó sử dụng countif()và tổng hợp:

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

Xem xét:

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