Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Добрый день, форумчане!

Прошу вашей помощи, 2 дня ломаю голову, как лучше сделать, всё сомневаюсь:
Есть такая табличка:
Date |Deal |Sum
12.12.2009 |111111 |12000
25.12.2009 |111111 |5000
12.12.2009 |122222 |10000
12.01.2010 |111111 |-10100
20.11.2009 |222221 |25000
20.12.2009 |222221 |20000
31.12.2009 |222221 |-10000
29.12.2009 |122222 |-10000
27.11.2009 |222221 |-30000

Date Дата
Customer Номер клиента
Deal Номер кредита
Currency Валюта кредита
Sum сумма, вынесенная на просрочку ("+") или выплаченная ("-")

Нужно найти
1. Общую (накопленную) сумму просроченного долга непогашенную (не выплаченную) к моменту расчета.
2. Дату начала текущей (последней) просрочки. Под датой начала просрочки, в данной задаче понимается первая дата непрерывного периода, в котором общая сумма просроченного непогашенного долга > 0.Учесть, что периодов просрочек может быть несколько.
3. Кол-во дней текущей просрочки.
6 июн 19, 14:23    [21903538]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
-2-
Member

Откуда:
Сообщений: 14650
troosheff
Прошу вашей помощи, 2 дня ломаю голову
Ты серьезно полагаешь, что кто-то на форуме поможет ее проломить?
6 июн 19, 14:32    [21903549]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
maverick2104
Member

Откуда:
Сообщений: 300
troosheff,

Уважайте людей если просите у них помощи . Дайте исходные данные таблиц в виде with table1 as (...) , table2 as (...) , и результат который Вы хотите получить .
6 июн 19, 14:39    [21903562]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
Vadim Lejnin
Member

Откуда:
Сообщений: 6685
troosheff
Добрый день, форумчане!

Прошу вашей помощи, 2 дня ломаю голову, как лучше сделать, всё сомневаюсь:
Есть такая табличка:
Date Deal Sum
12.12.2009 111111 12000
25.12.2009 111111 5000
12.12.2009 122222 10000
12.01.2010 111111 -10100
20.11.2009 222221 25000
20.12.2009 222221 20000
31.12.2009 222221 -10000
29.12.2009 122222 -10000
27.11.2009 222221 -30000


Date Дата
Customer Номер клиента
Deal Номер кредита
Currency Валюта кредита
Sum сумма, вынесенная на просрочку ("+") или выплаченная ("-")

Нужно найти
1. Общую (накопленную) сумму просроченного долга непогашенную (не выплаченную) к моменту расчета.

2. Дату начала текущей (последней) просрочки. Под датой начала просрочки, в данной задаче понимается первая дата непрерывного периода, в котором общая сумма просроченного непогашенного долга > 0. Учесть, что периодов просрочек может быть несколько.

3. Кол-во дней текущей просрочки.


используйте ТЭГ для оформления
например вот так оформляются Ваши тестовые данные:

with t ("Date" ,"Deal" ,"Sum") as (
   select to_date('12.12.2009' , 'DD.MM.YYYY'), 111111, 12000 FROM DUAL UNION ALL
   select to_date('25.12.2009' , 'DD.MM.YYYY'), 111111, 5000  FROM DUAL UNION ALL
   select to_date('12.12.2009' , 'DD.MM.YYYY'), 122222, 10000 FROM DUAL UNION ALL
   select to_date('12.01.2010' , 'DD.MM.YYYY'), 111111,-10100 FROM DUAL UNION ALL
   select to_date('20.11.2009' , 'DD.MM.YYYY'), 222221, 25000 FROM DUAL UNION ALL
   select to_date('20.12.2009' , 'DD.MM.YYYY'), 222221, 20000 FROM DUAL UNION ALL
   select to_date('31.12.2009' , 'DD.MM.YYYY'), 222221,-10000 FROM DUAL UNION ALL
   select to_date('29.12.2009' , 'DD.MM.YYYY'), 122222,-10000 FROM DUAL UNION ALL
   select to_date('27.11.2009' , 'DD.MM.YYYY'), 222221,-30000 FROM DUAL 
)
select * from t;



Ну и
Студентам, желающим помощи

приведите что Вы сделали сами, и что, именно не получается...
6 июн 19, 15:55    [21903690]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
MazoHist
Member

Откуда:
Сообщений: 99
Поминится именно это задание мне давали на входе в BTБ... Даже решение найду :)
6 июн 19, 16:06    [21903704]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Простите за всё.
Примерно что накидал, но мне не нравится, думаю над курсором:
+
with pdcl as
(select to_date('20091212','yyyymmdd') dt, 111111 deal, 12000 amount from dual
union all
select to_date('20091225','yyyymmdd') dt, 111111 deal, 5000 amount from dual
union all
select to_date('20101201','yyyymmdd') dt, 111111 deal, -10100 amount from dual
union all
select to_date('20091212','yyyymmdd') dt, 122222 deal, 10000 amount from dual
union all
select to_date('20091229','yyyymmdd') dt, 122222 deal, -10000 amount from dual
union all
select to_date('20091120','yyyymmdd') dt, 222221 deal, 25000 amount from dual
union all
select to_date('20091220','yyyymmdd') dt, 222221 deal, 20000 amount from dual
union all
select to_date('20091231','yyyymmdd') dt, 222221 deal, -10000 amount from dual
union all
select to_date('20091127','yyyymmdd') dt, 222221 deal, -30000 amount from dual
union all
select to_date('20100120','yyyymmdd') dt, 222221 deal, 10000 amount from dual)
,
a as (
select row_number()over(partition by a.deal order by a.dt) instalment
      ,nvl(lag(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.1000','dd.mm.yyyy')) prev_inst
      ,a.dt inst
      ,nvl(lead(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.4000','dd.mm.yyyy')) next_inst
      ,a.deal
      --,lag(a.amount)over(partition by a.deal order by a.dt) prev_amount
      ,a.amount
     --,lead(a.amount)over(partition by a.deal order by a.dt) next_amount
from pdcl a
WHERE amount > 0
order by deal, dt
)
, b as
(
SELECT instalment
      ,a.prev_inst
      ,a.inst
      ,a.next_inst
      ,a.deal
      ,a.amount
      --,sum(case when b.amount>0 then  b.amount end) prev_amt
      --,sum(case when b.amount<0 then  b.amount end) prev_pay
      ,sum(case when b.amount<0 then  b.amount end) + sum(case when b.amount>0 then  b.amount end) ostatok
FROM a
     left join pdcl b
          on b.deal=a.deal
         and b.dt<a.inst
group by instalment
      ,a.prev_inst
      ,a.inst
      ,a.next_inst
      ,a.deal
      ,a.amount
order by a.deal, a.inst)
 
SELECT instalment
      ,b.prev_inst
      ,b.inst
      ,b.next_inst
      ,b.deal
      ,b.amount
      ,ostatok
      ,sum(case when c.amount>0 then c.amount end) fut_amt
      ,sum(case when c.amount<0
                 and c.dt<b.next_inst
                then  c.amount end) pay_1
      ,sum(case when c.amount<0
                 and c.dt>b.next_inst
                then  c.amount end) pay_2
      --,sum(case when c.amount<0 then  c.amount end) + sum(case when c.amount>0 then  c.amount end) ostatok
      --,c.*
      ,case when ostatok is not null then sum(case when c.amount<0 and c.dt<b.next_inst then  c.amount end) + ostatok + b.amount end m
      ,nvl(sum(case when c.amount<0
                 --and c.dt<b.next_inst
                then  c.amount end),0)
       + nvl(ostatok,0)
       + b.amount
       + nvl(sum(case when c.amount>0 then c.amount end),0) n
FROM b
     left join pdcl c
          on c.deal=b.deal
         and c.dt>b.inst
         --
group by instalment
      ,b.prev_inst
      ,b.inst
      ,b.next_inst
      ,b.deal
      ,b.amount
      ,ostatok
order by b.deal, b.inst desc;
6 июн 19, 16:06    [21903705]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Прошу проверить, написал курсор, но чувствую, что коряво, может я как-то не таким путем рассуждаю:
declare
n integer:=1;
i integer:=1;
vdeal integer:=1;
vcnt integer;
vdt date;
--gdt date;
--gdeal integer;
--gamount integer;
vgt RDM_CTL.TMP_TOK_20190606_inst%rowtype;
cursor get_inst is
       SELECT *
       FROM RDM_CTL.TMP_TOK_20190606_inst
       WHERE amount > 0
       order by deal, dt;
begin
n:=n-1;
i:=0;
open get_inst;
DBMS_OUTPUT.ENABLE;
      loop
         fetch get_inst into vgt;
         if get_inst%notfound then exit;
         end if;
         exit when get_inst%notfound;
               if n=0 then vdeal := vgt.deal;
               end if;
               i:=i+1;
               if vdeal != vgt.deal
                  then vdeal := vgt.deal;
                       n := 0;
                       vdt := null;
               end if;
               SELECT
                     distinct
                     nvl(t.amount,0) + nvl(sum(case when d.dt>=t.inst and d.dt<t.next_inst then d.amount end),0)
                     + n, t.cnt into n, vcnt
               FROM (SELECT row_number()over(partition by a.deal order by a.dt) instalment
                           ,nvl(lag(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.1000','dd.mm.yyyy')) prev_inst
                           ,a.dt inst
                           ,nvl(lead(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.4000','dd.mm.yyyy')) next_inst
                           ,deal
                           ,amount
                           ,count(deal)over(partition by deal) cnt
                     FROM RDM_CTL.TMP_TOK_20190606_inst a
                     WHERE a.amount > 0
                    ) t
                          left join RDM_CTL.TMP_TOK_20190606_inst d
                               on t.deal = d.deal
                              and d.amount < 0
                     WHERE t.deal = vgt.deal
                      and inst = vgt.dt
                     group by t.deal, t.inst,t.amount, t.cnt;
               if n>0 and vdt is null then vdt := vgt.dt;
               end if;
               if vcnt = i then
                  i:=0;
                  if n>0 then
                     DBMS_OUTPUT.PUT_LINE(vdeal||'|'||n||'|'||vdt||'|'||vcnt);
                  end if;
               end if;
      end loop;
close get_inst;
 
end;
6 июн 19, 19:41    [21903977]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Может, я усложняю, а всё проще гораздо и можно простым селектом?
6 июн 19, 19:42    [21903978]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
+
автор
declare
n integer:=1;
i integer:=1;
vdeal integer:=1;
vcnt integer;
vdt date;
--gdt date;
--gdeal integer;
--gamount integer;
vgt RDM_CTL.TMP_TOK_20190606_inst%rowtype;
cursor get_inst is
SELECT *
FROM RDM_CTL.TMP_TOK_20190606_inst
WHERE amount > 0
order by deal, dt;
begin
n:=n-1;
i:=0;
open get_inst;
DBMS_OUTPUT.ENABLE;
loop
fetch get_inst into vgt;
if get_inst%notfound then exit;
end if;
exit when get_inst%notfound;
if n=0 then vdeal := vgt.deal;
end if;
i:=i+1;
if vdeal != vgt.deal
then vdeal := vgt.deal;
n := 0;
vdt := null;
end if;
SELECT
distinct
nvl(t.amount,0) + nvl(sum(case when d.dt>=t.inst and d.dt<t.next_inst then d.amount end),0)
+ n, t.cnt into n, vcnt
FROM (SELECT row_number()over(partition by a.deal order by a.dt) instalment
,nvl(lag(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.1000','dd.mm.yyyy')) prev_inst
,a.dt inst
,nvl(lead(a.dt)over(partition by a.deal order by a.dt),to_date('01.01.4000','dd.mm.yyyy')) next_inst
,deal
,amount
,count(deal)over(partition by deal) cnt
FROM RDM_CTL.TMP_TOK_20190606_inst a
WHERE a.amount > 0
) t
left join RDM_CTL.TMP_TOK_20190606_inst d
on t.deal = d.deal
and d.amount < 0
WHERE t.deal = vgt.deal
and inst = vgt.dt
group by t.deal, t.inst,t.amount, t.cnt;
if n>0 and vdt is null then vdt := vgt.dt;
end if;
if vcnt = i then
i:=0;
if n>0 then
DBMS_OUTPUT.PUT_LINE(vdeal||'|'||n||'|'||vdt||'|'||vcnt);
end if;
end if;
end loop;
close get_inst;

end;


Забыл дописать:
create table RDM_CTL.TMP_TOK_20190606_inst as
with pdcl as
(select to_date('20091212','yyyymmdd') dt, 111111 deal, 12000 amount from dual
union all
select to_date('20091225','yyyymmdd') dt, 111111 deal, 5000 amount from dual
union all
select to_date('20101201','yyyymmdd') dt, 111111 deal, -10100 amount from dual
union all
select to_date('20091212','yyyymmdd') dt, 122222 deal, 10000 amount from dual
union all
select to_date('20091229','yyyymmdd') dt, 122222 deal, -10000 amount from dual
union all
select to_date('20091120','yyyymmdd') dt, 222221 deal, 25000 amount from dual
union all
select to_date('20091220','yyyymmdd') dt, 222221 deal, 20000 amount from dual
union all
select to_date('20091231','yyyymmdd') dt, 222221 deal, -10000 amount from dual
union all
select to_date('20091127','yyyymmdd') dt, 222221 deal, -30000 amount from dual
union all
select to_date('20100120','yyyymmdd') dt, 222221 deal, 10000 amount from dual)
SELECT *
FROM pdcl
;
6 июн 19, 19:45    [21903980]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
MazoHist
Member

Откуда:
Сообщений: 99
troosheff,

Можно, да. Мой вариант был без курсора.
PS: а второе задание есть?
6 июн 19, 21:35    [21904035]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Через селект как смог:
with pdcl as
(select to_date('20091212','yyyymmdd') dt, 111111 deal, 12000 amount from dual
union all
select to_date('20091225','yyyymmdd') dt, 111111 deal, 5000 amount from dual
union all
select to_date('20101201','yyyymmdd') dt, 111111 deal, -10100 amount from dual
union all
select to_date('20091212','yyyymmdd') dt, 122222 deal, 10000 amount from dual
union all
select to_date('20091229','yyyymmdd') dt, 122222 deal, -10000 amount from dual
union all
select to_date('20091120','yyyymmdd') dt, 222221 deal, 25000 amount from dual
union all
select to_date('20091220','yyyymmdd') dt, 222221 deal, 20000 amount from dual
union all
select to_date('20091231','yyyymmdd') dt, 222221 deal, -10000 amount from dual
union all
select to_date('20091127','yyyymmdd') dt, 222221 deal, -30000 amount from dual)
,
t as (
select nvl(LEAD(A.DT)OVER(partition by a.deal ORDER BY a.DT),to_date('01.01.4000','dd.mm.yyyy')) next_dt
      ,a.dt, a.deal, a.amount
      ,nvl(SUM(CASE WHEN PR.AMOUNT > 0 THEN PR.AMOUNT END),0) PREV_INST
      ,nvl(SUM(CASE WHEN PR.AMOUNT < 0 THEN PR.AMOUNT END),0) PREV_PAY
      ,nvl(SUM(CASE WHEN PR.AMOUNT > 0 THEN PR.AMOUNT END),0) + nvl(SUM(CASE WHEN PR.AMOUNT < 0 THEN PR.AMOUNT END),0) ostatok
from pdcl a
     LEFT JOIN pdcl PR
          ON PR.dt< A.DT
         AND PR.DEAL=A.DEAL    
WHERE a.amount > 0
GROUP BY a.dt, a.deal, a.amount
order by A.deal, A.dt
),
pd as (
SELECT t.dt
      ,t.next_dt
      ,t.deal
      --,t.amount
      ,case when ostatok < 0 then t.amount+ostatok else t.amount end amount
      --,PREV_INST
      --,PREV_PAY
      ,nvl(SUM(CASE WHEN NT.AMOUNT < 0 and NT.dt between t.dt and t.next_dt-1 THEN NT.AMOUNT END),0) cur_PAY
      ,nvl(SUM(CASE WHEN NT.AMOUNT > 0 THEN NT.AMOUNT END),0) next_INST
      ,nvl(SUM(CASE WHEN NT.AMOUNT < 0 and NT.dt >= t.next_dt THEN NT.AMOUNT END),0) next_PAY
      --,ostatok
      --,case when ostatok < 0 then  t.amount+ostatok end
FROM t
     LEFT JOIN pdcl NT
          ON NT.dt> t.DT
         AND NT.DEAL=t.DEAL
group by t.dt
      ,t.next_dt
      ,t.deal
      ,t.amount
      ,PREV_INST
      ,PREV_PAY
      ,ostatok)
      
SELECT --DT, NEXT_DT
        DEAL
        --, AMOUNT, CUR_PAY
--      ,AMOUNT + CUR_PAY--, pd.*
--      ,next_INST + next_PAY
--      ,next_INST
--      ,next_PAY
      ,AMOUNT + CUR_PAY+next_INST + next_PAY
      ,max(dt)keep(dense_rank first order by dt)over(partition by deal) pd_dt
FROM pd
WHERE AMOUNT + CUR_PAY > 0
order by DEAL, dt;

Если кто-нибудь проверит или предложит лучше решение буду благодарен)
6 июн 19, 23:03    [21904082]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
MazoHist,

У меня второе на vba)
А так вроде правильное решение?
6 июн 19, 23:04    [21904083]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
MazoHist
Member

Откуда:
Сообщений: 99
У меня как-то покомпактнее получилось
+

/*
with pdcl as
(select to_date ('12.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 111111 deal, 'RUR'  currency, 12000 "SUM" from dual union all
select to_date ('25.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 111111 deal, 'RUR'  currency, 5000 "SUM" from dual union all
select to_date ('12.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, 10000 "SUM" from dual union all
select to_date ('12.01.2010', 'dd.mm.yyyy') "DATE", 111110 customer, 111111 deal, 'RUR'  currency, -10100 "SUM" from dual union all
select to_date ('20.11.2009', 'dd.mm.yyyy') "DATE", 220000 customer, 222221 deal, 'RUR'  currency, 25000 "SUM" from dual union all
select to_date ('20.12.2009', 'dd.mm.yyyy') "DATE", 220000 customer, 222221 deal, 'RUR'  currency, 20000 "SUM" from dual union all
select to_date ('31.12.2009', 'dd.mm.yyyy') "DATE", 220001 customer, 222221 deal, 'RUR'  currency, -10000 "SUM" from dual union all
select to_date ('29.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, -10000 "SUM" from dual union all
select to_date ('27.11.2009', 'dd.mm.yyyy') "DATE", 220001 customer, 222221 deal, 'RUR'  currency, -30000 "SUM" from dual union all 
select to_date ('31.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, 10000 "SUM" from dual union all
select to_date ('31.12.2010', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, -9000 "SUM" from dual 
)
*/
select deal
     , max(over_sum) keep (dense_rank first order by "DATE" desc) liability_sum
     , min(case when exclude_sign is null then "DATE" end) liability_date
  from (select "DATE"
             , deal
             , over_sum
             , lead(nullif(sign(over_sum),1) ignore nulls, 1, nullif(sign(over_sum),1)) over (partition by deal order by "DATE") exclude_sign
          from ( select "DATE"
                      , deal
                      , sum("SUM") over(partition by deal order by "DATE") over_sum
                   from pdcl
               )
       )
group by deal

7 июн 19, 08:44    [21904181]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
Dshedoo
Member

Откуда:
Сообщений: 322
Минут за 10 накидал
with q as
(select to_date ('12.12.2009', 'dd.mm.yyyy') "DDATE", 111110 customer, 111111 deal, 'RUR'  currency, 12000 "SSUM" from dual union all
select to_date ('25.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 111111 deal, 'RUR'  currency, 5000 "SUM" from dual union all
select to_date ('12.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, 10000 "SUM" from dual union all
select to_date ('12.01.2010', 'dd.mm.yyyy') "DATE", 111110 customer, 111111 deal, 'RUR'  currency, -10100 "SUM" from dual union all
select to_date ('20.11.2009', 'dd.mm.yyyy') "DATE", 220000 customer, 222221 deal, 'RUR'  currency, 25000 "SUM" from dual union all
select to_date ('20.12.2009', 'dd.mm.yyyy') "DATE", 220000 customer, 222221 deal, 'RUR'  currency, 20000 "SUM" from dual union all
select to_date ('31.12.2009', 'dd.mm.yyyy') "DATE", 220001 customer, 222221 deal, 'RUR'  currency, -10000 "SUM" from dual union all
select to_date ('29.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, -10000 "SUM" from dual union all
select to_date ('27.11.2009', 'dd.mm.yyyy') "DATE", 220001 customer, 222221 deal, 'RUR'  currency, -30000 "SUM" from dual union all 
select to_date ('31.12.2009', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, 10000 "SUM" from dual union all
select to_date ('31.12.2010', 'dd.mm.yyyy') "DATE", 111110 customer, 122222 deal, 'RUR'  currency, -9000 "SUM" from dual 
)

, w as (
select q1.*, sum(ssum) over (partition by deal order by ddate) as z from q q1)


select distinct first_value(ddate) over (partition by deal order by ddate) as "Дата начала текущей просрочки"
, deal
, last_value(z) over (partition by deal order by null) "Сумма просрочки"
, sysdate-last_value(ddate) over (partition by deal order by null) as "Кол-во дней просрочки"
 from w w1 
where not exists (select 1 from w w2 where w1.deal = w2.deal and w2.z <= 0 and w1.ddate <= w2.ddate)
7 июн 19, 09:20    [21904214]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
troosheff
Member

Откуда:
Сообщений: 8
Dshedoo, MazoHist,
Спасибо вам большое!
Отчего же я такой кривой!
7 июн 19, 15:14    [21904621]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
troosheff
Прошу проверить, написал курсор, но чувствую, что коряво, может я как-то не таким путем рассуждаю:

Аццкая жесть, ты когда нибудь программировал на процедурных языках?

Один проход по исходному отсортированному набору данных без дополнительных условий (курсор) с помощью for,
все остальное элементарно считается с сохранением промежуточных значений в переменных,
никаких запросов внутри цикла в данном случае не требуется.

Вывод с помощью dbms_output - для отладки, в твоем случае неплохо было бы сделать pipelined - функцию.


Запросом, с декомпозицией и если я правильно понял условия задачи:
+
with t ("Date" ,"Deal" ,"Sum") as (
   select to_date('12.12.2009' , 'DD.MM.YYYY'), 111111, 12000 FROM DUAL UNION ALL
   select to_date('25.12.2009' , 'DD.MM.YYYY'), 111111, 5000  FROM DUAL UNION ALL
   select to_date('12.12.2009' , 'DD.MM.YYYY'), 122222, 10000 FROM DUAL UNION ALL
   select to_date('12.01.2010' , 'DD.MM.YYYY'), 111111,-10100 FROM DUAL UNION ALL
   select to_date('20.11.2009' , 'DD.MM.YYYY'), 222221, 25000 FROM DUAL UNION ALL
   select to_date('20.12.2009' , 'DD.MM.YYYY'), 222221, 20000 FROM DUAL UNION ALL
   select to_date('31.12.2009' , 'DD.MM.YYYY'), 222221,-10000 FROM DUAL UNION ALL
   select to_date('29.12.2009' , 'DD.MM.YYYY'), 122222,-10000 FROM DUAL UNION ALL
   select to_date('27.11.2009' , 'DD.MM.YYYY'), 222221,-30000 FROM DUAL UNION ALL
   select to_date('27.11.2009' , 'DD.MM.YYYY'), 222221,-1000  FROM DUAL UNION ALL
   select to_date('27.11.2009' , 'DD.MM.YYYY'), 333331,-10000 FROM DUAL UNION ALL
   select to_date('28.11.2009' , 'DD.MM.YYYY'), 333331, 5000  FROM DUAL UNION ALL
   select to_date('29.11.2009' , 'DD.MM.YYYY'), 333331, 1000  FROM DUAL UNION ALL
   select to_date('02.12.2009' , 'DD.MM.YYYY'), 333331, 2000  FROM DUAL UNION ALL
   select to_date('01.12.2009' , 'DD.MM.YYYY'), 555551, 15000 FROM DUAL UNION ALL
   select to_date('02.12.2009' , 'DD.MM.YYYY'), 555551,-15000 FROM DUAL UNION ALL
   select to_date('03.12.2009' , 'DD.MM.YYYY'), 555551,  5000 FROM DUAL UNION ALL
   select to_date('04.12.2009' , 'DD.MM.YYYY'), 555551, -5000 FROM DUAL UNION ALL
   select to_date('05.12.2009' , 'DD.MM.YYYY'), 555551, 12000 FROM DUAL UNION ALL
   select to_date('06.12.2009' , 'DD.MM.YYYY'), 555551,  2000 FROM DUAL UNION ALL
   select to_date('07.12.2009' , 'DD.MM.YYYY'), 555551,  2000 FROM DUAL UNION ALL
   select to_date('08.12.2009' , 'DD.MM.YYYY'), 555551,-16000 FROM DUAL
), 
   a as
(
    select "Date", "Deal", "Sum"
         , trunc(sysdate) - "Date" - 3400 as per_day
         , sum("Sum") over (partition by "Deal" order by "Date") as sum_nak
      from t
),
   b as
(
    select "Date", "Deal", "Sum", sum_nak, per_day
         , case when sign(sum_nak) <= 0 then 0 else 1 end as rnk_flag1
         , case when sign(sum_nak) > 0  then 0 else 1 end as rnk_flag2
      from a
)
,
   q as
(
    select "Date", "Deal", "Sum", sum_nak, per_day, rnk_flag1, rnk_flag2
         , rnk_flag1 * (sum(rnk_flag2) over(order by "Deal", "Date") + 1) rnk
      from b
     order by "Deal", "Date"
)
select "Date", "Deal", "Sum", sum_nak, per_day, rnk_flag1, rnk_flag2, rnk
     , last_value(sum_nak) over (partition by "Deal" order by  "Date" RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "1. Долг"
     , first_value(case when rnk = 0 then null else "Date" end) 
         over (partition by "Deal" order by rnk desc, "Date" asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) "2. Дату начала текущей (последней) просрочки."
     , case when last_value(sum_nak) over (partition by "Deal" order by  "Date" RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) > 0
            then first_value(case when rnk = 0 then null else per_day end)
                 over (partition by "Deal" order by rnk desc, "Date" asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
            else null end "3. Кол-во дней текущей просрочки."
  from q
8 июн 19, 14:56    [21905142]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
vladymyr.k
Member

Откуда:
Сообщений: 7
SkilledJunior,

Добрый день. В соседней ветке идёт поиск решения аналогичной задачи, но под T-SQL. Обратите внимание, на практике важно не столько дату первого возникновения задолженности, сколько дату первого не закрытого начисления долга.

IF OBJECT_ID('t', 'U') IS NOT NULL DROP TABLE t;
IF OBJECT_ID('w', 'U') IS NOT NULL DROP TABLE w;
IF OBJECT_ID('res', 'U') IS NOT NULL DROP TABLE res;

create table dbo.t (DDATE date not null, deal nchar(6) not null, SSUM int not null);
insert t 
select '20091010', 111110,   500 union all
select '20091011', 111110,   600 union all
select '20091012', 111110,  1200 union all
select '20091017', 111110,  -1100 union all
select '20091019', 111110,  -700 union all
select '20091020', 111110, 10000 union all
select '20091022', 111110, -1000 union all
select '20091025', 111110,  1500 union all
select '20091030', 111110, 15000 union all
select '20091030', 111110, -9900            ;

Первых 3 операции формируют задолженность. Если смотреть дату возникновения задолженности по состоянию на 12 октября 2009 года, то да, считается корректно. Но вот 17-го числа заходит оплата, которая частично закрывает возникшую задолженность (полностью закрываются начисления от 10 и 11 числа), но остаётся задолженность от 12-го числа. Но задолженность всё равно считается 10-10-2009.
9 июн 19, 10:28    [21905327]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
vladymyr.k
Member

Откуда:
Сообщений: 7
vladymyr.k,

[url=]https://www.sql.ru/forum/1313467-a/raschyot-daty-vozniknoveniya-zadolzhennosti-t-sql[/url]
9 июн 19, 10:29    [21905328]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт задолженности по LIFO  [new]
SkilledJunior
Member

Откуда:
Сообщений: 303
vladymyr.k,

Если нужны какие то осмысленные промежуточные варианты, то сначала нужно определяться с алгоритмом разноски погашений на долги и тем что хотите получить в итоге.

Самый простой вариант найти дату с которой началась незакрытая задолженность, найти общую сумму погашений задолженности, далее из накопительного итога долгов (за исключением погашений задолженности) эту сумму вычитать, в нашем случае сложить поскольку погашения идут с минусом, в момент перехода в + и будет моментом начала непогашенной задолженности.

+
with t (debt_date, deal, amt) as
(
select to_date('20091010', 'YYYYMMDD'), 111110,   500 from dual union all
select to_date('20091011', 'YYYYMMDD'), 111110,   600 from dual union all
select to_date('20091012', 'YYYYMMDD'), 111110,  1200 from dual union all
select to_date('20091017', 'YYYYMMDD'), 111110, -1100 from dual union all
select to_date('20091019', 'YYYYMMDD'), 111110,  -700 from dual union all
select to_date('20091020', 'YYYYMMDD'), 111110, 10000 from dual union all
select to_date('20091022', 'YYYYMMDD'), 111110, -1000 from dual union all
select to_date('20091025', 'YYYYMMDD'), 111110,  1500 from dual union all
select to_date('20091030', 'YYYYMMDD'), 111110, 15000 from dual union all
select to_date('20091030', 'YYYYMMDD'), 111110, -9900 from dual
),
   a as
(
select debt_date, deal, amt, row_number() over (partition by deal order by debt_date, amt) rn
  from t
 where 1=1
--   and debt_date <= to_date('20091017', 'YYYYMMDD')
--   and debt_date <= to_date('20091022', 'YYYYMMDD')
),
   b as
(
select debt_date, deal, amt, rn
     , sum(case when amt<0 then amt end) over (partition by deal) sum_minus
     , sum(case when amt>0 then amt end) over (partition by deal order by rn  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) total
  from a
)
select debt_date, deal, amt, rn, sum_minus, total
     , total + sum_minus as total_sum_minus
     , sign(total + sum_minus) as sg
     , first_value(case when sign(total + sum_minus) = 1 then debt_date end)
         over (partition by deal order by sign(total + sum_minus) desc, rn asc RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as res
  from b
 order by rn
11 июн 19, 00:08    [21906268]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить