20.2.2. Агрегатная функция grouping

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

EMP
EMP_NO	DEPT_NO	EMP_BDATE	EMP_SAL
2440	1	1950	15000.00
2441	1	1950	16000.00
2442	1	1960	14000.00
2443	1	1960	19000.00
2452	1	NULL	15000.00
2453	1	NULL	17000.00
2444	2	1950	17000.00
2445	2	1950	16000.00
2446	2	1960	14000.00
2447	2	1960	20000.00
2448	3	1950	18000.00
2449	3	1950	13000.00
2450	3	1960	21000.00
2451	3	1960	22000.00
2454	NULL	1950	13000.00
2455	NULL	1950	14000.00
2456	NULL	NULL	19000.00

Тогда результат запроса изпримера 20.1имел бы следующий вид¹⁵¹⁾:

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

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

151 Мы опять искусственным образом упорядочили результат запроса для удобства пояснений.

для всех служащих отдела номер 1, а не для всех служащих отдела номер 1 с неизвестным годом рождения.

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

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

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);

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

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

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