Sql узагальнення даних за допомогою агрегатних функцій
У цьому розділі ви перейдете про простого використання запитів до вилучення значень з бази даних (БД) і визначте, як можна використовувати ці значення, щоб отримати з них інформацію. Це робиться за допомогою агрегатних (узагальнюючих) функцій, які беруть групи значень з поля і зводять їх до одиночного значення.
Ви дізнаєтеся, як використовувати ці функції, як визначити групи значень, до яких вони будуть застосовуватися, і як визначити, які групи вибираються для виводу.
Ви побачите також, за яких умов ви зможете об'єднати значення поля з цієї отриманою інформацією в одному запиті.
ЩО ТАКЕ агрегатні функції?
Запити можуть виробляти узагальнене групове значення полів точно так же, як і значення одного поля. Це робиться за допомогою агрегатних функцій. Агрегатні функції виробляють одиночне значення для всієї групи таблиці.
Ось список цих функцій:
COUNT видає кількість рядків або НЕ-NULL значень полів, які вибрав запит. SUM видає арифметичну суму всіх вибраних значень даного поля. AVG видає усереднення всіх обраних значень даного поля. MAX видає найбільше з усіх вибраних значень даного поля. MIN видає найменше з усіх вибраних значень даного поля.
ЯК ВИКОРИСТОВУВАТИ агрегатні функції?
Агрегатні функції використовуються, подібно іменам полів в реченні SELECT-запиту, але з одним винятком: вони беруть імена полів як аргументи.
Тільки числові поля можуть використовуватися з SUM і AVG.
З функціями COUNT, MAX і MIN можуть використовуватися і числові, і символьні поля.
При використанні з символьними полями, MAX і MIN транслюватимуть їх в еквівалент ASCII, який повинен повідомляти, що MIN означатиме перше, а MAX останнє значення в алфавітному порядку (алфавітний упорядкування обговорюється більш детально в Главі 4).
Щоб знайти SUM всіх наших покупок в таблиці Замовлень, ми можемо ввести наступний запит, з виведенням на рисунку 6.1:
Це, звичайно, відрізняється від вибору поля, при якому повертається одиночне значення, незалежно від того, скільки рядків знаходиться в таблиці. Через це агрегатні функції і поля не можуть вибиратися одночасно, якщо не буде використано пропозицію GROUP BY (описане далі).
Знаходження усередненої суми - схожа операція (висновок наступного запиту показаний на рисунку 6.2):
СПЕЦІАЛЬНИЙ АТРИБУТ COUNT
Функція COUNT дещо відрізняється від всіх інших. Вона вважає число значень в даному стовпці або число рядків в таблиці. Коли вона вважає значення стовпця, вона використовується з DISTINCT, щоб виробляти рахунок чисел різних значень в даному полі. Ми могли б використовувати її, наприклад, щоб порахувати кількість продавців, описаних в даний час в таблиці Замовлень (висновок показаний на рисунку 6.3):
ВИКОРИСТАННЯ DISTINCT
Зверніть увагу в вищезгаданому прикладі, що DISTINCT, супроводжуваний ім'ям поля, з яким він застосовується, поміщений в круглі дужки, але не відразу після SELECT, як раніше. Такого використання DISTINCT з COUNT, що застосовується до індивідуальних стовпцями, вимагає стандарт ANSI, але велика кількість програм не пред'являють такої вимоги.
Ви можете виконувати кілька підрахунків (COUNT) в полях за допомогою DISTINCT в одиночному запиті, що, як ми бачили в Главі 3. не виконувалося, коли ви вибирали рядки за допомогою DISTINCT.
DISTINCT може використовуватися таким чином з будь-агрегатної функцією, але найбільш часто він використовується з COUNT. З MAX і MIN це просто не буде мати ніякого ефекту, а SUM і AVG ви зазвичай застосовуєте для включення повторюваних значень, так як вони ефективніше загальних і середніх значень всіх стовпців.
ВИКОРИСТАННЯ COUNT СО рядками, А НЕ значення
Щоб підрахувати загальну кількість рядків в таблиці, використовуйте функцію COUNT із зірочкою замість імені поля, як в наступному прикладі, висновок з якого показаний на рисунку 6.4:
COUNT із зірочкою включає і NULL, і дублікати; з цієї причини DISTINCT не може бути використаний. DISTINCT може виробляти більш високі числа, ніж COUNT особливого поля, який видаляє всі
рядки, які мають надлишкові або NULL-дані в цьому полі. DISTINCT непридатний c COUNT (*), тому що він не має ніякого дії в добре розробленою і підтримуваної БД. У такій БД не повинно бути ні таких рядків, які були б повністю порожніми, ні дублікатів (перші не містять ніяких даних, а останні повністю надлишкові). Якщо все-таки є повністю порожні або надлишкові рядки, ви, ймовірно, не захочете, щоб COUNT приховав від вас цю інформацію.
ВКЛЮЧЕННЯ ДУБЛІКАТІВ В агрегатних функцій
Агрегатні функції можуть також (в більшості реалізацій) використовувати аргумент ALL, який поміщається перед ім'ям поля, подібно DISTINCT, але означає протилежне: включати дублікати. ANSI технічно не дозволяє цього для COUNT, але багато реалізації послаблюють це обмеження.
Відмінності між ALL і * при використанні з COUNT:
- ALL використовує ім'я поля як аргумент.
- ALL не може підрахувати NULL-значення.
Поки * є єдиним аргументом який включає NULL-значення, і тільки він використовується з COUNT; функції, крім COUNT, ігнорують NULL-значення в будь-якому випадку.
Наступна команда підрахує (COUNT) число не-NULL-значень в поле rating в таблиці Замовників (включаючи повторення):
АГРЕГАТИ, ПОБУДОВАНІ НА Скалярним вираженні
До цього ви використовували агрегатні функції з одиночними полями як аргументами. Ви можете також використовувати агрегатні функції з аргументами, які складаються з скалярних виразів, що включають одне або більше полів. (Якщо ви це робите, DISTINCT забороняється.)
Припустимо, що таблиця Замовлень має ще один стовпець, який зберігає попередній несплачений баланс (поле blnc) для кожного замовника. Ви повинні знайти цей поточний баланс додаванням суми придбань до попереднього балансу.
Ви можете знайти найбільший несплачений баланс наступним чином:
Для кожного рядка таблиці цей запит буде складати blnc і amt для даного замовника і вибирати найбільше значення, яке він знайде. Звичайно, поки замовники можуть мати кілька замовлень, їх несплачений баланс оцінюється окремо для кожного замовлення. Можливо, замовлення з більш пізньою датою матиме найбільший несплачений баланс. Інакше старий баланс повинен бути обраний, як в запиті вище.
Фактично є велика кількість ситуацій в SQL, де можна використовувати скалярні вирази з полями або замість полів, як ви побачите в Главі 7.
ПРОПОЗИЦІЯ GROUP BY
Пропозиція GROUP BY дозволяє вам визначати підмножина значень в особливому полі в термінах іншого поля і застосовувати агрегатну функцію до підмножини. Це дає можливість об'єднувати поля і агрегатні функції в єдиній пропозиції SELECT.
Наприклад, припустимо, що ви хочете знайти найбільшу суму продажу, отриману кожним продавцем. Ви можете зробити роздільний запит для кожного з них, вибравши MAX (amt) з таблиці Замовлень для кожного значення поля snum. GROUP BY, однак, дозволить вам помістити все в одну команду:
Висновок для цього запиту показаний на рисунку 6.5.
GROUP BY застосовує агрегатні функції, незалежно від серій груп, які визначаються за допомогою значення поля в цілому. У цьому випадку кожна група складається з усіх рядків з тим же самим значенням поля snum, і MAX функція застосовується окремо для кожної такої групи. Це значення поля, до якого застосовується GROUP BY, має, за визначенням, тільки одне значення на групу виводу так само, як це робить агрегатна функція. Результатом є сумісність, яка дозволяє агрегатам і полях об'єднуватися таким чином.
Ви можете також використовувати GROUP BY з декількома полями. Удосконалюючи вищезгаданий приклад, припустимо, що ви хочете побачити найбільшу суму продажів, одержувану кожним продавцем кожен день. Щоб зробити це, ви повинні згрупувати таблицю Замовлень по датах продавців і застосувати функцію MAX до кожної такої групи:
Висновок для цього запиту показаний на рисунку 6.6.
Звичайно ж, порожні групи в дні, коли поточний продавець не мав замовлень, не відображатимуться у висновку.
ПРОПОЗИЦІЯ HAVING
Припустимо, що в попередньому прикладі ви хотіли б побачити тільки максимальну суму придбань, значення якої вище $ 3000.00. Ви не зможете використовувати агрегатную функцію в реченні WHERE (якщо ви не використовуєте підзапит, описаний пізніше), тому що предикати оцінюються в термінах одиночного рядка, а агрегатні функції оцінюються в термінах груп рядків. Це означає, що ви не зможете зробити що-небудь подібне наступного:
Це буде відхиленням від суворої інтерпретації ANSI. Щоб побачити максимальну вартість придбань понад $ 3000.00, ви можете використовувати пропозицію HAVING.
Пропозиція HAVING визначає критерії, які використовуються, щоб видаляти певні групи з виводу, точно так же, як пропозиція WHERE робить це для окремих рядків.
Правильною командою буде наступна:
Висновок для цього запиту показаний на рисунку 6. 7.
Аргументи в пропозиції HAVING слідують тим же самим правилам, що і в реченні SELECT, що складається з команд, що використовують GROUP BY. Вони повинні мати одне значення на групу виводу.
Наступна команда буде заборонена:
Поле оdate не може бути викликано пропозицією HAVING, тому що воно може мати (і дійсно має) більше ніж одне значення на групу виводу. Щоб уникати такої ситуації, пропозиція HAVING має посилатися тільки на агрегати і поля, обрані GROUP BY. Є правильний спосіб зробити вищезгаданий запит (висновок показаний на рисунку 6.8):
Як говорилося раніше, HAVING може використовувати тільки аргументи, які мають одне значення на групу виводу. Практично посилання на агрегатні функції - найбільш загальні, а й поля, обрані за допомогою GROUP BY, також допустимі. Наприклад, ми хочемо побачити найбільші замовлення для Serres і Rifkin:
Висновок для цього запиту показаний на рисунку 6.9.
НЕ робить вкладення АГРЕГАТІВ
В суворої інтерпретації ANSI SQL ви не можете використовувати агрегат агрегату. Припустимо, що ви хочете з'ясувати, в який день була найбільша сума продажів. Якщо ви спробуєте зробити так,
то ваша команда буде, ймовірно, відхилена. (Деякі реалізації не наказують цього обмеження, що вигідно, тому що вкладені агрегати можуть бути дуже корисні, навіть якщо вони і кілька проблематичні.) У вищезгаданій команді, наприклад, SUM повинен застосовуватися до кожної групи поля odate, а MAX - до всіх груп, виробляють одиночне значення для всіх груп. Однак пропозиція GROUP BY має на увазі, що повинна бути одна рядок виводу для кожної групи поля odate.
Тепер ви використовуєте запити дещо по-іншому. Здатність отримувати, а не просто розміщувати значення, дуже потужна. Це означає, що ви не обов'язково повинні стежити за певною інформацією, якщо ви можете сформулювати запит так, щоб її отримати. Запит буде давати вам похвилинні результати, в той час як таблиця загального або середнього значень буде хороша тільки деякий час після її модифікації. Це не повинно наводити на думку, що агрегатні функції можуть повністю витіснити потреба у відстеженні інформації, такої, наприклад, як ця.
Ви можете застосовувати ці агрегати для груп значень, певних пропозицією GROUP BY. Ці групи мають значення поля в цілому і можуть постійно перебувати всередині інших груп, які мають значення поля в цілому. У той же час, предикати ще використовуються, щоб визначати, які рядки агрегатної функції застосовуються.
Об'єднані разом, ці особливості роблять можливим виробляти агрегати, засновані на чітко визначених подмножествах значень в поле. Потім ви можете визначати іншу умову для виключення певних результатів груп з пропозицією HAVING.
Тепер, коли ви стали знавцем того, як запит виробляє значення, ми покажемо вам, в Главі 7, чт # x0301; про ви можете робити зі значеннями, які він виробляє.