SQL में संचयी कुल

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

एक्सेल जैसे स्प्रेडशीट में, चल रहे कुल की गणना बहुत ही सरलता से की जाती है: पहले रिकॉर्ड में परिणाम इसके मूल्य से मेल खाता है:



... और फिर हम वर्तमान मूल्य और पिछले कुल को संक्षेप में प्रस्तुत करते हैं।



दूसरे शब्दों में

$$

$$Total_1 = Value_1 \\ Total_2 = Total_1 + Value_2 \\ Total_3 = Total_2 + मान_3 \\ \ ldots \\ Total_n = कुल_ {n-1} + मान_n \\ $$

... या:

$$

$$\ शुरू {के मामले} Total_1 = Value_1, n = 1 \\ Total_n = Total_ {n-1} + Value_n, n \ geq 2 \ end {मामले}$$

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



जैसा कि आप देख सकते हैं, संचयी कुल की गणना दो अपरिवर्तित घटकों से जुड़ी है:
(a) डेट और डेट के अनुसार डेटा छांटना
(b) पिछली पंक्ति का जिक्र है।

लेकिन एसक्यूएल क्या है? बहुत लंबे समय तक इसमें कोई आवश्यक कार्यक्षमता नहीं थी। एक आवश्यक उपकरण - विंडो फ़ंक्शन - पहली बार केवल SQL: 2003 मानक में दिखाई दिया। इस बिंदु पर, वे पहले से ही ओरेकल (संस्करण 8i) में थे। लेकिन अन्य DBMS में कार्यान्वयन में 5-10 साल की देरी हुई: SQL Server 2012, MySQL 8.0.2 (2018), MariaDB 10.2.0 (2017), PostgreSQL 8.4 (2009), z / OS के लिए DB2 9 (2007) वर्ष), और यहां तक ​​कि SQLite 3.25 (2018)।

1. खिड़की के कार्य

विंडो फ़ंक्शंस शायद सबसे आसान तरीका है। आधार मामले में (समूहों के बिना तालिका) हम तारीख द्वारा क्रमबद्ध आंकड़ों पर विचार करते हैं:

 order by dt 

... लेकिन हम केवल वर्तमान से पहले की लाइनों में रुचि रखते हैं:

 rows between unbounded preceding and current row 

अंततः, हमें इन मापदंडों के साथ योग की आवश्यकता है:

 sum(val) over (order by dt rows between unbounded preceding and current row) 

एक पूर्ण अनुरोध इस तरह दिखेगा:

 select s.*, coalesce(sum(s.val) over (order by s.dt rows between unbounded preceding and current row), 0) as total from test_simple s order by s.dt; 

समूहों ( grp क्षेत्र) के लिए संचयी कुल के मामले में हमें केवल एक छोटे से संपादन की आवश्यकता है। अब हम समूह के आधार पर डेटा को "विंडो" में विभाजित मानते हैं:



इस पृथक्करण के लिए, आपको कीवर्ड partition by उपयोग करना होगा:

 partition by grp 

और, तदनुसार, इन खिड़कियों के लिए राशि पर विचार करें:

 sum(val) over (partition by grp order by dt rows between unbounded preceding and current row) 

फिर पूरी क्वेरी को इस तरह परिवर्तित किया जाता है:

 select tg.*, coalesce(sum(tg.val) over (partition by tg.grp order by tg.dt rows between unbounded preceding and current row), 0) as total from test_groups tg order by tg.grp, tg.dt; 

विंडो फ़ंक्शन का प्रदर्शन आपके DBMS (और इसके संस्करण!), तालिका के आकार और अनुक्रमित की उपलब्धता पर निर्भर करेगा। लेकिन ज्यादातर मामलों में, यह विधि सबसे प्रभावी होगी। हालाँकि, DBMS के पुराने संस्करणों में विंडो फ़ंक्शंस उपलब्ध नहीं हैं (जो अभी भी उपयोग में हैं)। इसके अलावा, वे Microsoft Access और SAP / Sybase ASE जैसे DBMS में नहीं हैं। यदि एक विक्रेता-स्वतंत्र समाधान की आवश्यकता है, तो विकल्पों पर विचार किया जाना चाहिए।

2. उपशम

जैसा कि ऊपर उल्लेख किया गया है, मुख्य DBMS में विंडो फ़ंक्शन बहुत देर से पेश किए गए थे। यह देरी आश्चर्यजनक नहीं होनी चाहिए: संबंधपरक सिद्धांत में, डेटा का आदेश नहीं दिया गया है। संबंधपरक सिद्धांत की भावना से बहुत अधिक एक उपशम के माध्यम से समाधान से मेल खाती है।

इस तरह के एक उपशम को वर्तमान (और वर्तमान सहित) से पहले की तारीख के साथ मूल्यों के योग पर विचार करना चाहिए: $$$dt_ {पंक्ति} \ leq dt_ {वर्तमान पंक्ति}$।

कोड में ऐसा क्या दिखता है:

 select s.*, (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt <= s.dt) as total from test_simple s order by s.dt; 

थोड़ा और अधिक कुशल समाधान होगा जिसमें उप-धारा वर्तमान तिथि तक कुल मानती है (लेकिन इसे शामिल नहीं करती है), और फिर इसे पंक्ति में मान के साथ जोड़ते हैं:

 select s.*, s.val + (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt < s.dt) as total from test_simple s order by s.dt; 

कई समूहों के लिए संचयी परिणाम के मामले में, हमें सहसंबंधित उप-वर्ग का उपयोग करने की आवश्यकता है:

 select g.*, (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt) as total from test_groups g order by g.grp, g.dt; 

स्थिति g.grp = t2.grp समूह में शामिल करने के लिए लाइनों की जाँच करता है (जो, सिद्धांत रूप में, विंडो कार्यों में partition by grp के काम के समान है)।

3. आंतरिक संबंध

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

 select s.*, coalesce(sum(t2.val), 0) as total from test_simple s inner join test_simple t2 on t2.dt <= s.dt group by s.dt, s.val order by s.dt; 

जैसा कि आप देख सकते हैं, subquery t2.dt <= s.dt में फ़िल्टरिंग स्थिति एक t2.dt <= s.dt स्थिति बन गई है। इसके अलावा, एग्रिगेटिंग फंक्शन sum() का उपयोग करने के लिए sum() हमें group by s.dt, s.val द्वारा group by s.dt, s.val दिनांक और मान द्वारा समूहीकरण करने की आवश्यकता है।

इसी तरह, आप अलग-अलग grp समूहों के मामले के लिए कर सकते हैं:

 select g.*, coalesce(sum(t2.val), 0) as total from test_groups g inner join test_groups t2 on g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt; 

4. कार्टेशियन उत्पाद

जब से हमने उपकुंजी को ज्वाइन किया है, तो कार्टेशियन उत्पाद क्यों नहीं आज़माएं? इस समाधान के लिए केवल न्यूनतम संपादन की आवश्यकता होगी:

 select s.*, coalesce(sum(t2.val), 0) as total from test_simple s, test_simple t2 where t2.dt <= s.dt group by s.dt, s.val order by s.dt; 

या समूहों के मामले के लिए:

 select g.*, coalesce(sum(t2.val), 0) as total from test_groups g, test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt; 

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

5. पुनरावर्ती अनुरोध

अधिक विशिष्ट दृष्टिकोणों में से एक सामान्य तालिका अभिव्यक्ति में एक पुनरावर्ती प्रश्न है। ऐसा करने के लिए, हमें "एंकर" की आवश्यकता है - एक क्वेरी जो बहुत पहले लाइन लौटाती है:

 select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple) 

फिर, union all की सहायता से, पुनरावर्ती क्वेरी के परिणाम "लंगर" में जोड़ दिए जाते हैं। ऐसा करने के लिए, आप दिनांक दिनांक फ़ील्ड पर भरोसा कर सकते हैं, इसमें एक दिन जोड़ सकते हैं:

 select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) -- + 1   SQL Server 

एक दिन को जोड़ने वाले कोड का हिस्सा सार्वभौमिक नहीं है। उदाहरण के लिए, SQL सर्वर के लिए यह r.dt = dateadd(day, 1, cte.dt) , Oracle के लिए r.dt = cte.dt + 1 है, आदि।

"लंगर" और मुख्य अनुरोध को मिलाकर, हम अंतिम परिणाम प्राप्त करते हैं:

 with cte (dt, val, total) as (select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple) union all select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) -- r.dt = cte.dt + 1  Oracle,  .. ) select dt, val, total from cte order by dt; 

समूहों के साथ मामले का समाधान अधिक जटिल नहीं होगा:

 with cte (dt, grp, val, total) as (select g.dt, g.grp, g.val, g.val as total from test_groups g where g.dt = (select min(dt) from test_groups where grp = g.grp) union all select r.dt, r.grp, r.val, cte.total + r.val from cte inner join test_groups r on r.dt = dateadd(day, 1, cte.dt) -- r.dt = cte.dt + 1  Oracle,  .. and cte.grp = r.grp ) select dt, grp, val, total from cte order by grp, dt; 

6. पुनरावर्ती क्वेरी row_number() फ़ंक्शन के साथ

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

इसलिए, row_number() साथ पुनरावर्ती क्वेरी के लिए हमें दो row_number() की आवश्यकता है। पहले में, हम केवल रेखाएँ लिखते हैं:

 with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple) 

... और यदि पंक्ति संख्या पहले से ही तालिका में है, तो आप इसके बिना कर सकते हैं। निम्नलिखित प्रश्न में, हम पहले से ही cte1 कर रहे हैं:

 cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 ) 

और पूरा अनुरोध इस तरह दिखता है:

 with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple), cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 ) select dt, val, total from cte2 order by dt; 

... या समूहों के मामले के लिए:

 with cte1 (dt, grp, val, rn) as (select dt, grp, val, row_number() over (partition by grp order by dt) as rn from test_groups), cte2 (dt, grp, val, rn, total) as (select dt, grp, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.grp, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.grp = cte2.grp and cte1.rn = cte2.rn + 1 ) select dt, grp, val, total from cte2 order by grp, dt; 

7. CROSS APPLY / LATERAL

चल रहे कुल की गणना करने के सबसे विदेशी तरीकों में से एक CROSS APPLY स्टेटमेंट (SQL Server, Oracle) या इसके समकक्ष LATERAL (MySQL, PostgreSQL) का उपयोग करना है। ये ऑपरेटर देर से दिखाई दिए (उदाहरण के लिए, केवल Oracle में संस्करण 12c से)। और कुछ DBMSs (उदाहरण के लिए, MariaDB ) में वे बिल्कुल नहीं हैं। इसलिए, यह निर्णय विशुद्ध रूप से सौंदर्य के हित में है।

कार्यात्मक रूप से, CROSS APPLY या LATERAL उपयोग करना LATERAL समान है: हम गणना के परिणाम को मुख्य अनुरोध में संलग्न करते हैं:

 cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2 

... जो इस तरह दिखता है:

 select s.*, t2.total from test_simple s cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2 order by s.dt; 

समूहों के साथ मामले के लिए समाधान समान होगा:

 select g.*, t2.total from test_groups g cross apply (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt ) t2 order by g.grp, g.dt; 

कुल: हमने मुख्य मंच-स्वतंत्र समाधानों की जांच की। लेकिन विशिष्ट DBMS के लिए विशिष्ट समाधान हैं! चूंकि यहां बहुत सारे विकल्प हैं, आइए हम कुछ सबसे दिलचस्प लोगों पर ध्यान दें।

8. MODEL बयान (ओरेकल)

ओरेकल में MODEL स्टेटमेंट सबसे सुरुचिपूर्ण समाधानों में से एक प्रदान करता है। लेख की शुरुआत में, हमने संचयी कुल के सामान्य सूत्र की जांच की:

$$

$$\ शुरू {मामलों} Total_1 = Value_1, n = 1 \\ Total_n = Total_ {n-1} + Value_n, n \ geq 2 \ end {मामले}$$

MODEL आपको इस सूत्र को एक से एक करने के लिए लागू करने की अनुमति देता है! ऐसा करने के लिए, हम पहले total पंक्ति को वर्तमान पंक्ति के मानों से भरते हैं

 select dt, val, val as total from test_simple 

... फिर हम पंक्ति संख्या को row_number() over (order by dt) as rn (या संख्या के साथ समाप्त फ़ील्ड का उपयोग करें, यदि कोई हो) की गणना करते हैं। और अंत में, हम पहले को छोड़कर सभी पंक्तियों के लिए एक नियम पेश करते हैं: total[rn >= 2] = total[cv() - 1] + val[cv()] ।

यहाँ cv() फ़ंक्शन वर्तमान लाइन के मान के लिए जिम्मेदार है। और पूरा अनुरोध इस तरह दिखाई देगा:

 select dt, val, total from (select dt, val, val as total from test_simple) t model dimension by (row_number() over (order by dt) as rn) measures (dt, val, total) rules (total[rn >= 2] = total[cv() - 1] + val[cv()]) order by dt; 

9. कर्सर (एसक्यूएल सर्वर)

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

कर्सर के माध्यम से कार्यान्वयन बहुत तुच्छ है। पहले आपको एक अस्थायी तालिका बनाने और इसे मुख्य से तारीखों और मूल्यों के साथ भरने की आवश्यकता है:

 create table #temp (dt date primary key, val int null, total int null ); insert #temp (dt, val) select dt, val from test_simple order by dt; 

फिर हम स्थानीय वैरिएबल सेट करते हैं जिसके माध्यम से अपडेट होगा:

 declare @VarTotal int, @VarDT date, @VarVal int; set @VarTotal = 0; 

उसके बाद हम कर्सर के माध्यम से अस्थायी तालिका को अपडेट करते हैं:

 declare cur cursor local static read_only forward_only for select dt, val from #temp order by dt; open cur; fetch cur into @VarDT, @VarVal; while @@fetch_status = 0 begin set @VarTotal = @VarTotal + @VarVal; update #temp set total = @VarTotal where dt = @VarDT; fetch cur into @VarDT, @VarVal; end; close cur; deallocate cur; 

और अंत में, हम वांछित परिणाम प्राप्त करते हैं:

 select dt, val, total from #temp order by dt; drop table #temp; 

10. स्थानीय चर (SQL सर्वर) के माध्यम से अद्यतन करें

SQL सर्वर में स्थानीय चर के माध्यम से अद्यतन करना अनैच्छिक व्यवहार पर आधारित है, इसलिए इसे विश्वसनीय नहीं माना जा सकता है। फिर भी, यह शायद सबसे तेज़ समाधान है, और यह दिलचस्प है।

चलो दो चर बनाते हैं: एक संचयी योग और एक तालिका चर के लिए:

 declare @VarTotal int = 0; declare @tv table (dt date null, val int null, total int null ); 

सबसे पहले, मुख्य तालिका से डेटा के @tv भरें

 insert @tv (dt, val, total) select dt, val, 0 as total from test_simple order by dt; 

तब @tv का उपयोग करके तालिका चर @tv अपडेट करते हैं:

 update @tv set @VarTotal = total = @VarTotal + val from @tv; 

... जिसके बाद हमें अंतिम परिणाम मिलता है:

 select * from @tv order by dt; 

सारांश: हमने SQL में संचयी योगों की गणना करने के लिए शीर्ष 10 तरीकों की समीक्षा की। जैसा कि आप देख सकते हैं, यहां तक ​​कि खिड़की के कार्यों के बिना भी, यह समस्या पूरी तरह से हल करने योग्य है, और समाधान के यांत्रिकी को जटिल नहीं कहा जा सकता है।