🐤 💽 🛍️ शर्त के साथ समूह ☔️ 👨‍🔧 🍟

समय-समय पर, एक कार्य उत्पन्न होता है, जिसमें समूह में भाग नहीं लेने वाले गुणों के लिए डेटा सेट को समूहीकृत करने की आवश्यकता होती है, आपको एक फ़ील्ड में अधिकतम मूल्य के साथ एक टपल लेने की आवश्यकता होती है।

आइए एक साधारण उदाहरण देखें।
एक तालिका है:

CREATE TABLE IF NOT EXISTS shop ( id INT NOT NULL AUTO_INCREMENT, article INT(4) ZEROFILL NOT NULL, dealer VARCHAR(45) NOT NULL, price DECIMAL(8,2) NOT NULL, PRIMARY KEY (id)) ENGINE = InnoDB;

सभी लेख के लिए आवश्यक है कि अधिकतम मूल्य वाला डीलर ढूंढा जाए।

इस कार्य के लिए कई स्पष्ट और सरल उपाय हैं, लेकिन मैं उनमें से एक को जानता हूं, जो अन्य सभी से बेहतर है।
इस चुनौती का सामना किया? इसे हल करने के लिए एक नया तरीका देखना चाहते हैं? मैं बिल्ली माँगता हूँ।

यहां तक कि आधिकारिक mysql.com प्रलेखन ने इस कार्य को नहीं किया है, और 3 सुझाए गए समाधान हैं:
प्रत्येक अनुरोध से पहले मैं सूचकांक और इसके निष्पादन समय का संकेत दूंगा। तालिका 100,000 प्रविष्टियों के लिए भरी गई है

 DELIMITER $$ CREATE PROCEDURE InsertRand() BEGIN DECLARE i INT; SET i = 1; START TRANSACTION; WHILE i <= 100000 DO INSERT INTO shop (article, dealer, price) VALUES (CEIL(RAND() * 9999), CEIL(RAND() * 999), RAND() * 9999); SET i = i + 1; END WHILE; COMMIT; END$$ DELIMITER ;

पहला आईडीएक्स (लेख) 2,169 सी:

 SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article);

दूसरा आईडीएक्स (लेख, मूल्य) 0.203 सी

 SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article ) AS s2 ON s1.article = s2.article AND s1.price = s2.price;

तीसरा आईडीएक्स (लेख, मूल्य) 0.593 सी

 SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL;

खैर, अब मेरा समाधान:

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

यह समाधान इस तथ्य पर आधारित है कि उन विशेषताओं के लिए समूह जो निर्दिष्ट समूह संचालन नहीं हैं और जो समूह में भाग नहीं लेते हैं, पहले सामना किए गए मूल्य को लेता है। इस प्रकार, यदि डेटा सेट पूर्व-सॉर्ट किया गया है, तो हम वांछित अधिकतम मूल्य के साथ ट्यूपल्स प्राप्त करते हैं।

4. आईडीएक्स (कीमत) 0.328 सी

 SELECT article, dealer, price FROM ( SELECT article, dealer, price FROM shop ORDER BY price desc) as t GROUP BY article ORDER BY NULL;

क्योंकि चूंकि पिछले उदाहरण बिना किसी छंटनी के थे, और समूह इसे स्वचालित रूप से जोड़ता है, इसलिए आपको ORDER BY NULL को निर्दिष्ट करना होगा ताकि डेटा को आगे छांटा न जाए, अन्यथा परिणाम तुलनीय नहीं होंगे।
लेकिन हमें एक मध्यवर्ती तालिका बनाने की आवश्यकता क्यों है, क्योंकि हम सूचकांक का उपयोग करके सॉर्ट किए गए डेटा प्राप्त कर सकते हैं:

5. आईडीएक्स (लेख, मूल्य) 0.110 सी

 SELECT article, dealer, price FROM shop use index (idx) GROUP BY article DESC ORDER BY NULL;

बोनस समाधान:

इसका समाधान मिच डिकिंसन ब्लॉग पर पाया गया। यह सबसे तेज़, लेकिन बहुत मूल होने का दावा नहीं करता है।

6. आईडीएक्स (लेख) 0.202 एस

 SELECT article, SUBSTRING_INDEX(GROUP_CONCAT(dealer ORDER BY price DESC),',',1) AS dealer, MAX(price) AS price FROM shop GROUP BY article;

टिप्पणियों में, dm9 ने एक और 1 समाधान प्रदान किया, जिसे पहले संस्करणों के लिए प्रलेखन में वर्णित किया गया था:

 SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT(MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop GROUP BY article;

सामान्य तौर पर, लेख एक विशिष्ट समस्या को हल करने के लिए इतना समर्पित नहीं है कि आप इस उदाहरण पर विचार करें कि आप आम तौर पर स्वीकृत समाधानों के ढांचे से परे कैसे जा सकते हैं और वैकल्पिक तरीके खोजने की कोशिश कर सकते हैं। मेरे लिए, डेटाबेस हमेशा दिलचस्प रहे हैं क्योंकि दिलचस्प अनुकूलन विधियों को खोजने का अवसर है जो समय और प्रयास को महत्वपूर्ण रूप से बचा सकते हैं।

पुनश्च: एक चौकस पाठक ने शायद देखा कि प्रत्येक लेख के लिए विधियाँ 4-6 पहले मूल्य के साथ केवल 1 आपूर्तिकर्ता को देती हैं, पहले तरीकों के विपरीत जिसमें सभी आपूर्तिकर्ता वापस लौट आते हैं। लेकिन इस समस्या को हल करने में, मुझे किसी भी आपूर्तिकर्ता में दिलचस्पी थी, इसलिए यह समस्या नगण्य थी।

PPS: एक वैकल्पिक विधि, जो इस लेख में प्रस्तावित है, मध्यम आकार की तालिकाओं के साथ अच्छा प्रदर्शन करती है। एक लाख से अधिक रिकॉर्ड की संख्या के साथ, विधि 2 सबसे इष्टतम होगा। इसके अलावा, यदि रिकॉर्ड की संख्या पहले से ही इतनी बड़ी है, तो मैं अत्यधिक अनुशंसा करता हूं कि यह जानकारी अलग-अलग तालिकाओं में पहले से निर्धारित हो।

शर्त के साथ समूह