|
Изящный select
#39943249
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Москва
Сообщения: 18 719
|
|
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.
with def_table(myName, myType, myVal, myBit) as
(select 'AAA', 1, 2, '01'
from dual union all select 'AAA', 2, 2, '01'
from dual union all select 'AAA', 5, 3, '01'
from dual union all select 'BBB', 2, 4, '02'
from dual union all select 'BBB', 3, 1, '02'
from dual
)
select myName
, max(myVal) keep(dense_rank last order by myType) myVal
, max(myBit) keep(dense_rank last order by myType) myBit
from def_table
group by myName
;
with def_table(myName, myType, myVal, myBit) as
(select 'AAA', 1, 2, '01'
from dual union all select 'AAA', 2, 2, '01'
from dual union all select 'AAA', 5, 3, '01'
from dual union all select 'BBB', 2, 4, '02'
from dual union all select 'BBB', 3, 1, '02'
from dual
)
select *
from def_table match_recognize(
partition by myName
order by myType desc
measures strt.myVal as myVal
, strt.myBit as myBit
one row per match
pattern (strt x*)
define x as myName=myName
)
;
with def_table(myName, myType, myVal, myBit) as
(select 'AAA', 1, 2, '01'
from dual union all select 'AAA', 2, 2, '01'
from dual union all select 'AAA', 5, 3, '01'
from dual union all select 'BBB', 2, 4, '02'
from dual union all select 'BBB', 3, 1, '02'
from dual
)
select myName, myVal, myBit
from def_table
model
return updated rows
partition by (myName)
dimension by (myType r)
measures(myType, myVal, myBit)
rules ( myType[0] = max(myType)[any]
, myVal[0] = myVal[myType[0]]
, myBit[0] = myBit[myType[0]]
)
;
with def_table(myName, myType, myVal, myBit) as
(select 'AAA', 1, 2, '01'
from dual union all select 'AAA', 2, 2, '01'
from dual union all select 'AAA', 5, 3, '01'
from dual union all select 'BBB', 2, 4, '02'
from dual union all select 'BBB', 3, 1, '02'
from dual
)
select myName, myVal, myBit from(
select t.*
, dense_rank() over(partition by myName order by myType desc) x
from def_table t
) where x = 1
;
|
|
|
