BigQuery 및 Google Analytics SQL 쿼리

Question

Google Analytics 데이터에서 BigQuery로 가져온 테이블에서 매트릭스를 작성하려고합니다. 이 표는 URL, 타임 스탬프 등의 일부 속성과 함께 session_ID를 포함하는 웹 사이트의 조회수를 나타냅니다. 또한 이벤트라고하는 사용자 정의 작업을 기반으로하는 일부 메타 데이터가 있습니다. 아래는 표의 예입니다.

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

의도 한 결과는 아래 표와 같아야합니다.

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

url_target으로 연결되지 않는 이벤트는 대상을 포함하여 0으로 표시되어야합니다. 즉, 쿼리는 타임 스탬프를 조사하여 이벤트 뒤에 url_target이 오는지 확인하기 위해 타임 스탬프를 조사해야합니다. 예를 들어, event2 뒤에 "url_target"이 나오지 않았기 때문에 0으로 표시합니다. session_id 3의 경우와 동일합니다. event2 뒤에 url_target이 나오지 않았기 때문에 event2 이후가 아니라 이전에 있었던 url_target의 타임 스탬프를 확인하십시오. 따라서 0으로 표시됩니다.

해당 행렬을 생성하기 위해 SQL 쿼리를 구성하는 데 도움을 주시면 감사하겠습니다. session_id로만 그룹화 한 다음 "count"를 사용하여 카운트 이벤트를 수행 할 수 있었지만 타임 스탬프와 일치하는 쓰기 SQL 쿼리를 찾고 다른 필드를 확인할 수 없었습니다.

Answer 1

하위 쿼리를 사용하여 첫 번째 (또는 마지막) 대상 시간을 계산합니다. 그런 다음 사용 countif()및 집계 :

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

중히 여기다:

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