SQL HowTo: विंडो फ़ंक्शंस का उपयोग करके चेन का निर्माण

कार्य: अन्य लोगों के पैसे गिनना

नए साल की लंबी छुट्टी के बाद, बैंक ने अपने ग्राहकों को पुरस्कृत करने का फैसला किया - और प्रत्येक व्यक्ति जिसने इस वर्ष एक खाता खोला है, जब वह खाता "रीसेट" नहीं था तब सबसे लंबे समय तक निरंतर दैनिक बैलेंस का 1% अर्जित किया।

date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 

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

सीएसवी करने वाली तालिका

 CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T; 

चरण 1: आवेदन की स्थिति ठीक करें

 SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt; 

 dt | client | balance | cond ------------------------------------ 2020-01-01 |  | 150.00 | t 2020-01-02 |  | 100.00 | t 2020-01-03 |  | 200.00 | t 2020-01-05 |  | 0.00 | f 2020-01-06 |  | 50.00 | t 2020-01-08 |  | 0.00 | f 2020-01-09 |  | 0.00 | f 2020-01-10 |  | 5.00 | t 2020-01-01 |  | 100.00 | t 2020-01-02 |  | 150.00 | t 2020-01-08 |  | 200.00 | t 2020-01-09 |  | 0.00 | f 

चरण 2: लापता की गणना करें

 coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days 

 dt | client | balance | cond | days ------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 2020-01-02 |  | 100.00 | t | 1 2020-01-03 |  | 200.00 | t | 2 2020-01-05 |  | 0.00 | f | 1 2020-01-06 |  | 50.00 | t | 2 2020-01-08 |  | 0.00 | f | 1 2020-01-09 |  | 0.00 | f | 1 2020-01-10 |  | 5.00 | t | 2 2020-01-01 |  | 100.00 | t | 1 2020-01-02 |  | 150.00 | t | 6 2020-01-08 |  | 200.00 | t | 1 2020-01-09 |  | 0.00 | f | 3 

चरण 3: ब्रेक पॉइंट का पता लगाएं

 lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start 

 dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t 2020-01-02 |  | 100.00 | t | 1 | f 2020-01-03 |  | 200.00 | t | 2 | f 2020-01-05 |  | 0.00 | f | 1 | t 2020-01-06 |  | 50.00 | t | 2 | t 2020-01-08 |  | 0.00 | f | 1 | t 2020-01-09 |  | 0.00 | f | 1 | f 2020-01-10 |  | 5.00 | t | 2 | t 2020-01-01 |  | 100.00 | t | 1 | t 2020-01-02 |  | 150.00 | t | 6 | f 2020-01-08 |  | 200.00 | t | 1 | f 2020-01-09 |  | 0.00 | f | 3 | t 

चरण 4: लिंक को स्ट्रिंग करें

 count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid 

 dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t | 1 2020-01-02 |  | 100.00 | t | 1 | f | 1 2020-01-03 |  | 200.00 | t | 2 | f | 1 2020-01-06 |  | 50.00 | t | 2 | t | 2 2020-01-10 |  | 5.00 | t | 2 | t | 3 2020-01-01 |  | 100.00 | t | 1 | t | 1 2020-01-02 |  | 150.00 | t | 6 | f | 1 2020-01-08 |  | 200.00 | t | 1 | f | 1 

चरण 5: जंजीरों को लगाना

 SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid; 

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-06 | 100.00 | 2  | 2020-01-10 | 10.00 | 2  | 2020-01-01 | 1200.00 | 8 

चरण 6: एप्लाइड हाई की तलाश में

 SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC; 

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-01 | 1200.00 | 8 

हम गठबंधन और अनुकूलन करते हैं

 WITH step123 AS ( SELECT * , CASE WHEN cond THEN lag(cond) OVER(w) IS DISTINCT FROM cond END chain_start , CASE WHEN cond THEN coalesce(lead(dt) OVER(w), '2020-01-12') - dt END days FROM tbl , LATERAL(SELECT balance > 0 cond) T WINDOW w AS (PARTITION BY client ORDER BY dt) ) , step4 AS ( SELECT * , count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid FROM step123 WHERE cond ) SELECT DISTINCT ON(client) client , sum(days) OVER(w) days , min(dt) OVER(w) chain_dt , sum(days * balance) OVER(w) balance FROM step4 WINDOW w AS (PARTITION BY client, grpid) ORDER BY 1, 2 DESC; 

• LATERAL उपश्रेणी ने हमें चयन के माध्यम से अनावश्यक पास के बिना एक अतिरिक्त क्षेत्र की गणना करने और तुरंत फ़ंक्शन में इसका उपयोग करने की अनुमति दी
• WINDOW के तहत एक सामान्य परिभाषा को हटाने से PostgreSQL को "विंडो" बनाने के लिए डबल सॉर्टिंग नहीं करने में मदद मिलती है और दोनों कार्य एक WindowAgg नोड में गणना करते हैं
• CASE के तहत "आलसी" फ़ंक्शन की गणना प्रदर्शन किए गए कार्यों की संख्या को कम करती है