Використання узагальнюючих функцій мови SQL

Стандарт ISO містить визначення 5 узагальнюючих функцій

Перераховані функції оперують тільки з одним стовпцем таблиці і повертають одне значення. Функції COUNT, MIN, MAX застосовуються до будь-яких полів, а SUM та AVG – тільки до числових.

Якщо до застосування узагальнюючої функції необхідно виключити значення, що дублюються, слід перед ім'ям стовпця у визначенні функції помістити ключове слово DISTINCT. Стандарт ISO допускає використання ключового слова ALL з метою явної вказівки того, що виключати значення, що дублюються, не потрібно, хоча це ключове слово мається на увазі за замовчуванням, якщо ніякі інші визначники не задані. Ключове слово DISTINCT не має змісту для функцій MIN і МАХ. Однак його використання може вплинути на результати виконання функцій SUM і AVG, тому варто заздалегідь обміркувати, чи повинне воно бути присутнім у кожнім конкретному випадку. Крім того, ключове слово DISTINCT у кожнім запиті може бути зазначено не більш одного разу.

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

SELECT tn, priz, COUNT(prof)

FROM spivrob;

Помилка полягає в тому, що в даному запиті відсутня фраза GROUP BY, а звертання до стовпчиків tn, priz у пропозиції SELECT виконується без застосування узагальнюючої функції.

Підрахувати, скільки програмістів на підприємстві

SELECT COUNT(*) AS programisty

FROM spivrob

WHERE prof=’Програміст’;

Видати, кількість програмістів з різними зарплатами на підприємстві

SELECT COUNT(DISTINCT zarpl) AS zarplata

FROM spivrob

WHERE prof=’Програміст’;

Підрахувати, скільки програмістів на підприємстві та яка їх зарплата

SELECT COUNT(tn) AS programistu, SUM(zarpl) AS groshi

FROM spivrob

WHERE prof=’Програміст’;

Підрахувати, мінімальну, максимальну та середню зарплату на підприємстві

SELECT MIN(zarpl) AS minimum, MAX(zarpl) AS maximum, AVG(zarpl) AS serednja

FROM spivrob;

22. Групування результатів (фраза Group), Обмеження на виконання групування (фраза HAVING)

Приведені в попередній лекції приклади зведених даних подібні підсумковим рядкам, звичайно розміщуваним в кінці звітів. В підсумках всі детальні дані звіту стискаються в одну узагальнюючу рядок. Проте дуже часто в звітах вимагається формувати і проміжні підсумки. Для цієї мети в операторі SELECT може указуватися фраза GROUP. Запит, в якому присутня фраза GROUP, називається групуючим запитом, оскільки в ньому групуються дані, одержані в результаті виконання операції SELECT, після чого для кожної окремої групи створюється єдиний сумарний рядок. Стовпці, перераховані у фразі GROUP, називаються групованими стовпцями. Стандарт ISO вимагає, щоб пропозиція SELECT і фраза GROUP були тісно зв'язані між собою. При використовуванні в операторі SELECT фрази GROUP кожний елемент списку в пропозиції SELECT повинен мати єдине значення для всієї групи. Більш того, пропозиція SELECT може включати тільки наступні типи елементів:

• імена стовпців;

• узагальнюючі функції;

• константи;

• вирази, що включають комбінації перерахованих вище елементів.

Всі імена стовпців, приведені в списку пропозиції SELECT, повинні бути присутні і у фразі GROUP — за винятком випадків, коли ім'я стовпця використовується в узагальнюючій функції. Зворотне правило не є справедливим — у фразі GROUP можуть бути присутні імена стовпців, відсутні в списку пропозиції SELECT. Якщо спільно з фразою GROUP використовується пропозиція WHERE, то воно обробляється першим, а групуванню піддаються тільки ті рядки, які задовольняють умові пошуку.

Стандартом ISO визначено, що при проведенні групування всі відсутні значення розглядаються як рівні. Якщо два рядки таблиці в одному і тому ж групованому стовпці містять значення NULL і ідентичні значення у всій решті непорожніх групованих стовпців, вони поміщаються в одну і ту ж групу.

Підрахувати, мінімальну, максимальну та середню зарплату по кожній професії

SELECT prof, MIN(zarpl) AS minimum, MAX(zarpl) AS maximum, AVG(zarpl) AS serednja

FROM spivrob

GROUP BY prof

ORDER BY prof;