Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7083 / 760 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 6:

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

< Лекция 5 || Лекция 6: 1234 || Лекция 7 >

Соединение фраз 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 этого делать не умеет.

< Лекция 5 || Лекция 6: 1234 || Лекция 7 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001