16 SQL तकनीकें प्रत्येक नौसिखिए को जानना आवश्यक है

1 से 10 के पैमाने पर आपका डेटा भंडारण कौशल कितना अच्छा है? 7/10 से ऊपर जाना चाहते हैं? यह लेख तब आपके लिए है। आपका एसक्यूएल कितना अच्छा है? जल्द से जल्द नौकरी के लिए इंटरव्यू के लिए तैयार होना चाहते हैं? यह ब्लॉग पोस्ट सबसे जटिल डेटा वेयरहाउस SQL तकनीकों के बारे में विस्तार से बताता है। मैं इस विषय पर कुछ विचार लिखने के लिए BigQuery मानक SQL बोली का उपयोग करूँगा। 1. इंक्रीमेंटल टेबल और MERGE अद्यतन तालिका महत्वपूर्ण है। यह वाकई महत्वपूर्ण है। आदर्श स्थिति तब होती है जब आपके पास प्राथमिक कुंजी, अद्वितीय पूर्णांक और ऑटो वृद्धि वाले लेनदेन होते हैं। इस मामले में तालिका अद्यतन सरल है: https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql आधुनिक डेटा वेयरहाउस में असामान्य स्टार-स्कीमा डेटासेट के साथ काम करते समय हमेशा ऐसा नहीं होता है। आपको SQL के साथ बनाने और/या डेटा के केवल एक हिस्से के साथ डेटासेट को वृद्धिशील रूप से अपडेट करने का काम सौंपा जा सकता है। मौजूद नहीं हो सकता है लेकिन इसके बजाय आपको डेटा मॉडल से निपटना होगा जहां अद्वितीय कुंजी नवीनतम (या टाइमस्टैम्प) पर निर्भर करती है। उदाहरण के लिए, डेटासेट में नवीनतम ज्ञात कनेक्शन टाइमस्टैम्प पर निर्भर करता है। इस मामले में आप मौजूदा उपयोगकर्ताओं को चाहेंगे और नए । सत्र transaction_id transaction_id last_online user_id update insert मर्ज और वृद्धिशील अद्यतन आप उपयोग कर सकते हैं या आप ऑपरेशन को दो क्रियाओं में विभाजित कर सकते हैं। एक नए के साथ मौजूदा रिकॉर्ड को अपडेट करने के लिए और एक पूरी तरह से नए को सम्मिलित करने के लिए जो बाहर नहीं निकलते हैं (बाएं शामिल होने की स्थिति)। मेर्ज का एक स्टेटमेंट है जो आमतौर पर रिलेशनल डेटाबेस में उपयोग किया जाता है। Google BigQuery MERGE कमांड डेटा मैनीपुलेशन लैंग्वेज (DML) स्टेटमेंट्स में से एक है। यह अक्सर एक ही बयान में परमाणु रूप से तीन मुख्य कार्य करने के लिए प्रयोग किया जाता है। ये कार्य UPDATE, INSERT और DELETE हैं। MERGE UPDATE या DELETE क्लॉज का उपयोग तब किया जा सकता है जब दो या दो से अधिक डेटा मेल खाते हों। INSERT क्लॉज़ का उपयोग तब किया जा सकता है जब दो या दो से अधिक डेटा भिन्न हों और मेल न खाते हों। UPDATE या DELETE क्लॉज का उपयोग तब भी किया जा सकता है जब दिया गया डेटा स्रोत से मेल नहीं खाता हो। इसका अर्थ है कि Google BigQuery MERGE कमांड आपको अपनी Google BigQuery तालिकाओं से डेटा को अपडेट करने, सम्मिलित करने और हटाने के द्वारा Google BigQuery डेटा को मर्ज करने में सक्षम बनाता है। इस एसक्यूएल पर विचार करें: https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql 2. शब्दों की गिनती UNNEST() करना और जांचें कि आपको जिस शब्द की आवश्यकता है वह उस सूची में है जिसकी आपको आवश्यकता है, कई स्थितियों में उपयोगी हो सकती है, अर्थात डेटा वेयरहाउस भावना विश्लेषण: https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 3. सेलेक्ट स्टेटमेंट के बाहर IF () स्टेटमेंट का उपयोग करना यह हमें कोड की कुछ पंक्तियों को सहेजने और कोड-वार अधिक वाक्पटु होने का अवसर देता है। आम तौर पर आप इसे एक उप-क्वेरी में रखना चाहते हैं, और क्लॉज में एक फिल्टर जोड़ते हैं, लेकिन आप इसके बजाय कर सकते हैं: जहां ऐसा https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql एक और उदाहरण कैसे तालिकाओं के साथ इसका उपयोग करना है। । यह खराब उदाहरण है क्योंकि मिलान तालिका प्रत्यय संभवतः गतिशील रूप से निर्धारित किए जाते हैं (आपकी तालिका में किसी चीज़ के आधार पर) आपसे विभाजित नहीं ऐसा मत करो पूर्ण तालिका स्कैन के लिए शुल्क लिया जाएगा। https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql आप इसका उपयोग क्लॉज और कार्यों में भी कर सकते हैं। HAVING AGGREGATE 4. ग्रुप बाय रोलअप का उपयोग करना रोलअप फ़ंक्शन का उपयोग कई स्तरों पर एकत्रीकरण करने के लिए किया जाता है। यह तब उपयोगी होता है जब आपको डायमेंशन ग्राफ़ के साथ काम करना होता है। निम्न क्वेरी खंड में निर्दिष्ट लेन-देन प्रकार (is_gift) द्वारा प्रति दिन कुल क्रेडिट खर्च लौटाती है, और यह प्रत्येक दिन के लिए कुल खर्च और सभी उपलब्ध तिथियों में कुल खर्च भी दिखाती है। जहां https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 5. तालिका को JSON में बदलें कल्पना करें कि आपको अपनी तालिका को JSON ऑब्जेक्ट में बदलने की आवश्यकता है जहां प्रत्येक रिकॉर्ड नेस्टेड सरणी का एक तत्व है। यह वह जगह है जहाँ फ़ंक्शन उपयोगी हो जाता है: to_json_string() https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql फिर आप इसे कहीं भी उपयोग कर सकते हैं: दिनांक, मार्केटिंग फ़नल, इंडेक्स, हिस्टोग्राम ग्राफ़ इत्यादि। 6. पार्टिशन बाय का उपयोग करना दिया गया , और कॉलम। प्रत्येक तिथि के लिए, आप सभी पंक्तियों को रखते हुए प्रत्येक ग्राहक के लिए कुल राजस्व मूल्य कैसे दिखाते हैं? आप इसे इस प्रकार प्राप्त कर सकते हैं: user_id date total_cost https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 7. मूविंग एवरेज बहुत बार बीआई डेवलपर्स को अपनी रिपोर्ट और शानदार डैशबोर्ड में मूविंग एवरेज जोड़ने का काम सौंपा जाता है। यह 7, 14, 30 दिन/महीना या साल का MA लाइन ग्राफ हो सकता है। तो हम ऐसा कैसे करें? https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 8. दिनांक सरणियाँ जब आप के साथ काम करते हैं या लापता मानों, यानी तिथियों के लिए कुछ डेटासेट जांचना चाहते हैं तो वास्तव में आसान हो जाता है। BigQuery में नाम का एक फ़ंक्शन है: उपयोगकर्ता प्रतिधारण GENERATE_DATE_ARRAY https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 9. पंक्ति_संख्या () यह आपके डेटा से कुछ नवीनतम प्राप्त करने के लिए उपयोगी है, अर्थात नवीनतम अद्यतन रिकॉर्ड, आदि या डुप्लीकेट हटाने के लिए भी: https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 10. एनटीआईएलई () एक और नंबरिंग फ़ंक्शन। यदि आपके पास मोबाइल ऐप है तो जैसी चीजों की निगरानी करना वास्तव में उपयोगी है। उदाहरण के लिए, मेरे पास मेरा ऐप फायरबेस से जुड़ा है और जब उपयोगकर्ता तो मैं देख सकता हूं कि उनके लिए कितना समय लगा। Login duration in seconds login यह फ़ंक्शन पंक्ति क्रम के आधार पर पंक्तियों को बकेट में विभाजित करता है और प्रत्येक पंक्ति को असाइन किया गया 1-आधारित बकेट नंबर लौटाता है। बकेट में पंक्तियों की संख्या अधिकतम 1 से भिन्न हो सकती है। शेष मान (बाल्टी द्वारा विभाजित पंक्तियों की संख्या का शेष) प्रत्येक बकेट के लिए एक वितरित किया जाता है, बकेट 1 से शुरू होता है। यदि NULL, 0 या नकारात्मक का मूल्यांकन करता है, एक त्रुटि प्रदान की जाती है। constant_integer_expression constant_integer_expression https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11. रैंक / सघन_रैंक उन्हें फ़ंक्शन भी कहा जाता है। मैं उपयोग करता हूं क्योंकि यह अगली उपलब्ध रैंकिंग को नहीं छोड़ता है जबकि होगा। यह लगातार रैंक मान लौटाता है। आप इसे एक विभाजन के साथ उपयोग कर सकते हैं जो परिणामों को अलग-अलग बाल्टियों में विभाजित करता है। प्रत्येक विभाजन में पंक्तियाँ समान रैंक प्राप्त करती हैं यदि उनके समान मान हैं। नंबरिंग DENSE_RANK डिफ़ॉल्ट रैंकिंग फ़ंक्शन के रूप में RANK उदाहरण: https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql उत्पाद की कीमतों के साथ एक और उदाहरण: https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 12. पिवोट / अनपिवोट पिवट पंक्तियों को स्तंभों में बदलता है। यह सब करता है। Unpivot इसके करता है। विपरीत https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 13. फर्स्ट_वैल्यू / लास्ट_वैल्यू यह एक और उपयोगी कार्य है जो उस विशेष विभाजन में पहले/अंतिम मान के विरुद्ध प्रत्येक पंक्ति के लिए डेल्टा प्राप्त करने में सहायता करता है। https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 14. एक टेबल को स्ट्रक्चर्स के ऐरे में बदलें और उन्हें UDF में पास करें यह तब उपयोगी होता है जब आपको प्रत्येक पंक्ति या तालिका में कुछ जटिल तर्क के साथ उपयोगकर्ता परिभाषित फ़ंक्शन (यूडीएफ) लागू करने की आवश्यकता होती है। आप हमेशा अपनी तालिका को TYPE STRUCT ऑब्जेक्ट्स की एक सरणी के रूप में मान सकते हैं और फिर उनमें से प्रत्येक को UDF में पास कर सकते हैं। यह आपके तर्क पर निर्भर करता है। उदाहरण के लिए, मैं इसका उपयोग खरीद समाप्ति समय की गणना करने के लिए करता हूं: https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql इसी तरह से आप बिना टेबल बना सकते हैं। उदाहरण के लिए, मैं इसका उपयोग इकाई परीक्षणों के लिए कुछ परीक्षण डेटा का मज़ाक उड़ाने के लिए करता हूँ। इस तरह आप अपने एडिटर में + + का उपयोग करके इसे बहुत तेजी से कर सकते हैं। UNION ALL के Alt Shift Down https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 15. FOLLOWING और UNBOUNDED FOLLOWING का उपयोग करके ईवेंट फ़नल बनाना अच्छा उदाहरण मार्केटिंग फ़नल हो सकता है। आपके डेटासेट में एक ही प्रकार की लगातार दोहराई जाने वाली घटनाएँ हो सकती हैं लेकिन आदर्श रूप से आप प्रत्येक घटना को एक अलग प्रकार की अगली घटना के साथ श्रृंखलाबद्ध करना चाहेंगे। यह तब उपयोगी हो सकता है जब आपको फ़नल डेटासेट बनाने के लिए किसी चीज़, यानी ईवेंट, खरीदारी आदि की सूची प्राप्त करने की आवश्यकता हो। पार्टिशन बाय के साथ काम करने से आपको सभी आगामी घटनाओं को समूह में रखने का अवसर मिलता है, भले ही उनमें से कितने प्रत्येक विभाजन में मौजूद हों। https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 16. रेगेक्सप यदि आपको असंगठित डेटा, यानी एफएक्स दरें, कस्टम ग्रुपिंग इत्यादि से कुछ निकालने की ज़रूरत है तो आप इसका इस्तेमाल करेंगे। रेगेक्सपी का उपयोग कर मुद्रा विनिमय दरों के साथ कार्य करना विनिमय दर डेटा के साथ इस उदाहरण पर विचार करें: https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql रेगेक्सपी का उपयोग कर ऐप संस्करणों के साथ काम करना कभी-कभी आप अपने ऐप के , या संस्करण प्राप्त करने और एक कस्टम रिपोर्ट बनाने के लिए उपयोग करना चाह सकते हैं: प्रमुख रिलीज़ मॉड regexp https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql निष्कर्ष SQL एक शक्तिशाली उपकरण है जो डेटा में हेरफेर करने में मदद करता है। उम्मीद है कि डिजिटल मार्केटिंग के ये SQL यूज केस आपके लिए उपयोगी होंगे। यह वास्तव में एक आसान कौशल है और कई परियोजनाओं में आपकी मदद कर सकता है। इन SQL स्निपेट्स ने मेरे जीवन को बहुत आसान बना दिया है और मैं लगभग हर दिन काम पर उपयोग करता हूं। अधिक, SQL और आधुनिक डेटा वेयरहाउस डेटा विज्ञान के लिए आवश्यक उपकरण हैं। इसकी मजबूत बोली विशेषताएं डेटा को आसानी से मॉडल और विज़ुअलाइज़ करने की अनुमति देती हैं। क्योंकि SQL वह भाषा है जिसका उपयोग डेटा वेयरहाउस और व्यावसायिक खुफिया पेशेवर करते हैं, यदि आप उनके साथ डेटा साझा करना चाहते हैं तो यह एक उत्कृष्ट चयन है। यह बाजार में लगभग हर डेटा वेयरहाउस/झील समाधान के साथ संवाद करने का सबसे आम तरीका है। मूल रूप से में द्वारा प्रकाशित mydataschool.com डेटामाइक माइक एक भावुक और डिजिटल रूप से केंद्रित व्यक्ति है, जिसमें ड्राइव और उत्साह की प्रचुरता है, जो चुनौतियों से प्यार करता है, डिजिटल मार्केटिंग का पूरा मिश्रण फेंकता है। यूके में रहते हैं, 2015 में न्यूकैसल यूनिवर्सिटी से एमबीए पूरा किया।