skibars,
Просьба. Когда получится, посмотрите, pls, разницу планов с
вот таким вариантом: 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.
with src_data(reg, nd, np, kp, stp) as (
values
(6, 129241, cast('1988-10-03' as date), cast('1997-05-31' as date), '1-1'),
(6, 129241, cast('1997-07-01' as date), cast('1998-02-08' as date), '1-1'),
(6, 129241, cast('1998-02-17' as date), cast('1998-06-03' as date), '1-1'),
(6, 129241, cast('1998-06-04' as date), cast('1998-10-01' as date), '1-1'),
(6, 129241, cast('1998-10-07' as date), cast('1999-04-27' as date), '1-1'),
(6, 129241, cast('1999-04-28' as date), cast('2003-08-31' as date), '1-1'),
(6, 129241, cast('2003-09-01' as date), cast('2003-09-21' as date), '1-1'),
(6, 129241, cast('2003-09-22' as date), cast('2003-12-31' as date), '1-1'),
(6, 129241, cast('2004-01-01' as date), cast('2004-02-01' as date), '1-1'),
(6, 129241, cast('2004-02-02' as date), cast('2010-11-19' as date), '1-1'),
(6, 129241, cast('2010-11-20' as date), cast('2011-03-31' as date), '1-1')
),
get_prev_kp(reg, nd, np, kp, stp, prev_kp) as (
select reg, nd, np, kp, stp,
LAG(kp) OVER (PARTITION BY reg, nd, stp order by np) as prev_kp -- значение kp от "предыдущей строки"
from src_data
),
gen_seq_inc(reg, nd, np, kp, stp, seq_inc) as (
select reg, nd, np, kp, stp,
case when prev_kp + 1 day = np then 0 -- та же последовательность
else 1 -- новый интервал
end as seq_inc -- increment номера последовательности
from get_prev_kp
),
gen_seq_num(reg, nd, np, kp, stp, seq_num) as (
select reg, nd, np, kp, stp,
sum(seq_inc) over(PARTITION BY reg, nd, stp
order by nd ROWS UNBOUNDED PRECEDING
) as seq_num -- номер последовательности (считаем как сумму инкрементов по всем предыдущим строкам)
from gen_seq_inc
)
select reg, nd, min(np), max(kp), stp
from gen_seq_num
group by reg, nd, stp, seq_num;
или
в новых терминах: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with get_prev_d2(sn, d1, d2, stp, prev_d2) as (
select sn, d1, d2, stp,
LAG(d2) OVER (PARTITION BY sn, stp order by d1) as prev_d2 -- значение d2 от "предыдущей строки"
from t11_dopl
),
gen_seq_inc(sn, d1, d2, stp, seq_inc) as (
select sn, d1, d2, stp,
case when prev_d2 + 1 day = d1 then 0 -- та же последовательность
else 1 -- новый интервал
end as seq_inc -- increment номера последовательности
from get_prev_d2
),
gen_seq_num(sn, d1, d2, stp, seq_num) as (
select sn, d1, d2, stp,
sum(seq_inc) over(PARTITION BY sn, stp
order by d1 ROWS UNBOUNDED PRECEDING
) as seq_num -- номер последовательности (считаем как сумму инкрементов по всем предыдущим строкам)
from gen_seq_inc
)
select sn, min(d1), max(d2), stp
from gen_seq_num
group by sn, stp, seq_num;
Очень интересно. |
