Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос с lag, nullif, row_number() / 25 сообщений из 25, страница 1 из 1
04.01.2021, 13:00
    #40033374
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
Нужно создать запрос : нужно вернуть на каждый state(1,0,2) по одной записи на дату date_from ,
работая со строками в таком порядке, что 1 это статус подключен , 2 это блокировка , 0 это отключение, следовательно перед 0 было 1, перед 1 было 2 , т е смотреть на предыдущую дату, сравнивая их , id это Id человека
должны быть lag. nullif. row_number() и case
Допустим на 01.08.2020 дата последнего Подключения - 01.08.2020, Отключения - 08.04.2020, Блокировки - 27.07.2020
Есть данные
DATE_FROM DATE_UNTIL STATE ID
23.03.2020 07.04.2020 1 2
08.04.2020 08.04.2020 0 2
10.04.2020 31.05.2020 1 2
01.06.2020 11.07.2020 1 2
12.07.2020 26.07.2020 1 2
27.07.2020 29.07.2020 2 2
30.07.2020 31.07.2020 2 2
01.08.2020 15.09.2020 1 2
16.09.2020 31.07.2020 1 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 13:46
    #40033379
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
del
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 13:58
    #40033380
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 14:11
    #40033381
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369,

для 2 почему не 30.07.2020 ?

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 14:12
    #40033382
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
Stax,

всё верно, перепутала
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 14:31
    #40033386
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369,
Код: plsql
1.
2.
3.
4.
5.
6.
select max(case
             when state = 0 then
              date_from
           end)
from   t)
where  date_from <= to_date('01.08.2020', 'dd.mm.yyyy')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 14:37
    #40033387
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

нужно именно с использованием функций, которые перечислены
lag nullif row_number
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:00
    #40033391
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369,

Код: plsql
1.
nullif(lag(STATE)


может все же 27.07.2020 ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:22
    #40033394
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

мне главное запрос, чтобы полный был , а видимо да , будет 27 число
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:37
    #40033395
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369следовательно перед 0 было 1, перед 1 было 2 , т е смотреть на предыдущую дату, сравнивая их ,
Допустим на 01.08.2020 дата последнего Подключения (1) - 01.08.2020 , Отключения (0)- 08.04.2020 , Блокировки (2)- 27.07.2020

давайте уже как написано в лабе, а не то что вы придумали
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:39
    #40033396
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

там так и написано, запрос должен вернуть 3 даты по каждому state и на каждое Id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:44
    #40033397
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369,
опять гадаем.
автор27.07.2020 29.07.2020 2 2
30.07.2020 31.07.2020 2 2
27 или 30 ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 15:46
    #40033398
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

27 число , так дальше все время было 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:14
    #40033403
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369
123йй,

нужно именно с использованием функций, которые перечислены
lag nullif row_number

после блокировки может быть подключение без отключки?
30.07.2020 31.07.2020 2 2
01.08.2020 15.09.2020 1 2

ps
все ф-ции сразу без явного бреда ...

мож надо нескольео решений c разными ф-циями




.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:17
    #40033404
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
Stax,

получается человек может как блокировать, так и подключать или вовсе отключать

значит с функцией lag только

типо так
дата дата дата state
5/01/2020 null null 0
null 14/10/2020 null 1
null null 20/02/2020 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:28
    #40033406
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369
123йй,

там так и написано, запрос должен вернуть 3 даты по каждому state и на каждое Id


Но не сказано при LAG, NULLIF, ROWNUM. Тут самый банальный пивот:

Код: 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.
with t as (
           select to_date('23.03.2020','dd.mm.yyyy') date_from,to_date('07.04.2020','dd.mm.yyyy') date_until,1 state,2 id from dual union all
           select to_date('08.04.2020','dd.mm.yyyy'),to_date('08.04.2020','dd.mm.yyyy'),0,2 from dual union all
           select to_date('10.04.2020','dd.mm.yyyy'),to_date('31.05.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('01.06.2020','dd.mm.yyyy'),to_date('11.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('12.07.2020','dd.mm.yyyy'),to_date('26.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('27.07.2020','dd.mm.yyyy'),to_date('29.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('30.07.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('01.08.2020','dd.mm.yyyy'),to_date('15.09.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('16.09.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),1,2 from dual
          ),
     x as (
           select  date_from,
                   state,
                   id
             from  t
             where date_from <= date '2020-08-01'
          )
select  *
  from  x
  pivot(
        max(date_from)
        for state in (1 connected_on,2 disconnected_on,0 blocked_on)
       )
/

        ID CONNECTED_ON DISCONNECTED_ON BLOCKED_ON
---------- ------------ --------------- ----------
         2 01-AUG-20    30-JUL-20       08-APR-20

SQL>



Или:

Код: 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.
with t as (
           select to_date('23.03.2020','dd.mm.yyyy') date_from,to_date('07.04.2020','dd.mm.yyyy') date_until,1 state,2 id from dual union all
           select to_date('08.04.2020','dd.mm.yyyy'),to_date('08.04.2020','dd.mm.yyyy'),0,2 from dual union all
           select to_date('10.04.2020','dd.mm.yyyy'),to_date('31.05.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('01.06.2020','dd.mm.yyyy'),to_date('11.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('12.07.2020','dd.mm.yyyy'),to_date('26.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('27.07.2020','dd.mm.yyyy'),to_date('29.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('30.07.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('01.08.2020','dd.mm.yyyy'),to_date('15.09.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('16.09.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),1,2 from dual
          )
select  id,
        max(case state when 1 then date_from end) connected_on,
        max(case state when 2 then date_from end) disconnected_on,
        max(case state when 0 then date_from end) blocked_on
  from  t
  where date_from <= date '2020-08-01'
  group by id
/

        ID CONNECTED_ON DISCONNECTED_ON BLOCKED_ON
---------- ------------ --------------- ----------
         2 01-AUG-20    30-JUL-20       08-APR-20

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:34
    #40033407
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
Перепутал. Блокировка - 2:

Код: 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.
with t as (
           select to_date('23.03.2020','dd.mm.yyyy') date_from,to_date('07.04.2020','dd.mm.yyyy') date_until,1 state,2 id from dual union all
           select to_date('08.04.2020','dd.mm.yyyy'),to_date('08.04.2020','dd.mm.yyyy'),0,2 from dual union all
           select to_date('10.04.2020','dd.mm.yyyy'),to_date('31.05.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('01.06.2020','dd.mm.yyyy'),to_date('11.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('12.07.2020','dd.mm.yyyy'),to_date('26.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('27.07.2020','dd.mm.yyyy'),to_date('29.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('30.07.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('01.08.2020','dd.mm.yyyy'),to_date('15.09.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('16.09.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),1,2 from dual
          )
select  id,
        max(case state when 1 then date_from end) connected_on,
        max(case state when 0 then date_from end) disconnected_on,
        max(case state when 2 then date_from end) blocked_on
  from  t
  where date_from <= date '2020-08-01'
  group by id
/

        ID CONNECTED_ON DISCONNECTED_ON BLOCKED_ON
---------- ------------ --------------- ----------
         2 01-AUG-20    08-APR-20       30-JUL-20

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:38
    #40033408
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
SY,

нужно обязательно с lag потому что нужно брать предыдущее значение даты для каждого state когда оно было последним и еще даты как-то сравнивать ( как написано )
типо так
дата дата дата state
5/01/2020 null null 0
null 14/10/2020 null 1
null null 20/02/2020 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:42
    #40033409
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369
Stax,

получается человек может как блокировать, так и подключать или вовсе отключать

значит с функцией lag только

типо так
дата дата дата state
5/01/2020 null null 0
null 14/10/2020 null 1
null null 20/02/2020 2


я считал что его (человека/ид) блокируют, а не он блокирует

для решения столько ф-ций не надо

я начал что-то лепить шоб все ф-ции зацепить, но получается что кто-то явно лишний

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:44
    #40033410
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369,
автордолжны быть lag. nullif. row_number() и case

Код: plsql
1.
2.
3.
select max(case when state=0 then DATE_FROM end),max(case when state=1 then DATE_FROM end) ,max(case when state=2 then DATE_FROM end)  from (
select row_number() over (partition by nu    order by DATE_FROM desc  ) rnb, t.* from (
select  nullif(STATE,lag(STATE) over (order by DATE_FROM )) nu, t.* from t where DATE_FROM<=to_date('01.08.2020', 'dd.mm.yyyy')) t) where rnb =1 and nu is not null
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:52
    #40033414
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

у меня вывело 2 одинаковые даты 01.08 01.08 и 31.07
получается надо еще state вывести и id пользователей же много
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 16:58
    #40033415
dn33369
dn33369
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
123йй,

и возможно где Partition nu нужно заменить на id , ну т е у каждого человека есть Id надо на каждого это учесть + алиасы столбцов хоть какие-то если можно)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.01.2021, 17:38
    #40033423
123йй
123йй
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369 id пользователей же много
напишите group by
авторвозможно где Partition nu нужно заменить на id ,
возможно. Вам никто не мешает проверить id, nu
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.01.2021, 16:32
    #40033656
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
dn33369

нужно обязательно с lag потому что нужно брать предыдущее значение даты для каждого state когда оно было последним и еще даты как-то сравнивать ( как написано )


нужно брать предыдущее значение даты для каждого state != нужно обязательно с 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.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
with t as (
           select to_date('23.03.2020','dd.mm.yyyy') date_from,to_date('07.04.2020','dd.mm.yyyy') date_until,1 state,2 id from dual union all
           select to_date('08.04.2020','dd.mm.yyyy'),to_date('08.04.2020','dd.mm.yyyy'),0,2 from dual union all
           select to_date('10.04.2020','dd.mm.yyyy'),to_date('31.05.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('01.06.2020','dd.mm.yyyy'),to_date('11.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('12.07.2020','dd.mm.yyyy'),to_date('26.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('27.07.2020','dd.mm.yyyy'),to_date('29.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('30.07.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('01.08.2020','dd.mm.yyyy'),to_date('15.09.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('16.09.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),1,2 from dual
          ),
     c as (
           select  max(date_from) last_connect_date,
                   id
             from  t
             where state = 1
               and date_from <= date '&dt'
             group by id
          ),
     b as (
           select  last_connect_date,
                   (
                    select  max(t.date_from)
                      from  t
                      where t.id = c.id
                        and t.state = 2
                        and t.date_from < c.last_connect_date
                   ) last_block_date,
                   id
             from  c
          )
select  id,
        last_connect_date,
        last_block_date,
        (
         select  max(t.date_from)
           from  t
           where t.id = b.id
             and t.state = 2
             and t.date_from < b.last_block_dat
        ) last_disconnect_date
  from  b
  order by id
/
Enter value for dt: 2020-08-01
old  17:                and date_from <= date '&dt'
new  17:                and date_from <= date '2020-08-01'

        ID LAST_CONNECT_DATE LAST_BLOCK_DATE LAST_DISCONNECT_DATE
---------- ----------------- --------------- --------------------
         2 01-AUG-20         30-JUL-20       27-JUL-20

SQL>



А с lag не получится ибо аналитика на аналитику в одном флаконе не работает. Можно через иерархию, recursive subquery factoring или match_recognize. Для match_recognize ищем первый в обратном порядке DATE_FROM подключение за которым следует >= 0 "не блокировка" затем блокировка затем >= 0 "не отключение" затем отключение:

Код: 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.
with t as (
           select to_date('23.03.2020','dd.mm.yyyy') date_from,to_date('07.04.2020','dd.mm.yyyy') date_until,1 state,2 id from dual union all
           select to_date('08.04.2020','dd.mm.yyyy'),to_date('08.04.2020','dd.mm.yyyy'),0,2 from dual union all
           select to_date('10.04.2020','dd.mm.yyyy'),to_date('31.05.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('01.06.2020','dd.mm.yyyy'),to_date('11.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('12.07.2020','dd.mm.yyyy'),to_date('26.07.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('27.07.2020','dd.mm.yyyy'),to_date('29.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('30.07.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),2,2 from dual union all
           select to_date('01.08.2020','dd.mm.yyyy'),to_date('15.09.2020','dd.mm.yyyy'),1,2 from dual union all
           select to_date('16.09.2020','dd.mm.yyyy'),to_date('31.07.2020','dd.mm.yyyy'),1,2 from dual
          ),
     x as (
           select  date_from,
                   state,
                   id,
                   row_number() over(partition by id order by date_from desc) rn
             from  t
             where date_from <= date '&dt'
          )
select  id,
        last_connect_date,
        last_disconnect_date,
        last_block_date
  from  x
  match_recognize(
                  partition by id
                  order by rn
                  measures
                    first(c.date_from) last_connect_date,
                    first(d.date_from) last_disconnect_date,
                    first(b.date_from) last_block_date
                  one row per match
                  pattern((c nb* b nd* d)+)
                  define c as state = 1,
                         b as state = 2,
                         d as state = 0,
                         nb as state != 2,
                         nd as state != 0
                 )
/
Enter value for dt: 2020-08-01
old  18:              where date_from <= date '&dt'
new  18:              where date_from <= date '2020-08-01'

        ID LAST_CONNECT_DATE LAST_DISCONNECT_DATE LAST_BLOCK_DATE
---------- ----------------- -------------------- ---------------
         2 08-APR-20         08-APR-20            30-JUL-20

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.01.2021, 16:51
    #40033658
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос с lag, nullif, row_number()
SY

А с lag не получится ибо аналитика на аналитику в одном флаконе не работает.
SY.

имхо
сначала надо определится с постановкой, что ищем

а там возможно и lag хватит

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос с lag, nullif, row_number() / 25 сообщений из 25, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 459ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1880548]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 66ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 57ms
get tp. blocked users:
 1ms
others: 294ms
total:  459ms
x
x
Закрыть


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