1 से 10 के पैमाने पर आपका डेटा भंडारण कौशल कितना अच्छा है?
7/10 से ऊपर जाना चाहते हैं? यह लेख तब आपके लिए है।
आपका एसक्यूएल कितना अच्छा है? जल्द से जल्द नौकरी के लिए इंटरव्यू के लिए तैयार होना चाहते हैं?
यह ब्लॉग पोस्ट सबसे जटिल डेटा वेयरहाउस SQL तकनीकों के बारे में विस्तार से बताता है। मैं इस विषय पर कुछ विचार लिखने के लिए BigQuery मानक SQL बोली का उपयोग करूँगा।
1. इंक्रीमेंटल टेबल और MERGE
अद्यतन तालिका महत्वपूर्ण है। यह वाकई महत्वपूर्ण है। आदर्श स्थिति तब होती है जब आपके पास प्राथमिक कुंजी, अद्वितीय पूर्णांक और ऑटो वृद्धि वाले लेनदेन होते हैं। इस मामले में तालिका अद्यतन सरल है:
आधुनिक डेटा वेयरहाउस में असामान्य स्टार-स्कीमा डेटासेट के साथ काम करते समय हमेशा ऐसा नहीं होता है। आपको SQL के साथ सत्र बनाने और/या डेटा के केवल एक हिस्से के साथ डेटासेट को वृद्धिशील रूप से अपडेट करने का काम सौंपा जा सकता है। transaction_id मौजूद नहीं हो सकता है लेकिन इसके बजाय आपको डेटा मॉडल से निपटना होगा जहां अद्वितीय कुंजी नवीनतम transaction_id (या टाइमस्टैम्प) पर निर्भर करती है। उदाहरण के लिए, last_online डेटासेट में user_id नवीनतम ज्ञात कनेक्शन टाइमस्टैम्प पर निर्भर करता है। इस मामले में आप मौजूदा उपयोगकर्ताओं को update चाहेंगे और नए insert ।
मर्ज और वृद्धिशील अद्यतन
आप मेर्ज का उपयोग कर सकते हैं या आप ऑपरेशन को दो क्रियाओं में विभाजित कर सकते हैं। एक नए के साथ मौजूदा रिकॉर्ड को अपडेट करने के लिए और एक पूरी तरह से नए को सम्मिलित करने के लिए जो बाहर नहीं निकलते हैं (बाएं शामिल होने की स्थिति)।
MERGE एक स्टेटमेंट है जो आमतौर पर रिलेशनल डेटाबेस में उपयोग किया जाता है। Google BigQuery MERGE कमांड डेटा मैनीपुलेशन लैंग्वेज (DML) स्टेटमेंट्स में से एक है। यह अक्सर एक ही बयान में परमाणु रूप से तीन मुख्य कार्य करने के लिए प्रयोग किया जाता है। ये कार्य UPDATE, INSERT और DELETE हैं।
- UPDATE या DELETE क्लॉज का उपयोग तब किया जा सकता है जब दो या दो से अधिक डेटा मेल खाते हों।
- INSERT क्लॉज़ का उपयोग तब किया जा सकता है जब दो या दो से अधिक डेटा भिन्न हों और मेल न खाते हों।
- UPDATE या DELETE क्लॉज का उपयोग तब भी किया जा सकता है जब दिया गया डेटा स्रोत से मेल नहीं खाता हो।
इसका अर्थ है कि Google BigQuery MERGE कमांड आपको अपनी Google BigQuery तालिकाओं से डेटा को अपडेट करने, सम्मिलित करने और हटाने के द्वारा Google BigQuery डेटा को मर्ज करने में सक्षम बनाता है।
इस एसक्यूएल पर विचार करें:
2. शब्दों की गिनती
UNNEST() करना और जांचें कि आपको जिस शब्द की आवश्यकता है वह उस सूची में है जिसकी आपको आवश्यकता है, कई स्थितियों में उपयोगी हो सकती है, अर्थात डेटा वेयरहाउस भावना विश्लेषण:
3. सेलेक्ट स्टेटमेंट के बाहर IF () स्टेटमेंट का उपयोग करना
यह हमें कोड की कुछ पंक्तियों को सहेजने और कोड-वार अधिक वाक्पटु होने का अवसर देता है। आम तौर पर आप इसे एक उप-क्वेरी में रखना चाहते हैं, और जहां क्लॉज में एक फिल्टर जोड़ते हैं, लेकिन आप इसके बजाय ऐसा कर सकते हैं:
एक और उदाहरण कैसे विभाजित तालिकाओं के साथ इसका उपयोग नहीं करना है। ऐसा मत करो । यह खराब उदाहरण है क्योंकि मिलान तालिका प्रत्यय संभवतः गतिशील रूप से निर्धारित किए जाते हैं (आपकी तालिका में किसी चीज़ के आधार पर) आपसे पूर्ण तालिका स्कैन के लिए शुल्क लिया जाएगा।
आप इसका उपयोग HAVING क्लॉज और AGGREGATE कार्यों में भी कर सकते हैं।
4. ग्रुप बाय रोलअप का उपयोग करना
रोलअप फ़ंक्शन का उपयोग कई स्तरों पर एकत्रीकरण करने के लिए किया जाता है। यह तब उपयोगी होता है जब आपको डायमेंशन ग्राफ़ के साथ काम करना होता है।
निम्न क्वेरी जहां खंड में निर्दिष्ट लेन-देन प्रकार (is_gift) द्वारा प्रति दिन कुल क्रेडिट खर्च लौटाती है, और यह प्रत्येक दिन के लिए कुल खर्च और सभी उपलब्ध तिथियों में कुल खर्च भी दिखाती है।
5. तालिका को JSON में बदलें
कल्पना करें कि आपको अपनी तालिका को JSON ऑब्जेक्ट में बदलने की आवश्यकता है जहां प्रत्येक रिकॉर्ड नेस्टेड सरणी का एक तत्व है। यह वह जगह है जहाँ to_json_string() फ़ंक्शन उपयोगी हो जाता है:
फिर आप इसे कहीं भी उपयोग कर सकते हैं: दिनांक, मार्केटिंग फ़नल, इंडेक्स, हिस्टोग्राम ग्राफ़ इत्यादि।
6. पार्टिशन बाय का उपयोग करना
दिया गया user_id , date और total_cost कॉलम। प्रत्येक तिथि के लिए, आप सभी पंक्तियों को रखते हुए प्रत्येक ग्राहक के लिए कुल राजस्व मूल्य कैसे दिखाते हैं? आप इसे इस प्रकार प्राप्त कर सकते हैं:
7. मूविंग एवरेज
बहुत बार बीआई डेवलपर्स को अपनी रिपोर्ट और शानदार डैशबोर्ड में मूविंग एवरेज जोड़ने का काम सौंपा जाता है। यह 7, 14, 30 दिन/महीना या साल का MA लाइन ग्राफ हो सकता है। तो हम ऐसा कैसे करें?
8. दिनांक सरणियाँ
जब आप उपयोगकर्ता प्रतिधारण के साथ काम करते हैं या लापता मानों, यानी तिथियों के लिए कुछ डेटासेट जांचना चाहते हैं तो वास्तव में आसान हो जाता है। BigQuery में GENERATE_DATE_ARRAY नाम का एक फ़ंक्शन है:
9. पंक्ति_संख्या ()
यह आपके डेटा से कुछ नवीनतम प्राप्त करने के लिए उपयोगी है, अर्थात नवीनतम अद्यतन रिकॉर्ड, आदि या डुप्लीकेट हटाने के लिए भी:
10. एनटीआईएलई ()
एक और नंबरिंग फ़ंक्शन। यदि आपके पास मोबाइल ऐप है तो Login duration in seconds जैसी चीजों की निगरानी करना वास्तव में उपयोगी है। उदाहरण के लिए, मेरे पास मेरा ऐप फायरबेस से जुड़ा है और जब उपयोगकर्ता login तो मैं देख सकता हूं कि उनके लिए कितना समय लगा।
यह फ़ंक्शन पंक्ति क्रम के आधार पर पंक्तियों को constant_integer_expression बकेट में विभाजित करता है और प्रत्येक पंक्ति को असाइन किया गया 1-आधारित बकेट नंबर लौटाता है। बकेट में पंक्तियों की संख्या अधिकतम 1 से भिन्न हो सकती है। शेष मान (बाल्टी द्वारा विभाजित पंक्तियों की संख्या का शेष) प्रत्येक बकेट के लिए एक वितरित किया जाता है, बकेट 1 से शुरू होता है। यदि constant_integer_expression NULL, 0 या नकारात्मक का मूल्यांकन करता है, एक त्रुटि प्रदान की जाती है।
11. रैंक / सघन_रैंक
उन्हें नंबरिंग फ़ंक्शन भी कहा जाता है। मैं DENSE_RANK डिफ़ॉल्ट रैंकिंग फ़ंक्शन के रूप में उपयोग करता हूं क्योंकि यह अगली उपलब्ध रैंकिंग को नहीं छोड़ता है जबकि RANK होगा। यह लगातार रैंक मान लौटाता है। आप इसे एक विभाजन के साथ उपयोग कर सकते हैं जो परिणामों को अलग-अलग बाल्टियों में विभाजित करता है। प्रत्येक विभाजन में पंक्तियाँ समान रैंक प्राप्त करती हैं यदि उनके समान मान हैं। उदाहरण:
उत्पाद की कीमतों के साथ एक और उदाहरण:
12. पिवोट / अनपिवोट
पिवट पंक्तियों को स्तंभों में बदलता है। यह सब करता है। Unpivot इसके विपरीत करता है।
13. फर्स्ट_वैल्यू / लास्ट_वैल्यू
यह एक और उपयोगी कार्य है जो उस विशेष विभाजन में पहले/अंतिम मान के विरुद्ध प्रत्येक पंक्ति के लिए डेल्टा प्राप्त करने में सहायता करता है।
14. एक टेबल को स्ट्रक्चर्स के ऐरे में बदलें और उन्हें UDF में पास करें
यह तब उपयोगी होता है जब आपको प्रत्येक पंक्ति या तालिका में कुछ जटिल तर्क के साथ उपयोगकर्ता परिभाषित फ़ंक्शन (यूडीएफ) लागू करने की आवश्यकता होती है। आप हमेशा अपनी तालिका को TYPE STRUCT ऑब्जेक्ट्स की एक सरणी के रूप में मान सकते हैं और फिर उनमें से प्रत्येक को UDF में पास कर सकते हैं। यह आपके तर्क पर निर्भर करता है। उदाहरण के लिए, मैं इसका उपयोग खरीद समाप्ति समय की गणना करने के लिए करता हूं:
इसी तरह से आप बिना UNION ALL के टेबल बना सकते हैं। उदाहरण के लिए, मैं इसका उपयोग इकाई परीक्षणों के लिए कुछ परीक्षण डेटा का मज़ाक उड़ाने के लिए करता हूँ। इस तरह आप अपने एडिटर में Alt + Shift + Down का उपयोग करके इसे बहुत तेजी से कर सकते हैं।
15. FOLLOWING और UNBOUNDED FOLLOWING का उपयोग करके ईवेंट फ़नल बनाना
अच्छा उदाहरण मार्केटिंग फ़नल हो सकता है। आपके डेटासेट में एक ही प्रकार की लगातार दोहराई जाने वाली घटनाएँ हो सकती हैं लेकिन आदर्श रूप से आप प्रत्येक घटना को एक अलग प्रकार की अगली घटना के साथ श्रृंखलाबद्ध करना चाहेंगे। यह तब उपयोगी हो सकता है जब आपको फ़नल डेटासेट बनाने के लिए किसी चीज़, यानी ईवेंट, खरीदारी आदि की सूची प्राप्त करने की आवश्यकता हो। पार्टिशन बाय के साथ काम करने से आपको सभी आगामी घटनाओं को समूह में रखने का अवसर मिलता है, भले ही उनमें से कितने प्रत्येक विभाजन में मौजूद हों।
16. रेगेक्सप
यदि आपको असंगठित डेटा, यानी एफएक्स दरें, कस्टम ग्रुपिंग इत्यादि से कुछ निकालने की ज़रूरत है तो आप इसका इस्तेमाल करेंगे।
रेगेक्सपी का उपयोग कर मुद्रा विनिमय दरों के साथ कार्य करना
विनिमय दर डेटा के साथ इस उदाहरण पर विचार करें:
रेगेक्सपी का उपयोग कर ऐप संस्करणों के साथ काम करना
कभी-कभी आप अपने ऐप के प्रमुख , रिलीज़ या मॉड संस्करण प्राप्त करने और एक कस्टम रिपोर्ट बनाने के लिए regexp उपयोग करना चाह सकते हैं:
निष्कर्ष
SQL एक शक्तिशाली उपकरण है जो डेटा में हेरफेर करने में मदद करता है। उम्मीद है कि डिजिटल मार्केटिंग के ये SQL यूज केस आपके लिए उपयोगी होंगे। यह वास्तव में एक आसान कौशल है और कई परियोजनाओं में आपकी मदद कर सकता है। इन SQL स्निपेट्स ने मेरे जीवन को बहुत आसान बना दिया है और मैं लगभग हर दिन काम पर उपयोग करता हूं। अधिक, SQL और आधुनिक डेटा वेयरहाउस डेटा विज्ञान के लिए आवश्यक उपकरण हैं। इसकी मजबूत बोली विशेषताएं डेटा को आसानी से मॉडल और विज़ुअलाइज़ करने की अनुमति देती हैं। क्योंकि SQL वह भाषा है जिसका उपयोग डेटा वेयरहाउस और व्यावसायिक खुफिया पेशेवर करते हैं, यदि आप उनके साथ डेटा साझा करना चाहते हैं तो यह एक उत्कृष्ट चयन है। यह बाजार में लगभग हर डेटा वेयरहाउस/झील समाधान के साथ संवाद करने का सबसे आम तरीका है।
मूल रूप से mydataschool.com में डेटामाइक द्वारा प्रकाशित
माइक एक भावुक और डिजिटल रूप से केंद्रित व्यक्ति है, जिसमें ड्राइव और उत्साह की प्रचुरता है, जो चुनौतियों से प्यार करता है, डिजिटल मार्केटिंग का पूरा मिश्रण फेंकता है। यूके में रहते हैं, 2015 में न्यूकैसल यूनिवर्सिटी से एमबीए पूरा किया।
