252 Глава 7
■ 01-JAN-2000 для пустых или пропущенных значений типа даты
■ Null для других типов базы данных.
KEEP NAV возвращает пустое значение для всех пустых или пропущенных
числовых величин. Помните, что KEEP NAV является значением по
умолчанию.
Следующий запрос демонстрирует использование IGNORE NAV:
□ SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL IGNORE NAV
PARTITION BY(prd_type_id)
DIMENSION BY (month, year)
MEASURES (amount sales_amount) (
sales_amount[FOR month FROM 1 TO 3 INCREMERNT 1, 2004] =
ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2), 0)
) ORDER BY prd_type_id, year, month;
Обновление существующих ячеек
По умолчанию, если ячейка, указанная в выражении слева от знака равенства,
существует, то эта ячейка будет обновлена. Если такой ячейки нет, будет
создана новая строка массива. Это правило можно изменить, используя
ключевые слова RULES UPDATE, которые служат указанием на то, что если
ячейки не существует, то не следует создавать новую строку.
С л е д у ю щ и й запрос служит иллюстрацией использования RULES UPDATE:
□ SELECT prd_type_id, year, month, sales_amount
FROM all_sales
WHERE prd_type_id BETWEEN 1 AND 2
AND emp_id = 21
MODEL
PARTITION BY(prd_type_id)
DIMENSION BY (month, year)
MEASURES (amount sales_amount)
RULES UPDATE (
sales_amount[F0R month FROM 1 TO 3 INCREMERNT 1, 2004] -
ROUND(sales_amount[CURRENTV(), 2003] * 1.25, 2)
) ORDER BY prd_type_id, year, month;
Поскольку ячеек для 2004 г. не существует и использованы ключевые
слова RULES UPDATE, в массиве не будет создано новых строк для 2004 г., а
запрос не возвратит строки для 2004 г. Ниже приводятся выходные данные
этого запроса; обратите внимание, что здесь отсутствуют строки для
2004 г.:
□ PRD_TYPE_ID YEAR MONTH SALES_AM0UNT
1 2003 1 10034.84
Расширенные запросы 253
2003 2 15144.65
2003 3 20137.83
2003 4 25057.45
2003 5 17214.56
2003 6 15564.64
2003 7 12654.84
2003 8 17434.82
2003 9 19854.57
2003 10 21754.19
2003 11 13029.73
2003 12 10034.84
2 2003 1 1034.84
2 2003 2 1544.65
2 2003 3 2037.83
2 2003 4 2557.45
2 2003 5 1714.56
2 2003 6 1564.64
2 2003 7 1264.84
2 2003 8 1734.82
2 2003 9 1854.57
2 2003 ' 10 2754.19
2 2003 11 1329.73
2 2003 12 1034.84
Использование выражений PIVOT и UNPIV0T
Выражение PIVOT впервые появилось в базе данных Oracle 1 lg и позволяет
переводить строки в столбцы в выходных данных запроса, и одновременно
выполнять функцию агрегирования над данными. База данных
Oracle l l g также имеет выражение UNPIV0T, которое переводит столбцы в
строки в выходных данных запроса.
Выражения PIVOT и UNPIV0T полезны для просмотра общих тенденций
в больших объёмах данных, например, тенденций объёмов продаж на
протяжении определённого промежутка времени. В следующих разделах
вы увидите запросы, которые демонстрируют использование PIVOT и
UNPIV0T.
Простой пример выражения PIVOT
Наиболее лёгкий способ понять, как использовать выражение PIVOT - посмотреть
пример. Следующий запрос показывает общий объём продаж
для типов товаров 1, 2 и В для первых трёх месяцев 2003 г.; обратите внимание,
что ячейки в выходных данных запроса показывают сумму объёмов
продаж для каждого типа товара в каждом месяце:
□ SELECT *
FROM (
SELECT month, prd_type_id, amount
FROM all_sales
WHERE year = 2003
AND prd_type_id IN (1, 2, 3)
254 Гпава 7
) PIVOT (
SUM(amount) FOR month IN (1 AS JAN, 2 AS FEB, 3 AS MAR, 4 AS APR)
) ORDER BY prd_type_id;
PRD TYPE ID JAN
38909.04
14309.04
24909.04
FEB
70567.9
13367.9
15467.9
MAR
91826.98
16826.98
20626.98
APR
120344.7
15664.7
23844.7
Начиная с первой строки выходных данных, можно увидеть, что
■ В январе было продано товара типа 1 на $38,909.04
■ В феврале было продано товара типа 1 на $70,567.90
■ ... и так далее до конца первой строки.
Вторая строка выходных данных показывает, что
■ В январе было продано товара типа 2 на $14,309.04
■ В феврале было продано товара типа 2 на $13,367.90
■ ... и так далее до конца строки.
Примечание Выражение PIV0T является мощным инструментом, который позволяет увидеть
тенденции продаж различных типов товаров на протяжении периода в несколько месяцев.
Реальный магазин мог бы использовать информацию об этих тенденциях для смены тактики
продаж и формулировки новых маркетинговых кампаний. _____________
Предыдущее выражение SELECT имеет следующую структуру:
□ SELECT *
FROM (
Внутренний_запрос
) PIVOT (
агрегатная_функция FOR CTon6eu,_pivot IN (список_значений)
) ORDER BY ...;
Давайте разобьём предыдущий пример на структурные элементы.
■ Существует внутренний и внеший запрос. Внутренний запрос получает
месяц, тип товара, а также количество из таблицы all_sales и передаёт