Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Аналитические функции OVER, LEAD, LAG / 15 сообщений из 15, страница 1 из 1
18.03.2021, 08:46
    #40054783
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Добрый день.

Есть таблица:

Код: plsql
1.
2.
3.
4.
5.
CREATE TABLE FCT_DAYBALANCE(
  DT DATE, 
  ID_ACCOUNT NUMBER, 
  VAL_RST_NAT NUMBER
);



Есть данные:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '18.03.2014', 1000221980318, -45516,51
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '19.03.2014', 1000221980318, -45516,51
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '20.03.2014', 1000221980318, -45516,51
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '21.03.2014', 1000221980318, -45516,51
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '22.03.2014', 1000221980318, -45516,51
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '23.03.2014', 1000221980318, -45516,51
  );
....
...
...
...и т.д.

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '06.03.2021', 1000221980318, -649242,94
  );

INSERT INTO FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT) 
VALUES 
  (
    '07.03.2021', 1000221980318, -649242,94
  );


Мне нужно определить последнюю дату просрочки, используя оконные функции OVER(подсказка, lag,lead,max).
Необходимо вывести id_account и последнюю дату просрочки.

В поле val_rst_nat есть значения как 0 - это погаил задолженность, и есть значения с +, это нет долгов,
есть значения с -, это задолженность, вот тут надо найти когда он в последний раз вышел на просрочку(дата).

Пишу так:

Код: plsql
1.
2.
3.
select * from (select id_account, lead(dt,1) over (PARTITION BY id_account ORDER BY dt) as maxdate
 from FCT_DAYBALANCE) x
 where rownum = 1;



Но дата выходит не верная.
У меня выходит:
1000221980318 19.03.2014

Но должна выйти:
1000221980318 18.08.2017

Где я не правильно делаю?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 08:50
    #40054785
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Вот сам файл.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:11
    #40054793
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Так же пробую другим способом, но где то я не могу додуматься:
Код: sql
1.
2.
3.
4.
5.
SELECT * FROM (SELECT id_account, 
       LEAD (dt,1) OVER (PARTITION BY id_account ORDER BY dt) AS next_order_date
  FROM FCT_DAYBALANCE
 WHERE val_rst_nat = (SELECT MAX(val_rst_nat) FROM FCT_DAYBALANCE)) x
 WHERE ROWNUM = 1;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:30
    #40054800
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuidВ поле val_rst_nat есть значения как 0 - это погаил задолженность, и есть значения с +, это нет долгов,
есть значения с -, это задолженность, вот тут надо найти когда он в последний раз вышел на просрочку(дата).
Код: plsql
1.
select max(date) from table where val_rst_nat<0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:39
    #40054805
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Код: plsql
1.
2.
3.
4.
5.
SELECT * FROM (SELECT id_account, 
       LEAD (dt,1) OVER (PARTITION BY id_account ORDER BY dt) AS max_date
  FROM FCT_DAYBALANCE
 WHERE val_rst_nat = (SELECT MAX(val_rst_nat) FROM FCT_DAYBALANCE WHERE val_rst_nat < 0)) x
 WHERE ROWNUM = 1;


Вернул:
1000221980318 23.01.15

Фактически должно быть:
1000221980318 18.08.17
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:47
    #40054811
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuid

Но должна выйти:
1000221980318 18.08.2017

Где я не правильно делаю?


Где 18.08.2017 в тестовых данных?

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:51
    #40054813
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
В файле create_insert_table.sql только один клиент с ID = 1000221980318,
19.05.17 он вышел по нулям (= 0), но 18.08.17 он последний раз перед выходом по нулям без долгов, задолжал - 65 000.
После 18.08.17 он много раз задолжал, но задача в том, чтобы запрос вытащил именно последний долг перед тем, как вышел по нулям.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:54
    #40054814
delphinotes
delphinotes
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuid,

ну так сформулируйте задачу максимально прозрачно.
а) надо поймать факты перехода через 0 в минус
б) выбрать последний переход

как-то так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select id_account, max(dt) 
  from (
        select *
          from (select id_account,
                       lag(val_rst_nat, 1, 0) over (partition by id_account order by dt) prev_val,
                       val_rst_nat cur_val,
                       dt 
                  from fct_daybalance
               )
         where cur_val < 0 
           and prev_val >= 0
       )
 group by id_account
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:54
    #40054815
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Если набрать запрос:

Код: plsql
1.
 select * from FCT_DAYBALANCE where dt > '18.08.2017' and val_rst_nat = 0;


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

Вот пытаюсь используя аналитические функции LEAD, LAG (как дано в задаче) вытащить эту дату и ID клиента.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:58
    #40054820
PuM256
PuM256
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
with FCT_DAYBALANCE(DT, ID_ACCOUNT, VAL_RST_NAT)
as (
    select date'2021-03-01', 3, -5 from dual union all
    select date'2021-03-02', 3, -4 from dual union all
    select date'2021-01-01', 1, 10 from dual union all
    select date'2021-01-03', 1, 5 from dual union all
    select date'2021-01-05', 1, 0 from dual union all
    select date'2021-01-08', 1, -1 from dual union all
    select date'2021-01-10', 1, -4 from dual union all
    select date'2021-01-11', 1, 8 from dual union all
    select date'2021-01-12', 1, -5 from dual union all
    select date'2021-01-13', 1, -6 from dual union all
    select date'2021-02-14', 2, 2 from dual union all    
    select date'2021-02-01', 2, 10 from dual union all
    select date'2021-02-03', 2, 5 from dual union all
    select date'2021-02-05', 2, 0 from dual union all
    select date'2021-02-08', 2, -1 from dual union all
    select date'2021-02-10', 2, -4 from dual union all
    select date'2021-02-11', 2, 8 from dual union all
    select date'2021-02-12', 2, -5 from dual union all
    select date'2021-02-13', 2, -6 from dual union all
    select date'2021-02-14', 2, 2 from dual
)
select id_account, max(debt_dt) maxdate from FCT_DAYBALANCE
match_recognize(
    partition by id_account
    order by dt
    measures last(start_of_debt.DT) as debt_dt
    pattern (start_of_debt)
    define
        start_of_debt as start_of_debt.VAL_RST_NAT < 0 and (prev(start_of_debt.VAL_RST_NAT) >= 0 or prev(start_of_debt.VAL_RST_NAT) is null)
) t
group by id_account;


Код: plaintext
1.
2.
3.
4.
ID_ACCOUNT	MAXDATE
1		12-JAN-21
2		12-FEB-21
3		01-MAR-21
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 09:59
    #40054821
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
delphinotes,
Спасибо, ваш запрос вернул последнюю дату, когда клиент вышел по нулям, т.е 19.05.2017, пытаюсь изменить на следующую дату, т.е
после 19.05.2017 сразу идет 18.08.2017:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select id_account, max(dt) 
  from (
        select *
          from (select id_account,
                       lead(val_rst_nat, 1) over (partition by id_account order by dt) prev_val,
                       val_rst_nat cur_val,
                       dt 
                  from fct_daybalance
               )
         where cur_val >= 0 
           and prev_val < 0
       )
 group by id_account


Вместо LAG использовал LEAD и поставил 1, но запрос возвращает 19.05.2017 вместо следующей даты 18.08.2017, хотя поставил 1, чтобы переключился на след.запись.
Почему?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 10:01
    #40054822
delphinotes
delphinotes
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuid,

Прошу прощения, я исправил запрос видимо после того, как вы его скопировали. Проверьте его ещё раз.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 10:04
    #40054825
delphinotes
delphinotes
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuid,

Т.е. смысл в том, чтобы при отображении текущей строки (cur_val) показать значение из предыдущей (prev_val), и если текущая в минусе, а предыдущая в плюсе - то текущая и есть факт перехода через 0.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 10:08
    #40054827
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
LiQuid,


данные представте с помощью with
и что надо получить
варианты расписать для разных ID_ACCOUNT


Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SQL> ed
Wrote file afiedt.buf

  1  with t(dt,acc,val) as (
  2  select date '2021-03-01',1000,0 from dual union all
  3  select date '2021-03-02',1000,0 from dual union all
  4  select date '2021-03-03',1000,0 from dual union all
  5  select date '2021-03-04',1000,-3 from dual union all
  6  select date '2021-03-05',1000,0 from dual union all
  7  select date '2021-03-06',1000,-2 from dual union all
  8  --
  9  select date '2021-03-01',1002,0 from dual union all
 10  select date '2021-03-02',1002,0 from dual union all
 11  select date '2021-03-03',1002,-1 from dual union all
 12  select date '2021-03-03',1002,0 from dual)
 13  select
 14    acc,min(dt) dt,min(val)  KEEP (DENSE_RANK first ORDER BY dt) val
 15  from t where val<0
 16* group by acc
SQL> /

       ACC DT                VAL
---------- ---------- ----------
      1000 04.03.2021         -3
      1002 03.03.2021         -1

SQL>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.03.2021, 10:09
    #40054829
LiQuid
LiQuid
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитические функции OVER, LEAD, LAG
delphinotes,
Огромное спасибо вам. Я новичок в аналитических функциях Oracle, очень все понятно и супер.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Аналитические функции OVER, LEAD, LAG / 15 сообщений из 15, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 519ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1880358]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 158ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 270ms
total:  519ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]