Выборка данных. Фраза SELECT предложения SELECT

Соединение фраз SELECT и FROM фразами PIVOT/UNPIVOT

Версия Oracle 11 позволила дополнить фразу FROM подчиненными фразами PIVOT и UNPIVOT, приводящими к автоматическому появлению во фразе SELECT столбцов, "импортированных" из этих конструкций. Обе формулировки предназначены для переформатирования данных таблиц средствами SQL, без программирования. Они удобны для построения отчетов и анализа имеющихся данных.

Разворачивание данных в столбцы указанием PIVOT

Сочетание SELECT … FROM … PIVOT … позволяет развернуть данные одного столбца в отдельные столбцы конечного результата.

Рассмотрим для начала запрос о наличии в разных отделах сотрудников на разных должностях:

SQL> SELECT job, deptno FROM emp;
JOB           DEPTNO
--------- ----------
CLERK             20
SALESMAN          30
SALESMAN          30
MANAGER           20
SALESMAN          30
MANAGER           30
MANAGER           10
ANALYST           20
PRESIDENT         10
SALESMAN          30
CLERK             20
CLERK             30
ANALYST           20
CLERK             10

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

SQL> SELECT * 
  2> FROM   ( SELECT job, deptno FROM emp ) 
  3> PIVOT  ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) );
JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK              1          2          1          0
SALESMAN           0          0          4          0
PRESIDENT          1          0          0          0
MANAGER            1          1          1          0
ANALYST            0          2          0          0

Внутренняя отработка такого предложения осуществляется как при группировке GROUP BY job (см. ниже), но приводит к появлению дополнительных столбцов вместо, казалось бы, указанного DEPTNO. Вот как мог бы выглядеть аналог последнего запроса в версиях Oracle до 11:

SELECT
  JOB
, COUNT ( CASE WHEN deptno = 10 THEN 1 END ) "10"
, COUNT ( CASE WHEN deptno = 20 THEN 1 END ) "20"
, COUNT ( CASE WHEN deptno = 30 THEN 1 END ) "30"
, COUNT ( CASE WHEN deptno = 40 THEN 1 END ) "40"
FROM   ( SELECT job, deptno FROM emp )
GROUP BY job
;

Именно так Oracle и обработает запрос с PIVOT (по крайней мере в версии 11), но форма с PIVOT приводит к краткости и определенной выразительной гибкости.

Для правильного разворачивания существенно правильно указать структуру источника во фразе FROM. Если в последнем запросе после FROM указать не подзапрос, а таблицу EMP (или же в подзапросе указать другие поля EMP), принцип разворачивания изменится и результат окажется иным.

Вот некоторые другие примеры. Выдать только данные по отделам 10 и 30:

SELECT * 
FROM   ( SELECT job, deptno FROM emp ) 
PIVOT  ( COUNT ( * ) FOR deptno IN ( 10, 30 ) )
;

То же самое:

SELECT job, "10", "30" 
FROM   ( SELECT job, deptno FROM emp ) 
PIVOT  ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) )
;

Самостоятельное задание имен столбцам результата делается как во фразе SELECT:

SELECT * 
FROM   ( SELECT job, deptno FROM emp ) 
PIVOT  ( COUNT ( * ) FOR deptno IN ( 10 tenth, 30 thirtieth ) )
;

Выдача сумм окладов сотрудников по каждой должности в указанных отделах:

SELECT * 
FROM   ( SELECT job, deptno, sal FROM emp ) 
PIVOT  ( SUM ( sal ) FOR deptno IN ( 10, 20, 30, 40 ) )
;

Выдача сразу двух агрегатов (суммы зарплат и количества сотрудников):

SELECT * 
FROM   ( SELECT job, deptno, sal FROM emp ) 
PIVOT  ( SUM ( sal ), COUNT ( * ) c FOR deptno IN ( 10, 20, 30, 40 ) )
;

Сформировать столбцы для сотрудников 10-го отдела с окладами 5000 и 1300 и сотрудников 30-го отдела с окладами 1250:

SELECT *
FROM   ( SELECT job, deptno, sal FROM emp )
PIVOT  ( 
   COUNT ( * ) c 
   FOR ( deptno, sal ) IN ( ( 10, 5000), ( 10, 1300), ( 30, 1250 ) ) 
);

Во фразе PIVOT также предусмотрены конструкции для переформатирования специально данных XML.

Сворачивание данных в столбец указанием UNPIVOT

Фраза UNPIVOT выполняет действие, содержательно противоположное фразе PIVOT.

Создадим таблицу по запросу выше:

CREATE TABLE total AS
SELECT * 
FROM   ( SELECT job, deptno FROM emp ) 
PIVOT  ( COUNT ( * ) FOR deptno IN ( 10, 20, 30, 40 ) )
;

Выдача данных со сворачиванием показателей в один столбец:

SQL> SELECT  *
  2  FROM    total
  3  UNPIVOT ( jobcount FOR deptno IN ( "10", "20", "30", "40" ) )
  4  ;
JOB       DE   JOBCOUNT
--------- -- ----------
CLERK     10          1
CLERK     20          2
CLERK     30          1
CLERK     40          0
SALESMAN  10          0
SALESMAN  20          0
SALESMAN  30          4
SALESMAN  40          0
PRESIDENT 10          1
PRESIDENT 20          0
PRESIDENT 30          0
PRESIDENT 40          0
MANAGER   10          1
MANAGER   20          1
MANAGER   30          1
MANAGER   40          0
ANALYST   10          0
ANALYST   20          2
ANALYST   30          0
ANALYST   40          0

От выдачи JOBCOUNT можно отказаться, указав вместо SELECT * … формулировку SELECT job, deptno ….

Заметьте, что агрегат COUNT в отличие от всех остальных всегда возвращает значение, хоть бы 0. Если бы в столбце JOBCOUNT оказались пропуски (NULL), был бы законным вопрос, как их учитывать при сворачивании данных. Две возможные схемы поведения обозначаются уточнениями UNPIVOT INCLUDE NULLS и UNPIVOT EXCLUDE NULLS.

Подобное сворачивание в столбец (иначе, переворачивание, "транспонирование") таблицы TOTAL позволяет ответить на вопросы типа "В каких отделах число продавцов больше 10?", или даже "больше 10%". Иначе SQL этого делать не умеет.