Просмотр исходного текста страницы Использование SQL window functions для построения журнала-ордера

[[Журнал-ордер]] строится в два этапа. Сначала с помощью EXECUTE BLOCK подготавливаются исходные данные, затем в набор данных вставляются строки с итоговыми значениям по группам, чтобы на экране мы увидели такую картину:

http://gsbelarus.com/gs/images/gs/2016/jo-01.png

Запрос для вышеприведенного журнала с группировкой по клиентам, затем по месяцам, выглядит так:

<syntaxhighlight lang="sql">
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
</syntaxhighlight>

Firebird 3 предоставляет в наше распоряжение SQL Window Functions, которые позволяют подсчитывать агрегатные значения внутри групп данных в выборке без сворачивания самих групп с помощью GROUP BY. Напишем запрос, подготавливающий данные аналогично показанному выше:

<syntaxhighlight lang="sql">
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   
</syntaxhighlight>

Запрос с window функциями выполняется в 1.5 раза быстрее чем исходный EXECUTE BLOCK.

Если же исключить проверку на внутренние проводки, разница в скорости выполнения возрастает до 4-х раз.

[[Category:Постановка]]
[[Category:База данных]]
[[Category:Firebird]]