BigQuery 및 Google Analytics SQL 쿼리-확장 질문

Question

여기 에 내 답변 된 질문을 확장하려고합니다 . 그래서 주어진 데이터 :

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

목표가 0 인 경우를 반영하기 위해 주어진 결과를 확장하고 싶습니다. 확인 날짜에 관계없이 이벤트 개수로 이러한 케이스에 주석을 달 수 있습니까?

따라서 새로운 의도 된 결과는 다음과 같습니다.

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

특히 url_target을 방문하지 않고 이벤트가 많이 발생하는 session_id = 2에 관심이 있습니다. 마지막으로 session_id = 3은 처리 방법을 잘 모르는 또 다른 경우입니다! 하나의 이벤트 (event2)가 있지만 url_target을 방문한 후에 수행되었습니다. 특수한 경우이므로 target = 2로 표시해야 할 수도 있습니다. 그러나 SQL에서 이것이 어려울 경우 위의 의도 한 결과 테이블과 같이 결과에서이를 삭제하고 0으로 유지합니다.

모든 기여에 미리 감사드립니다.

Answer 1

설명하는 내용에서 조건부 논리를 원합니다. 이것은 작동합니다.

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_timestamp것입니다 NULL아무 대상 URL이없는 경우.