Використання агрегатні функції
T-SQL надає агрегатні функції, такі як SUM, MAX і AVG, для виконання обчислень, які приймають кілька значень і повертають один результат.
Робота з агрегатним функціоналом
Більшість запитів, які ми дивилися, працюють у рядку одночасно, використовуючи речення WHERE для фільтрування рядків. Кожен повернутий рядок відповідає одному рядку у вихідному наборі даних.
Багато агрегатні функції надаються в SQL Server. У цьому розділі ми розглянемо найпоширеніші функції, як-от SUM, MIN, MAX, AVG і COUNT.
Працюючи з агрегатним функціоналом, потрібно враховувати такі моменти:
- Агрегатні функції повертають одне (скалярне) значення та можуть використовуватися в інструкціях SELECT практично в будь-якому місці, де можна використовувати одне значення. Наприклад, ці функції можна використовувати в реченнях SELECT, HAVING і ORDER BY. Однак їх не можна використовувати в реченні WHERE.
- Агрегатні функції ігнорують NULLs, за винятком випадків, коли використовується функція COUNT(*).
- Агрегатні функції в списку SELECT не мають заголовка стовпця, якщо ви не надаєте псевдонім за допомогою as.
- Агрегатні функції в списку SELECT працюють на всіх рядках, переданих до операції SELECT. Якщо речення GROUP BY відсутнє, усі рядки, які відповідають будь-якому фільтру в реченні WHERE, буде підсумовано. Докладні відомості про GROUP BY див. в наступному розділі.
- Якщо ви не використовуєте ФУНКЦІЮ GROUP BY, не слід об'єднувати агрегатні функції зі стовпцями, не включеними до функцій в одному списку SELECT.
Щоб вийти за рамки вбудованих функцій, SQL Server надає механізм для користувацьких агрегатних функцій за допомогою середовища виконання .NET Common Language Runtime (CLR). Ця стаття виходить за рамки цього модуля.
Вбудовані агрегатні функції
Як уже згадувалося, Transact-SQL надає багато вбудованих агрегатних функцій. Часто використовувані функції включають:
Ім'я функції
синтаксису
опис
СУМА
SUM(вираз)
Підсумує всі ненульові числові значення в стовпці.
СЕРЕДНЄ ЗНАЧЕННЯ
AVG(вираз)
Обчислює всі ненульові числові значення в стовпці (сума або кількість).
ХВ
MIN(вираз)
Повертає найменше число, найраніший рядок дати й часу або перший рядок (за правилами сортування зіставлення).
МАКС
MAX(вираз)
Повертає найбільше число, останню дату й час або останній рядок (за правилами сортування зіставлення).
COUNT або COUNT_BIG
COUNT(*) або COUNT(вираз)
З (*) підраховує всі рядки, включно зі значеннями NULL. Якщо стовпець указано як вираз, повертає кількість рядків, які не є null-значеннями для цього стовпця. Функція COUNT повертає аргумент int; COUNT_BIG повертає big_int.
Щоб використовувати вбудований агрегат у реченні SELECT, розгляньте наведений нижче приклад у зразку бази даних MyStore :
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Результати цього запиту виглядають приблизно так:
Середня ціна
Мінімальна ціна
Максимальна ціна
744.5952
2.2900
3578.2700
Зверніть увагу, що в наведеному вище прикладі підсумовано всі рядки з таблиці Production.Product . Ми могли б легко змінити запит, щоб повернути середні, мінімальні та максимальні ціни для продуктів у певній категорії, додавши речення WHERE, наприклад:
SELECT AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;
Під час використання агрегатів у реченні SELECT усі стовпці, на які посилається список SELECT, мають використовуватися як входи для агрегатних функцій або посилатися на речення GROUP BY.
Розглянемо такий запит, який намагається включити поле ProductCategoryID до сукупних результатів:
SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;
Виконання цього запиту призводить до наведеної нижче помилки
Msg 8120, рівень 16, стан 1, рядок 1
Стовпець "Production.ProductCategoryID" неприпустимий у списку вибору, оскільки він не міститься ні в агрегатній функції, ні в реченні GROUP BY.
Запит розглядає всі рядки як одну агреговану групу. Таким чином, усі стовпці потрібно використовувати як входи для агрегатних функцій.
У попередніх прикладах ми агрегували числові дані, наприклад ціну та кількість у попередньому прикладі. Деякі агрегатні функції також можна використовувати для зведення даних про дату, час і символи. У наведених нижче прикладах показано використання агрегатів із датами та символами:
Цей запит повертає ім'я та останню компанію за назвою, використовуючи MIN і MAX:
SELECT MIN(CompanyName) AS MinCustomer,
MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;
Цей запит повертає перше та останнє значення для CompanyName у послідовності зіставлення бази даних, яка в цьому випадку має алфавітний порядок:
MinCustomer
MaxCustomer
Магазин велосипедів
Жовтий велосипед компанії
Інші функції можуть бути вкладені в агрегатні функції.
Наприклад, скалярна функція YEAR використовується в наведеному нижче прикладі, щоб повернути лише частину року дати замовлення, перш ніж буде оцінено MIN і MAX:
SELECT MIN(YEAR(OrderDate)) AS Earliest,
MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;
Ранні
Останні
2008
2021
Функції MIN і MAX також можна використовувати з даними дат, щоб повернути найраніші та найновіші хронологічні значення. Однак avg і SUM можна використовувати лише для числових даних, які включають цілі числа, грошові, плаваючі та десяткові типи даних.
Використання DISTINCT із агрегатним функціоналом
Слід пам'ятати про використання функції DISTINCT у реченні SELECT для видалення повторюваних рядків. Якщо використовується з агрегатною функцією, ФУНКЦІЯ DISTINCT видаляє повторювані значення зі стовпця вводу, перш ніж обчислювати зведене значення. ФУНКЦІЯ DISTINCT корисна під час зведення унікальних екземплярів значень, наприклад клієнтів у таблиці замовлень.
У наведеному нижче прикладі повертається кількість клієнтів, які розмістили замовлення незалежно від кількості замовлень, які вони розмістили:
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;
COUNT(<some_column>) лише підраховує кількість рядків, які мають певне значення в стовпці. Якщо null-значення відсутні, функція COUNT(<some_column>) буде такою ж, як COUNT(*). Функція COUNT (DISTINCT <some_column>) підраховує кількість різних значень у стовпці.
Використання агрегатні функції з null-значенням
Важливо знати про можливу присутність NUL-адрес у даних, а також про те, як NULL взаємодіє з компонентами запиту T-SQL, включно з агрегатними функціями. Є кілька зауважень, про які слід звернути увагу:
- За винятком функції COUNT, яка використовується з параметром (*), агрегатні функції T-SQL ігнорують NULLs. Наприклад, функція SUM додасть лише значення, не пов'язаних із null-значеннями. NULLs не обчислюється до нуля. Count(*) обчислює кількість усіх рядків незалежно від значення або незначення в будь-якому стовпці.
- Наявність NUL-адрес у стовпці може призвести до неточних обчислень для AVG, які підсумовують лише заповнені рядки та розділяють цю суму на кількість рядків, не пов'язаних із NULL. Результати можуть відрізнятися між AVG(<стовпцем>) і (SUM(<стовпець>)/COUNT(*)).
Наприклад, розглянемо таку таблицю з іменем t1:
C1
C2
1
НУЛЬ
2
10
3
20
4
30
5
40
6
50
Цей запит ілюструє різницю між тим, як AVG обробляє NULL і як можна обчислити середнє значення за допомогою обчислюваного стовпця SUM/COUNT(*):
SELECT SUM(c2) AS sum_nonnulls,
COUNT(*) AS count_all_rows,
COUNT(c2) AS count_nonnulls,
AVG(c2) AS average,
(SUM(c2)/COUNT(*)) AS arith_average
FROM t1;
Результатом буде:
sum_nonnulls
count_all_rows
count_nonnulls
середній
arith_average
150
6
5
30
25
У цьому набору результатів стовпець " середнє " – це сукупність, яка внутрішньо отримує суму 150 і ділить на кількість ненульових значень у стовпці c2. Обчислення буде 150/5 або 30. Стовпець під назвою arith_average явно ділить суму на кількість усіх рядків, тому обчислення – 150/6 або 25.
Якщо потрібно підсумувати всі рядки (null чи ні), радимо замінити NULLs на інше значення, яке не ігноруватиметься агрегатними функціями. Для цього можна використовувати функцію COALESCE.