|
помогите с простым запросом условия для группы
#39619993
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
|
|
Этот тип задач решается ч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.
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.
156.
157.
SQL> WITH CTE(FIO,ZAP) AS
2 (
3 SELECT 'A',10 FROM DUAL UNION ALL
4 SELECT 'A',10 FROM DUAL UNION ALL
5 SELECT 'A',5 FROM DUAL UNION ALL
6 SELECT 'B',10 FROM DUAL UNION ALL
7 SELECT 'B',10 FROM DUAL UNION ALL
8 SELECT 'C',5 FROM DUAL UNION ALL
9 SELECT 'C',5 FROM DUAL
10 )
11 SELECT DISTINCT C.FIO
12 FROM CTE C
13 WHERE NOT EXISTS
14 (
15 SELECT 1 FROM CTE C2 WHERE C2.FIO=C.FIO AND C2.ZAP=5
16 )
17 /
F
-
B
SQL> select *
2 from table(
3 dbms_xplan.display_cursor(
4 format=>'allstats last'
5 )
6 )
7 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5cxag9r99wn67, child number 0
-------------------------------------
WITH CTE(FIO,ZAP) AS ( SELECT 'A',10 FROM DUAL UNION ALL SELECT
'A',10 FROM DUAL UNION ALL SELECT 'A',5 FROM DUAL UNION ALL SELECT
'B',10 FROM DUAL UNION ALL SELECT 'B',10 FROM DUAL UNION ALL SELECT
'C',5 FROM DUAL UNION ALL SELECT 'C',5 FROM DUAL ) SELECT DISTINCT
C.FIO FROM CTE C WHERE NOT EXISTS ( SELECT 1 FROM CTE C2 WHERE
C2.FIO=C.FIO AND C2.ZAP=5 )
Plan hash value: 2778598225
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6647_9CB02E5B | | 1024 | 1024 | |
| 3 | UNION-ALL | | | | | |
| 4 | FAST DUAL | | 1 | | | |
| 5 | FAST DUAL | | 1 | | | |
| 6 | FAST DUAL | | 1 | | | |
| 7 | FAST DUAL | | 1 | | | |
| 8 | FAST DUAL | | 1 | | | |
| 9 | FAST DUAL | | 1 | | | |
| 10 | FAST DUAL | | 1 | | | |
| 11 | HASH UNIQUE | | 1 | 2618K| 2618K| 721K (0)|
|* 12 | HASH JOIN ANTI | | 7 | 2715K| 2715K| 913K (0)|
| 13 | VIEW | | 7 | | | |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_9CB02E5B | 7 | | | |
|* 15 | VIEW | | 7 | | | |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_9CB02E5B | 7 | | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
12 - access("C2"."FIO"="C"."FIO")
15 - filter("C2"."ZAP"=5)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
45 rows selected.
SQL> WITH CTE(FIO,ZAP) AS
2 (
3 SELECT 'A',10 FROM DUAL UNION ALL
4 SELECT 'A',10 FROM DUAL UNION ALL
5 SELECT 'A',5 FROM DUAL UNION ALL
6 SELECT 'B',10 FROM DUAL UNION ALL
7 SELECT 'B',10 FROM DUAL UNION ALL
8 SELECT 'C',5 FROM DUAL UNION ALL
9 SELECT 'C',5 FROM DUAL
10 ),
11 t as
12 (
13 select fio,
14 row_number() over(partition by fio order by 1) rn,
15 count(case zap when 5 then 1 end) over(partition by fio) flag from cte
16 )
17 select fio from t where rn = 1 and flag = 0
18 /
F
-
B
SQL> select *
2 from table(
3 dbms_xplan.display_cursor(
4 format=>'allstats last'
5 )
6 )
7 /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 94y57x8g5psb9, child number 0
-------------------------------------
WITH CTE(FIO,ZAP) AS ( SELECT 'A',10 FROM DUAL UNION ALL SELECT
'A',10 FROM DUAL UNION ALL SELECT 'A',5 FROM DUAL UNION ALL SELECT
'B',10 FROM DUAL UNION ALL SELECT 'B',10 FROM DUAL UNION ALL SELECT
'C',5 FROM DUAL UNION ALL SELECT 'C',5 FROM DUAL ), t as ( select
fio, row_number() over(partition by fio order by 1) rn,
count(case zap when 5 then 1 end) over(partition by fio) flag from
cte ) select fio from t where rn = 1 and flag = 0
Plan hash value: 2979383283
---------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | VIEW | | 7 | | | |
| 2 | WINDOW SORT | | 7 | 2048 | 2048 | 2048 (0)|
| 3 | VIEW | | 7 | | | |
| 4 | UNION-ALL | | | | | |
| 5 | FAST DUAL | | 1 | | | |
| 6 | FAST DUAL | | 1 | | | |
| 7 | FAST DUAL | | 1 | | | |
| 8 | FAST DUAL | | 1 | | | |
| 9 | FAST DUAL | | 1 | | | |
| 10 | FAST DUAL | | 1 | | | |
| 11 | FAST DUAL | | 1 | | | |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("RN"=1 AND "FLAG"=0))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
40 rows selected.
SQL>
SY. |
|
|
