yandex rtb 1
ГоловнаЗворотній зв'язок
yande share

Організація баз даних і знань

2.5 Вбудовані функції

Арифметичні функції

SQL підтримує повний набір арифметичних операцій і математичних функцій для побудови арифметичних виражень над колонками БД (+, -, *, /, ABS, LN, SQRT і т.д.). Перелік основних вбудованих математичних функцій поданий нижче:

ABS(X)          Повертає абсолютне значення числа Х;

ACOS(X)       Повертає арккосинус числа Х;

ASIN(X)        Повертає арксинус числа Х;

ATAN(X)       Повертає арктангенс числа Х;

COS(X)          Повертає косинус числа Х;

EXP(X)          Повертає експоненту числа Х;

SIGN(X)         Повертає -1, якщо Х<0,0, якщо Х=0, +1, якщо Х>0;

LN(X)             Повертає натуральний логарифм числа Х;

MOD(X,Y)     Повертає залишок від розподілу Х на Y;

CEIL(X)         Повертає найменше ціле, більше або рівне Х;

ROUND(X, n)           Округляє число Х до числа з n знаками після крапки;

SIN(X)           Повертає синус числа Х;

SQRT(X)        Повертає квадратний корінь числа Х;

TAN(X)          Повертає тангенс числа Х;

FLOOR(X)     Повертає найбільше ціле менше або рівне Х;

LOG(А, X)     Повертає логарифм числа Х по основі А;

SINH(X)        Повертає гіперболічний синус числа Х;

COSH(X)       Повертає гіперболічний косинус числа Х;

TANH(X)       Повертає гіперболічний тангенс числа Х;

TRANC(X,n)  Скорочує число Х до числа з n знаками після десяткової крапки;

POWER(A,X)            Повертає значення А, зведене в ступінь Х.

Арифметичні вираження необхідні для одержання даних, які безпосередньо не зберігаються в колонках таблиць БД, але значення яких необхідні користувачеві. Припустимо, що вам необхідний список службовців, що показує виплату, що одержав кожен службовець із урахуванням премій і штрафів

Функції для обробки дати

У діалекті SQL є невеликий набір функцій для маніпулювання колонками з типом 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])     Обчислює квадрат дисперсії.

Приклад. Одержати загальну кількість постачальників (ключове слово COUNT):

SELECT COUNT(*) AS N

FROM P;

У результаті одержимо таблицю з одним стовпцем й одним рядком, що містить кількість рядків з таблиці P.

Приклад. Одержати загальну, максимальну, мінімальну й середню кількості деталей, що поставляють, (ключові слова SUM, MAX, MIN, AVG):

SELECT    SUM(PD.VOLUME) AS SM,

                   MAX(PD.VOLUME) AS MX,

                   MIN(PD.VOLUME) AS MN,

                   AVG(PD.VOLUME) AS AV

FROM PD;

В результаті одержимо таку таблицю з одним рядком:

SM

MX

MN

AV

2000

1000

100

333. 33333333

 

Використання агрегатних функцій з угрупованнями

Приклад. Для кожної деталі одержати сумарну кількість, що поставляється (ключове слово GROUP BY…):

SELECT..DNUM, SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Цей запит буде виконуватися в такий спосіб. Спочатку рядки вихідної таблиці будуть згруповані так, щоб у кожну групу потрапили рядки з однаковими значеннями DNUM. Потім усередині кожної групи буде просумовано поле VOLUME. Від кожної групи до результуючої таблиці буде включений один рядок.

У переліку полів оператора SELECT, який містить розділ GROUP BY можна включати тільки агрегатні функції й поля, які входять в умову групування. Наступний запит видасть синтаксичну помилку:

SELECT  PD.PNUM, PD.DNUM,

                 SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM;

Причина помилки у тому, що у перелік полів, які відбираються, включене поле PNUM, що не входить у розділ GROUP BY. І дійсно у кожну отриману групу рядків може входити кілька рядків із різними значеннями поля PNUM. З кожної групи рядків буде сформовано по одному підсумковому рядку. При цьому немає однозначної відповіді на питання, яке значення вибрати для поля PNUM у підсумковому рядку.

Деякі діалекти SQL не вважають це за помилку. Запит буде виконаний, але передбачити, які значення будуть внесені у поле PNUM у результуючій таблиці, неможливо.

Приклад. Одержати номери деталей, сумарна кількість поставки яких перевершує 400 (ключове слово HAVING…).

Умова, що сумарна кількість поставки повинна бути більше 400, не може бути сформульована у розділі WHERE, тому що в цьому розділі не можна використовувати агрегатні функції. Умови, що використовують агрегатні функції повинні бути розміщені у спеціальному розділі HAVING:

SELECT   PD.DNUM, SUM(PD.VOLUME) AS SM

GROUP BY PD.DNUM

HAVING SUM(PD.VOLUME) > 400;

В одному запиті можуть зустрітися як умови відбору рядків у розділі WHERE, так й умови відбору груп у розділі HAVING. Умови відбору груп не можна перенести з розділу HAVING у розділ WHERE. Аналогічно й умови відбору рядків не можна перенести з розділу WHERE у розділ HAVING, за винятком умов, що включають поля зі списку угруповання GROUP BY.

 

22