ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Аналитические функции OVER, LEAD, LAG
15 сообщений из 15, страница 1 из 1
Аналитические функции OVER, LEAD, LAG
    #40054783
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
Добрый день.

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

Код: 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:46
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054785
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
Вот сам файл.
...
Рейтинг: 0 / 0
18.03.2021, 08:50
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054793
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
Так же пробую другим способом, но где то я не могу додуматься:
Код: 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:11
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054800
123йй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
123йй
Участник
LiQuidВ поле val_rst_nat есть значения как 0 - это погаил задолженность, и есть значения с +, это нет долгов,
есть значения с -, это задолженность, вот тут надо найти когда он в последний раз вышел на просрочку(дата).
Код: plsql
1.
select max(date) from table where val_rst_nat<0
...
Рейтинг: 0 / 0
18.03.2021, 09:30
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054805
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
Код: 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:39
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054811
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
LiQuid

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

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


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

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

Код: 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:54
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054820
PuM256
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PuM256
Гость
Код: 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:58
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054821
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
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, 09:59
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054822
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
LiQuid,

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

Т.е. смысл в том, чтобы при отображении текущей строки (cur_val) показать значение из предыдущей (prev_val), и если текущая в минусе, а предыдущая в плюсе - то текущая и есть факт перехода через 0.
...
Рейтинг: 0 / 0
18.03.2021, 10:04
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054827
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
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:08
| Ответить | Цитировать | Написать  
Аналитические функции OVER, LEAD, LAG
    #40054829
LiQuid
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiQuid
Участник
delphinotes,
Огромное спасибо вам. Я новичок в аналитических функциях Oracle, очень все понятно и супер.
...
Рейтинг: 0 / 0
18.03.2021, 10:09
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Аналитические функции OVER, LEAD, LAG
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40054811&tid=1880358]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 135ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 263ms
total:  492ms
созд. / загр.: 492ms
x
x
Закрыть


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