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

Выражения в Oracle SQL

< Лекция 3 || Лекция 4: 12345 || Лекция 5 >
Функции для работы со временем

Позволяют пополнить "временную арифметику" необходимыми или практичными операциями. Примеры функций:

ADD_MONTHS
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
TRUNC
EXTRACT(9-)

(9-) начиная с версии 9

Допустим, что в поле BDATE типа DATE текущей строки находится значение "5 сентября 1999 г., 13 часов 30 минут 05 секунд". Справедливы следующие оценки выражений:

Выражение Значение
EXTRACT ( DAY FROM SYSTIMESTAMP )
EXTRACT ( HOUR FROM SYSTIMESTAMP )

EXTRACT ( MINUTE FROM INTERVAL '04:03' HOUR TO MINUTE )
[сегодняшний день](*) (типа NUMBER)
[время суток](*) (типа NUMBER)
и так далее
3 "минуты" (типа NUMBER)
TRUNC ( BDATE )
TRUNC ( BDATE, 'year' )
5 сентября 1999 года, 00:00:00
1 января 1999 года, 00:00:00
и так далее
ADD_MONTHS ( BDATE, 2 ) 5 ноября 1999 года, 13:30:05
( ADD_MONTHS ( TRUNC ( SYSDATE, 'year' ), 12 ) - 1 ) - TRUNC ( SYSDATE ) число суток до ближайшего Нового года (типа NUMBER)

(*) время компьютера с СУБД

Можно заметить, что Oracle, как и стандарт SQL, непоследователен в своем синтаксисе. Сравните указание компоненты момента времени в виде строки текста ('year' в функции TRUNC, а также ROUND) и с помощью ключевого слова (DAY, HOUR в функции EXTRACT).

Примеры использования:

SELECT EXTRACT ( YEAR FROM SYSTIMESTAMP ) FROM dual;
SELECT MONTHS_BETWEEN ( DATE '2009-03-01', DATE '2009-02-28' ) 
FROM   dual
;

Заметьте, что функции "месячной арифметики" ADD_MONTHS и MONTHS_BETWEEN вовсе не так очевидны.

Упражнение. Проверьте значения следующих выражений:

ADD_MONTHS ( DATE '2009-01-28', 1 )
ADD_MONTHS ( DATE '2009-01-29', 1 )
ADD_MONTHS ( DATE '2008-01-29', 1 )
ADD_MONTHS ( DATE '2009-01-30', 1 )
Функции условной подстановки значений

Дают возможность выполнить "преобразование" аргументов, а по сути — условную замену конкретных величин. Часть таких функций связана с желательной для программиста переработкой отсутствующих значений (в отдельном случае Not a Number), а функция DECODE — нет:

Функция Логический эквивалент
NVL (E1, E2) IF E1 IS NULL THEN E2 ELSE E1
NVL2 (E1, E2, E3) IF E1 IS NULL THEN E3 ELSE E2
NANVL (E1, E2)(IEEE 754) IF E1 IS NAN THEN E2 ELSE E1
COALESCE (E1, E2, E3, …[9-) первое по списку Ei со значением не NULL
DECODE ( E1, E2, E3, …[, EN]) IF E1 = E2 THEN E3 [ ELSE IF E1 = E4 THEN E5 […] ] [ELSE EN]

(IEEE 754) для типов BINARY_FLOAT/BINARY_DOUBLE

[9-) начиная с версии 9

Примеры:

SELECT ename, comm, NVL ( comm, 0 ) FROM emp;
SELECT comm, sal, COALESCE ( comm, sal ) FROM emp;

В отличие от NVL и NVL2, COALESCE не вычисляет выражения-аргументы без надобности:

SELECT NVL ( 123, 1 / 0 ) FROM dual;
-- Ошибка !
SELECT COALESCE ( 123, 1 / 0 ) FROM dual;
-- OK 

Пример DECODE:

SELECT 
  deptno
, loc
, DECODE ( loc, 'NEW YORK', 'NEW YORK CITY', 'BOSTON', 'BOSTON AREA' )
FROM dept
;

Фактически DECODE позволяет сформулировать в тексте запроса таблицу подстановки значений. В нашем случае, если потребуется выдать исходное значение LOC, когда там не значения 'NEW YORK' и 'BOSTON', нужно будет добавить замыкающий четный аргумент:

DECODE 
( loc, 'NEW YORK', 'NEW YORK CITY', 'BOSTON', 'BOSTON AREA', loc )

Это не самое хорошее решение, так как иногда приходится повторять сложное выражение вторично, что чревато ошибками и лишними вычислениями. Кроме того, методически оправданно держать правила преобразования в БД, а не в тексте запроса (если только эти правила имеют прикладное значение). На практике же нахождение таблицы преобразования в БД резко замедлит вычисление.

Нескалярные функции для анализа данных

Таковых имеется две категории: "агрегатные", то есть обобщающие, и "аналитические".

Агрегатные функции иначе называют "стандартными агрегатными" функциями и "агрерирующими" функциями. До версии 8.1.6 они (сокращенным количеством) назывались "статистическими". Они дают скалярный результат, но аргументом им служит столбец значений, чем они конструктивно отличаются от большинства других встроенных функций. Это же сообщает им обобщающий характер.

Некоторые из них:

Функция Описание
COUNT Число значений в столбце или строк в таблице
MIN Наименьшее значение в столбце
MAX Наибольшее значение в столбце
SUM Сумма значений в столбце
AVG Среднее арифметическое значений в столбце
VARIANCE Дисперсия (мера отклонения от математического ожидания)
STDDEV,
STDDEV_POP,
STDDEV_SAM
Стандартное отклонение (квадратный корень от дисперсии) в разных вариациях
MEDIAN[10-) Медиана значений в столбце

[10-) с версии 10

Некоторые другие примеры: CORR, COVAR_POP, COVAR_SAMP, CUME_DIST, PERCENTILE_COUNT и так далее.

Начиная с версии 10 Oracle позволяет производить более "серьезные" статистические обобщения данных столбца таблицы, но уже не средствами SQL, а программно, с помощью процедур из встроенного пакета DBMS_STAT_FUNCS. Они позволяют определить соответствие указанного значения тому или иному виду статистического распределения, а также обобщать данные столбца всеми способами стандартных агрегатных функций, но вдобавок со значительным количеством дополнительной обобщающей информации.

Аналитические функции идут дальше агрегатных, не только имея столбцовые аргументы, но и возвращая в виде столбца результат. Они не только позволяют обобщить данные, как агрегатные, но способны делать это без потери детализации.

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

< Лекция 3 || Лекция 4: 12345 || Лекция 5 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 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'));

И сколько строк он все таки вернет
Ольга Угарова
Ольга Угарова
Россия, Москва, НИЯУ МИФИ