#sql #oracle #group-by #case #having
#sql #Oracle #группировать по #случай #имея
Вопрос:
Я пытаюсь выяснить, как запросить таблицу (таблица на самом деле является результирующим набором, поэтому она будет подзапросом), сгруппировать ее по ColA=ColB (см. Ниже) и создать вычисляемое поле — все за один шаг.
Итак, если мои тестовые данные выглядят как
ColA ColB ColC
1 1 aaa 1 2 bbbb 13 cccc 2 2 dddd 3 3 eeee 3 4 ffff 3 5 gggg 3 6 хххх 4 4 iiii 5 5 jjjj 6 6 кккк 6 7 llll 6 8 мммм
Я хотел бы получить только строки, где ColA=ColB а также добавить новый столбец, который сообщает мне, повторялись ли исходные данные в ColA . Смотрите ниже.
ColA ColB кратные ColC
1 1 аааа да 2 2 dddd нет 3 3 ээээ, да 4 4 iiii нет 5 5 jjjj нет 6 6 кккк да
Кто-нибудь может мне помочь с синтаксисом? Я безрезультатно играл с группировками и подвыборками. Нужно ли мне использовать оператор case для поля multiples?
Ответ №1:
Более полезно опубликовать инструкции create table и insert вместо Desc table и выбрать * из table_name;http://tkyte.blogspot.com/2005/06/how-to-ask-questions.html
create table test_repeat(
cola number,
colb number,
colc varchar2(20)
);
insert into test_repeat values (1,1,'aaa');
insert into test_repeat values (1,2,'bbbb');
insert into test_repeat values (1,3,'cccc');
insert into test_repeat values (2,2,'dddd');
insert into test_repeat values (3,3,'eeee');
insert into test_repeat values (3,4,'ffff');
insert into test_repeat values (3,5,'gggg');
insert into test_repeat values (3,6,'hhhh');
insert into test_repeat values (4,4,'iiii');
insert into test_repeat values (5,5,'jjjj');
insert into test_repeat values (6,6,'kkkk');
insert into test_repeat values (6,7,'llll');
insert into test_repeat values (6,8,'mmmm');
commit;
1. Вы можете использовать аналитическую функцию Oracle Lead, чтобы просмотреть свой набор результатов, чтобы увидеть, совпадает ли colA для следующей строки (после упорядочивания ее ..), например..
select * from
(select colA, colb,
(case when colA = (lead(cola) over
(partition by colA order by cola, colb))
then 'Yes'
else 'No'
end) multiples,
colc
from test_repeat)
where colA = colb
/
COLA COLB MUL COLC
---------- ---------- --- --------------------
1 1 Yes aaa
2 2 No dddd
3 3 Yes eeee
4 4 No iiii
5 5 No jjjj
6 6 Yes kkkk
2. Или вы можете получить количество для каждого значения COLA и сравнить его, чтобы увидеть, есть ли дубликаты…
select a.colA, a.colb, a.colc,
(case when (select count(*) from test_repeat t where t.cola = a.colA) > 1
then 'Yes'
else 'No'
end) Repeat
from test_repeat a
where colA = colB
/
COLA COLB COLC REP
---------- ---------- -------------------- ---
1 1 aaa Yes
2 2 dddd No
3 3 eeee Yes
4 4 iiii No
5 5 jjjj No
6 6 kkkk Yes
Они оба одинаково просты, но я бы предложил подход с использованием аналитических функций, поскольку я обнаружил, что он, как правило, быстрее для всех запросов, с которыми я работал в прошлом.
Комментарии:
1. Спасибо, они оба действительно полезны. В будущем я опубликую инструкции create / insert. Кстати, я на самом деле ввел данные вручную.
Ответ №2:
SQL> select *
2 from test_repeat
3 order by cola
4 /
COLA COLB COLC
---------- ---------- --------------------
1 2 bbbb
1 1 aaa
1 3 cccc
2 2 dddd
3 4 ffff
3 3 eeee
3 5 gggg
3 6 hhhh
4 4 iiii
5 5 jjjj
6 6 kkkk
6 7 llll
6 8 mmmm
7 9 nnnn
14 rows selected.
SQL> select cola "ColA"
2 , max(decode(colb,cola,colb)) "ColB"
3 , max(decode(colb,cola,colc)) "ColC"
4 , case count(*) when 1 then 'no' else 'yes' end "multiples"
5 from test_repeat
6 group by cola
7 having cola = max(decode(colb,cola,colb))
8 order by cola
9 /
ColA ColB ColC mul
---------- ---------- -------------------- ---
1 1 aaa yes
2 2 dddd no
3 3 eeee yes
4 4 iiii no
5 5 jjjj no
6 6 kkkk yes
6 rows selected.
С точки зрения производительности этот SQL приблизительно равен первому запросу Раджеша. Таким образом, вы можете выбрать тот, с которым вам удобнее.
SQL> set autotrace on
SQL> select * from
2 (select colA, colb,
3 (case when colA = (lead(cola) over
4 (partition by colA order by cola, colb))
5 then 'Yes'
6 else 'No'
7 end) multiples,
8 colc
9 from test_repeat)
10 where colA = colb
11 /
COLA COLB MUL COLC
---------- ---------- --- --------------------
1 1 Yes aaa
2 2 No dddd
3 3 Yes eeee
4 4 No iiii
5 5 No jjjj
6 6 Yes kkkk
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1491815685
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 574 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 532 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_REPEAT | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLA"="COLB")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> /
COLA COLB MUL COLC
---------- ---------- --- --------------------
1 1 Yes aaa
2 2 No dddd
3 3 Yes eeee
4 4 No iiii
5 5 No jjjj
6 6 Yes kkkk
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1491815685
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 574 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 14 | 574 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 532 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_REPEAT | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLA"="COLB")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select cola "ColA"
2 , max(decode(colb,cola,colb)) "ColB"
3 , max(decode(colb,cola,colc)) "ColC"
4 , case count(*) when 1 then 'no' else 'yes' end "multiples"
5 from test_repeat
6 group by cola
7 having cola = max(decode(colb,cola,colb))
8 order by cola
9 /
ColA ColB ColC mul
---------- ---------- -------------------- ---
1 1 aaa yes
2 2 dddd no
3 3 eeee yes
4 4 iiii no
5 5 jjjj no
6 6 kkkk yes
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3021378319
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 14 | 532 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_REPEAT | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLA"=MAX(DECODE("COLB","COLA","COLB")))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> /
ColA ColB ColC mul
---------- ---------- -------------------- ---
1 1 aaa yes
2 2 dddd no
3 3 eeee yes
4 4 iiii no
5 5 jjjj no
6 6 kkkk yes
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3021378319
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 532 | 4 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 14 | 532 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_REPEAT | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("COLA"=MAX(DECODE("COLB","COLA","COLB")))
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
238 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> set autotrace off
С уважением,
Rob.
Ответ №3:
Я думаю, вам нужен дополнительный выбор для multiples столбца. Группировка не требуется. Напишите что-то вроде:
select ColA, ColB, ColC,
(CASE
WHEN (select b.ColA from thistable b where b.ColA = a.ColA) > 1 THEN 'yes'
ELSE 'no'
END)
from thistable a
where ColA = ColB
Комментарии:
1. -1 Это непроверенный SQL. Ключевое слово END отсутствует, и если вы добавите его, оператор все равно завершится ошибкой с «ORA-01427: однострочный подзапрос возвращает более одной строки»
Ответ №4:
Я не знал о функции lead, очень приятно. Роб и Раджеш, спасибо за ответы на данный момент, я думаю, что смог частично ответить на свой собственный вопрос, используя два подзапроса и внешнее соединение.
Я предполагаю, что мой запрос не так эффективен, как другие опубликованные, но мне интересно, могут ли люди прокомментировать этот запрос. Всегда ли это будет работать? Можно ли это изменить, чтобы сделать его более эффективным? Для кратных чисел все еще нужно ввести ‘yes’ или ‘no’, прямо сейчас он вводит значение multiple count или null вместо 0.
SELECT BB.ColA, ColB, ColC, AA.CNT
FROM
(SELECT ColA, count(*) as CNT FROM TMPY where ColA<>ColB Group by ColA) AA,
(SELECT ColA, ColB, ColC FROM TMPY WHERE COLA=COLB ) BB
WHERE
AA.COLa( )=BB.COLB order by bb.ColA;
Еще раз спасибо!