результаты во внешний запрос.
■ SUM(amount) FOR month IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR) является
строкой в выражении PIVOT.
■ Функция SUM ( ) складывает объёмы продаж для разных типов товаров
в течение первых четырёх месяцев (месяцы перечислены в части
IN). Вместо того, чтобы вернуть месяцы как 1, 2, 3 и 4, часть AS переименовывает
номера в JAN, FEB, MAR и APR, чтобы сделать их более
читаемыми на выходе.
Расширенные запросы 255
■ Столбец month таблицы all_sales используется в качестве поворотного.
Это означает, что в выходных данных месяцы появятся в виде
столбцов. Таким образом, строки поворачивают для того, чтобы показать
месяцы в виде столбцов.
■ В самом конце примера строка ORDER BY prd_type_id просто упорядочивает
результаты по типу товара.
Поворот нескольких столбцов
Вы можете поворачивать несколько столбцов, размещая эти столбцы в
части FOR выражения PIVOT. Следующий пример поворачивает столбцы
month и prd_type_id, которые указаны в части FOR; обратите внимание, что
список значений в части IN выражения PIVOT содержит значение для столбцов
month и prd_type_id:
□ SELECT *
FROM (
SELECT month, prd_type_id, amount
FROM all_sales
WHERE year = 2003 . -
AND prd_type_id IN (1, 2, 3)
) PIVOT (
SUM(amount) FOR (month, prd_type_id) IN (
(1, 2) AS JAN_PRDTYPE2,
(2, 3) AS FEB_PRDTYPE3,
(3, 1) AS MAR_PRDTYPE1,
(4, 2) AS APR_PRDTYPE2
))
;
JAN_PRDTYPE2 FEB_PRDTYPE3 MAR_PRDTYPE1 APR_PRDTYPE2
14309.04 15467.9 91826.98 15664.7
Ячейки в выходных данных показывают сумму объёмов продаж для
каждого типа товаров в указанный месяц (запрашиваемые тип товара и
месяц помещены в список значений в части IN). Как видно из результатов
этого запроса, суммы продаж следующие:
■ В январе для товара 2 - $14,309.04
■ В феврале для товара 3 - $15,467.90
■ В марте для товара 1 - $91,826.98
■ В апреле для товара 2 - $15,664.70
В части IN вы можете разместить любые значения, чтобы получить интересующие
вас данные. В следующем примере значения для типов товаров
в части IN переставлены местами, чтобы получить продажи для этих
товаров в указанные месяцы:
□ SELECT *
FROM (
256 Глава 7
SELECT month, prd_type_id, amount
FROM all_sales
WHERE year = 2003
AND prd_type_id IN (1, 2, 3)
) PIVOT (
SUM(amount) FOR (month, prd_type_id) IN (
(1, 1) AS JAN_PRDTYPE1,
(2, 2) AS FEB_PRDTYPE2,
(3, 3) AS MAR_PRDTYPE3,
(4, 1) AS APR_PRDTYPE1
))
;
JAN_PRDTYPE1 FEB_PRDTYPE2 MAR_PRDTYPE3 APR_PRDTYPE1
38909.04 13367.9 20626.98 120344.7
Как видно из результатов этого запроса, суммы продаж следующие:
■ В январе для товара 1 - $38,909.04
■ В феврале для товара 2 - $13,367.90
■ В марте для товара 3 - $20,626.98
■ В апреле для товара 1 - $120,344.70
Использование нескольких агрегатных функций в повороте
В повороте можно использовать несколько агрегатных функций. Например,
следующий запрос использует SUM( ) для получения общих объёмов
продаж для разных типов товара в январе и феврале и AVG() для получения
средних значений продаж:
□ SELECT *
FROM (
SELECT month, prd_type_id, amount
FROM all_sales
WHERE year = 2003
AND prd_type_id IN (1, 2, 3)
) PIVOT (
SUM(amount) AS sum_amount,
AVG(amount) AS avg_amount
FOR (month) IN (
1 AS JAN, 2 AS FEB
)
);
ORDER BY prd_type_id;
PRD_TYPE_ID JAN_SUM_AM0UNT JAN_AVG_AM0UNT FEB_SUM_AM0UNT FEB_AVG_AM0UNT
1 38909.04
2 14309.04
3 24909.04
6484.84
2384.84
4151.50667
70567.9
13367.9
15467.9
11761.3167
2227.98333
2577.98333
Расширенные запросы 257
Как видно, первая строка на выходе имеет для товара 1:
■ Общий объём $38,909.04 и средняя величина продаж $6,484.84 в ян-
■ Общий объём $70,567.90 и средняя величина продаж $11,761.32 в
феврале
Вторая строка на выходе имеет для товара 2:
■ Общий объём $14,309.04 и средняя величина продаж $2,384.84 в ян-
■ Общий объём $13,367.90 и средняя величина продаж $2,227.98 в феврале
... и так далее.
Использование выражения UNPIV0T
Выражение UNPIV0T разворачивает столбцы в строки. Примеры в этом разделе
используют таблицу по имени pivot_sales_data (создаваемую сценарием
store_schema.sql); pivot_sales_data наполняется запросом, который
возвращает повёрнутую версию данных о продажах:
□ CREATE TABLE pivot_sales_data AS
SELECT *
FROM (
SELECT month, prd_type_id, amount
FROM all_sales
WHERE year = 2003
AND prd_type_id IN (1, 2, 3)
) PIVOT (
SUM(amount) FOR month IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR)
) ORDER BY prd_type_id;
Следующий запрос извлекает содержимое таблицы pivot_sales_data:
□ SELECT *
FROM pivot_sales_data;
PRD_TYPE_ID JAN FEB MAR APR
Следующий запрос использует UN PIVOT для получения данных о продажах
в перевернутой форме:
П SELECT *
FROM pivot_sales_data
UNPIV0T (
Amount FOR month IN (JAN, FEB, MAR, APR)
) ORDER BY prd_type_id;
варе