|
Выбор промежуточных значений запросом
#39368996
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
|
|
leprechaunеще осталось сообразить как туда также красиво nvl вставить
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with x as (
select t.*,
count(case col1 when 21 then 1 end) over() lower_bound_flag
from t
)
select col1,
col2,
col3
from x
match_recognize(
order by col3,col2,col1
all rows per match
pattern(up+)
define
up as match_number() = 1 and (first(col1) = 21 or lower_bound_flag = 0) and (nvl(prev(col1),0) != 55)
)
/
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.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
SQL> with t as (
2 select 10 col1,'A' col2,1 col3 from dual union all
3 select 13,'B',1 from dual union all
4 select 21,'B',1 from dual union all
5 select 14,'C',2 from dual union all
6 select 66,'D',2 from dual union all
7 select 55,'D',3 from dual union all
8 select 24,'F',3 from dual union all
9 select 11,'E',3 from dual
10 ),
11 x as (
12 select t.*,
13 count(case col1 when 21 then 1 end) over() lower_bound_flag
14 from t
15 )
16 select col1,
17 col2,
18 col3
19 from x
20 match_recognize(
21 order by col3,col2,col1
22 all rows per match
23 pattern(up+)
24 define
25 up as match_number() = 1 and (first(col1) = 21 or lower_bound_flag = 0) and (nvl(prev(col1),0) != 55)
26 )
27 /
COL1 C COL3
---------- - ----------
21 B 1
14 C 2
66 D 2
55 D 3
SQL> with t as (
2 select 10 col1,'A' col2,1 col3 from dual union all
3 select 13,'B',1 from dual union all
4 select 21,'B',1 from dual union all
5 select 14,'C',2 from dual union all
6 select 66,'D',2 from dual union all
7 select 99,'D',3 from dual union all
8 select 24,'F',3 from dual union all
9 select 11,'E',3 from dual
10 ),
11 x as (
12 select t.*,
13 count(case col1 when 21 then 1 end) over() lower_bound_flag
14 from t
15 )
16 select col1,
17 col2,
18 col3
19 from x
20 match_recognize(
21 order by col3,col2,col1
22 all rows per match
23 pattern(up+)
24 define
25 up as match_number() = 1 and (first(col1) = 21 or lower_bound_flag = 0) and (nvl(prev(col1),0) != 55)
26 )
27 /
COL1 C COL3
---------- - ----------
21 B 1
14 C 2
66 D 2
99 D 3
11 E 3
24 F 3
6 rows selected.
SQL> with t as (
2 select 10 col1,'A' col2,1 col3 from dual union all
3 select 13,'B',1 from dual union all
4 select 77,'B',1 from dual union all
5 select 14,'C',2 from dual union all
6 select 66,'D',2 from dual union all
7 select 55,'D',3 from dual union all
8 select 24,'F',3 from dual union all
9 select 11,'E',3 from dual
10 ),
11 x as (
12 select t.*,
13 count(case col1 when 21 then 1 end) over() lower_bound_flag
14 from t
15 )
16 select col1,
17 col2,
18 col3
19 from x
20 match_recognize(
21 order by col3,col2,col1
22 all rows per match
23 pattern(up+)
24 define
25 up as match_number() = 1 and (first(col1) = 21 or lower_bound_flag = 0) and (nvl(prev(col1),0) != 55)
26 )
27 /
COL1 C COL3
---------- - ----------
10 A 1
13 B 1
77 B 1
14 C 2
66 D 2
55 D 3
6 rows selected.
SQL> with t as (
2 select 10 col1,'A' col2,1 col3 from dual union all
3 select 13,'B',1 from dual union all
4 select 77,'B',1 from dual union all
5 select 14,'C',2 from dual union all
6 select 66,'D',2 from dual union all
7 select 99,'D',3 from dual union all
8 select 24,'F',3 from dual union all
9 select 11,'E',3 from dual
10 ),
11 x as (
12 select t.*,
13 count(case col1 when 21 then 1 end) over() lower_bound_flag
14 from t
15 )
16 select col1,
17 col2,
18 col3
19 from x
20 match_recognize(
21 order by col3,col2,col1
22 all rows per match
23 pattern(up+)
24 define
25 up as match_number() = 1 and (first(col1) = 21 or lower_bound_flag = 0) and (nvl(prev(col1),0) != 55)
26 )
27 /
COL1 C COL3
---------- - ----------
10 A 1
13 B 1
77 B 1
14 C 2
66 D 2
99 D 3
11 E 3
24 F 3
8 rows selected.
SQL>
SY. |
|
|
