मैं डेटा को बड़ी क्वेरी में समूहित करने का प्रयास कर रहा हूं जो साधारण एकत्रीकरण से परे है। हालांकि मुझे यकीन नहीं है कि मैं जो करने की कोशिश कर रहा हूं वह संभव है। डेटा के पीछे का विचार:

एक कर्मचारी लॉग इन होगा और कई लेनदेन कर सकता है। Hit.eventInfo इस सभी डेटा को कैप्चर करता है लेकिन लेन-देन को एक दूसरे से अलग करने वाला एकमात्र क्षेत्र एक flight_search फ़ील्ड है जो किसी लेनदेन से पहले किसी व्यक्ति के रिकॉर्ड को देखने के लिए किया जाता है (मैंने एक लेनदेन विभाजक के रूप में रीसेट हिटनंबर का उपयोग करने के बारे में भी सोचा था, लेकिन यह हमेशा प्रति लेनदेन एक साफ रीसेट नहीं होता है)।

मेरा प्रश्न है, क्या fullVisitorId+VisitId, दिनांक और इस तर्क के आधार पर समूह बनाना संभव है, जहां हर बार flight_search फ़ील्ड के सक्रिय होने पर हम सभी array_agg को रीसेट कर देंगे? वर्तमान में, सभी लेन-देन संबंधी डेटा प्रति लेन-देन अलग-अलग सरणियों के बजाय एक सरणी में जा रहा है। इसके बाद यह बताना असंभव है कि कौन से क्षेत्र किस लेनदेन के साथ जाते हैं। इसके अलावा, अधिकतम लेना मुझे प्रत्येक लेन-देन में अंतिम अपडेट देना माना जाता है, लेकिन यह मुझे केवल अंतिम लेनदेन देता है क्योंकि वे सभी एक साथ हैं।

नीचे मेरी क्वेरी का उदाहरण। मुझे array_agg या ऐसा कुछ उपयोग करना होगा क्योंकि सबक्वायरीज़ में केवल एक रिटर्न हो सकता है

WITH eventData AS (
  SELECT
    CONCAT(fullVisitorId, ' ', CAST(VisitId AS string)) sessionId,
    date AS date,
    hit.hour AS checkinHour,
    hit.minute AS checkinMin,
    (SELECT ARRAY_AGG(hit.eventInfo.eventAction) FROM UNNEST(hits) hit WHERE hit.eventInfo.eventCategory = 'pnr') AS pnr,
    (SELECT ARRAY_AGG(STRUCT(hit.eventInfo.eventAction)) AS val FROM UNNEST(hits) hit WHERE hit.eventInfo.eventCategory = 'submit_checkin') AS names
  FROM
    `web-analytics.192016109.ga_sessions_20191223`,
    UNNEST(hits) AS hit
    ## group by sessionId, date, hit.eventInfo.eventCategory ='flight_search'
 )

SELECT
  sessionId,
  date,
  MAX(checkinHour) chkHr,
  MAX(checkinMin) AS chkMin,
  # end of transaction
  MAX(pnr[ORDINAL(ARRAY_LENGTH(pnr))]) AS pnr,
  names.eventAction AS pax_name
FROM
  eventData,
  UNNEST (names) AS names
GROUP BY
  sessionId,
  date,
  pax_name

तकनीकी रूप से अगर मैं यहां एक समूह जोड़ता हूं, तो सब कुछ टूट जाएगा क्योंकि बीमार को घंटे, मिनट और फिर हिट करने के लिए कहा जाएगा जो एक सरणी है ...

उदाहरण परीक्षण डेटा

यह मूल ईवेंटडेटा है क्योंकि इसे Google Analytics से BigQuery में फीड किया जाता है। मैंने प्रदर्शित ईवेंट श्रेणियों को सरल बना दिया है। यह वह जगह है जहां आंतरिक क्वेरी सोर्सिंग कर रही है। सबमिट_चेकिन घटना होने के बाद एक लेनदेन पूरा हो गया है। जैसा कि हम देख सकते हैं, एक पीएनआर (पहचानकर्ता) है, लेकिन उस पीएनआर के लिए कई लोगों को चेक-इन किया जाता है।

यहां छवि विवरण दर्ज करें यह इवेंटडेटा से आउटपुट का एक नमूना जैसा दिखता है। जैसा कि आप देख सकते हैं, पीएनआर को एक सरणी में समूहीकृत किया जाता है और नाम एक सरणी में होते हैं। यह सीधे तौर पर यह देखना संभव नहीं है कि कौन से लेन-देन में एक साथ थे। यहां छवि विवरण दर्ज करें

अंत में, यहां संपूर्ण क्वेरी आउटपुट है। मैंने चित्र पर लिखा है कि अपेक्षित परिणाम क्या है। यहां छवि विवरण दर्ज करें

0
noc_coder 5 जिंदा 2020, 12:47

1 उत्तर

सबसे बढ़िया उत्तर

यदि आप देखना चाहते हैं कि एक ही हिट में कौन सी जानकारी ट्रैक की गई थी तो आपको उनके बीच संबंध बनाए रखना चाहिए। लेकिन ऐसा लगता है कि वे एक ही हिट में नहीं हैं क्योंकि एक बार इवेंट श्रेणी 'पीएनआर' और दूसरी बार 'सबमिट_चेकिन' होती है। मुझे यकीन नहीं है कि यह जानबूझकर है, लेकिन आप हिट के साथ तालिका में शामिल हो रहे हैं ... और फिर आप array_agg() - हिट सरणी प्रति हिट फिर से कर रहे हैं। यह गलत लगता है।

यदि आप सत्र के दायरे में रह रहे हैं तो कुछ भी समूहबद्ध करने की आवश्यकता नहीं है, क्योंकि तालिका पहले से ही 1 पंक्ति = 1 सत्र के साथ आती है।

यह क्वेरी एक और विंडो फ़ंक्शन तैयार करती है

SELECT
  fullVisitorId, 
  visitstarttime,
  date,
  ARRAY(
    SELECT AS STRUCT 
      hitNumber,
      IF(eventInfo.eventCategory='flight_search' 
           AND 
           LAG(eventInfo.eventCategory) OVER (ORDER BY hitnumber ASC) = 'submit_checkin', 1, 0
        ) as breakInfo,
      eventInfo,
      hour,
      minute
    FROM UNNEST(hits) hit 
    WHERE hit.eventInfo.eventCategory IN ('pnr', 'submit_checkin', 'flight_search')
    ORDER BY hitnumber ASC
  ) AS myhits1,
  ARRAY(SELECT AS STRUCT
    *,
    SUM(breakInfo) OVER (order by hitnumber) as arrayId
  FROM (SELECT 
      hitNumber,
      IF(eventInfo.eventCategory='flight_search' 
           AND 
           LAG(eventInfo.eventCategory) OVER (ORDER BY hitnumber ASC) = 'submit_checkin', 1, 0
        ) as breakInfo,
      eventInfo,
      hour,
      minute
    FROM UNNEST(hits) hit 
    WHERE hit.eventInfo.eventCategory IN ('pnr', 'submit_checkin', 'flight_search')
    ORDER BY hitnumber ASC
  )) AS myhits2
FROM
  `web-analytics.192016109.ga_sessions_20191223`

यह आपको समूह के अनुसार आईडी के रूप में एक नंबर देता है। आपको केवल उस आउटपुट को फीड करने की आवश्यकता है जो सरणी फ़ंक्शन को एक और उप-क्वेरी को खिलाया जाता है जो अंततः इसे array_agg() और group by arrrayId का उपयोग करके सरणी में समूहित करता है।

2
Martin Weitzmann 8 जिंदा 2020, 08:44