Обчислення і підведення підсумків в запитах

Побудова обчислюваних полів

У загальному випадку для створення обчислюваного (похідного) поля в списку SELECT слід вказати деякий вираз мови SQL. У цих виразах застосовуються арифметичні операції додавання, віднімання, множення і ділення, а також вбудовані функції мови SQL. Можна вказати ім'я будь-якого стовпця (поля) таблиці або запиту, але використовувати ім'я стовпця тільки тієї таблиці або запиту, які вказані в списку пропозиції FROM відповідної інструкції. При побудові складних виразів можуть знадобитися дужки.

Стандарти SQL дозволяють явно задавати імена стовпців результуючої таблиці, для чого застосовується фраза AS.

Приклад 6.1. Розрахувати загальну вартість для кожної угоди. Цей запит використовує розрахунок результуючих стовпців на основі арифметичних виразів.

Приклад 6.1. Розрахунок загальної вартості для кожної угоди.

Приклад 6.2. Отримати список фірм із зазначенням прізвища та ініціалів клієнтів.

Приклад 6.2. Отримання списку фірм із зазначенням прізвища та ініціалів клієнтів.

У запиті використана вбудована функція Left. що дозволяє вирізати в текстовій змінної один символ зліва в даному випадку.

Приклад 6.3. Отримати список товарів із зазначенням року і місяця продажу.

Приклад 6.3. Отримання списку товарів із зазначенням року і місяця продажу.

У запиті використані вбудовані функції Year і Month для виділення року і місяця з дати.

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

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

Користувачеві доступні наступні основні підсумкові функції:

Count (Вираз) - визначає кількість записів в вихідному наборі SQL-запиту;
Min / Max (Вираз) - визначають найменше та найбільше з безлічі значень в деякому полі запиту;
Avg (Вираз) - ця функція дозволяє розрахувати середнє значення безлічі значень, що зберігаються в певному полі відібраних запитом записів. Воно є арифметичним середнім значенням, тобто сумою значень, поділеній на їх кількість.
Sum (Вираз) - обчислює суму безлічі значень, що містяться в певному полі відібраних запитом записів.

Найчастіше в якості вираження виступають імена стовпців. Вираз може обчислюватися і за значеннями кількох таблиць.

Всі ці функції оперують зі значеннями в єдиному стовпці таблиці або з арифметичним виразом і повертають єдине значення. Функції COUNT. MIN і MAX застосовні як до числовим, так і до нечислове полях, тоді як функції SUM і AVG можуть використовуватися тільки в разі числових полів, за винятком COUNT (*). При обчисленні результатів будь-яких функцій спочатку виключаються всі порожні значення, після чого необхідна операція застосовується тільки до решти конкретним значенням стовпця. Варіант COUNT (*) - особливий випадок використання функції COUNT. його призначення полягає в підрахунку всіх рядків в результуючій таблиці, незалежно від того, містяться там порожні, що дублюються або будь-які інші значення.

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

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

Приклад 6.4. Визначити першу за алфавітом назву товару.

Приклад 6.4. Визначення першого за алфавітом назви товару.

Приклад 6.5. Визначити кількість угод.

Приклад 6.6. Визначити сумарну кількість проданого товару.

Приклад 6.6. Визначення сумарної кількості проданого товару.

Приклад 6.7. Визначити середню ціну проданого товару.

Приклад 6.7. Визначення середньої ціни проданого товару.

Приклад 6.8. Підрахувати загальну вартість проданих товарів.

Приклад 6.8. Підрахунок загальної вартості проданих товарів.

Пропозиція GROUP BY

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

Всі імена полів, наведені в списку пропозиції SELECT. повинні бути присутніми і у фразі GROUP BY - за винятком випадків, коли ім'я стовпця використовується в підсумковій функції. Зворотне правило не є справедливим - у фразі GROUP BY можуть бути імена стовпців, відсутні в списку пропозиції SELECT.

Якщо спільно з GROUP BY використовується пропозицію WHERE. то воно обробляється першим, а гуртування піддаються тільки ті рядки, які задовольняють умові пошуку.

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

Приклад 6.9. Обчислити середній обсяг покупок, зроблених кожним покупцем.

Приклад 6.9. Обчислення середнього обсягу покупок, зроблених кожним покупцем.

Фраза "кожним покупцем" знайшла своє відображення в SQL-запиті у вигляді пропозиції GROUP BY Кліент.Фамілія.

Приклад 6.10. Визначити, на яку суму був проданий товар кожного найменування.

Приклад 6.10. Визначення, на яку суму був проданий товар кожного найменування.

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

Приклад 6.11. Підрахунок кількості угод, здійснених кожною фірмою.

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

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

Приклад 6.13. Визначити сумарну вартість кожного товару за кожний місяць.

Приклад 6.13. Визначення сумарної вартості кожного товару за кожний місяць.

Приклад 6.14. Визначити сумарну вартість кожного товару першого сорту за кожен місяць.

Приклад 6.14. Визначення сумарної вартості кожного товару першого сорту за кожен місяць.

пропозиція HAVING

За допомогою HAVING відображаються всі попередньо згруповані за допомогою GROUP BY блоки даних, що задовольняють заданим в HAVING умов. Це додаткова можливість "профільтрувати" вихідний набір.

Умови в HAVING відрізняються від умов в WHERE:

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

Приклад 6.15. Визначити фірми, у яких загальна кількість угод перевищила три.

Приклад 6.15. Визначення фірм, у яких загальна кількість угод перевищила три.

Приклад 6.16. Вивести список товарів, проданих на суму понад 10000 руб.

Приклад 6.16. Вид списку товарів, проданих на суму понад 10000 руб.

Приклад 6.17. Вивести список товарів, проданих на суму понад 10000 без зазначення суми.

Приклад 6.17. Вид списку товарів, проданих на суму понад 10000 без зазначення суми.