|
Можно ли упростить запрос (first_value, только для агрегации)
#40025436
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
|
|
Кстати:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
create table t as
with
t (id, dt, txt, z) as
(
select 1, date'2020-12-01', 'name1_1',0 from dual union all
select 1, date'2020-12-02', 'name1_2',0 from dual union all
select 1, date'2020-12-03', 'name1_3',1 from dual union all
select 2, date'2020-12-01', 'name2_1',0 from dual union all
select 2, date'2020-12-02', 'name2_2',1 from dual
)
select * from t
/
Через fetch first:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select t.*,
1 / z
from t
order by row_number() over(partition by id order by dt desc)
fetch first 1 rows with ties
/
1 / z
*
ERROR at line 2:
ORA-01476: divisor is equal to zero
SQL>
Без fetch first:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with x as (
select t.*,
1 / z zz,
row_number() over(partition by id order by dt desc) rn
from t
)
select *
from x
where rn <= 1
/
ID DT TXT Z ZZ RN
---------- --------- ------- ---------- ---------- ----------
1 03-DEC-20 name1_3 1 1 1
2 02-DEC-20 name2_2 1 1 1
SQL>
Почему:
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.
SQL> explain plan for
2 select t.*,
3 1 / z
4 from t
5 order by row_number() over(partition by id order by dt desc)
6 fetch first 1 rows with ties
7 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2991172602
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 415 | 5 (40)| 00:00:01 |
|* 1 | VIEW | | 5 | 415 | 5 (40)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 5 | 350 | 5 (40)| 00:00:01 |
| 3 | VIEW | | 5 | 350 | 4 (25)| 00:00:01 |
| 4 | WINDOW SORT | | 5 | 105 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T | 5 | 105 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_003"."rowlimit_$$_rank"<=1)
2 - filter(RANK() OVER ( ORDER BY
"from$_subquery$_002"."rowlimit_$_0")<=1)
19 rows selected.
SQL> explain plan for
2 with x as (
3 select t.*,
4 1 / z zz,
5 row_number() over(partition by id order by dt desc) rn
6 from t
7 )
8 select *
9 from x
10 where rn <= 1
11 /
Explained.
SQL> select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3047187157
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 350 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 5 | 350 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 5 | 105 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 5 | 105 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ID" ORDER BY
INTERNAL_FUNCTION("DT") DESC )<=1)
17 rows selected.
SQL>
Так-что доверяй но проверяй.
SY. |
|
|
