Использование GROUP BY

dbstalker, 23 мая

Что это такое и как с ним бороться?

В этой статье затронуты такие понятия:

Для Oracle V9.0-V10g конструкция выглядит так:


      ----------rollup_cube_clause ----¬
GROUP BY ---- expr  ---------------- HAVING condition
      L--------grouping_sets_clause ----      

В самом простом случае конструкция Group By используется для вычисления определенного группового значения, основанного на столбцах, перечисленных в этом операторе. Проще говоря, выборка разбивается на группы. Группы состоят из записей отличающихся друг от друга значениями в столбцах перечисленных в конструкции group by. Например, таблицу разбиваем на группы (секции) group by city,name

drop table DBST_TABL;
CREATE TABLE DBST_TABL
(
  CITY    VARCHAR2(20 BYTE),
  NAME    VARCHAR2(30 BYTE),
  PERIOD  number(6),
  GOODS   VARCHAR2(6 BYTE)
);
truncate table dbst_tabl;
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200601', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200605', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200607', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200609', 't08');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't02');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200605', 't03');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200612', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n1', '200603', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n2', '200601', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n2', '200601', 't04');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't03');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't04');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't01');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't05');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200603', 't06');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200604', 't00');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c1', 'n4', '200604', 't02');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200612', 't19');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200611', 't10');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200609', 't11');
Insert into dbst_tabl (CITY, NAME, PERIOD, GOODS) Values ('c2', 'n2', '200610', 't20');
commit;
CITY|NAME|PERIOD|GOODS
c1|n1|200601|t00
c1|n1|200605|t00
c1|n1|200607|t01
c1|n1|200609|t08
c1|n1|200603|t01
c1|n1|200603|t02
c1|n1|200605|t03
c1|n1|200612|t05
c1|n1|200603|t05
c1|n2|200601|t00
c1|n2|200601|t04
c1|n4|200603|t03
c1|n4|200603|t04
c1|n4|200603|t01
c1|n4|200603|t05
c1|n4|200603|t06
c1|n4|200604|t00
c1|n4|200604|t02
c2|n2|200612|t19
c2|n2|200611|t10
c2|n2|200609|t11
c2|n2|200610|t20
Разные группы отмечены разным шрифтом. К группам можно применять групповые операции. Например, подсчитаем количество записей в каждой группе:
select city,name,count(*) from dbst_tabl group by city,name

CITY|NAME|COUNT(*)
c1|n4|7
c1|n2|2
c1|n1|9
c2|n2|4
Теперь сделаем другие секции:
group by city,name,period

CITY|NAME|PERIOD|GOODS
c1|n1|200601|t00
c1|n1|200605|t00
c1|n1|200607|t01
c1|n1|200609|t08
c1|n1|200603|t01
c1|n1|200603|t02
c1|n1|200605|t03
c1|n1|200612|t05
c1|n1|200603|t05
c1|n2|200601|t00
c1|n2|200601|t04
c1|n4|200603|t03
c1|n4|200603|t04
c1|n4|200603|t01
c1|n4|200603|t05
c1|n4|200603|t06
c1|n4|200604|t00
c1|n4|200604|t02
c2|n2|200612|t19
c2|n2|200611|t10
c2|n2|200609|t11
c2|n2|200610|t20
Очевидно, что теперь на запрос
select city,name,period,count(*) from dbst_tabl group by city,name,period
получим совсем другой результат
CITY|NAME|PERIOD|COUNT(*)
c1|n1|200601|1
c2|n2|200609|1
c2|n2|200611|1
c1|n4|200603|5
c1|n4|200604|2
c1|n1|200607|1
c2|n2|200612|1
c2|n2|200610|1
c1|n2|200601|2
c1|n1|200605|2
c1|n1|200609|1
c1|n1|200603|3
c1|n1|200612|1

Давайте немного усложним запрос.

В конструкцию Group By можно добавить опцию Rollup. Эта опция приводит к созданию промежуточных итогов по столбцу назначения. Промежуточные итоги вычисляются от самого низкого уровня до общей суммы. То есть, включая ROLLUP во фразе GROUP BY, мы указываем Oracle, чтобы он просуммировал данные по уровнях указанных столбцов и подвел общий итог.

Когда Oracle сообщает общий итог, то остается незаполненной строка в столбце, по которому строилась фраза GROUP BY. Если столбец GROUP BY также содержит пустые значения, то может быть трудно отличить его значения от итога по строке.

select city,name,period,count(*) from dbst_tabl group by rollup (city,name,period)
Результат
CITY|NAME|PERIOD|COUNT(*)
c1|n1|200601|1
c1|n1|200603|3
c1|n1|200605|2
c1|n1|200607|1
c1|n1|200609|1
c1|n1|200612|1
c1|n1|null|9
c1|n2|200601|2
c1|n2|null|2
c1|n4|200603|5
c1|n4|200604|2
c1|n4|null|7
c1|null|null|18
c2|n2|200609|1
c2|n2|200610|1
c2|n2|200611|1
c2|n2|200612|1
c2|n2|null|4
c2|null|null|4
null|null|null|22

Для того, чтобы однозначно определить промежуточные итоги (могут же быть записи с пустыми значения в столбцах) рекомендую использовать функцию, называемую GROUPING, которая сообщает о статусе (summarization) текущего уровня. Функция возвращает два значения: "0" указывает, что текущая строка является группой, специфицированной уровнем GROUP BY(не агрегат), а "1" указывает, что строка сгруппирована на более высоком уровне (агрегат).

select decode(grouping(city),1,'itogo city',city) city,
decode(grouping(name),1,'itogo name',name) name ,
decode(grouping(period),1,'itogo period',period) period,
count(*) from dbst_tabl group by rollup (city,name,period)
результат
CITY|NAME|PERIOD|COUNT(*)
c1|n1|200601|1
c1|n1|200603|3
c1|n1|200605|2
c1|n1|200607|1
c1|n1|200609|1
c1|n1|200612|1
c1|n1|itogo period|9
c1|n2|200601|2
c1|n2|itogo period|2
c1|n4|200603|5
c1|n4|200604|2
c1|n4|itogo period|7
c1|itogo name|itogo period|18
c2|n2|200609|1
c2|n2|200610|1
c2|n2|200611|1
c2|n2|200612|1
c2|n2|itogo period|4
c2|itogo name|itogo period|4
itogo city|itogo name|itogo period|22

Уже немножко краше стало. Но есть еще функция GROUPING_ID , которая полезна, когда работаем с агрегатами (итоговыми записями). То есть, функцию GROUPING_ID используют для определения, какое агрегирование (группирование) представляет каждая строка. Функция GROUPING_ID возвращает 0 или 1, когда вызывается для одного столбца. (В этом случае она работает так же, как и функция GROUPING.) Она возвращает 0, указывая на значение детальной записи указанного столбца, когда он не агрегировался (не был свернут). Если же функция возвращает 1, то указанный столбец был агрегирован.

Функция GROUPING_ID отличается от функции GROUPING тем, что можно указывать список столбцов и функция GROUPING_ID будет рассматривать его как битовый вектор, возвращая десятичное число. Это означает, что когда вы вызываете GROUPING_ID(a,b,c), то может быть возращено число в диапазоне от 0 до 7, поскольку возвращаются различные комбинации 0 и 1. Это позволяет использовать в запросе оператор CASE, чтобы определить, чем является строка.

abcGrouping_id
0000
0011
0102
0113
1004
1015
1106
1117

Посмотрим это на примере:

select decode(grouping(city),1,'itogo city',city) city,
decode(grouping(name),1,'itogo name',name) name ,
decode(grouping(period),1,'itogo period',period) period,
count(*),
case when grouping_id(city,name,period) = 0
                then '' --'Не агрегированная'
               when grouping_id(city,name,period) = 1
               then 'Агрегат по period'
               when grouping_id(city,name,period) = 2
               then 'Агрегат по name'
               when grouping_id(city,name,period) = 3
               then 'Агрегат по name,period'
			   when grouping_id(city,name,period) = 4
               then 'Агрегат по city'
			   when grouping_id(city,name,period) = 5
               then 'Агрегат по city,period'
			   when grouping_id(city,name,period) = 6
               then 'Агрегат по city,name'
			   when grouping_id(city,name,period) = 7
               then 'Агрегат по city,name,period'
			   end what
 from dbst_tabl group by rollup (city,name,period)
CITY|NAME|PERIOD|COUNT(*)|WHAT
c1|n1|200601|1|
c1|n1|200603|3|
c1|n1|200605|2|
c1|n1|200607|1|
c1|n1|200609|1|
c1|n1|200612|1|
c1|n1|itogo period|9|Агрегат по period
c1|n2|200601|2|
c1|n2|itogo period|2|Агрегат по period
c1|n4|200603|5|
c1|n4|200604|2|
c1|n4|itogo period|7|Агрегат по period
c1|itogo name|itogo period|18|Агрегат по name,period
c2|n2|200609|1|
c2|n2|200610|1|
c2|n2|200611|1|
c2|n2|200612|1|
c2|n2|itogo period|4|Агрегат по period
c2|itogo name|itogo period|4|Агрегат по name,period
itogo city|itogo name|itogo period|22|Агрегат по city,name,period

И только теперь, по-моему, все становиться наглядным. Еще боле интересно будет при использовании конструкции cube.

select decode(grouping(city),1,'itogo city',city) city,
decode(grouping(name),1,'itogo name',name) name ,
decode(grouping(period),1,'itogo period',period) period,
count(*),
case when grouping_id(city,name,period) = 0
                then '' --'Не агрегированная'
               when grouping_id(city,name,period) = 1
               then 'Агрегат по period'
               when grouping_id(city,name,period) = 2
               then 'Агрегат по name'
               when grouping_id(city,name,period) = 3
               then 'Агрегат по name,period'
			   when grouping_id(city,name,period) = 4
               then 'Агрегат по city'
			   when grouping_id(city,name,period) = 5
               then 'Агрегат по city,period'
			   when grouping_id(city,name,period) = 6
               then 'Агрегат по city,name'
			   when grouping_id(city,name,period) = 7
               then 'Агрегат по city,name,period'
			   end what
 from dbst_tabl group by cube (city,name,period)
CITY|NAME|PERIOD|COUNT(*)|WHAT
itogo city|itogo name|itogo period|22|Агрегат по city,name,period
itogo city|itogo name|200601|3|Агрегат по city,name
itogo city|itogo name|200603|8|Агрегат по city,name
itogo city|itogo name|200604|2|Агрегат по city,name
itogo city|itogo name|200605|2|Агрегат по city,name
itogo city|itogo name|200607|1|Агрегат по city,name
itogo city|itogo name|200609|2|Агрегат по city,name
itogo city|itogo name|200610|1|Агрегат по city,name
itogo city|itogo name|200611|1|Агрегат по city,name
itogo city|itogo name|200612|2|Агрегат по city,name
itogo city|n1|itogo period|9|Агрегат по city,period
itogo city|n1|200601|1|Агрегат по city
itogo city|n1|200603|3|Агрегат по city
itogo city|n1|200605|2|Агрегат по city
itogo city|n1|200607|1|Агрегат по city
itogo city|n1|200609|1|Агрегат по city
itogo city|n1|200612|1|Агрегат по city
itogo city|n2|itogo period|6|Агрегат по city,period
itogo city|n2|200601|2|Агрегат по city
itogo city|n2|200609|1|Агрегат по city
itogo city|n2|200610|1|Агрегат по city
itogo city|n2|200611|1|Агрегат по city
itogo city|n2|200612|1|Агрегат по city
itogo city|n4|itogo period|7|Агрегат по city,period
itogo city|n4|200603|5|Агрегат по city
itogo city|n4|200604|2|Агрегат по city
c1|itogo name|itogo period|18|Агрегат по name,period
c1|itogo name|200601|3|Агрегат по name
c1|itogo name|200603|8|Агрегат по name
c1|itogo name|200604|2|Агрегат по name
c1|itogo name|200605|2|Агрегат по name
c1|itogo name|200607|1|Агрегат по name
c1|itogo name|200609|1|Агрегат по name
c1|itogo name|200612|1|Агрегат по name
c1|n1|itogo period|9|Агрегат по period
c1|n1|200601|1|
c1|n1|200603|3|
c1|n1|200605|2|
c1|n1|200607|1|
c1|n1|200609|1|
c1|n1|200612|1|
c1|n2|itogo period|2|Агрегат по period
c1|n2|200601|2|
c1|n4|itogo period|7|Агрегат по period
c1|n4|200603|5|
c1|n4|200604|2|
c2|itogo name|itogo period|4|Агрегат по name,period
c2|itogo name|200609|1|Агрегат по name
c2|itogo name|200610|1|Агрегат по name
c2|itogo name|200611|1|Агрегат по name
c2|itogo name|200612|1|Агрегат по name
c2|n2|itogo period|4|Агрегат по period
c2|n2|200609|1|
c2|n2|200610|1|
c2|n2|200611|1|
c2|n2|200612|1|

На этом примере становится очевиднее разница между функциями grouping и grouping_id.

Немного про CUBE.

Оператор CUBE формирует результирующий набор, представляющий собой многомерный куб. То есть, результирующий набор содержит все возможные комбинации значений указанных столбцов вместе со значениями статистических вычислений соответствующих строк, которые совпадают с комбинацией значений указанных столбцов.

Если запрос с CUBE возвращает больше значений, чем вам нужно, то лишнее можно спрятать в представление или вложенный запрос.

Различия между CUBE и ROLLUP

CUBE создает результирующий набор, содержащий статистические выражения для всех комбинаций значений заданных столбцов. ROLLUP создает результирующий набор, содержащий статистические выражения иерархии значений в заданных столбцах.

GROUPING SETS

Если вам нужны только некоторые из агрегатов полученных с помощью CUBE, лучше использовать запрос с предложением GROUPING SETS, тогда можно избежать ненужных вычислений.

Функция GROUPING_ID не позволяет избежать вызова многочисленных функций группирования, а предложение GROUPING SETS позволяет. Тем не менее, функция GROUPING_ID играет важную роль, показывая, что представляют данные.

select decode(grouping(city),1,'itogo city',city) city,
decode(grouping(name),1,'itogo name',name) name ,
decode(grouping(period),1,'itogo period',period) period,
count(*),
case when grouping_id(city,name,period) = 0
                then '' --'Не агрегированная'
               when grouping_id(city,name,period) = 1
               then 'Агрегат по period'
               when grouping_id(city,name,period) = 2
               then 'Агрегат по name'
               when grouping_id(city,name,period) = 3
               then 'Агрегат по name,period'
			   when grouping_id(city,name,period) = 4
               then 'Агрегат по city'
			   when grouping_id(city,name,period) = 5
               then 'Агрегат по city,period'
			   when grouping_id(city,name,period) = 6
               then 'Агрегат по city,name'
			   when grouping_id(city,name,period) = 7
               then 'Агрегат по city,name,period'
			   end what
 from dbst_tabl group by GROUPING SETS  (city,name,period)
CITY|NAME|PERIOD|COUNT(*)|WHAT
itogo city|itogo name|200610|1|Агрегат по city,name
itogo city|itogo name|200609|2|Агрегат по city,name
itogo city|itogo name|200604|2|Агрегат по city,name
itogo city|itogo name|200612|2|Агрегат по city,name
itogo city|itogo name|200601|3|Агрегат по city,name
itogo city|itogo name|200603|8|Агрегат по city,name
itogo city|itogo name|200611|1|Агрегат по city,name
itogo city|itogo name|200607|1|Агрегат по city,name
itogo city|itogo name|200605|2|Агрегат по city,name
itogo city|n4|itogo period|7|Агрегат по city,period
itogo city|n1|itogo period|9|Агрегат по city,period
itogo city|n2|itogo period|6|Агрегат по city,period
c1|itogo name|itogo period|18|Агрегат по name,period
c2|itogo name|itogo period|4|Агрегат по name,period

Конструкция HAVING

Для применения условий к группам, созданным фразой GROUP BY, используется фраза HAVING, которая играет такую же роль для групп, что и фраза WHERE для записей.

Фраза HAVING применяется только совместно с фразой GROUP. Она оставляет в результирующей таблице только те группы, для которых условие является истинным. Синтаксис фразы HAVING:

HAVING <aggregating_op_result> <condition> <value>;

Каждое выражение, фигурирующее в условии, должно быть столбцом, выбираемым для группы или агрегатной функцией

Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.

Запрос может содержать и фразу WHERE, и фразу HAVING. В этом случае первой выполняется фраза WHERE, поскольку она выполняется до разбиения на группы.

2 комментария

Прокоментировать

Юлия
29 октября 2010 г. в 23:33

Отличная статья, спасибо!

Василий
7 апреля 2011 г. в 05:50

SQL Сборник рецептов отличная книга Энтони Молинаро

 

Новый комментарий

Я не спамер: введите суму 3+3



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Спутниковое тв
21 января, 3 ответа