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

मैं उन मामलों पर प्रतिबिंबित करने के लिए दिए गए परिणाम पर विस्तार करना चाहूंगा जहां लक्ष्य शून्य है। क्या मैं तारीखों की जाँच की परवाह किए बिना घटनाओं की गिनती संख्या के साथ उन मामलों को भी एनोटेट कर सकता हूं?

तो नया इच्छित परिणाम निम्नानुसार होगा:

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

मुझे सत्र_आईडी = 2 के बारे में विशेष रूप से दिलचस्पी है, जहां कई घटनाएं हो रही हैं, जिनमें कोई url_target का दौरा नहीं किया जा रहा है। अंत में, session_id = 3 भी एक और मामला है जहां मुझे यकीन नहीं है कि कैसे संभालना है! जैसा कि इसमें एक ईवेंट (इवेंट 2) है, लेकिन यह url_target पर जाने के बाद किया गया था। शायद मैं इसे एक विशेष मामले के रूप में लक्ष्य 2 = के रूप में निरूपित करूं। लेकिन, यदि यह एसक्यूएल के साथ मुश्किल है, तो मैं इसे परिणाम से हटा देगा और इसे शून्य के रूप में रखूंगा, जैसा कि ऊपर दिए गए परिणाम तालिका में है।

किसी भी योगदान के लिए अग्रिम धन्यवाद।

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अगर कोई लक्ष्य यूआरएल है।