Специальные функции

DECODE(E,S1,R1,S2,R2,…,[def]) Если E соответствует Si, то возвращается Ri, в противном случае - def или NULL, если умолчание не задано

TO_NUMBER(S) Возвращает результат преобразования строки S в аргумент типа NUMBER

TO_CHAR(X[,F]) Возвращает результат преобразования строки S в аргумент типа DATE согласно заданному формату даты F

TO_DATE(S[,F]) Возвращает результат преобразования значения параметра S символьного типа в тип DATE

Функции обработки даты и времени

SYSDATE Возвращает текущую дату и время

ROUND(D[,F]) Округляет значение даты D согласно заданному шаблону

TRANC(D[,F]) Усекает значение даты D согласно заданному шаблону

NEXT_DAY(D,S) Возвращает дату дня, который является первым днем, более поздним, чем текущая дата с названием S

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

SELECT ENAME, HIREDATE, HIREDATE + 92 DAYS

FROM EMPLOYEE

WHERE HIREDATE + 92 DAYS > SYSDATE

AND DEPNO=30;

Ключевое слово SYSDATE всегда возвращает текущую дату. В этом примере также показано, как используется арифметический оператор сложения с переменными типа "дата". К переменной типа "дата" можно прибавлять и вычитать из него целое число дней, месяцев, лет, часов, минут, секунд, микросекунд. Для этого используются соответствующие ключевые слова (DAY, MONTH и т.д.), следующие за целой константой (дробная часть игнорируется, если вы указываете число с десятичной точкой). Имеется ограничение на использование скобок в таких выражениях (так, заключение в скобки выражения 1 DAYS + 1 YEARS приведет к ошибке).

Агрегатные функции

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

AVG(X) = AVG(ALL X) AVG(DISTINCT X) Вычисляет среднее значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

COUNT(*) COUNT(X) = COUNT(ALL X) COUNT(DISTINCT X) Вычисляет числа итемов. При указании * всегда возвращается число строк в таблице. Указание DISTINCT подавляет дубликаты

MAX(X) = MAX(ALL X) MAX (DISTINCT X) Вычисляет максимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

MIN(X) = MIN(ALL X) MIN (DISTINCT X) Вычисляет минимальное значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

SUM(X) = SUM(ALL X) SUM (DISTINCT X) Вычисляет сумму значение аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты

STDDEV([DISTINCT|ALL]X) Вычисляет стандартное отклонение на множестве значений аргумента, который может быть выражением любого типа. Нуль-значения игнорируются, ключевое слово DISTINCT подавляет дубликаты:

VARIANCE([DISTINCT|ALL]) Вычисляет квадрат дисперсии

Использование функций агрегирования позволяет вам находить суммарные значения колонок и разброс данных в колонке. Так, после выполнения запроса:

SELECT SUM(SAL)

FROM EMPLOYEE;

- вы узнаете итоговую сумму зарплаты по организации, а из запроса

SELECT AVG(SAL), STDDEV(SAL)

FROM EMPLOYEE;

- среднюю зарплату по организации и ее разброс (дисперсию).

Однако наиболее часто требуется подобная итоговая информация не для таблицы в целом, а для определенных наборов (групп) строк таблицы.

Для того чтобы группировать строки таблицы по какому-либо признаку, в команде SELECT существует специальное предложение GROUP BY, которое задает колонку (или колонки) для проведения группировки. Это предложение группирует строки таблицы по значениям колонок группировки с последующим подавлением дублирующих значений в колонках группировки, т.е. позволяет определять подмножество значений некоторой колонки в терминах другой колонки и применять к полученным подмножествам функции агрегирования.

Предположим, что вы хотите найти минимальные и максимальные оклады служащих в подразделениях, тогда вы можете написать

SELECT DEPNO, MIN(SAL), MAX(SAL)

FROM EMPLOYEE

GROUP BY DEPNO.

Предложение GROUP BY должно следовать после предложения WHERE, если последнее присутствует в команде SELECT. Каждая строка результирующей таблицы относится к одной группе строк. Число групп определяется числом различных значений в колонке группировки (в данном случае DEPNO). Агрегатные функции применяются к каждой группе как к отдельному множеству.

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

SELECT DNAME, JOB, SUM(SAL), COUNT(*), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO

GROUP BY DNAME, JOB

Функции SUM( ), COUNT( ), AVG( ) вычисляют суммы, число строк в группе и среднее значение в группе строк.

В SQL можно задавать условия поиска для группы строк. Для этого в команде SELECT существует предложение HAVING, которое должно следовать за предложением GROUP BY. HAVING задает условие поиска для группы строк.

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

SELECT DNAME, JOB, SUM(SAL), AVG(SAL)

FROM EMPLOYEE, DEPARTAMENT

WHERE EMPLOYEE.DEPNO=DEPARTAMENT.DEPNO

GROUP BY DNAME, JOB

HAVING COUNT(*)>=2

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