analytical functions-CodePudding

good afternoon, a question, how can I optimize the code, I don't know, maybe using oracle analytical functions :

-- tabledeuda : this table contains 2 months 202212 and 202211

SELECT B.*,
         NVL(B.DEUDAPRESTAMO_PAGPER,0)-NVL(A.DEUDAPRESTAMO_PAGPER,0) AS SALE_CT -- current month - previous month
  FROM tabledeuda B
  LEFT JOIN tabledeuda A ON (A.CODLLAVE = B.CODLLAVE 
                            AND A.CODMES = TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE(B.CODMES,'YYYYMM'),-1),'YYYYMM'))
                            AND A.financial_company = B.financial_company 
                            AND A.CODMONEY=B.CODMONEY)
  WHERE NVL(B.DEUDAPRESTAMO_PAGPER,0)>NVL(A.DEUDAPRESTAMO_PAGPER,0)
        AND B.CODMES = &CODMES; --->  &CODMES 202212

OUTPUT

CodePudding user response：

Looks like a candidate for lag analytic function.

Sample data is rather poor so it is unclear what happens when there's more data, but - that's the general idea.

Sample data:

SQL> with test (codmes, customer, deudaprestamo_pagper) as
  2    (select 202212, 'T1009', 200 from dual union all
  3     select 202211, 'T1009', 150 from dual
  4    )

Query:

  5  select codmes, customer,
  6    deudaprestamo_pagper,
  7    deudaprestamo_pagper -
  8      lag(deudaprestamo_pagper) over (partition by customer order by codmes) sale_ct
  9  from test;

    CODMES CUSTO DEUDAPRESTAMO_PAGPER    SALE_CT
---------- ----- -------------------- ----------
    202211 T1009                  150
    202212 T1009                  200         50

SQL>

If you want to fetch only the last row (sorted by codmes), you could e.g.

  6  with temp as
  7    (select codmes, customer,
  8       deudaprestamo_pagper,
  9       deudaprestamo_pagper -
 10         lag(deudaprestamo_pagper) over (partition by customer order by codmes) sale_ct,
 11       --
 12       row_number() over (partition by customer order by codmes desc) rn
 13     from test
 14    )
 15  select codmes, customer, deudaprestamo_pagper, sale_ct
 16  from temp
 17  where rn = 1;

    CODMES CUSTO DEUDAPRESTAMO_PAGPER    SALE_CT
---------- ----- -------------------- ----------
    202212 T1009                  200         50

SQL>