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 का नेतृत्व नहीं किया जाना चाहिए, लक्ष्य सहित शून्य के रूप में चिह्नित किया जाना चाहिए। इसका मतलब यह है कि क्वेरी को टाइमस्टैम्प में देखना चाहिए कि किसी भी घटना के बाद url_target द्वारा उनके टाइमस्टैम्प में देख लिया जाए। उदाहरण के लिए, इवेंट 2 को "url_target" द्वारा फॉलो नहीं किया गया था, इसीलिए हम इसे शून्य के रूप में दर्शा रहे हैं। सेशन_ड 3 में समान मामला, जैसा कि इवेंट 2 को url_target द्वारा फॉलो नहीं किया गया था, url_target के टाइमस्टैम्प पर ध्यान दें, जो इवेंट 2 से पहले था, उसके बाद नहीं। इसलिए शून्य के रूप में निरूपित किया गया।

मैं उस मैट्रिक्स को बनाने के लिए SQL क्वेरी के निर्माण में किसी भी मदद की सराहना करूंगा। मैं केवल session_id द्वारा समूह बनाने में सक्षम था और फिर "गिनती" का उपयोग करके गिनती की घटनाओं का प्रदर्शन करता था, लेकिन टाइमस्टैम्प के खिलाफ मैच करने और अन्य क्षेत्रों की जांच करने के लिए लिखने के लिए 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