Использование SQL window functions для построения журнала-ордера
Журнал-ордер строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные данные, затем в набор данных вставляются строки с итоговыми значениям по группам, чтобы на экране мы увидели такую картину:
Запрос для вышеприведенного журнала с группировкой по клиентам, затем по месяцам, выглядит так:
EXECUTE BLOCK( datebegin DATE=:BEGINDATE, dateend DATE=:ENDDATE ) RETURNS( c0 VARCHAR(180), NAME0 VARCHAR(180), s0 VARCHAR(180), c1 VARCHAR(180), NAME1 VARCHAR(180), s1 VARCHAR(180), sortfield INTEGE, ncu_begin_debit NUMERIC(15,2), ncu_begin_credit NUMERIC(15,2), ncu_debit NUMERIC(15,2), ncu_credit NUMERIC(15,2), ncu_end_debit NUMERIC(15,2), ncu_end_credit NUMERIC(15,2), curr_begin_debit NUMERIC(15,2), curr_begin_credit NUMERIC(15,2), curr_debit NUMERIC(15,2), curr_credit NUMERIC(15,2), curr_end_debit NUMERIC(15,2), curr_end_credit NUMERIC(15,2), eq_begin_debit NUMERIC(15,2), eq_begin_credit NUMERIC(15,2), eq_debit NUMERIC(15,2), eq_credit NUMERIC(15,2), eq_end_debit NUMERIC(15,2), eq_end_credit NUMERIC(15,2) ) AS DECLARE VARIABLE varncubegin dcurrency; DECLARE VARIABLE varcurrbegin dcurrency; DECLARE VARIABLE vareqbegin dcurrency; DECLARE VARIABLE varncudebit dcurrency; DECLARE VARIABLE varncucredit dcurrency; DECLARE VARIABLE varcurrdebit dcurrency; DECLARE VARIABLE varcurrcredit dcurrency; DECLARE VARIABLE vareqdebit dcurrency; DECLARE VARIABLE vareqcredit dcurrency; DECLARE VARIABLE varncuend dcurrency; DECLARE VARIABLE varcurrend dcurrency; DECLARE VARIABLE vareqend dcurrency; DECLARE VARIABLE wasmovement INTEGER; DECLARE VARIABLE closedate DATE; DECLARE VARIABLE temp_c0 VARCHAR(180); BEGIN closedate=CAST((CAST('17.11.1858'AS DATE)+GEN_ID(gd_g_entry_balance_date,0))AS DATE); temp_c0=''; FOR SELECT m.ncu, m.curr, m.eq, c0.ID, SUBSTRING(c0.NAME FROM 1 FOR 180)AS c0, m.dateparam_c1, m.dateparam_c1, m.dateparam_c1, m.debitncu, m.creditncu, m.debitcurr, m.creditcurr, m.debiteq, m.crediteq FROM ( SELECT COALESCE(SUM(en.debitncu-en.creditncu),0)AS ncu, COALESCE(SUM(en.debitcurr-en.creditcurr),0)AS curr, COALESCE(SUM(en.debiteq-en.crediteq),0)AS eq ,en.USR$GS_CUSTOMER,EXTRACT(MONTH FROM en.entrydate)AS dateparam_c1 ,COALESCE(SUM(en.debitncu_m),0)AS debitncu, COALESCE(SUM(en.creditncu_m),0)AS creditncu, COALESCE(SUM(en.debitcurr_m),0)AS debitcurr, COALESCE(SUM(en.creditcurr_m),0)AS creditcurr, COALESCE(SUM(en.debiteq_m),0)AS debiteq, COALESCE(SUM(en.crediteq_m),0)AS crediteq FROM ( SELECT bal.debitncu, bal.creditncu, bal.debitcurr, bal.creditcurr, bal.debiteq, bal.crediteq ,bal.USR$GS_CUSTOMER,CAST(NULL AS DATE)AS entrydate ,CAST(0 AS NUMERIC(15,2))AS debitncu_m, CAST(0 AS NUMERIC(15,2))AS creditncu_m, CAST(0 AS NUMERIC(15,2))AS debitcurr_m, CAST(0 AS NUMERIC(15,2))AS creditcurr_m, CAST(0 AS NUMERIC(15,2))AS debiteq_m, CAST(0 AS NUMERIC(15,2))AS crediteq_m FROM ac_entry_balance bal WHERE bal.accountkey IN(393008)AND bal.companykey+0 IN(153583337,146866007,153408611) UNION ALL SELECT e1.debitncu, e1.creditncu, e1.debitcurr, e1.creditcurr, e1.debiteq, e1.crediteq ,e.USR$GS_CUSTOMER,CAST(NULL AS DATE)AS entrydate ,CAST(0 AS NUMERIC(15,2))AS debitncu_m, CAST(0 AS NUMERIC(15,2))AS creditncu_m, CAST(0 AS NUMERIC(15,2))AS debitcurr_m, CAST(0 AS NUMERIC(15,2))AS creditcurr_m, CAST(0 AS NUMERIC(15,2))AS debiteq_m, CAST(0 AS NUMERIC(15,2))AS crediteq_m FROM ac_entry e LEFT JOIN ac_entry e1 ON e1.id=e.id AND e1.entrydate>=:closedate AND e1.entrydate<:datebegin WHERE e.accountkey IN(393008)AND e.entrydate>=:closedate AND e.entrydate<=:dateend AND e.companykey+0 IN(153583337,146866007,153408611) UNION ALL SELECT CAST(0 AS NUMERIC(15,4))AS debitncu, CAST(0 AS NUMERIC(15,4))AS creditncu, CAST(0 AS NUMERIC(15,4))AS debitcurr, CAST(0 AS NUMERIC(15,4))AS creditcurr, CAST(0 AS NUMERIC(15,4))AS debiteq, CAST(0 AS NUMERIC(15,4))AS crediteq, em.USR$GS_CUSTOMER,em.entrydate ,SUM(IIF(emc.issimple=0,emc.creditncu,em.debitncu))AS debitncu_m, SUM(IIF(emc.issimple=0,emc.debitncu,em.creditncu))AS creditncu_m, SUM(IIF(emc.issimple=0,emc.creditcurr,em.debitcurr))AS debitcurr_m, SUM(IIF(emc.issimple=0,emc.debitcurr,em.creditcurr))AS creditcurr_m, SUM(IIF(emc.issimple=0,emc.crediteq,em.debiteq))AS debiteq_m, SUM(IIF(emc.issimple=0,emc.debiteq,em.crediteq))AS crediteq_m FROM ac_entry em LEFT JOIN ac_entry emc ON emc.recordkey=em.recordkey AND emc.accountpart<>em.accountpart WHERE em.accountkey IN(393008)AND em.companykey+0 IN(153583337,146866007,153408611) AND em.entrydate>=:datebegin AND em.entrydate<=:dateend AND NOT EXISTS( SELECT e_cm.id FROM ac_entry e_cm JOIN ac_transaction tr ON e_cm.transactionkey=tr.id AND tr.isinternal=1 WHERE e_cm.recordkey=em.recordkey AND e_cm.accountpart<>em.accountpart AND e_cm.accountkey+0=em.accountkey AND (em.debitncu=e_cm.creditncu AND em.creditncu=e_cm.debitncu AND em.debitcurr=e_cm.creditcurr AND em.creditcurr=e_cm.debitcurr)AND em.USR$GS_CUSTOMER=e_cm.USR$GS_CUSTOMER+0) GROUP BY em.USR$GS_CUSTOMER,em.entrydate )en GROUP BY en.USR$GS_CUSTOMER,EXTRACT(MONTH FROM en.entrydate) )m LEFT JOIN GD_CONTACT c0 ON c0.ID=m.USR$GS_CUSTOMER ORDER BY c0.NAME,m.dateparam_c1 INTO :varncubegin,:varcurrbegin,:vareqbegin ,:c0,:NAME0,:s0 ,:c1,:NAME1,:s1 ,:varncudebit,:varncucredit, :varcurrdebit,:varcurrcredit, :vareqdebit,:vareqcredit DO BEGIN sortfield=1; wasmovement=0; IF(varncubegin IS NULL)THEN varncubegin=0; IF(varcurrbegin IS NULL)THEN varcurrbegin=0; IF(vareqbegin IS NULL)THEN vareqbegin=0; IF(varncudebit IS NULL)THEN varncudebit=0; IF(varncucredit IS NULL)THEN varncucredit=0; IF(varcurrdebit IS NULL)THEN varcurrdebit=0; IF(varcurrcredit IS NULL)THEN varcurrcredit=0; IF(vareqdebit IS NULL)THEN vareqdebit=0; IF(vareqcredit IS NULL)THEN vareqcredit=0; IF((temp_c0=c0))THEN BEGIN varncubegin=varncuend; varcurrbegin=varcurrend; vareqbegin=vareqend; END ELSE BEGIN temp_c0=c0; END ncu_debit=CAST((varncudebit/1)AS NUMERIC(15,2)); ncu_credit=CAST((varncucredit/1)AS NUMERIC(15,2)); curr_debit=CAST((varcurrdebit/1)AS NUMERIC(15,2)); curr_credit=CAST((varcurrcredit/1)AS NUMERIC(15,2)); eq_debit=CAST((vareqdebit/1)AS NUMERIC(15,2)); eq_credit=CAST((vareqcredit/1)AS NUMERIC(15,2)); varncuend=varncubegin+(varncudebit-varncucredit); varcurrend=varcurrbegin+(varcurrdebit-varcurrcredit); vareqend=vareqbegin+(vareqdebit-vareqcredit); IF(varncubegin>0)THEN BEGIN ncu_begin_debit=CAST((varncubegin/1)AS NUMERIC(15,2)); ncu_begin_credit=0; END ELSE BEGIN ncu_begin_debit=0; ncu_begin_credit=-CAST((varncubegin/1)AS NUMERIC(15,2)); END IF(varncuend>0)THEN BEGIN ncu_end_debit=CAST((varncuend/1)AS NUMERIC(15,2)); ncu_end_credit=0; END ELSE BEGIN ncu_end_debit=0; ncu_end_credit=-CAST((varncuend/1)AS NUMERIC(15,2)); END IF(varcurrbegin>0)THEN BEGIN curr_begin_debit=CAST((varcurrbegin/1)AS NUMERIC(15,2)); curr_begin_credit=0; END ELSE BEGIN curr_begin_debit=0; curr_begin_credit=-CAST((varcurrbegin/1)AS NUMERIC(15,2)); END IF(varcurrend>0)THEN BEGIN curr_end_debit=CAST((varcurrend/1)AS NUMERIC(15,2)); curr_end_credit=0; END ELSE BEGIN curr_end_debit=0; curr_end_credit=-CAST((varcurrend/1)AS NUMERIC(15,2)); END IF(vareqbegin>0)THEN BEGIN eq_begin_debit=CAST((vareqbegin/1)AS NUMERIC(15,2)); eq_begin_credit=0; END ELSE BEGIN eq_begin_debit=0; eq_begin_credit=-CAST((vareqbegin/1)AS NUMERIC(15,2)); END IF(vareqend>0)THEN BEGIN eq_end_debit=CAST((vareqend/1)AS NUMERIC(15,2)); eq_end_credit=0; END ELSE BEGIN eq_end_debit=0; eq_end_credit=-CAST((vareqend/1)AS NUMERIC(15,2)); END IF((ncu_debit<>0)OR(ncu_credit<>0)OR(CAST((varncubegin/1)AS NUMERIC(15,2))<>0) )THEN BEGIN wasmovement=1; SUSPEND; END END END
Firebird 3 предоставляет в наше распоряжение SQL Window Functions, которые позволяют подсчитывать агрегатные значения внутри групп данных в выборке без сворачивания самих групп с помощью GROUP BY. Напишем запрос, подготавливающий данные аналогично показанному выше:
SELECT c.name, d.m, IIF(d.pdt > d.pct, d.pdt - d.pct, 0) AS pdt, IIF(d.pdt < d.pct, d.pct - d.pdt, 0) AS pct, d.dt, d.ct, IIF(d.rdt > d.rct, d.rdt - d.rct, 0) AS rdt, IIF(d.rdt < d.rct, d.rct - d.rdt, 0) AS rct --d.tdt, --d.tct FROM ( WITH raw_data AS ( SELECT e.USR$GS_CUSTOMER AS cust, CASE WHEN e.entrydate < :db THEN 0 ELSE EXTRACT(MONTH FROM e.entrydate) END AS m, e.recordkey, SUM(IIF(e.ACCOUNTPART = 'D', e.accountkey, 0)) AS dtacc, SUM(IIF(e.ACCOUNTPART = 'C', e.accountkey, 0)) AS ctacc, SUM(debitncu) AS dt, SUM(creditncu) AS ct FROM ac_entry e WHERE e.accountkey = 393008 AND e.entrydate <= :de AND e.companykey IN (153583337,146866007,153408611) GROUP BY 1, 2, 3 ) SELECT r.cust, r.m, r.dt, r.ct, LAG(r.dt) OVER (PARTITION BY r.cust ORDER BY r.m) AS pdt, LAG(r.ct) OVER (PARTITION BY r.cust ORDER BY r.m) AS pct, SUM(r.dt) OVER (PARTITION BY r.cust ORDER BY r.m) AS rdt, SUM(r.ct) OVER (PARTITION BY r.cust ORDER BY r.m) AS rct --SUM(IIF(r.m > 0, r.dt, 0)) OVER (PARTITION BY r.cust ORDER BY r.m) AS tdt, --SUM(IIF(r.m > 0, r.ct, 0)) OVER (PARTITION BY r.cust ORDER BY r.m) AS tct FROM (SELECT ir.cust, ir.m, SUM(ir.dt) AS dt, SUM(ir.ct) AS ct FROM raw_data ir WHERE ir.dtacc <> ir.ctacc GROUP BY 1, 2) r ) d LEFT JOIN gd_contact c ON c.id = d.cust WHERE d.m > 0 ORDER BY c.name, d.m
Запрос с window функциями выполняется в 1.5 раза быстрее чем исходный EXECUTE BLOCK.
Если же исключить проверку на внутренние проводки, разница в скорости выполнения возрастает до 4-х раз.