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

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

प्रदर्शन के लिए विभिन्न समाधानमैं खाते की शेष राशि का उपयोग करूंगा। यहां वह कोड है जो परीक्षण डेटा की एक छोटी राशि के साथ लेनदेन तालिका बनाता है और पॉप्युलेट करता है:

खाता चालू करें; टीएसक्यूएल2012 का उपयोग करें; यदि OBJECT_ID ("dbo.Transactions", "U") NULL DROP TABLE dbo.Transactions नहीं है; क्रिएट टेबल dbo.Transactions (actid INT NOT NULL, - tranid विभाजन कॉलम INT NOT NULL, - ऑर्डरिंग कॉलम वैल मनी नॉट न्यूल, - मापें CONSTRAINT PK_Transactions PRIMARY KEY (actid, tranid)); GO - छोटे परीक्षण डेटा सेट INSERT INTO dbo.Transactions (actid, tranid, val) VALUES (1, 1, 4.00), (1, 2, -2.00), (1, 3, 5.00), (1, 4, 2.00) ), (1, 5, 1.00), (1, 6, 3.00), (1, 7, -4.00), (1, 8, -1.00), (1, 9, -2.00), (1, 10 , -3.00), (2, 1, 2.00), (2, 2, 1.00), (2, 3, 5.00), (2, 4, 1.00), (2, 5, -5.00), (2, 6 , 4.00), (2, 7, 2.00), (2, 8, -4.00), (2, 9, -5.00), (2, 10, 4.00), (3, 1, -3.00), (3, 2 , 3.00), (3, 3, -2.00), (3, 4, 1.00), (3, 5, 4.00), (3, 6, -1.00), (3, 7, 5.00), (3, 8) , 3.00), (3, 9, 5.00), (3, 10, -3.00);

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

दोनों समाधानों का परीक्षण करने के लिए अधिक डेटा की आवश्यकता है। यह इस तरह की एक क्वेरी के साथ किया जा सकता है:

DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transactions; INSERT INTO. (TABLOCK) के साथ लेनदेन (actid, tranid, val) NP.n, RPP.n चुनें, (ABS (CHECKSUM (NEWID ())% 2) * 2-1) * (1 + ABS (CHECKSUM (चेकसम) NEWID ())% 5)) से dbo.GetNums (1, @num_partitions) एनपी क्रॉस के रूप में शामिल हों dbo.GetNums (1, @rows_per_partition) AS RPP;

आप किसी अनुभाग में अनुभागों (खातों) और पंक्तियों (लेन-देन) की संख्या को बदलने के लिए अपना इनपुट डेटा निर्दिष्ट कर सकते हैं।

विंडोिंग फ़ंक्शंस का उपयोग करके एक सेट-आधारित समाधान

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

dbo.Transactions से शेष राशि के रूप में चयन करें actid, tranid, val, SUM (val) OVER (अनबाउंडेड प्रीसीडिंग और करंट रो के बीच ट्रांजिड पंक्तियों द्वारा एक्टिड ऑर्डर द्वारा विभाजन);

यह कोड न केवल सरल और सीधा है - यह जल्दी से निष्पादित भी होता है। इस क्वेरी की योजना चित्र में दिखाई गई है:

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

SQL सर्वर 2012 से पहले, नेस्टेड क्वेरी या जॉइन का उपयोग किया जाता था। सबक्वेरी का उपयोग करते समय, रनिंग टोटल की गणना सभी पंक्तियों को बाहरी पंक्ति के समान एक्टिड मान और बाहरी पंक्ति मान से कम या उसके बराबर एक ट्रैनिड मान के साथ फ़िल्टर करके की जाती है। फिर एकत्रीकरण को फ़िल्टर की गई पंक्तियों पर लागू किया जाता है। यहाँ प्रासंगिक क्वेरी है:

जॉइन का उपयोग करके एक समान दृष्टिकोण लागू किया जा सकता है। जॉइन के ON क्लॉज में सबक्वेरी के WHERE क्लॉज के समान ही विधेय का उपयोग किया जाता है। इस मामले में, T1 के रूप में निर्दिष्ट उदाहरण में एक ही खाते A के Nवें लेनदेन के लिए, आप उदाहरण T2 में N मिलान पाएंगे, जबकि लेन-देन संख्या 1 से N तक चलती है। तुलना के परिणामस्वरूप, पंक्तियों में T1 को दोहराया जाता है, इसलिए आपको वर्तमान लेन-देन के बारे में जानकारी प्राप्त करने के लिए T1 के साथ सभी तत्वों में समूह पंक्तियों की आवश्यकता होती है और चल रहे कुल की गणना करने के लिए T2 से वैल एट्रिब्यूट को एकत्रित करना होता है। समाप्त अनुरोध कुछ इस तरह दिखता है:

T1.actid, T1.tranid, T1.val, SUM (T2.val) को dbo से शेष राशि के रूप में चुनें। T1 के रूप में लेनदेन dbo में शामिल हों। T2.actid पर T2 के रूप में लेनदेन = T1.actid और T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

नीचे दिया गया आंकड़ा दोनों समाधानों की योजनाओं को दर्शाता है:

ध्यान दें कि दोनों ही मामलों में, उदाहरण के लिए T1 पर एक पूर्ण क्लस्टर इंडेक्स स्कैन किया जाता है। फिर, योजना में प्रत्येक पंक्ति के लिए, सूचकांक के अंतिम पृष्ठ पर चालू खाता अनुभाग की शुरुआत के सूचकांक में एक खोज अभियान होता है, और सभी लेनदेन जिसमें T2.tranid T1.tranid से कम या उसके बराबर होता है। पढ़े जाते हैं। जिस बिंदु पर पंक्ति एकत्रीकरण होता है वह योजनाओं में थोड़ा भिन्न होता है, लेकिन पढ़ी गई पंक्तियों की संख्या समान होती है।

यह समझने के लिए कि कितनी पंक्तियों को स्कैन किया जा रहा है, आपको डेटा आइटम की संख्या पर विचार करना होगा। मान लीजिए p अनुभागों (खातों) की संख्या है और r अनुभाग (लेन-देन) में पंक्तियों की संख्या है। तब तालिका में पंक्तियों की संख्या लगभग p * r के बराबर होती है, यदि हम मानते हैं कि लेन-देन खातों में समान रूप से वितरित किए जाते हैं। इस प्रकार, शीर्ष पर दृश्य p * r रेखाएँ फैलाता है। लेकिन जिस चीज में हमें सबसे ज्यादा दिलचस्पी है, वह है नेस्टेड लूप्स इटरेटर में क्या होता है।

प्रत्येक खंड में, योजना 1 + 2 + ... + r पंक्तियों को पढ़ने का प्रावधान करती है, जो कुल मिलाकर (r + r * 2) / 2 है। योजनाओं में संसाधित लाइनों की कुल संख्या p * r + p * है। (r + r2) / 2. इसका मतलब है कि योजना में संचालन की संख्या अनुभाग के आकार में वृद्धि के साथ बढ़ती है, अर्थात, यदि अनुभाग का आकार f गुना बढ़ा दिया जाता है, तो कार्य की मात्रा बढ़ जाएगी लगभग 2 गुना वृद्धि करें। ये गलत है। उदाहरण के लिए, 100 लाइनें 10 हजार लाइनों से मेल खाती हैं, और एक हजार लाइनें एक मिलियन से मेल खाती हैं, आदि। सीधे शब्दों में कहें, यह एक बड़े खंड आकार के साथ क्वेरी निष्पादन में एक मजबूत मंदी की ओर जाता है, क्योंकि द्विघात फ़ंक्शन बहुत तेज़ी से बढ़ता है। ऐसे समाधान प्रति अनुभाग कई दसियों पंक्तियों के साथ संतोषजनक ढंग से काम करते हैं, लेकिन अब और नहीं।

कर्सर समाधान

कर्सर-आधारित समाधान आमने-सामने लागू किए जाते हैं। एक्टिड और ट्रैनिड द्वारा डेटा ऑर्डर करने वाले क्वेरी के आधार पर एक कर्सर घोषित किया जाता है। उसके बाद, कर्सर रिकॉर्ड का एक पुनरावृत्त ट्रैवर्सल किया जाता है। जब कोई नया खाता मिलता है, तो कुल वाले वेरिएबल को रीसेट कर दिया जाता है। प्रत्येक पुनरावृत्ति में, नए लेन-देन की मात्रा को चर में जोड़ा जाता है, जिसके बाद पंक्ति को तालिका चर में संग्रहीत किया जाता है जिसमें वर्तमान लेनदेन और संचयी कुल के वर्तमान मूल्य के बारे में जानकारी होती है। एक पुनरावृत्त पास के बाद, परिणाम तालिका चर से वापस कर दिया जाता है। यहाँ पूर्ण समाधान के लिए कोड है:

DECLARE @Result as TABLE (actid INT, tranid INT, val MONEY, balance MONEY); DECLARE @actid AS INT, @prvactid AS INT, @tranid AS INT, @val AS MONEY, @balance as MONE; DECLARE C CURSOR FAST_FORWARD सेलेक्ट एक्टिड, ट्रैनिड, वैल फ्रॉम डीबीओ के लिए। एक्टिड द्वारा ट्रांजेक्शन ऑर्डर, ट्रैनिड; ओपन सी फ़ेच सी से @actid, @tranid, @val; चुनें @prvactid = @actid, @balance = 0; जबकि @@ fetch_status = 0 यदि @actid . पर शुरू होता है<>@prvactid चुनें @prvactid = @actid, @balance = 0; सेट @ बैलेंस = @ बैलेंस + @ वैल; INSERT INTO @Result VALUES (@actid, @tranid, @val, @balance); सी से @actid, @tranid, @val; अंत बंद सी; डीअलोकेट सी; चुनें * @Result से;

कर्सर का उपयोग कर क्वेरी योजना चित्र में दिखाई गई है:

यह योजना रैखिक रूप से मापी जाती है क्योंकि सूचकांक से डेटा एक विशिष्ट क्रम में केवल एक बार स्कैन किया जाता है। साथ ही, कर्सर से एक पंक्ति प्राप्त करने के प्रत्येक ऑपरेशन की लागत प्रति पंक्ति लगभग समान होती है। यदि हम कर्सर की एक पंक्ति को g के बराबर संसाधित करके उत्पन्न भार लेते हैं, तो इस समाधान की लागत का अनुमान p * r + p * r * g के रूप में लगाया जा सकता है (जैसा कि आपको याद है, p अनुभागों की संख्या है, और r है अनुभाग में पंक्तियों की संख्या)। इसलिए, यदि हम प्रति खंड पंक्तियों की संख्या को f गुना बढ़ाते हैं, तो सिस्टम पर भार p * r * f + p * r * f * g होगा, अर्थात यह रैखिक रूप से बढ़ेगा। प्रति पंक्ति प्रसंस्करण लागत अधिक है, लेकिन स्केलिंग की रैखिक प्रकृति के कारण, एक निश्चित विभाजन आकार से यह समाधान इन समाधानों के द्विघात स्केलिंग के कारण नेस्टेड और जॉइन समाधानों की तुलना में बेहतर मापनीयता दिखाएगा। एक प्रदर्शन माप मैंने दिखाया कि जब कर्सर समाधान तेज होता है तो संख्या प्रति खंड कई सौ लाइनें होती है।

कर्सर-आधारित समाधानों द्वारा प्रदान किए गए प्रदर्शन लाभ के बावजूद, सामान्य तौर पर उन्हें टाला जाना चाहिए क्योंकि वे संबंधपरक नहीं हैं।

सीएलआर आधारित समाधान

पर आधारित एक संभावित समाधान सीएलआर (सामान्य भाषा रनटाइम)अनिवार्य रूप से कर्सर-आधारित समाधान का एक रूप है। अंतर यह है कि एक टी-एसक्यूएल कर्सर का उपयोग करने के बजाय, जो अगली पंक्ति लाने और पुनरावृति के लिए बहुत सारे संसाधनों को बर्बाद करता है, .NET SQLDataReader और .NET पुनरावृत्तियों का उपयोग करें, जो बहुत तेज़ हैं। सीएलआर की एक विशेषता जो इस विकल्प को तेज बनाती है, वह यह है कि अस्थायी तालिका में परिणामी पंक्ति की आवश्यकता नहीं है - परिणाम सीधे कॉलिंग प्रक्रिया में भेजे जाते हैं। सीएलआर-आधारित निर्णय तर्क कर्सर-आधारित टी-एसक्यूएल निर्णय तर्क के समान है। यहाँ C # कोड है जो समाधान संग्रहीत कार्यविधि को परिभाषित करता है:

सिस्टम का उपयोग करना; System.Data का उपयोग करना; System.Data.SqlClient का उपयोग करना; System.Data.SqlTypes का उपयोग करना; Microsoft.SqlServer.Server का उपयोग करना; सार्वजनिक आंशिक वर्ग StoredProcedures (सार्वजनिक स्थैतिक शून्य AccountBalances () ((SqlConnection conn = new SqlConnection ("संदर्भ कनेक्शन = सत्य;") का उपयोग करके) (SqlCommand कॉम = नया SqlCommand (); Comm.Connection = conn; Comm.CommandText = @ " "+" सेलेक्ट एक्टिड, ट्रैनिड, वैल "+" डीबीओ से। ट्रांजेक्शन्स "+" ऑर्डर बाय एक्टिड, ट्रैनिड; "; SqlMetaData कॉलम = नया SqlMetaData; कॉलम = नया SqlMetaData ("actid", SqlDbType.Int); कॉलम = नया SqlMetaData ("tranid", SqlDbType.Int); कॉलम = नया SqlMetaData ("वैल", SqlDbType.Money); कॉलम = नया SqlMetaData ("बैलेंस", SqlDbType.Money); SqlDataqlord रिकॉर्ड = नए कॉलम पाइप।SendResultsStart (रिकॉर्ड) ; conn.Open (); SqlDataReader रीडर = Comm.ExecuteReader (); SqlInt32 prvactid = 0; SqlMoney बैलेंस = 0; जबकि (reader.Read ()) (SqlInt32 actid = Reader.GetSqlInt32 (0)); SqlMoney वैल = रीडर। GetSqlMoney (2); अगर (actid == prvactid) (बैलेंस + = वैल;) और (बैलेंस = वैल;) prvactid = actid; rec ord.SetSqlInt32 (0, रीडर। GetSqlInt32 (0)); Record.SetSqlInt32 (1, Reader.GetSqlInt32 (1)); रिकॉर्ड.सेटएसक्लमनी (2, वैल); रिकॉर्ड.सेटएसक्लमनी (3, बैलेंस); SqlContext.Pipe.SendResultsRow (रिकॉर्ड); ) SqlContext.Pipe.SendResultsEnd (); )))

SQL सर्वर में इस संग्रहीत कार्यविधि को निष्पादित करने में सक्षम होने के लिए, आपको सबसे पहले इस कोड से AccountBalances नामक एक असेंबली बनाने और इसे TSQL2012 डेटाबेस में तैनात करने की आवश्यकता है। यदि आप SQL सर्वर में असेंबलियों को परिनियोजित करने के लिए नए हैं, तो आप संग्रहीत कार्यविधियाँ और संग्रहीत कार्यविधियाँ आलेख का CLR अनुभाग पढ़ सकते हैं।

यदि आपने असेंबली AccountBalances नाम दिया है, और असेंबली फ़ाइल का पथ "C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll" है, तो आप असेंबली को डेटाबेस में लोड कर सकते हैं और निम्न कोड के साथ संग्रहीत कार्यविधि को पंजीकृत कर सकते हैं:

"C: \ Projects \ AccountBalances \ bin \ Debug \ AccountBalances.dll" से असेंबली अकाउंट बैलेंस बनाएं; जाओ प्रक्रिया बनाएँ dbo.खाता शेष बाहरी नाम खाते के रूप में शेष राशि। संग्रहीत प्रक्रियाएं। खाता शेष;

असेंबली को तैनात करने और प्रक्रिया को पंजीकृत करने के बाद, आप इसे निम्नलिखित कोड के साथ निष्पादित कर सकते हैं:

EXEC dbo.खाता शेष;

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

समाप्त होने पर, निम्न क्लीनअप कोड चलाएँ:

ड्रॉप प्रक्रिया डीबीओ.खाता शेष; ड्रॉप असेंबली अकाउंट बैलेंस;

नेस्टेड पुनरावृत्तियों

इस बिंदु तक, मैंने पुनरावृत्त और सेट-आधारित समाधान दिखाए हैं। निम्नलिखित समाधान नेस्टेड पुनरावृत्तियों पर आधारित है, जो पुनरावृत्त और सेट-आधारित दृष्टिकोणों का एक संकर है। विचार स्रोत तालिका से पंक्तियों को पूर्व-कॉपी करना है (हमारे मामले में, ये बैंक खाते हैं) एक अस्थायी तालिका में राउनम नामक एक नई विशेषता के साथ, जिसकी गणना ROW_NUMBER फ़ंक्शन का उपयोग करके की जाती है। पंक्ति संख्याओं को एक्टिड द्वारा विभाजित किया जाता है और ट्रांड द्वारा आदेशित किया जाता है, इसलिए प्रत्येक बैंक खाते में पहला लेन-देन नंबर 1 असाइन किया जाता है, दूसरा लेनदेन नंबर 2 असाइन किया जाता है, और इसी तरह। फिर अस्थायी तालिका पर चाबियों (राउनम, एक्टिड) की सूची के साथ एक संकुल सूचकांक बनाया जाता है। फिर एक पुनरावर्ती CTE अभिव्यक्ति या विशेष रूप से डिज़ाइन किए गए लूप का उपयोग सभी चालानों में एक पंक्ति प्रति पुनरावृत्ति को संसाधित करने के लिए किया जाता है। फिर चल रहे कुल की गणना पिछली पंक्ति से संबद्ध मान के साथ वर्तमान पंक्ति के संगत मान को जोड़कर की जाती है। यहाँ एक पुनरावर्ती CTE का उपयोग करके इस तर्क का कार्यान्वयन है:

एक्टिड, ट्रैनिड, वैल, ROW_NUMBER () ओवर (एक्टिड ऑर्डर द्वारा ट्रांजिड द्वारा पार्टिशन) को डीबीओ से #लेन-देन में #लेन-देन के रूप में चुनें। #लेन-देन (rownum, actid) पर UNIQUE CLUSTERED INDEX idx_rownum_actid बनाएं; सी एएस के साथ (चयन १ एएस राउनम, एक्टिड, ट्रैनिड, वैल, वैल एएस योग #लेन-देन से जहां राउनम = १ यूनियन ऑल सेलेक्ट पीआरवी.रोउनम + १, पीआरवी.एक्टिड, सीयूआर.ट्रानिड, सीयूआर.वैल, पीआरवी.समकटी + CUR.val C से PRV जॉइन के रूप में #लेन-देन CUR पर CUR.rownum = PRV.rownum + 1 और CUR.actid = PRV.actid) C विकल्प से चयन करें actid, tranid, val, sumqty from C OPTION (MAXRECURSION 0); ड्रॉप टेबल #लेनदेन;

और यह एक स्पष्ट लूप का उपयोग करके एक कार्यान्वयन है:

ROW_NUMBER () ओवर (एक्टिड ऑर्डर द्वारा ट्रांज़िड द्वारा पार्टिशन) का चयन करें जैसे कि राउनम, एक्टिड, ट्रैनिड, वैल, कास्ट (वैल एएस बिगिनट) योग के रूप में # डीबीओ से लेन-देन। लेनदेन; #लेन-देन पर अद्वितीय क्लस्टर इंडेक्स idx_rownum_actid बनाएं (rownum, actid); DECLARE @rownum AS INT; सेट @rownum = 1; जबकि 1 = 1 BEGIN SET @rownum = @rownum + 1; अद्यतन CUR SET sumqty = PRV.sumqty + CUR.val #लेन-देन से CUR जॉइन के रूप में #लेन-देन CUR.rownum पर PRV के रूप में = @rownum और PRV.rownum = @rownum - 1 और CUR.actid = PRV.actid; अगर @@ पंक्ति गणना = 0 BREAK; #लेन-देन से END SELECT actid, tranid, val, sumqty FROM; ड्रॉप टेबल #लेनदेन;

यह समाधान अच्छा प्रदर्शन प्रदान करता है जब प्रति अनुभाग कुछ पंक्तियों के साथ बड़ी संख्या में अनुभाग होते हैं। फिर पुनरावृत्तियों की संख्या छोटी होती है, और अधिकांश कार्य समाधान के सेट-आधारित भाग द्वारा किया जाता है, जो समान पंक्ति संख्या से संबद्ध रेखाओं को पिछली पंक्ति संख्या से संबद्ध रेखाओं से जोड़ता है।

चर के साथ बहु-पंक्ति अद्यतन

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

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

चर @prevaccount और @prevbalance का उपयोग किया जाता है, और शेष कॉलम में मान की गणना निम्न अभिव्यक्ति का उपयोग करके की जाती है:

सेट @prevbalance = शेष राशि = मामला जब सक्रिय = @prevaccount तब @prevbalance + वैल ELSE वैल END

CASE एक्सप्रेशन जाँचता है कि क्या वर्तमान और पिछले खातों के पहचानकर्ता समान हैं और यदि वे समान हैं, तो बैलेंस कॉलम में पिछले और वर्तमान मानों का योग लौटाता है। यदि खाता आईडी भिन्न हैं, तो वर्तमान लेनदेन राशि वापस कर दी जाती है। CASE एक्सप्रेशन का परिणाम तब बैलेंस कॉलम में डाला जाता है और @prevbalance वैरिएबल को असाइन किया जाता है। एक अलग अभिव्यक्ति में, चर © prevaccount को चालू खाते का पहचानकर्ता सौंपा गया है।

अद्यतन विवरण के बाद, समाधान अस्थायी तालिका से पंक्तियों को प्रस्तुत करता है और अंतिम को हटा देता है। यहाँ पूर्ण समाधान के लिए कोड है:

तालिका बनाएं # लेन-देन (एक्टिड INT, tranid INT, वैल मनी, बैलेंस मनी); #लेनदेन (actid, tranid) पर CLUSTERED INDEX idx_actid_tranid बनाएं; INSERT INTO (TABLOCK) के साथ लेन-देन (actid, tranid, val, balance) सेलेक्ट actid, tranid, val, 0.00 from dbo.Transaction ORDER by actid, tranid; DECLARE @prevaccount as INT, @prevbalance as MONEY; अद्यतन # लेन-देन सेट @prevbalance = शेष = मामला जब सक्रिय = @prevaccount तब @prevbalance + val ELSE वैल END, @prevaccount = actid FROM #Transactions with (INDEX (1), TABLOCKX) विकल्प (MAXDOP 1); चुनें * #लेनदेन से; ड्रॉप टेबल #लेनदेन;

इस समाधान की योजना निम्न आकृति में दिखाई गई है। पहला भाग एक INSERT कथन है, दूसरा एक अद्यतन है, और तीसरा एक चयन है:

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

सौभाग्य से, SQL Server 2012 में, यह विकल्प लगभग अनावश्यक हो जाता है। एक अत्यंत कुशल विंडोड एग्रीगेशन समाधान के साथ, आपको अन्य समाधानों के बारे में सोचने की आवश्यकता नहीं है।

परफॉरमेंस नापना

मैंने विभिन्न तकनीकों के प्रदर्शन को मापा और तुलना की है। परिणाम नीचे दिए गए आंकड़ों में दिखाए गए हैं:

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

मैं किसी विशेष विक्रेता द्वारा उत्पादित पीसी मॉडल की संख्या का पता कैसे लगा सकता हूं? समान विनिर्देशों वाले कंप्यूटरों के लिए औसत मूल्य कैसे निर्धारित करें? कुछ सांख्यिकीय जानकारी से संबंधित इन और कई अन्य प्रश्नों का उत्तर इसका उपयोग करके दिया जा सकता है सारांश (कुल) कार्य... मानक निम्नलिखित समग्र कार्यों के लिए प्रदान करता है:

ये सभी कार्य एक ही मान लौटाते हैं। इस मामले में, कार्य काउंट, मिनतथा मैक्सकिसी भी डेटा प्रकार पर लागू होते हैं, जबकि योगतथा औसतकेवल संख्यात्मक क्षेत्रों के लिए उपयोग किया जाता है। फ़ंक्शन के बीच अंतर COUNT (*)तथा COUNT (<имя поля>) यह है कि दूसरा गणना करते समय NULL मानों को ध्यान में नहीं रखता है।

उदाहरण। पर्सनल कंप्यूटर के लिए न्यूनतम और अधिकतम मूल्य ज्ञात करें:

उदाहरण। निर्माता A से उपलब्ध कंप्यूटरों की संख्या ज्ञात कीजिए:

उदाहरण। यदि हम निर्माता ए द्वारा उत्पादित विभिन्न मॉडलों की संख्या में रुचि रखते हैं, तो क्वेरी निम्नानुसार तैयार की जा सकती है (इस तथ्य का उपयोग करके कि उत्पाद तालिका में प्रत्येक मॉडल एक बार दर्ज किया गया है):

उदाहरण। निर्माता ए से उपलब्ध विभिन्न मॉडलों की संख्या पाएं। क्वेरी पिछले एक के समान है, जिसमें निर्माता ए द्वारा उत्पादित मॉडलों की कुल संख्या निर्धारित करना आवश्यक था। यहां आपको विभिन्न मॉडलों की संख्या भी ढूंढनी होगी पीसी टेबल (यानी बिक्री के लिए उपलब्ध)।

यह सुनिश्चित करने के लिए कि सांख्यिकीय संकेतक प्राप्त करते समय केवल अद्वितीय मानों का उपयोग किया जाता है, जब कुल कार्यों का तर्कइस्तेमाल किया जा सकता है DISTINCT पैरामीटर... एक और पैरामीटर सभीडिफ़ॉल्ट है और मानता है कि कॉलम में सभी रिटर्न मान गिने जाते हैं। ऑपरेटर,

अगर हमें उत्पादित पीसी मॉडल की संख्या प्राप्त करने की आवश्यकता है प्रत्येकनिर्माता, आपको उपयोग करने की आवश्यकता होगी ग्रुप बाय क्लॉजवाक्य रचनात्मक रूप से निम्नलिखित जहां खंड.

ग्रुप बाय क्लॉज

यदि SELECT में दिनांक के साथ एक कॉलम होता है, तो प्रत्येक विशिष्ट तिथि के लिए इन संकेतकों की गणना करना संभव होगा। ऐसा करने के लिए, आपको एक समूहीकरण कॉलम के रूप में एक तिथि जोड़ने की आवश्यकता है, और फिर मूल्यों के प्रत्येक संयोजन (मॉडल-तिथि) के लिए कुल कार्यों की गणना की जाएगी।

कई विशिष्ट हैं समग्र कार्य करने के नियम:

• यदि क्वेरी के परिणामस्वरूप कोई पंक्तियाँ प्राप्त नहीं हुई(या इस समूह के लिए एक से अधिक पंक्तियाँ), तो किसी भी समग्र कार्य की गणना के लिए प्रारंभिक डेटा अनुपस्थित हैं। इस स्थिति में, COUNT फ़ंक्शन निष्पादित करने का परिणाम शून्य होगा, और अन्य सभी फ़ंक्शन का परिणाम NULL होगा।
• तर्ककुल कार्य स्वयं में समग्र कार्य नहीं हो सकते हैं(फ़ंक्शन से फ़ंक्शन)। वे। एक प्रश्न में, आप कह सकते हैं, अधिकतम औसत मान प्राप्त नहीं कर सकते।
• COUNT फ़ंक्शन निष्पादित करने का परिणाम है पूर्णांक(पूर्णांक)। अन्य समग्र कार्य संसाधित किए जा रहे मानों के डेटा प्रकारों को इनहेरिट करते हैं।
• यदि, SUM फ़ंक्शन को निष्पादित करते समय, एक परिणाम प्राप्त होता है जो उपयोग किए गए डेटा प्रकार के अधिकतम मान से अधिक होता है, a त्रुटि.

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

खंड होना

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

यह ट्यूटोरियल आपको दिखाएगा कि कैसे उपयोग करें योग समारोहसिंटेक्स और उदाहरणों के साथ SQL सर्वर (Transact-SQL) में।

विवरण

SQL सर्वर (ट्रांजैक्ट-एसक्यूएल) योग समारोहअभिव्यक्ति का कुल मूल्य देता है।

वाक्य - विन्यास

SQL सर्वर (Transact-SQL) में SUM फ़ंक्शन के लिए सिंटैक्स है:

या SUM फ़ंक्शन के लिए सिंटैक्स जब परिणामों को एक या अधिक स्तंभों द्वारा समूहीकृत किया जाता है:

पैरामीटर या तर्क

एक्सप्रेशन1, एक्सप्रेशन2,… एक्सप्रेशन_एन ऐसे एक्सप्रेशन हैं जो SUM फ़ंक्शन में शामिल नहीं हैं और SQL स्टेटमेंट के अंत में ग्रुप बाय क्लॉज में शामिल होने चाहिए।
एग्रीगेट_एक्सप्रेशन वह कॉलम या एक्सप्रेशन है जिसे संक्षेप में प्रस्तुत किया जाना है।
टेबल - वे टेबल जिनसे आप रिकॉर्ड प्राप्त करना चाहते हैं। FROM खंड में कम से कम एक तालिका सूचीबद्ध होनी चाहिए।
जहां शर्तें वैकल्पिक हैं। ये वे शर्तें हैं जिन्हें चयनित रिकॉर्ड के लिए पूरा किया जाना चाहिए।

आवेदन

SUM फ़ंक्शन का उपयोग SQL सर्वर (Transact-SQL) के निम्नलिखित संस्करणों में किया जा सकता है:
एसक्यूएल सर्वर वीनेक्स्ट, एसक्यूएल सर्वर 2016, एसक्यूएल सर्वर 2015, एसक्यूएल सर्वर 2014, एसक्यूएल सर्वर 2012, एसक्यूएल सर्वर 2008 आर2, एसक्यूएल सर्वर 2008, एसक्यूएल सर्वर 2005

एकल फ़ील्ड उदाहरण

आइए SQL सर्वर (Transact-SQL) में SUM फ़ंक्शन का उपयोग करने के तरीके को समझने के लिए कुछ SQL सर्वर SUM फ़ंक्शन उदाहरण देखें।

उदाहरण के लिए, आप यह पता लगा सकते हैं कि सभी उत्पादों की कुल संख्या, जिनकी संख्या 10 से अधिक है।

SUM फ़ंक्शन के इस उदाहरण में, हम SUM (मात्रा) व्यंजक को "कुल मात्रा" पर सेट करते हैं। परिणाम सेट लौटाते समय - "कुल मात्रा" फ़ील्ड नाम के रूप में प्रदर्शित होगी।

DISTINCT . का उपयोग करने का एक उदाहरण

आप SUM फ़ंक्शन में DISTINCT ऑपरेटर का उपयोग कर सकते हैं। उदाहरण के लिए, निम्न SQL कथन अद्वितीय वेतन मानों के साथ कुल वेतन देता है जहां वेतन $ 29,000 प्रति वर्ष से कम है।

यदि दो वेतन $ 24,000 प्रति वर्ष थे, तो इनमें से केवल एक मान का उपयोग SUM फ़ंक्शन में किया जाएगा।

सूत्र का उपयोग करने का उदाहरण

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

कारोबार-एसक्यूएल

आदेशों से "कुल कमीशन" के रूप में SUM (बिक्री * 0.03) चुनें;

ग्रुप बाय का उपयोग करने का उदाहरण

कुछ मामलों में, आपको SUM फ़ंक्शन के साथ ग्रुप बाय क्लॉज का उपयोग करना होगा।

SQL पाठ 11. सारांश कार्य, परिकलित कॉलम और दृश्य

• AVG () फ़ंक्शन किसी कॉलम का औसत मान देता है।


• COUNT () फ़ंक्शन एक कॉलम में पंक्तियों की संख्या देता है।


• MAX () फ़ंक्शन कॉलम में सबसे बड़ा मान देता है।


• MIN () फ़ंक्शन कॉलम में सबसे छोटा मान देता है।


• SUM () फ़ंक्शन कॉलम मानों का योग देता है।

कीमतों से MIN (कीमत), MAX (कीमत), AVG (कीमत) चुनें;

अब, हम जानना चाहते हैं कि आपूर्तिकर्ता "प्रिंटिंग हाउस" हमारे लिए कितना माल लाया (आईडी = 2)। ऐसा अनुरोध लिखना इतना आसान नहीं है। आइए विचार करें कि इसे कैसे बनाया जाए:

1. सबसे पहले, डिलीवरी (इनकमिंग) टेबल से, उन डिलीवरी के पहचानकर्ता (id_incoming) का चयन करें जो "प्रिंटिंग हाउस" सप्लायर (आईडी = 2) द्वारा किए गए थे:

2. अब तालिका से डिलीवरी की पत्रिका (पत्रिका_इनकमिंग) से माल (id_product) और उनकी मात्रा (मात्रा) का चयन करना आवश्यक है, जो पैराग्राफ 1 में मिली डिलीवरी में किए गए थे। अर्थात्, अनुच्छेद 1 से अनुरोध नेस्टेड हो जाता है:

3. अब हमें परिणामी तालिका में उन उत्पादों की कीमतों को जोड़ने की जरूरत है, जो मूल्य तालिका में संग्रहीत हैं। यही है, हमें id_product कॉलम द्वारा मैगज़ीन_इनकमिंग और कीमतों की तालिका को संयोजित करने की आवश्यकता है:

4. परिणामी तालिका में स्पष्ट रूप से योग कॉलम का अभाव है, अर्थात परिकलित कॉलम... ऐसे कॉलम बनाने की क्षमता MySQL में प्रदान की गई है। ऐसा करने के लिए, आपको केवल क्वेरी में परिकलित कॉलम का नाम निर्दिष्ट करना होगा और इसकी गणना क्या करनी चाहिए। हमारे उदाहरण में, ऐसे कॉलम को सुम्मा कहा जाएगा, और यह मात्रा और मूल्य कॉलम के उत्पाद की गणना करेगा। नया कॉलम नाम AS शब्द से अलग किया गया है:

मैगज़ीन_इनकमिंग.आईडी_प्रोडक्ट, मैगज़ीन_इनकमिंग.क्वांटिटी, प्राइस.प्राइस, मैगज़ीन_इनकमिंग.क्वांटिटी का चयन करें। मैगज़ीन_इनकमिंग से कीमतों के रूप में मूल्य, कीमतें जहां मैगज़ीन_इनकमिंग.आईडी_प्रोडक्ट = कीमतें.आईडी_प्रोडक्ट और आईडी_इनकमिंग = (आने वाले से आईडी_इनकमिंग चुनें जहां 2 आईडी_वेंडर

5. बढ़िया, हमें केवल सारांश कॉलम का योग करना है और अंत में यह पता लगाना है कि आपूर्तिकर्ता "प्रिंटिंग हाउस" ने हमें कितना माल लाया। SUM () फ़ंक्शन का उपयोग करने का सिंटैक्स इस प्रकार है:

तालिका_नाम से SUM (कॉलम_नाम) चुनें;

हम कॉलम का नाम जानते हैं - सुम्मा, लेकिन हमारे पास टेबल का नाम नहीं है, क्योंकि यह क्वेरी का परिणाम है। क्या करें? ऐसे मामलों के लिए, MySQL दृश्य प्रदान करता है। एक दृश्य एक चुनिंदा क्वेरी है जिसे एक अद्वितीय नाम दिया गया है और बाद में उपयोग के लिए डेटाबेस में संग्रहीत किया जा सकता है।

एक दृश्य बनाने के लिए वाक्य रचना इस प्रकार है:

व्यू_नाम क्वेरी के रूप में बनाएं;

हमारे अनुरोध को रिपोर्ट_वेंडर नाम के एक दृश्य के रूप में सहेजते हैं:

रिपोर्ट_विक्रेता को चुनें पत्रिका_इनकमिंग.आईडी_प्रोडक्ट, मैगज़ीन_इनकमिंग.मात्रा, कीमतें.कीमत, मैगज़ीन_इनकमिंग.क्वांटिटी के रूप में देखें ;

6. अब आप अंतिम SUM () फ़ंक्शन का उपयोग कर सकते हैं:

रिपोर्ट_विक्रेता से SUM (सुम्मा) चुनें;

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

परिकलित फ़ील्ड (कॉलम)

तालिका_नाम से कॉलम_1_नाम, कॉलम_2_नाम, कॉलम_1_नाम * कॉलम_2_नाम गणना_कॉलम_नाम के रूप में चुनें;

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

रिपोर्ट_विक्रेता को चुनें A.id_product, A. मात्रा, B.मूल्य, A. मात्रा के रूप में देखें जहां id_vendor = 2);

सहमत हूं कि यह बहुत छोटा और स्पष्ट है।

प्रतिनिधित्व

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

हम संक्षेप करना सीखेंगे। नहीं, ये अभी तक SQL सीखने के परिणाम नहीं हैं, बल्कि डेटाबेस तालिकाओं के स्तंभों के मानों के परिणाम हैं। SQL एग्रीगेट फ़ंक्शन एकल परिणाम मान उत्पन्न करने के लिए कॉलम मानों पर कार्य करता है। सबसे अधिक उपयोग किए जाने वाले SQL एग्रीगेट फ़ंक्शन SUM, MIN, MAX, AVG और COUNT हैं। कुल कार्यों का उपयोग करने के दो मामलों के बीच अंतर करना आवश्यक है। सबसे पहले, कुल कार्यों का उपयोग स्वयं द्वारा किया जाता है और एक परिणामी मूल्य लौटाता है। दूसरा, कुल कार्यों का उपयोग SQL GROUP BY क्लॉज के साथ किया जाता है, अर्थात प्रत्येक समूह में परिणाम मान प्राप्त करने के लिए फ़ील्ड (कॉलम) द्वारा समूहीकृत किया जाता है। आइए पहले समूहीकरण के बिना कुल कार्यों का उपयोग करने के मामलों पर विचार करें।

एसक्यूएल योग समारोह

SQL SUM फ़ंक्शन डेटाबेस तालिका में किसी स्तंभ के मानों का योग लौटाता है। इसे केवल उन स्तंभों पर लागू किया जा सकता है जिनके मान संख्याएँ हैं। परिणामी योग इस तरह शुरू करने के लिए SQL क्वेरी:

योग चुनें (COLUMN_NAME)...

इस अभिव्यक्ति के बाद FROM (TABLE_NAME) आता है, और फिर WHERE क्लॉज का उपयोग करके एक शर्त निर्दिष्ट की जा सकती है। इसके अलावा, कॉलम नाम के सामने DISTINCT निर्दिष्ट किया जा सकता है, जिसका अर्थ है कि केवल अद्वितीय मानों की गणना की जाएगी। डिफ़ॉल्ट रूप से, सभी मानों को ध्यान में रखा जाता है (इसके लिए, आप विशेष रूप से DISTINCT नहीं, बल्कि ALL निर्दिष्ट कर सकते हैं, लेकिन शब्द ALL वैकल्पिक है)।

यदि आप MS SQL सर्वर पर इस पाठ से डेटाबेस के लिए क्वेरी चलाना चाहते हैं, लेकिन यह DBMS आपके कंप्यूटर पर स्थापित नहीं है, तो आप इसे इस लिंक पर दिए गए निर्देशों का उपयोग करके स्थापित कर सकते हैं। .

सबसे पहले, हम कंपनी डेटाबेस - Company1 के साथ काम करेंगे। इस डेटाबेस को बनाने की स्क्रिप्ट, इसकी टेबल और टेबल को डेटा से भरने के लिए - इस लिंक पर फाइल में .

उदाहरण 1।कंपनी का एक डेटाबेस है जिसमें उसके डिवीजनों और कर्मचारियों के डेटा हैं। स्टाफ टेबल, सब कुछ के अलावा, कर्मचारी वेतन पर डेटा के साथ एक कॉलम है। तालिका से चयन इस प्रकार है (चित्र को बड़ा करने के लिए, बाईं माउस बटन से उस पर क्लिक करें):

सभी वेतनों का योग प्राप्त करने के लिए, हम निम्नलिखित क्वेरी का उपयोग करते हैं (MS SQL सर्वर पर - उपसर्ग USE company1 के साथ;):

कर्मचारियों से SUM (वेतन) चुनें

यह क्वेरी 287664.63 लौटाएगी।

और अब । अभ्यास में, हम पहले से ही कार्यों को जटिल करना शुरू कर रहे हैं, उन्हें उन लोगों के करीब लाते हैं जो व्यवहार में आते हैं।

एसक्यूएल मिन समारोह

SQL MIN फ़ंक्शन उन स्तंभों पर भी काम करता है जिनके मान संख्याएँ हैं और स्तंभ में सभी मानों का न्यूनतम लौटाता है। इस फ़ंक्शन में SUM फ़ंक्शन के समान सिंटैक्स है।

उदाहरण 3.डेटाबेस और तालिका उदाहरण 1 के समान हैं।

विभाग संख्या 42 के कर्मचारियों के न्यूनतम वेतन का पता लगाना आवश्यक है। ऐसा करने के लिए, निम्नलिखित प्रश्न लिखें (MS SQL सर्वर पर - उपसर्ग USE company1 के साथ;):

अनुरोध 10505.90 मान लौटाएगा।

और फिर स्वयं सहायता व्यायाम... इसमें और कुछ अन्य अभ्यासों में, आपको न केवल स्टाफ तालिका, बल्कि संगठन तालिका की भी आवश्यकता होगी, जिसमें कंपनी के प्रभागों के बारे में डेटा शामिल है:

उदाहरण 4.संगठन तालिका को स्टाफ तालिका में जोड़ा जाता है, जिसमें फर्म के विभाजनों के बारे में डेटा होता है। बोस्टन में स्थित एक विभाग में एक कर्मचारी द्वारा काम करने की न्यूनतम संख्या प्रदर्शित करें।

एसक्यूएल मैक्स फ़ंक्शन

SQL MAX फ़ंक्शन समान रूप से काम करता है और इसमें एक समान सिंटैक्स होता है, जिसका उपयोग तब किया जाता है जब आपको किसी कॉलम में सभी मानों के बीच अधिकतम मान निर्धारित करने की आवश्यकता होती है।

उदाहरण 5.

विभाग संख्या 42 के कर्मचारियों के अधिकतम वेतन का पता लगाना आवश्यक है। ऐसा करने के लिए, निम्नलिखित प्रश्न लिखें (MS SQL सर्वर पर - पूर्ववर्ती USE company1 के साथ; निर्माण):

अनुरोध 18352.80 . मान लौटाएगा

समय आ गया है आत्म-समाधान के लिए व्यायाम.

उदाहरण 6.हम फिर से दो टेबल - स्टाफ और संगठन के साथ काम कर रहे हैं। पूर्वी डिवीजन से संबंधित विभाग में विभाग का नाम और एक कर्मचारी द्वारा अर्जित अधिकतम कमीशन प्रदर्शित करें। उपयोग शामिल हों (तालिकाओं में शामिल हों) .

एसक्यूएल एवीजी फ़ंक्शन

पहले वर्णित फ़ंक्शन के लिए उपरोक्त सिंटैक्स SQL ​​AVG फ़ंक्शन के लिए भी सही है। यह फ़ंक्शन एक कॉलम में सभी मानों का औसत लौटाता है।

उदाहरण 7.डेटाबेस और तालिका पिछले उदाहरणों की तरह ही हैं।

मान लीजिए कि आप विभाग संख्या 42 के कर्मचारियों की औसत वरिष्ठता का पता लगाना चाहते हैं। ऐसा करने के लिए, निम्नलिखित प्रश्न लिखें (MS SQL सर्वर पर - उपसर्ग USE company1 के साथ;):

परिणाम 6.33 . का मान होगा

उदाहरण 8.हम एक टेबल - स्टाफ के साथ काम करते हैं। 4 से 6 साल के अनुभव वाले कर्मचारियों का औसत वेतन निकालें।

SQL COUNT फ़ंक्शन

SQL COUNT फ़ंक्शन डेटाबेस तालिका में रिकॉर्ड की संख्या देता है। यदि आप क्वेरी में SELECT COUNT (COLUMN_NAME) ... निर्दिष्ट करते हैं, तो परिणाम उन रिकॉर्ड्स को छोड़कर, रिकॉर्ड्स की संख्या होगी, जिनमें कॉलम वैल्यू NULL (अपरिभाषित) है। यदि आप तर्क के रूप में तारक का उपयोग करते हैं और SELECT COUNT (*) ... क्वेरी शुरू करते हैं, तो परिणाम तालिका में सभी रिकॉर्ड्स (पंक्तियों) की संख्या होगी।

उदाहरण 9.डेटाबेस और तालिका पिछले उदाहरणों की तरह ही हैं।

आप कमीशन प्राप्त करने वाले सभी कर्मचारियों की संख्या जानना चाहते हैं। कर्मचारियों की संख्या जिनके कॉम कॉलम मान NULL नहीं हैं, निम्नलिखित क्वेरी (MS SQL सर्वर पर - पूर्ववर्ती USE company1 के साथ; निर्माण) लौटाएंगे:

स्टाफ से COUNT (Com) चुनें

परिणाम 11 है।

उदाहरण 10.डेटाबेस और तालिका पिछले उदाहरणों की तरह ही हैं।

यदि आपको किसी तालिका में रिकॉर्ड की कुल संख्या का पता लगाने की आवश्यकता है, तो हम COUNT फ़ंक्शन (MS SQL सर्वर पर - पूर्ववर्ती USE company1; निर्माण के साथ) के तर्क के रूप में तारक के साथ एक क्वेरी का उपयोग करते हैं:

स्टाफ से COUNT (*) चुनें

परिणाम 17 है।

अगले स्वयं सहायता व्यायामआपको एक सबक्वायरी का उपयोग करने की आवश्यकता होगी।

उदाहरण 11.हम एक टेबल - स्टाफ के साथ काम करते हैं। नियोजन विभाग (मैदान) में कर्मचारियों की संख्या प्रदर्शित करें।

एसक्यूएल ग्रुप बाय (ग्रुपिंग) के साथ कुल कार्य

अब आइए SQL GROUP BY क्लॉज के साथ संयोजन के रूप में एग्रीगेट फ़ंक्शंस का उपयोग करते हुए देखें। SQL GROUP BY क्लॉज का उपयोग डेटाबेस तालिका के कॉलम द्वारा परिणाम मानों को समूहित करने के लिए किया जाता है। साइट है इस ऑपरेटर को अलग से समर्पित एक सबक .

हम "विज्ञापन पोर्टल 1" डेटाबेस के साथ काम करेंगे। इस डेटाबेस को बनाने, इसकी तालिका और डेटा तालिका भरने की स्क्रिप्ट इस लिंक पर फ़ाइल में है .

उदाहरण 12.तो एक क्लासीफाइड पोर्टल डेटाबेस है। इसमें विज्ञापन तालिका होती है, जिसमें सप्ताह के लिए सबमिट किए गए विज्ञापनों के बारे में डेटा होता है। श्रेणी कॉलम में विज्ञापनों की बड़ी श्रेणियों (उदाहरण के लिए, रियल एस्टेट) के बारे में डेटा होता है, और पार्ट्स कॉलम में श्रेणी में शामिल छोटे हिस्सों के बारे में डेटा होता है (उदाहरण के लिए, अपार्टमेंट और विला के हिस्से रियल एस्टेट श्रेणी के हिस्से हैं)। यूनिट कॉलम में सबमिट किए गए विज्ञापनों की संख्या पर डेटा होता है, और मनी कॉलम में विज्ञापन सबमिट करने के लिए प्राप्त राशि शामिल होती है।

SQL GROUP BY कथन का उपयोग करके, प्रत्येक श्रेणी में विज्ञापन प्रदर्शित करने से अर्जित धन की राशि ज्ञात करें। हम निम्नलिखित क्वेरी लिखते हैं (MS SQL सर्वर पर - पूर्ववर्ती निर्माण USE adportal1 के साथ;):

श्रेणी के अनुसार विज्ञापन समूह से धन के रूप में श्रेणी, योग (धन) का चयन करें

उदाहरण 13.डेटाबेस और टेबल पिछले उदाहरण की तरह ही हैं।

प्रत्येक श्रेणी के किस भाग में सबसे अधिक विज्ञापन थे, यह जानने के लिए SQL GROUP BY कथन का उपयोग करें। हम निम्नलिखित क्वेरी लिखते हैं (MS SQL सर्वर पर - पूर्ववर्ती निर्माण USE adportal1 के साथ;):

श्रेणी के अनुसार विज्ञापन समूह से अधिकतम श्रेणी, भाग, MAX (इकाइयाँ) चुनें

परिणाम निम्न तालिका होगी:

एक तालिका में कुल और व्यक्तिगत मान प्राप्त किए जा सकते हैं UNION ऑपरेटर का उपयोग करके क्वेरी परिणामों का संयोजन .

संबंधपरक डेटाबेस और SQL भाषा