Lab_04_Oracle %28PL_SQL%29 / Додатково_Теорія_03_Вбудовані_функції

SUM ( [{DISTINCT ALL}] n );	Сума значень, що ігнорує порожні значення
VARIANCE ( [{DISTINCT ALL}] n );	Дисперсія в групі

У наведеній таблиці під числом nрозуміється ім'я поля, константа або арифметичний вираз числового типу. Вираз може мати будь-який тип. Найчастіше в якості числа nі виразів використовуються імена стовпців таблиць.

Перераховані функції оперують із групами рядків вихідних таблиць (наприклад, з усією таблицею, як з однією групою) і у зв'язку із цим називаються груповими, або агрегованими функціями.

Ключове слово DISTINCT дає команду груповій функції оперувати тільки з неоднаковими рядками в групах, тобто дубльовані рядки при роботі функції відкидаються.

Ключове слово ALL залишає дубльовані рядки для обробки груповими функціями. За замовчуванням всі функції використовують ALL.

Тип даних виразу може бути CHAR, NUMBER або DATE (для функцій, що використовують в якості аргументу вираз).

Усі групові функції, крім COUNT(*), ігнорують порожні значення. Для обробки порожніх значень варто використати функцію NVL.

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

Щоб обчислити середню зарплату службовців, задайте команду:

SQL> ------------------

SQL> -- Вправа №54

SQL> ------------------

SQL> SELECT AVG(оклад) 2 FROM служ;

AVG(ОКЛАД)

------------------

2073,2143

Слід зазначити, що в цьому випадку всі рядки таблиці «служ» розглядаються як одна група.

Групова функція може обробляти частину рядків таблиці, обраної за критерієм у реченні WHERE.

Щоб визначити мінімальну зарплату в реалізаторів, виконайте команду:

SQL> ------------------

SQL> -- Вправа №55

SQL> ------------------

SQL> SELECT MIN(оклад)

2FROM служ

3WHERE должность = 'реализатор';

MIN(ОКЛАД)

-----------------

1250

Щоб підрахувати кількість службовців у відділі 20, задайте команду:

SQL> ------------------

SQL> -- Вправа №56

SQL> ------------------

SQL> SELECT COUNT(*)

2FROM служ

3WHERE отделы_номер = 20;

COUNT(*)

---------------

5

Слово GROUP BY

Слово GROUP BY може бути використане для розбиття таблиці результатів на більш дрібні групи рядків за значенням полів розбивки. При цьому групові функції обчислюють агреговані значення в кожній із груп.

Наприклад, щоб обчислити середнє значення зарплати для кожної посади, виконайте:

SQL> ------------------

SQL> -- Вправа №57

SQL> ------------------

SQL> SELECT должность, AVG(оклад)

2FROM служ

3GROUP BY должность;

ДОЛЖНОСТЬ	AVG(ОКЛАД)
--------------------	------------------
аналитик	3000
горничная	800
менеджер	2758,3333
повар	950
президент	5000
реализатор	1400
охранник	1100
шофер	1300

8 строк выбрано.

Виключення рядків в GROUP BY

Слово GROUP BY групує тільки ті рядки, які знайдені за заданим у слові WHERE критерієм пошуку. Інакше кажучи, воно оперує з рядками таблиці результатів.

Наприклад, щоб обчислити середню зарплату за всіма посадами, крім менеджерів, уведіть запит:

SQL> ------------------

SQL> -- Вправа №58

SQL> ------------------

SQL> SELECT должность, AVG(оклад)

2FROM служ

3WHERE должность <> 'менеджер'

4GROUP BY должность;

ПОСАДА	AVG(ОКЛАД)
--------------	-----------------
аналитик	3000
горничная	800
повар	950
президент	5000
реализатор	1400
охранник	1100
шофер	1300

7 строк выбрано.

Групи усередині груп

Ви також можете використати слово GROUP BY для одержання результатів за групами всередині груп.

Наприклад, щоб визначити середню зарплату за посадами для кожного з відділів, виконайте команду:

SQL> ------------------

SQL> -- Вправа №58

SQL> ------------------

SQL> SELECT отделы_номер, должность, AVG(оклад)

2	FROM служ
3	GROUP BY отделы_номер, должность;
ОТДЕЛЫ_НОМЕР ДОЛЖНОСТЬ AVG(ОКЛАД)

---------------------------------------------------------------------

10 горничная		800
10	менеджер	2450
10	президент	5000
20	аналитик	3000
20	менеджер	2975
20	охранник	1100
20	шофер	1300
30	менеджер	2850
30	повар	950
30	реализатор	1400

10 строк выбрано.

Індивідуальні стовпці в таблиці результатів

Наведена нижче команда визначає максимальну зарплату за кожною посадою. Однак результат недостатньо інформативний, тому що самі посади у вихідній таблиці не присутні:

SQL> -----------------

SQL> -- Вправа №59

SQL> -----------------

SQL> SELECT MAX(оклад) 2 FROM служ

3 GROUP BY должность;

MAX(ОКЛАД)

------------------

3000

800

2975

950

5000

1600

1100

1300

8 строк выбрано.

Вихідна таблиця буде набагато більше інформативною, якщо в неї додати стовпець «посада», тобто стовпець, за яким зроблена розбивка на групи.

SQL> ----------------

SQL> -- Вправа №60

SQL> ------------------

SQL> SELECT MAX(оклад), должность 2 FROM служ

3 GROUP BY дожность;

MAX(оклад) должность

-------------------------------

3000 аналитик

800 горничная

2975 менеджер

950 повар

5000 президент

1600 реализатор

1100 охранник

1300 шофер

8 строк выбрано.

При підстановці стовпців таблиці в операторі SELECT, що містить групові функції, запам'ятайте й дотримуйтесь наступного правила:

Можна ставити в SELECT тільки ті стовпці, за якими виконується розбиття на групи, тобто задані в GROUP BY. Можливо також задання литералів, псевдостовпців. Інші стовпці таблиць БД у реченні SELECT при цьому заборонені.

Інакше кажучи, якщо стовпці не стоять у реченні GROUP BY, то вони не можуть бути розміщені в реченні SELECT разом із груповими функціями.

Наприклад, команда некоректна, і система видає помилку:

SQL> ------------------

SQL> -- Вправа №61

SQL> ------------------

SQL> SELECT отделы_номер, MIN(оклад) 2 FROM служ;

SELECT отделы_номер, MIN(оклад)

*

Ошибка в строке 1:

ORA-00937: групповая функция не является одногруппной

Зірочка * указує на місце в команді, де виявлена помилка. Команда некоректна, тому що стовпець «отделы_номер» заданий у реченні SELECT разом із груповою функцією MIN(оклад), але не поставлений в GROUP BY.

Щоб виправити помилку, додамо в команду слово GROUP BY:

SQL> -------------------

SQL> -- Вправа №62

SQL> -------------------

SQL> SELECT отделы_номер, MIN(оклад)

2FROM служ

3GROUP BY отделы_номер;

ОТДЕЛЫ_НОМЕР MIN(ОКЛАД)

--------------------------------------------

10	800
20	1100
30	950

Тепер «отделы_номер» – це не окреме поле таблиці, а поле – найменування групи. Якщо потрібно поставити в операторі SELECT більше одного стовпця, то всі ці стовпці повинні стояти й в GROUP BY, тобто бути стовпцями розбивки.

Таким чином, є правило, якому потрібно слідувати, використовуючи слово GROUP BY:

Будь-який стовпець таблиці БД, що стоїть в реченні SELECT не в груповій функції, повинен бути й у реченні GROUP BY.

Слово HAVING

Задавайте слово HAVING, якщо необхідно вибрати не всі формовані GROUP BY групи, а лише їх частину за певним критерієм (на групові характеристики).

Наприклад, щоб визначити середню зарплату у відділах, що мають більше трьох співробітників, задайте команду:

SQL> -------------------
SQL> -- Вправа №63
SQL> -------------------
SQL> SELECT отделы_номер, AVG(оклад)
2	FROM служ
3	GROUP BY отделы_номер
4	HAVING COUNT(*) > 3;

ОТДЕЛ_НОМЕР AVG(ОКЛАД)

----------------------- ------------------

20	2275
30	1566,6667

Щоб визначити посади, згідно з якими максимальна зарплата не менше 3000 гр., виконайте команду:

SQL> ------------------

SQL> -- Вправа №64

SQL> ------------------

SQL> SELECT должность, MAX(оклад)

2FROM служ

3HAVING MAX(оклад) >= 3000

4GROUP BY должность; ДОЛЖНОСТЬ MAX(ОКЛАД)

-------------------- -------------------

аналитик 3000 президент 5000

Варто відзначити, що слово HAVING може стояти як до, так і після GROUP BY, однак рекомендується завжди ставити його після, тому що це логічніше й зручніше для сприйняття. Групи формуються й групові функції обчислюються до виконання умов, що задають в HAVING.

На відміну від слова HAVING, слово WHERE не може бути використане для завдання обмежень на групові функції.

У наведеному нижче прикладі WHERE задане некоректно, й при спробі його виконання буде видана помилка:

SQL> ------------------

SQL> -- Вправа №65

SQL> ------------------

SQL> SELECT отдел_номер, AVG(оклад)

2FROM служ

3WHERE AVG(оклад) > 2000

4GROUP BY отдел_номер; WHERE AVG(оклад) > 2000

*

ошибка в строке 3:

ORA-00934: групповая функция здесь не разрешена

Слово WHERE можна використовувати для завдання обмежень тільки на індивідуальні рядки таблиць. Для задавання обмежень на групи використовується слово HAVING.

SQL> -------------------

SQL> -- Вправа №66

SQL> -------------------
SQL> SELECT		отдел_номер, AVG(оклад)
2	FROM служ
3	GROUP BY отдел_номер
4	HAVING AVG(оклад) > 2000;
ОТДЕЛ_НОМЕР AVG(ОКЛАД)
-----------------------		-----------------
	10	2750
	20	2275

Відмітимо, що обидва слова HAVING і WHERE, кожне для своєї мети,
можуть бути задані в одній команді. Наприклад, групуючи дані за посадами,
можна виключити з розгляду менеджерів.
SQL> ------------------
SQL> -- Вправа №67
SQL> ------------------
SQL> SELECT		должность, AVG(оклад)
2	FROM служ
3	WHERE должность <> 'менеджер'
4	GROUP BY должность
5	HAVING AVG(оклад) > 1000;
ДОЛЖНОСТЬ		AVG(ОКЛАД)
---------------------		-----------------
аналитик		3000
президент		5000
реализатор		1400
телохранитель		1100
шофер		1300

Порядок слідування слів у операторі SELECT

Якщо всі розглянуті слова задані в одній команді, то їхній порядок повинен бути наступним:

SELECT стовпці

FROM таблиці

WHERE критерій_пошуку_на_окремі_рядки

GROUP BY стовпці

HAVING критерій_пошуку_на_групи

ORDER BY стовпці ;

При виконанні команди SELECT, мова SQL використовує слова:

1.WHERE – для вибору й відсіювання індивідуальних рядків таб-лиць (групові функції включені бути не можуть).

2.GROUP BY – для розбивки знайдених рядків на групи.

3.HAVING – для вибору потрібних за критерієм груп.

Функції перетворення

Перерахуємо всі функції:

1. Функція HEXTORAW(string) перетворює символьне подання 16річного значення в еквівалентне байтове подання.

SQL> select HEXTORAW ('8F') from dual;

HE

--

8F

SQL> select HEXTORAW ('acbdf8F') from dual;

HEXTORAW

----------------

0ACBDF8F

2. Функція rawtohex(string) виконує зворотнє перетворення

HEXTORAW.

SQL> select rawtohex('abacd') from dual;

RAWTOHEX('

------------------

6162616364

SQL> select hextoraw ('22f') from dual;

HEXT

-------

022F

SQL> select rawtohex('22f') from dual;

RAWTOH

------------

323266