👩🏻‍🤝‍👨🏽 🏴 ⚡️ MySQL - एक क्वेरी में चर का उपयोग करना 👨🏿‍🍳 🖐️ ❄️

अक्सर वे पूछते हैं कि क्या MySQL में विश्लेषणात्मक (विंडो) फ़ंक्शन के एनालॉग हैं। नोट। लेखन के समय, इस तरह के कोई एनालॉग नहीं थे, लेकिन लेख अभी भी MySQL के लिए चर का उपयोग करने के लिए मूल दृष्टिकोण को पार्स करने के संदर्भ में अकादमिक रुचि है।

विश्लेषणात्मक कार्यों को बदलने के लिए, स्व-कनेक्टिंग क्वेरी, जटिल उपश्रेणी, और अधिक अक्सर उपयोग किए जाते हैं। प्रदर्शन के संदर्भ में इनमें से अधिकांश समाधान अप्रभावी हैं।

इसके अलावा MySQL में कोई पुनरावृत्ति नहीं है। हालाँकि, कुछ कार्य जो आमतौर पर विश्लेषणात्मक कार्यों या पुनरावृत्ति द्वारा हल किए जाते हैं, उन्हें MySQL टूल द्वारा नियंत्रित किया जा सकता है।

इन उपकरणों में से एक SQL क्वेरी के अंदर चर के साथ काम करने के अन्य DBMS तंत्र के लिए एक अनूठा, अप्राप्य है। हम क्वेरी के अंदर एक चर घोषित कर सकते हैं, इसके मूल्य को बदल सकते हैं और इसे आउटपुट के लिए चयन में बदल सकते हैं। इसके अलावा, अनुरोध में लाइनों को संसाधित करने का क्रम और, परिणामस्वरूप, चर को मान असाइन करने का क्रम कस्टम छँटाई में सेट किया जा सकता है!

चेतावनी। लेख मानता है कि चयन खंड में अभिव्यक्तियों का प्रसंस्करण बाएं से दाएं किया जाता है, हालांकि, MySQL प्रलेखन में इस प्रसंस्करण आदेश की कोई आधिकारिक पुष्टि नहीं है। सर्वर संस्करण को बदलते समय इसे ध्यान में रखा जाना चाहिए। स्थिरता सुनिश्चित करने के लिए, आप डमी CASE या IF स्टेटमेंट का उपयोग कर सकते हैं।

पुनरावर्तन एनालॉग

एक साधारण उदाहरण पर विचार करें जो एक फाइबोनैचि अनुक्रम उत्पन्न करता है (फाइबोनैचि अनुक्रम में, प्रत्येक शब्द पिछले दो के योग के बराबर है, और पहले 2 एक के बराबर हैं):

SELECT IF(X=1, Fn_1, Fn_2) F FROM( SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2 FROM (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a, (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b, (SELECT @I := 1, @J := 1)IJ )T, /* ,     1 */ (SELECT 1 X UNION ALL SELECT 2)X;

यह क्वेरी 18 फाइबोनैचि संख्या उत्पन्न करती है, पहले दो की गिनती नहीं:

 2,3,5,8,13,21,34,55,89,144,233,377,610,987,1597,2584,4181,6765

अब देखते हैं कि यह कैसे काम करता है।

5) 6) लाइनों में 9 रिकॉर्ड उत्पन्न होते हैं। यहां कुछ भी असामान्य नहीं है।

लाइन 7 में) हम दो वेरिएबल्स @I, @J घोषित करते हैं और उन्हें 1 असाइन करते हैं।

लाइन 3 पर), निम्न होता है: पहला, चर @I को दो चर का योग सौंपा गया है। तब हम वैरिएबल @J को समान असाइन करते हैं, इस तथ्य को ध्यान में रखते हुए कि @I का मूल्य पहले ही बदल चुका है।

दूसरे शब्दों में, SELECT में गणना बाएं से दाएं की जाती है - लेख की शुरुआत में टिप्पणी भी देखें।

इसके अलावा, चर का परिवर्तन हमारे प्रत्येक 9 रिकॉर्ड में किया जाता है, अर्थात प्रत्येक नई पंक्ति को संसाधित करते समय, चर @I और @J में पिछली पंक्ति को संसाधित करके गणना किए गए मान शामिल होंगे।

अन्य DBMS का उपयोग करके इसी समस्या को हल करने के लिए, हमें एक पुनरावर्ती प्रश्न लिखना होगा !

नोट:
चर को एक अलग उपवर्ग (लाइन 7) में घोषित किया जाना चाहिए, अगर हम SELECT क्लॉज में एक चर घोषित करते हैं, तो यह सबसे अधिक संभावना केवल 1 बार मूल्यांकन किया जाएगा (हालांकि विशिष्ट व्यवहार सर्वर के संस्करण पर निर्भर करेगा)। एक चर का प्रकार उस मूल्य से निर्धारित किया जाता है जिसके द्वारा इसे आरंभीकृत किया जाता है। यह प्रकार गतिशील रूप से बदल सकता है। यदि आप वैरिएबल को NULL पर सेट करते हैं, तो इसका प्रकार BLOB होगा।

क्रम जिसमें SELECT में प्रोसेस किया गया है, जैसा कि ऊपर बताया गया है, कस्टम छँटाई पर निर्भर करता है। दिए गए क्रम में लाइन नंबरिंग का एक सरल उदाहरण:

 SELECT val, @I:=@I+1 Num FROM (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a, (SELECT @I := 0)I ORDER BY val;

 Val Num 10 1 20 2 30 3 50 4

विश्लेषणात्मक कार्यों के एनालॉग

चर का उपयोग विश्लेषणात्मक कार्यों को बदलने के लिए भी किया जा सकता है। निम्नलिखित कुछ उदाहरण हैं। सादगी के लिए, हम मानते हैं कि सभी फ़ील्ड NULL नहीं हैं, और एक फ़ील्ड पर सॉर्टिंग और पार्टीशन (पार्टीशन बाय) होते हैं। NULL मान और अधिक जटिल सॉर्टिंग का उपयोग करने से उदाहरण अधिक बोझिल हो जाएंगे, लेकिन सार नहीं बदलेगा।

उदाहरण के लिए, TestTable तालिका बनाएँ:

 CREATE TABLE TestTable( group_id INT NOT NULL, order_id INT UNIQUE NOT NULL, value INT NOT NULL );

जहाँ
group_id - समूह पहचानकर्ता (विश्लेषणात्मक फ़ंक्शन विंडो का एनालॉग);
order_id - छँटाई के लिए एक अनूठा क्षेत्र;
मूल्य कुछ संख्यात्मक मूल्य है।

परीक्षण डेटा के साथ हमारी तालिका भरें:

 INSERT TestTable(order_id, group_id, value) SELECT * FROM( SELECT 1 order_id, 1 group_id, 1 value UNION ALL SELECT 2, 1, 2 UNION ALL SELECT 3, 1, 2 UNION ALL SELECT 4, 2, 1 UNION ALL SELECT 5, 2, 2 UNION ALL SELECT 6, 2, 3 UNION ALL SELECT 7, 3, 1 UNION ALL SELECT 8, 3, 2 UNION ALL SELECT 9, 4, 1 UNION ALL SELECT 11, 3, 2 )T;

कुछ विश्लेषणात्मक कार्यों के प्रतिस्थापन के उदाहरण।

1) ROW_NUMBER () OVER (ORDER BY order_id)

 SELECT T.*, @I:=@I+1 RowNum FROM TestTable T,(SELECT @I:=0)I ORDER BY order_id;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 4
2 5 2 5
2 6 3 6
3 7 1 7
3 8 2 8
4 9 1 9
3 11 2 10

2) ROW_NUMBER () OVER (विभाजन group_id ORDER BY ऑर्डर_आईडी द्वारा)

 SELECT group_id, order_id, value, RowNum FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNum, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;

group_id order_id value RowNum
1 1 1 1
1 2 2 2
1 3 2 3
2 4 1 1
2 5 2 2
2 6 3 3
3 7 1 1
3 8 2 2
3 11 2 3
4 9 1 1

3) SUM (मान) OVER (विभाजन group_id ORDER BY ऑर्डर_आईडी द्वारा)

 SELECT group_id, order_id, value, RunningTotal FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+value, @I:=value) RunningTotal, @last_group_id := group_id FROM TestTable T, (SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id )T;

group_id order_id value RunningTotal
1 1 1 1
1 2 2 3
1 3 2 5
2 4 1 1
2 5 2 3
2 6 3 6
3 7 1 1
3 8 2 3
3 11 2 5
4 9 1 1

4) LAG (मान) OVER (विभाजन group_id ORDER BY ऑर्डर_आईडी द्वारा)

 SELECT group_id, order_id, value, LAG FROM( SELECT T.*, IF(@last_group_id = group_id, @last_value, NULL) LAG, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL)I ORDER BY group_id, order_id )T;

group_id order_id value LAG
1 1 1 NULL
1 2 2 1
1 3 2 2
2 4 1 NULL
2 5 2 1
2 6 3 2
3 7 1 NULL
3 8 2 1
3 11 2 2
4 9 1 NULL

LEAD के लिए, सब कुछ एक जैसा है, केवल आपको क्रम-समूह द्वारा ORDER को क्रमबद्ध रूप से बदलने की आवश्यकता है, आदेश_D DESC

COUNT, MIN, MAX के कार्यों के लिए, सब कुछ कुछ अधिक जटिल है, क्योंकि जब तक हम समूह (विंडो) में सभी लाइनों का विश्लेषण नहीं करेंगे, तब तक हम फ़ंक्शन के मूल्य का पता नहीं लगा पाएंगे। एमएस एसक्यूएल, उदाहरण के लिए, इन उद्देश्यों के लिए एक विंडो "स्पूल" (अस्थायी रूप से उन्हें फिर से एक्सेस करने के लिए एक छिपी हुई बफर तालिका में विंडो पंक्तियां डालता है), MySQL में ऐसी कोई संभावना नहीं है। लेकिन हम किसी दिए गए सॉर्टिंग के लिए प्रत्येक विंडो के लिए अंतिम पंक्ति में फ़ंक्शन के मूल्य की गणना कर सकते हैं (यानी, पूरी विंडो का विश्लेषण करने के बाद), और फिर, रिवर्स ऑर्डर में विंडो में लाइनों को सॉर्ट करते हुए, पूरे विंडो में गणना मूल्य डालते हैं।

इसलिए हमें दो छंटनी चाहिए। ताकि अंतिम छँटाई उसी तरह बनी रहे जैसे ऊपर के उदाहरणों में, हम पहले फ़ील्ड group_id ASC, order_id DESC, फिर फ़ील्ड group_id ASC, order_id ASC द्वारा क्रमबद्ध करते हैं।

5) COUNT (*) OVER (विभाजन by group_id)

पहले प्रकार में, हम बस प्रविष्टियों को संख्या देते हैं। दूसरे में, हम विंडो की सभी लाइनों को अधिकतम संख्या प्रदान करते हैं, जो विंडो में लाइनों की संख्या के अनुरूप होगी।

 SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxRowNum, @MaxRowNum := RowNumDesc) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, @I:=@I+1, @I:=1) RowNumDesc, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @I:=0)I ORDER BY group_id, order_id DESC /* */ )T,(SELECT @last_group_id:=NULL, @MaxRowNum:=NULL)I ORDER BY group_id, order_id /* */ )T;

group_id order_id value Cnt
1 1 1 3
1 2 2 3
1 3 2 3
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 3
3 8 2 3
3 11 2 3
4 9 1 1

अधिकतम और न्यूनतम कार्यों की गणना सादृश्य द्वारा की जाती है। मैं केवल MAX के लिए एक उदाहरण दूंगा:

6) MAX (मान) OVER (विभाजन by group_id)

 SELECT group_id, order_id, value, MaxVal FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @MaxVal, @MaxVal := MaxVal) MaxVal, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, GREATEST(@MaxVal, value), @MaxVal:=value) MaxVal, @last_group_id := group_id FROM TestTable T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id DESC )T,(SELECT @last_group_id:=NULL, @MaxVal:=NULL)I ORDER BY group_id, order_id )T;

group_id order_id value MaxVal
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

7) COUNT (DISTINCT मूल्य) OVER (पार्टी समूह द्वारा भाग)

एक दिलचस्प बात जो एमएस SQL सर्वर में उपलब्ध नहीं है, लेकिन इसकी गणना आरएके से मैक्स लेने पर एक उपशम के साथ की जा सकती है। हम यहां भी वही करेंगे। पहले प्रकार में, हम RANK () OVER (PARTITION BY group_id ORDER BY value DESC) की गणना करते हैं, फिर दूसरे क्रम में हम प्रत्येक विंडो में सभी लाइनों के लिए अधिकतम मूल्य डालते हैं:

 SELECT group_id, order_id, value, Cnt FROM( SELECT group_id, order_id, value, IF(@last_group_id = group_id, @Rank, @Rank := Rank) Cnt, @last_group_id := group_id FROM( SELECT T.*, IF(@last_group_id = group_id, IF(@last_value = value, @Rank, @Rank:=@Rank+1) , @Rank:=1) Rank, @last_group_id := group_id, @last_value := value FROM TestTable T,(SELECT @last_value:=NULL, @last_group_id:=NULL, @Rank:=0)I ORDER BY group_id, value DESC, order_id DESC )T,(SELECT @last_group_id:=NULL, @Rank:=NULL)I ORDER BY group_id, value, order_id )T;

group_id order_id value Cnt
1 1 1 2
1 2 2 2
1 3 2 2
2 4 1 3
2 5 2 3
2 6 3 3
3 7 1 2
3 8 2 2
3 11 2 2
4 9 1 1

उत्पादकता

इसके साथ शुरू करने के लिए, हम स्व-जुड़ने और चर का उपयोग करके क्वेरी में लाइन नंबरिंग के प्रदर्शन की तुलना करते हैं।

1) सेल्फ-कनेक्टिंग के साथ क्लासिक तरीका

 SELECT COUNT(*)N, T1.* FROM TestTable T1 JOIN TestTable T2 ON T1.order_id >= T2.order_id GROUP BY T1.order_id;

तालिका में 10000 रिकॉर्ड के लिए क्या टेस्टटेबल का उत्पादन होता है:

अवधि / लाम
16.084 सेकंड / 0.016 सेकंड

2) चर का उपयोग करना:

 SELECT @N:=@N+1 N, T1.* FROM TestTable T1, (SELECT @N := 0)M ORDER BY T1.order_id;

यह उत्पादन करता है:

अवधि / लाम
0.016 सेकंड / 0.015 सेकंड

परिणाम अपने लिए बोलता है। हालांकि, यह समझना चाहिए कि चर का उपयोग करके गणना किए गए मानों को फ़िल्टरिंग स्थितियों में बेहतर रूप से उपयोग नहीं किया जाता है। सॉर्टिंग और गणना सभी पंक्तियों के लिए होगी, इस तथ्य के बावजूद कि अंत में हमें उनमें से केवल एक छोटा सा हिस्सा चाहिए।

आइए ऐसे कार्य के उदाहरण द्वारा अधिक विस्तार से विचार करें:

प्रत्येक Group_id मान के लिए TestTable तालिका से पहले 2 पंक्तियों को प्रिंट करें, ऑर्डर_ड द्वारा सॉर्ट किया गया।

यहां बताया गया है कि विश्लेषणात्मक कार्यों के समर्थन के साथ DBMS में इस कार्य को कैसे हल किया जाएगा:

 SELECT group_id, order_id, value FROM( SELECT *, ROW_NUMBER()OVER(PARTITION BY group_id ORDER BY order_id) RowNum FROM TestTable )T WHERE RowNum <= 2;

हालाँकि, MySQL ऑप्टिमाइज़र उन नियमों के बारे में कुछ नहीं जानता जिनके द्वारा हम रोवनम क्षेत्र की गणना करते हैं। उसे सभी पंक्तियों को नंबर देना होगा, और उसके बाद ही आवश्यक का चयन करना होगा।

अब कल्पना करें कि हमारे पास 1 मिलियन रिकॉर्ड और 20 अद्वितीय group_id मान हैं। यानी 40 पंक्तियों का चयन करने के लिए, MySQL एक लाख पंक्तियों के लिए RowNum मान की गणना करेगा! MySQL में एकल क्वेरी के साथ इस समस्या का कोई सुंदर समाधान नहीं है। लेकिन आप पहले अद्वितीय group_id मानों की एक सूची प्राप्त कर सकते हैं, उदाहरण के लिए, इस तरह:

 SELECT DISTINCT group_id FROM TestTable;

फिर, किसी अन्य प्रोग्रामिंग भाषा का उपयोग करते हुए, फॉर्म की एक क्वेरी उत्पन्न करें:

 SELECT * FROM TestTable WHERE group_id=1 ORDER BY order_id LIMIT 2 UNION ALL SELECT * FROM TestTable WHERE group_id=2 ORDER BY order_id LIMIT 2 UNION ALL … SELECT * FROM TestTable WHERE group_id=20 ORDER BY order_id LIMIT 2;

एक लाख लाइनों के लिए रोवनम की गणना की तुलना में 20 आसान प्रश्न बहुत तेजी से काम करेंगे।

MySQL - एक क्वेरी में चर का उपयोग करना