Составление отчетов и управление хранилищами данных

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

Разворачивание результирующего множества в одну строку

Задача

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

DEPTNO CNT

10 3

20 5

30 6

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

DEPTNO 10 DEPTNO 20 DEPTNO 30

Решение

Транспонируйте результирующее множество с помощью выражения CASE и агрегатной функции SUM:

1 select sum(case when deptno=10 then 1 else 0 end) as deptno 10,

2 sum(case when deptno=20 then 1 else 0 end) as deptno 20,

3 sum(case when deptno=30 then 1 else 0 end) as deptno 30

4 from emp

Обсуждение

Данный пример является превосходным введением в разворачивание таблиц. Принцип прост: к каждой строке, возвращенной запросом, применяем выражение CASE, чтобы разложить строки в столбцы. Затем, поскольку стоит конкретная задача пересчитать служащих в каждом отделе, с помощью агрегатной функции SUM подсчитываем количество экземпляров каждого значения DEPTNO. Если что-то не понятно, выполните запрос без агрегатной функции SUM и включите в него DEPTNO для удобства чтения:

select deptno,

case when deptno=10 then 1 else 0 end as deptno 10, case when deptno=20 then 1 else 0 end as deptno 20, case when deptno=30 then 1 else 0 end as deptno 30 from emp order by 1

DEPTNO DEPTNO 10 DEPTNO 20 DEPTNO 30

10 1 0 0

10 1 0 0

10 1 0 0

20 0 1 0

20 0 1 0

20 0 1 0

20 0 1 0

20 0 1 0

30 0 0 1

30 0 0 1

30 0 0 1

30 0 0 1

30 0 0 1

30 0 0 1

Выражения CASE, так сказать, расставляют флаги, обозначая, к какому DEPTNO относится строка. На данный момент преобразование строк в столбцы уже выполнено. Осталось просто сложить значения, возвращенные в столбцах DEPTNO 10, DEPTNO 20 и DEPTNO 30, и сгруппировать их по DEPTNO. Ниже представлены результаты:

select deptno,

sum(case when deptno=10 then 1 else 0 end) as deptno 10,

sum(case when deptno=20 then 1 else 0 end) as deptno 20, sum(case when deptno=30 then 1 else 0 end) as deptno 30 from emp group by deptno

DEPTNO DEPTNO 10 DEPTNO 20 DEPTNO 30

10 3 0 0

20 0 5 0

30 0 0 6

Если внимательно посмотреть на это результирующее множество, то станет ясно, что с логической точки зрения такой вывод имеет смысл: например, в столбце DEPTNO 10 для DEPTNO 10 указано 3 служащих и нуль для других отделов. Поскольку поставлена цель возвратить одну строку, последний шаг - убрать DEPTNO и GROUP BY и просто суммировать выражения CASE:

select sum(case when deptno=10 then 1 else 0 end) as deptno 10,

sum(case when deptno=20 then 1 else 0 end) as deptno 20,

sum(case when deptno=30 then 1 else 0 end) as deptno 30 from emp

DEPTNO 10 DEPTNO 20 DEPTNO 30 3 5 6

Далее представлен другой подход, который иногда применяют для решения такого рода задач:

select max(case when deptno=10 then empcount else null end) as deptno 10 max(case when deptno=20 then empcount else null end) as deptno 20, max(case when deptno=30 then empcount else null end) as deptno 30 from (

select deptno, count(*) as empcount

from emp group by deptno ) x

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

DEPTNO 10 DEPTNO 20 DEPTNO 30

3 NULL NULL NULL 5 NULL

NULL NULL 6

Затем функция MAX сворачивает столбцы в одну строку:

DEPTNO 10 DEPTNO 20 DEPTNO 30