Студопедия

КАТЕГОРИИ:



Мы поможем в написании ваших работ!

Архитектура-(3434)Астрономия-(809)Биология-(7483)Биотехнологии-(1457)Военное дело-(14632)Высокие технологии-(1363)География-(913)Геология-(1438)Государство-(451)Демография-(1065)Дом-(47672)Журналистика и СМИ-(912)Изобретательство-(14524)Иностранные языки-(4268)Информатика-(17799)Искусство-(1338)История-(13644)Компьютеры-(11121)Косметика-(55)Кулинария-(373)Культура-(8427)Лингвистика-(374)Литература-(1642)Маркетинг-(23702)Математика-(16968)Машиностроение-(1700)Медицина-(12668)Менеджмент-(24684)Механика-(15423)Науковедение-(506)Образование-(11852)Охрана труда-(3308)Педагогика-(5571)Полиграфия-(1312)Политика-(7869)Право-(5454)Приборостроение-(1369)Программирование-(2801)Производство-(97182)Промышленность-(8706)Психология-(18388)Религия-(3217)Связь-(10668)Сельское хозяйство-(299)Социология-(6455)Спорт-(42831)Строительство-(4793)Торговля-(5050)Транспорт-(2929)Туризм-(1568)Физика-(3942)Философия-(17015)Финансы-(26596)Химия-(22929)Экология-(12095)Экономика-(9961)Электроника-(8441)Электротехника-(4623)Энергетика-(12629)Юриспруденция-(1492)Ядерная техника-(1748)

Мы поможем в написании ваших работ!

Агрегатная функция GROUPING





 

Обсудим теперь один более тонкий вопрос. Если вы помните (см. Лекцию 12), определение столбцов DEPT_NO и EMP_BDATE таблицы EMP допускает появление в этих столбцах неопределенных значений. Поэтому тело таблицы EMP могло бы иметь, например, следующий вид:

 

EMP

EMP_NO DEPT_NO EMP_BDATE EMP_SAL
15000.00
16000.00
14000.00
19000.00
NULL 15000.00
NULL 17000.00
17000.00
16000.00
14000.00
20000.00
18000.00
13000.00
21000.00
22000.00
NULL 13000.00
NULL 14000.00
NULL NULL 19000.00

 

Тогда результат запроса из примера 16.1 будет иметь следующий вид*:

 

DEPT_NO EMP_BDATE MAX_SAL
NULL NULL 22000.00
NULL NULL 19000.00
NULL NULL 14000.00
NULL 19000.00
NULL 20000.00
NULL 22000.00
NULL 17000.00
16000.00
19000.00
17000.00
20000.00
18000.00
22000.00
NULL 14000.00

 

Рис. 16.2. Результат запроса с разделом GROUP BY ROLLUP к таблице с неопределенными значениями столбцов группировки

 

Очевидно, что просматривая строки таблицы, показанной на рис. 16.2, невозможно установить, в какой из первой трех строк неопределенное значение столбцов DEPT_NO и EMP_BDATE означает, что эта строка является сводной для всего предприятия, а не то, что она является сводной для всех сотрудников с неизвестными номером отдела и годом рождения или просто для всех сотрудников с неизвестным номером отдела. Аналогичным образом, невозможно понять, какая строка в следующей далее паре строк является сводной для всех сотрудников отдела номер 1, а не сводной для всех сотрудников отдела номер 1 с неизвестным годом рождения.



 

Для того чтобы можно было всегда разобраться в результатах запросов, включающих раздел GROUP BY ROLLUP, в язык SQL была введена специальная агрегатная функция GROUPING. Эта функция применяется к столбцу, входящему в список столбцов раздела GROUP BY ROLLUP, и принимает целое значение 1 в тех строках результирующей таблицы, в которых соответствующий столбец имеет значение NULL по той причине, что строка является сводной для более обобщенной группы. В противном случае функция GROUPING принимает значение 0.

 

Уточним формулировку запроса из примера 16.1 (пример 16.1a):

 

SELECT DEPT_NO, EMP_BDATE, MAX(EMP_SAL) AS MAX_SAL,
GROUPING (DEPT_NO) AS GDN, GROUPING (EMP_BDATE) AS GEB
FROM EMP
GROUP BY ROLLUP (DEPT_NO, EMP_BDATE);

 

Результирующая таблица для этого запроса будет иметь следующий вид:

 

DEPT_NO EMP_BDATE MAX_SAL GDN GEB
NULL NULL 22000.00
NULL NULL 19000.00
NULL NULL 14000.00
NULL 19000.00
NULL 20000.00
NULL 22000.00
NULL 17000.00
16000.00
19000.00
17000.00
20000.00
18000.00
22000.00
NULL 14000.00

 

Рис. 16.3. Результат запроса с разделом GROUP BY ROLLUP и вызовами агрегатной функции GROUPING к таблице с неопределенными значениями столбцов группировки

 

Анализируя значения столбцов GDN и GEB в строках таблицы, показанной на рис. 16.4, можно убедиться, что значение столбца MAX_SAL в первой является максимальным значением зарплаты всех служащих предприятия, во второй строке – максимальным значением зарплаты служащих с неизвестными номером отдела и годом рождения, а в третьей строке –максимальным значением зарплаты всех служащих с неизвестным номером отдела. В следующих трех строках значения столбца MAX_SAL являются максимальными значениями зарплаты сотрудников с неизвестным годом рождения из отделов с номерами 1, 2 и 3 соответственно. Как видно, значения столбцов GDN и GEB являются своего рода индикаторами, указывающими на природу основных значений строки.

 

Раздел GROUP BY CUBE

 

Наконец, заметим, что, в отличие от запросов с традиционной группировкой, результат запроса, содержащего раздел GROUP BY ROLLUP, зависит от порядка столбцов в списке группировки. При выполнении запроса происходит движение по этому списку слева направо с повышением уровня детальности результирующих данных. Существует еще одна разновидность запроса с группировкой, основанная на использовании раздела GROUP BY CUBE.

 

Пусть раздел группировки запроса имеет вид GROUP BY CUBE (cname1, cname2,…, cnamen), где cnamei (i = 1, 2,…, n) – имя столбца таблицы-результата раздела FROM запроса. Обозначим через SGBC множество {cname1, cname2,…, cnamen}. Пусть Si является произвольным подмножеством SGBC, т.е. Si является пустым множеством или имеет вид {cnamei1, cnamei2,…, cnameim}, где m £ n, и каждое имя столбца cnameij совпадает с одним и только одним именем столбца из списка столбцов раздела GROUP BY CUBE. Очевидно, что у множества SGBC существует 2n подмножеств различных вида Si. Тогда по определению результат этого запроса совпадает с объединением результатов 2n запросов с теми же разделами SELECT, FROM и WHERE, что и у запроса с GROUP BY CUBE, и с разделом группировки вида GROUP BY Si, причем во всех строках результата частичного запроса значением любого столбца cnamej такого, что cnamej Î SGBC и cnamej Ï Si, является NULL. Запрос с разделом группировки вида GROUP BY S, где S – пустое множество, трактуется как запрос без раздела GROUP BY. Вот пример запроса, содержащего раздел GROUP BY CUBE.



 

Пример 16.2. Найти максимальный размер зарплаты во всем предприятии, максимальный размер зарплаты в каждом отделе, максимальный размер зарплаты сотрудников в каждой возрастной категории и максимальный размер зарплаты сотрудников каждой возрастной категории каждого отдела.

 

SELECT DEPT_NO, EMP_BDATE, MAX (EMP_SAL)AS MAX_SAL,
GROUPING (DEPT_NO) AS GDN, GROUPING (EMP_BDATE) AS GEB
FROM EMP
GROUP BY CUBE (DEPT_NO, EMP_BDATE);

 

Результирующая таблица для этого запроса будет иметь следующий вид:

 

DEPT_NO EMP_BDATE MAX_SAL GDN GEB
NULL NULL 22000.00
NULL NULL 19000.00
NULL NULL 14000.00
NULL 19000.00
NULL 20000.00
NULL 22000.00
NULL 17000.00
16000.00
19000.00
17000.00
20000.00
18000.00
22000.00
NULL 14000.00
NULL 18000.00
NULL 22000.00

 

Рис. 16.4. Результат запроса с разделом GROUP BY CUBE и вызовами агрегатной функции GROUPING к таблице с неопределенными значениями столбцов группировки

 

Как видно, результат запроса из примера 16.2 совсем немного отличается от результата запроса из примера 16.1a. Добавились две последние строки, показывающие максимальные значения зарплаты всех сотрудников предприятия, родившихся в 1950-м и 1960-м годах соответственно.

 

Наш пример может навести на мысль, что и в общем случае запросы, содержащие раздел GROUP BY CUBE, не слишком отличаются от запросов с GROUP BY ROLLUP, и выполнение этих запросов тоже не слишком различается. Однако это совсем не так. Запрос, содержащий раздел GROUP BY CUBE, действительно вырождается в объединение результатов 2n запросов с обычным разделом GROUP BY. Соответственно, сложность выполнения такого запроса несравненно больше сложности выполнения похожего запроса с GROUP BY ROLLUP. В нашем примере все получилось так просто только по той причине, что в запросе имеются всего два столбца группировки.

 





Дата добавления: 2014-01-03; Просмотров: 378; Нарушение авторских прав?; Мы поможем в написании вашей работы!


Нам важно ваше мнение! Был ли полезен опубликованный материал? Да | Нет



ПОИСК ПО САЙТУ:


Рекомендуемые страницы:

Читайте также:
studopedia.su - Студопедия (2013 - 2021) год. Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав! Последнее добавление
Генерация страницы за: 0.005 сек.