MS SQL: newID () का उपयोग करके छद्म यादृच्छिक डेटा पीढ़ी। सुविधाएँ और नुकसान

यह ज्ञात है कि बिल्ट-इन फंक्शन न्यूआईडी () व्यापक रूप से डेवलपर्स द्वारा न केवल अपने इच्छित उद्देश्य के लिए उपयोग किया जाता है - अर्थात, अद्वितीय प्राथमिक कुंजी उत्पन्न करने के लिए, बल्कि एक उद्देश्य के रूप में छद्म आयामी डेटा उत्पन्न करने के लिए।
अंतर्निहित कार्यों के हिस्से के रूप में, newID () वास्तव में एकमात्र ऐसा है जो न केवल गैर-नियतात्मक है, बल्कि सुपर-गैर-निर्धारक भी है, क्योंकि बाकी सभी के विपरीत, यह प्रत्येक नई पंक्ति के लिए एक नया मान उत्पन्न करने में सक्षम है, और पूरे बैच के लिए समान नहीं है - जो इस तरह की बड़े पैमाने पर पीढ़ी के लिए बेहद उपयोगी है। NewID () के अतिरिक्त, इस संपत्ति में newSequentialID () भी है, हालांकि, इसके उपयोग कहीं और है, इसके अलावा, टाइप यूनीकॉलीफायर के कॉलम का डिफ़ॉल्ट मान सेट करने के लिए निषिद्ध है।
आपको उदाहरण के लिए दूर नहीं जाना है - नीचे कोड है:

SELECT TOP 100 ABS(CHECKSUM(NEWID())) % 1000 FROM sysobjects A CROSS JOIN sysobjects B 

या यह एक (यदि चेकसम एक समय लेने वाली कार्रवाई की तरह लगता है):

 SELECT TOP 100 ABS(CONVERT(INT, (CONVERT(BINARY(4), (NEWID()))))) % 1000 FROM sysobjects A CROSS JOIN sysobjects B 

यह 0 से 999 तक की सीमा में 100 यादृच्छिक पूर्णांकों की एक तालिका उत्पन्न करेगा।

फ़्लोटिंग नंबरों के लिए, आप एक पूर्णांक के साथ जनरेटर को आरंभीकृत करने के लिए रैंड () फ़ंक्शन प्रॉपर्टी का उपयोग कर सकते हैं:

 SELECT TOP 100 RAND(CHECKSUM(NEWID())) FROM sysobjects A CROSS JOIN sysobjects B 

इस स्थिति में, रैंड () को अनिवार्य रूप से इंट 32 रेंज से रेंज [0..1) तक एक कनवर्टर के रूप में उपयोग किया जाता है। एक लाख के आदेश के रिकॉर्ड की संख्या पर इस पद्धति द्वारा वितरण की गुणवत्ता का सांख्यिकीय सत्यापन दिखाता है कि यह रैंड () के मानक उपयोग से नीच नहीं है, एक बार आरम्भ में, और फिर लूप में उपयोग किया जाता है। इसलिए - आप सुरक्षित रूप से उपयोग कर सकते हैं।

एक और दिलचस्प विकल्प सामान्य रूप से वितरित डेटा की पीढ़ी है। यहां हम बॉक्स-मुलर विधि का उपयोग करेंगे:

 SELECT TOP 1000 COS(2 * PI() * RAND(BINARY_CHECKSUM(NEWID()))) * SQRT(-2 * LOG(RAND(BINARY_CHECKSUM(NEWID())))) FROM sysobjects A CROSS JOIN sysobjects B CROSS JOIN sysobjects C 

जो लोग चाहते हैं वे यह सत्यापित कर सकते हैं कि उत्पन्न वितरण साजिश रचकर सामान्य के बहुत करीब है।

यह सब अच्छी तरह से काम करता है और आपको बहुत जल्दी से "हेड-ऑन" समाधानों का उपयोग किए बिना कम से कम एक दर्जन मिलियन रिकॉर्ड बनाने की अनुमति देता है, जैसे कि लूप, कर्सर, या यहां तक ​​कि एप्लिकेशन परत से एक बार रिकॉर्ड दर्ज करना। आपको केवल यह सुनिश्चित करने की आवश्यकता है कि पंक्ति स्रोत के रूप में आपके द्वारा उपयोग की जाने वाली तालिकाओं में पर्याप्त क्षमता है, और या तो CROSS JOINs की संख्या में वृद्धि करें, या स्रोत के रूप में वांछित संख्या पंक्तियों के साथ तालिका चर का उपयोग करें।

हालाँकि, विषय केवल इस बारे में नहीं है। अधिकांश मामलों में, उत्पन्न पंक्तियाँ भौतिक होती हैं, अर्थात, एक स्थिर या अस्थायी तालिका में, या एक तालिका चर में डाली जाती है। यदि ऐसा है, तो आप आगे पढ़ना छोड़ सकते हैं - भौतिक डेटा पूरी तरह से काम करेगा। हालांकि, ऐसे मामले हैं जहां उप-विवरणों में उपर्युक्त बयानों का उपयोग किया जाता है। और यहाँ, SQL इंजन के व्यवहार की प्रतीत होता है कि अकथनीय विशेषताएं दिखाई देती हैं। आइए उन्हें उदाहरणों के साथ देखें, और फिर विश्लेषण करने की कोशिश करें कि ऐसा क्यों होता है और इससे कैसे निपटें:

आरंभ करने के लिए, बस सब-वेरी में newID () के साथ स्टेटमेंट लिखें और इसे कई बार लूप में चलाएं:

 declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ set @c = @c + 1 end 

कोड अपेक्षित रूप में काम करता है - यह 5 परिणाम सेट देता है, प्रत्येक कड़ाई से 0 से 4 तक की संख्या में एक रिकॉर्ड होता है। मैं परिणामों का स्क्रीनशॉट नहीं देता - जब सब कुछ ठीक होता है, तो उनमें बहुत कम समझ होती है।

अब और दिलचस्प। हम SUBQ से परिणाम को किसी अन्य तालिका में ट्यून करने का प्रयास कर रहे हैं। आप इसे बना सकते हैं, या आप सबक्वेरी को सबक्वेरी में बदल सकते हैं - इसका नतीजा नहीं बदलेगा। हम लिखते हैं:

 declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ INNER JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end 

हम निष्पादन के परिणाम के स्क्रीनशॉट को देखते हैं - और धीरे-धीरे कुर्सी के नीचे क्रॉल करते हैं - प्रत्येक परिणाम सेट में लाइनों की संख्या ठीक 1 नहीं है। कहीं न कहीं खाली है (इसे कम से कम किसी तरह समझाया जा सकता है - INNER JOIN ने काम नहीं किया क्योंकि RNDID ने सीमा छोड़ दी थी [0]। .4] (जो अपने आप में अविश्वसनीय है!)), लेकिन कहीं - एक से अधिक (!) रिकॉर्ड।



अब हम एक निर्दोष परिवर्तन करते हैं - INNER को LEFT में बदलें:

 declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ) SUBQ LEFT JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end 

हम प्रदर्शन करते हैं - सब कुछ सही ढंग से काम करना शुरू कर दिया (!) - अपने आप को जांच लें, मैंने सही संचालन के लिए स्क्रीनशॉट नहीं लिया। ध्यान दें कि किसी भी RNDIDX मान के लिए रेंज [0..4] से जो कि SUBQ सबक्वेरी को आउटपुट करने में सक्षम है, NUM सबक्वेरी से हमेशा वैल वैल्यू है, तर्क के दृष्टिकोण से, LEFT और INNER JOIN का परिणाम समान होना चाहिए। हालाँकि, वास्तव में ऐसा नहीं है!

एक और परीक्षण - हम INNER लौटाते हैं, लेकिन पहले उपश्रेणी में TOP / ORDER BY जोड़ते हैं। क्यों - उस पर और बाद में, आइए कोशिश करते हैं:

 declare @c int = 0 while @c < 5 begin SELECT * FROM ( SELECT TOP 1 ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM ( SELECT 1 AS ID ) ROWSRC ORDER BY RNDIDX ) SUBQ INNER JOIN ( SELECT 0 AS VAL UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) NUM ON SUBQ.RNDIDX = NUM.VAL set @c = @c + 1 end 

सब कुछ फिर से सही ढंग से काम करता है! रहस्यवादी!

Googling, हमें पता चलता है कि दुनिया भर के SQL डेवलपर्स समय-समय पर इस तरह के व्यवहार का सामना करते हैं - यहाँ या यहाँ उदाहरण

लोग सुझाव देते हैं कि उपशमिकीय भौतिकीकरण मदद करता है। वास्तव में, यदि हम उदाहरण को फिर से लिखते हैं, तो पहले अस्थायी तालिका में स्पष्ट रिकॉर्ड का चयन करें, और उसके बाद ही इसे समाप्त करें, सब कुछ ठीक काम करता है। क्यों LEFT के साथ INNER का प्रतिस्थापन या TOP / ORDER का जोड़ जहां इसकी आवश्यकता नहीं है? यह सब इसलिए है क्योंकि - एक मामले में सबक्वेरी परिणामों का एक भौतिककरण है, दूसरे में - नहीं। उदाहरण के लिए, अधिक विस्तृत मामले की योजना का विश्लेषण, यह अधिक स्पष्ट अंतर दिखा सकता है:

 DECLARE @B TABLE (VAL INT) INSERT INTO @B VALUES (0), (1), (2), (3), (4) SELECT * FROM ( SELECT ABS(CONVERT(INT, CONVERT(BINARY(4), NEWID()))) % 5 AS RNDIDX FROM @B ) SUBQ INNER JOIN @BB ON SUBQ.RNDIDX = B.VAL  ,   (query 2),   @B    : 

हम देखते हैं कि क्वेरी न्यूआईडी () के आधार पर कॉलम के मूल्य की गणना करने से पहले पंक्तियों की दो धाराओं को मिलाती है। ऐसा इसलिए हो सकता है क्योंकि SQL इंजन मानता है कि newID द्वारा लौटाया गया मान (), हालांकि गैर-नियतात्मक है, पूरे बैच में नहीं बदलता है। हालांकि, यह मामला नहीं है - और सबसे अधिक संभावना है इसलिए अनुरोध सही तरीके से काम नहीं करता है। अब INNER को LEFT में बदलें, और योजना देखें:



हाँ, बाएँ ज्वाइंट ने SQL इंजन को थ्रेड्स मर्ज करने से पहले कंप्यूट स्केलर निष्पादित करने के लिए मजबूर किया, इसलिए हमारी क्वेरी सही तरीके से काम करना शुरू कर दी।

अंत में, TOP / ORDER BY के साथ संस्करण की जाँच करें:



दरअसल, निदान स्पष्ट है। एमएस SQL ​​नएआईडी () की ख़ासियत को ध्यान में नहीं रखता है, और तदनुसार, यह गलत तरीके से योजनाओं का निर्माण करता है, जो बैच के दायरे में फ़ंक्शन द्वारा लौटाए गए निरंतर मूल्य पर निर्भर करता है। इस सुविधा के लिए चारों ओर एक काम है - निर्भर प्रश्नों में उपयोग करने से पहले किसी भी तरह से चयन को उत्प्रेरित करने के लिए SQL इंजन को मजबूर करना। आप कैसे मटेरियल करेंगे, यह आप पर निर्भर करता है, लेकिन टेबल वेरिएबल्स का उपयोग करना सबसे अच्छा है, खासकर अगर सब्स्क्राइब का आकार छोटा है। अन्यथा, परिणाम, इसे हल्के ढंग से रखने के लिए, 100% गारंटी नहीं है; इसके अलावा, इस बात की कोई गारंटी नहीं है कि एक दिन आप स्वयं या कोई और “अनावश्यक” TOP / ORDER द्वारा या बुद्धिमानी से LEFT को INNER के साथ प्रतिस्थापित करके कोड की समीक्षा नहीं करेगा।

दरअसल, बस इतना ही। एक अच्छी SQL प्रोग्रामिंग करें!