нужно обязательно с lag потому что нужно брать предыдущее значение даты для каждого state когда оно было последним и еще даты как-то сравнивать ( как написано )
нужно брать предыдущее значение даты для каждого state != нужно обязательно с lag. Можно просто в лоб:
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 "не отключение" затем отключение:
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. |
