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 ، لكن رصيد حساب Alice في نهاية هذه الفترة هو غير صفري. نأخذ أيضًا في الاعتبار الحاجة إلى الحد من هذه الفترة.

CSV إلى المائدة

 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 يقلل من عدد العمليات المنفذة