Дополнительные возможности формирования запросов

Предложение SELECT, определяющее необходимые запросы, обладает большими возможностями. Кроме возможностей, рассмотренных ранее, здесь рассматриваются следующие:

• дополнительные возможности группирования;

• использование общих табличных выражений;

• использование рекурсивных запросов.

Дополнительные возможности группирования

В списке выбора в предложении SELECT, при использовании агрегатных функций, требующих группирование, могут быть использованы разные формы группирования. Как указывалось ранее, конструкция GROUP BY в предложении SELECT имеет следующий вид:

GROUP BY элемент [, элемент …]

В качестве элемента конструкции GROUP BY, кроме выражения группирования, рассмотренного ранее, могут быть использованы еще группирующее_множество и супер_группы, которые добавляют к рассмотренным ранее правилам записи конструкции GROUP BY некоторые дополнительные возможности.

Группирующее множество

Группирующее множество позволяет указать несколько способов группирования данных в одном предложении SELECT и имеет следующий вид:

GROUPING SETS ( элемент_группирования [, … ] ),

Элемент группирования может быть представлен одним простым элементом или списком простых элементов, заключенным в круглые скобки; каждый простой элемент представляется в виде выражения группирования или супер группы.

Группирующее множество можно рассматривать как объединение двух или более групп строк в один результирующий набор данных. Использование группирующего множества позволяет вычислять необходимые группы за один просмотр базовой таблицы.

Если в конструкции GROUPING SETS указывается несколько элементов группирования, тогда в результат включается несколько групп строк. Каждая группа строк имеет одинаковые значения колонок, определяемых элементом группирования. Не агрегатные колонки из списка выбора SELECT, не включенные в элемент группирования, возвращают значение NULL для каждой строки, сгенерированной для группирующего множества. Это отражает тот факт, что агрегация данных была выполнена без учета значений этих колонок.

Рассмотрим пример. Пусть имеется следующая таблица:

INVOICE	ACode	SId	WId	Qty
	A1	S1	W1
	A1	S1	W2
	A1	S2	W1
	A1	S2	W2
	A2	S1	W1
	A2	S1	W2
	A2	S2	W1
	A2	S2	W2

Пусть необходимо сформировать отчет, в котором отражено суммарное количество поставленного товара для каждого вида товара (ACode) и для каждого поставщика (SId).

Результат может быть получен в одном запросе, если использовать группирующее множество:

SELECT ACode, SId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY GROUPING SETS (ACode, SId)

В данном запросе определены два элемента группирования, каждый из которых определяет свои группы строк. В результате выполнения запроса будут сформированы группы строк двух видов: с одинаковым значением атрибута ACode и с одинаковым значением атрибута SId, и для каждой группы будет вычислена агрегатная функция SUM(Qty). Результат отчета может иметь следующий вид:

ACode	SId	Total
-	S1
-	S2
A1	-
A2	-

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

SELECT ACode, SId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY GROUPING SETS ((ACode, Sid))

В этом случае в группирующем множестве определен только один элемент группирования, который определяет группы строк с одинаковыми значениями атрибутов ACode и SId. Результат такого запроса может иметь следующий вид:

ACode	SId	Total
A1	S1
A1	S2
A2	S1
A2	S2

Видно, что данный запрос эквивалентен следующему:

SELECT ACode, SId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY ACode, SId

Рассмотрим следующий запрос:

SELECT ACode, SId, WId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY GROUPING SETS ((ACode, SId), (ACode, WId))

В нем определены два элемента группирования. Первый элемент определяет группы строк с одинаковыми значениями атрибутов ACode и SId, второй – группы строк с одинаковыми значениями атрибутов ACode и WId. Для каждой группы вычисляется функция SUM(Qty). Результат выполнения запроса может быть следующим:

ACode	SId	WId	Total
A1	-	W1
A1	-	W2
A2	-	W1
A2	-	W2
A1	S1	-
A1	S2	-
A2	S1	-
A2	S2	-

Следует заметить, что группирующее множество определяет фундамент построения результатов при использовании конструкции GROUP BY. Например, конструкция GROUP BY Col1 эквивалентна конструкции GROUP BY GROUPING SETS((Col1)), а конструкция GROUP BY Col1, Col2, Col3 эквивалентна конструкции GROUP BY GROUPING SETS((Col1, Col2, Col3)).

Супер группа

Супер группа задается одним из следующих способов:

ROLLUP ( элемент_группирования [, …] )

CUBE ( элемент_группирования [, …] )

()

Элемент группирования может быть представлен отдельным выражением группирования или списком выражений, заключенным в круглые скобки.

Последний элемент (пустые круглые скобки) определяет общий итог, в соответствии с которым весь результат, полученный при выполнении запроса, рассматривается как одна группа строк.

Конструкция ROLLUP расширяет возможности группирования, определяемые конструкцией GROUP BY, включая в результат запроса дополнительные итоговые строки. Итоговые строки получаются в результате создания, на основе списка, указанного в ROLLUP, дополнительных группирующих множеств, путем последовательного исключения последних элементов из списка. Так, если была определена следующая конструкция: ROLLUP(a, b, c), при ее обработке будут созданы следующие группирующие множества: (a, b, c), (a, b), (a), ().

Таким образом, конструкция ROLLUP, в которой указано n элементов, трансформируется в n + 1 группирующее множество.

Следует отметить, что на результат запроса влияет порядок перечисления элементов в конструкции ROLLUP. Так, при обработке конструкции ROLLUP(a, b)будут созданы группирующие множества (a, b), (a), (), тогда как при обработке конструкции ROLLUP(b, a)будут созданы группирующие множества (b, a) (или (a, b)), (b), ().

Рассмотрим пример. Пусть для приведенной выше таблицы выполняется следующий запрос:

SELECT ACode, SId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY ROLLUP (ACode, SId)

ORDER BY ACode, SId

При выполнении данного запроса будут определены несколько групп; в первую группу включаются строки, имеющие одинаковое значение совокупности атрибутов ACode и SId, во вторую группу – строки с одинаковым значением атрибута ACode и третью группу составят все строки таблицы. Результат выполнения запроса может иметь следующий вид:

ACode	SId	Total
A1	S1
A1	S2
A1	-
A2	S1
A2	S2
A2	-
-	-

Конструкция CUBE расширяет возможности группирования, определяемые конструкцией GROUP BY, также включая в результат запроса дополнительные итоговые строки. Однако, в отличие от ROLLUP, дополнительные итоговые строки формируются путем создания всех комбинаций из элементов, указанных в списке CUBE. Так, если была определена следующая конструкция: CUBE(a, b, c), при ее обработке будут созданы следующие группирующие множества: (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), (). В соответствии с этим, порядок перечисления элементов в конструкции CUBE на результат не влияет.

Таким образом, конструкция CUBE, в которой указано n элементов, трансформируется в 2 ⁿ группирующих множеств.

Рассмотрим пример. Пусть для приведенной выше таблицы выполняется следующий запрос:

SELECT ACode, SId, SUM(Qty) AS Total

FROM INVOICE

GROUP BY CUBE (ACode, SId)

ORDER BY ACode, SId

При выполнении данного запроса будут определены несколько групп. В первую группу включаются строки, имеющие одинаковое значение совокупности атрибутов ACode и SId, во вторую группу – строки с одинаковым значением атрибута ACode, в третью группу – строки с одинаковым значением атрибута SId, и, наконец, четвертую группу составят все строки таблицы. Результат выполнения запроса может иметь следующий вид:

ACode	SId	Total
A1	S1
A1	S2
A1	-
A2	S1
A2	S2
A2	-
-	S1
-	S2
-	-

Дополнительные функции

При использовании различных способов группирования в списке SELECT могут быть использованы дополнительные функции – GROUPING и RANK.

Функция GROUPING относится к категории агрегатных функций и имеет следующий синтаксис:

GROUPING( выражение )

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

Функция возвращает целое значение. Результат функции:

1 – значение выражения в строке результата имеет NULL значение, строка была сгенерирована супер группой;

0 – в противном случае.

Например, пусть имеется следующая таблица EMPL:

EMPNO	LASTNAME	SALARY	JOB	EDLEVEL
	O’Connel	29250.00	Clerk
	Spenser	26150.00	Manager
	Quintana	23800.00	Analyst
	Schneider	26250.00	Operator
	Smith	19180.00	Clerk	-
	Stern	32250.00	Manager

Пусть выполняется следующий запрос:

SELECT EDLEVEL, DECIMAL(AVG(SALARY), 8, 2) AS AVG_Salary

FROM EMPL

GROUP BY ROLLUP(EDLEVEL)

Будут получены следующие результаты:

EDLEVEL	AVG_SALARY
-	26146.66
	27700.00
	28025.00
	26250.00
-	19180.00

В полученных результатах две строки имеют в колонке EDLEVEL пустое значение, и не понятно, какое из них соответствует строке таблицы, а какое – итоговому значению. Использование функции

GROUPING() позволяет ответить на данный вопрос:

SELECT EDLEVEL, GROUPING(EDLEVEL) AS TP,

DECIMAL(AVG(SALARY), 8, 2) AS AVG_Salary

FROM EMPL

GROUP BY ROLLUP(EDLEVEL)

Будут получены следующие результаты:

EDLEVEL	TP	AVG_SALARY
-		26146.66
		27700.00
		28025.00
		26250.00
-		19180.00

Значение 1 в колонке TP в первой строке указывает, что данная строка сгенерирована супер группой (получена при вычислении итоговых значений при группировании), тогда как третья строка получена в результате обработки строк таблицы.

Функция RANK относится к категории OLAP функций и используется для ранжирования результата.

Функция имеет следующий синтаксис:

RANK () OVER ( [ PARTITION BY колонка ] ORDER BY выражение [ ASC | DESC ] )

Функция ранжирования вычисляет ранг строки в пределах некоторого множества строк.

PARTITION BY указывает, в пределах каких строк следует определять ранг. Если конструкция

PARTITION BY отсутствует, тогда ранг вычисляется в пределах всех строк, определяющих результат запроса. Если PARTITION BY указано, тогда значение ранга вычисляется в пределах каждой группы строк, имеющих одинаковое значение указанной колонки.

Строки, не различаемые в пределах данного множества, получают одинаковое значение ранга.

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

Если указывается RANK, ранг строки определяется как 1 плюс количество строк, которые физически предшествуют данной строке. Поэтому, если несколько строк не отличаются друг от друга с позиций упорядоченности, тогда в последовательности значений рангов будут пропуски.

Вместо RANK можно использовать функцию DENSE_RANK, тогда пропусков не будет.

Пример:

SELECT EMPNO, LASTNAME, SALARY,

RANK() OVER(ORDER BY SALARY DESC) AS SALARY_RANK

FROM EMPLOYEE

ORDER BY EMPNO

Здесь одна группа строк, общий механизм вычисления ранга.

Еще пример:

SELECT WORKDEPT, EMPNO, LASTNAME, EDLEVEL,

DENSE_RANK() OVER(PARTITION BY WORKDEPT

ORDER BY EDLEVEL DESC) AS RANK_EDLEVEL

FROM EMPLOYEE