|
Группировка(склеивание) значений одной группы
#39344434
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
|
|
ElicА по ссылкам походить?
Прошелся. C LAG(NULL - буковок меньше, но вот эффективность хужe:
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.
SQL> explain plan for with t as (
2 select 1005667402 c1,21100 c2,1225 c3,21100 c4,300 c5 from dual union all
3 select 1005667401,21100,1225,21100,925 from dual
4 )
5 select lag(to_number(null),1,c1) over(partition by c1 order by c1,c2,c3,c4,c5) c1,
6 lag(to_number(null),1,c2) over(partition by c2 order by c1,c3,c4,c5) c2,
7 lag(to_number(null),1,c3) over(partition by c3 order by c1,c2,c4,c5) c3,
8 lag(to_number(null),1,c4) over(partition by c4 order by c1,c2,c3,c5) c4,
9 lag(to_number(null),1,c5) over(partition by c5 order by c1,c2,c3,c4) c5
10 from t
11 order by c1 nulls last,
12 c2 nulls last,
13 c3 nulls last,
14 c4 nulls last,
15 c5 nulls last
16 /
Explained.
SQL> set linesize 132
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3764458696
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 10 (60)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 34 | 10 (60)| 00:00:01 |
| 2 | WINDOW SORT | | 2 | 34 | 10 (60)| 00:00:01 |
| 3 | WINDOW SORT | | 2 | 34 | 10 (60)| 00:00:01 |
| 4 | WINDOW SORT | | 2 | 34 | 10 (60)| 00:00:01 |
| 5 | WINDOW SORT | | 2 | 34 | 10 (60)| 00:00:01 |
| 6 | WINDOW SORT | | 2 | 34 | 10 (60)| 00:00:01 |
| 7 | VIEW | | 2 | 34 | 4 (0)| 00:00:01 |
| 8 | UNION-ALL | | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 10 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
17 rows selected.
SQL> explain plan for
2 with t as (
3 select 1005667402 c1,21100 c2,1225 c3,21100 c4,300 c5 from dual union all
4 select 1005667401,21100,1225,21100,925 from dual
5 )
6 select case
7 when lag(c1) over(order by c1,c2,c3,c4,c5) = c1 then null
8 else c1
9 end c1,
10 case
11 when lag(c2) over(order by c1,c2,c3,c4,c5) = c2 then null
12 else c2
13 end c2,
14 case
15 when lag(c3) over(order by c1,c2,c3,c4,c5) = c3 then null
16 else c3
17 end c3,
18 case
19 when lag(c4) over(order by c1,c2,c3,c4,c5) = c4 then null
20 else c4
21 end c4,
22 case
23 when lag(c5) over(order by c1,c2,c3,c4,c5) = c5 then null
24 else c5
25 end c5
26 from t
27 order by c1 nulls last,
28 c2 nulls last,
29 c3 nulls last,
30 c4 nulls last,
31 c5 nulls last
32 /
Explained.
SQL> set linesize 132
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4281479882
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 34 | 6 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 | 34 | 6 (34)| 00:00:01 |
| 2 | WINDOW SORT | | 2 | 34 | 6 (34)| 00:00:01 |
| 3 | VIEW | | 2 | 34 | 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------
13 rows selected.
SQL>
SY. |
|
|
