Добро пожаловать!
Здесь вы можете найти ответ на интересующий вас вопрос в отрасли сайтостроения, познакомится ближе с web технологиями и web стандартами.

Учебник SQL

Урок 9. Суммирование данных

В этом уроке вы узнаете, что такое статистические SQL-функции и как их можно использовать для суммирования данных таблицы.

Использование статистических функций

Часто бывает необходимо просуммировать данные без их выборки, и в SQL предусмотрены для этого специальные функции. SQL-запросы с этими функциями часто используются с целью выборки данных для анализа и создания отчетов. Примерами таких выборок могут послужить:

  • определение числа строк в таблице (либо числа строк, которые удовлетворяют какому-то условию или содержат определенное значение);
  • получение суммы по набору строк в таблице;
  • поиск наибольшего, наименьшего и среднего значений из столбца таблицы (из всех или из каких-то конкретных строк).

В каждом из этих примеров необходимы какие-то итоговые данные по таблице, а не сами данные. Поэтому возвращение реальных данных таблицы было бы пустой тратой времени и ресурсов (не говоря о пропускной способности сети). Итак, все, что вам нужно, – это только итоговая информация.

Чтобы облегчить такой способ извлечения информации, в SQL предусмотрен набор из пяти статистических функций, которые перечислены в табл. 9.1. Эти функции позволяют выполнять все варианты выборки, которые были перечислены выше. В отличие от функций манипулирования данными из предыдущего урока, статистические SQL-функции поддерживаются без особых изменений в большинстве реализаций SQL.

Статистические (итоговые) функции

Функции, обрабатывающие набор строк для подсчета и возвращения одного значения.

Таблица 9.1. Статистические SQL-функции

Функция Описание
AVG() Возвращает среднее значение столбца
COUNT() Возвращает число строк в столбце
МАХ() Возвращает самое большое значение в столбце
MIN() Возвращает самое маленькое значение в столбце
SUM() Возвращает сумму значений столбца

Способы использования каждой из этих функций рассматриваются в следующих разделах.

Функция AVG()

Функция AVG() используется для возвращения среднего значения определенного столбца путем подсчета числа строк в таблице и суммирования их значений. Эту функцию можно использовать для возвращения среднего значения всех столбцов или определенных столбцов или строк.

В первом примере функция AVG() исользуется для возвращения средней цены для всех продуктов таблицы Products: 

SELECT AVG(prod_price) AS avg_price 
FROM Products;

Вывод:

avg_price
---------
6.823333

Выражение SELECT, приведенное выше, возвращает одно значение, avg_price, в котором содержится средняя цена всех продуктов таблицы Products. Здесь avg_price – это псевдоним, описанный в уроке 7, "Создание вычисляемых полей".

Функцию AVG() можно также использовать для нахождения среднего значения определенных столбцов или строк. В следующем примере возвращается средняя цена продуктов, предлагаемых определенным поставщиком: 

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

Вывод:

avg_price
--------- 
6.8650

Этот оператор SELECT отличается от предыдущего только тем, что в нем содержится предложение WHERE. В соответствии с предложением WHERE выбираются только те наименования продуктов, значение vend_id для которых равно DLL01, поэтому значение, возвращенное в столбце с псевдонимом avg_price, является средним только для продуктов этого изготовителя.

Только отдельные столбцы

Функцию AVG() можно использовать только для вычисления среднего значения определенного числового столбца, имя этого столбца должно быть указано в качестве параметра функции. Чтобы получить среднее значение нескольких столбцов, необходимо использовать несколько функций AVG().

Значения NULL

Строки столбца, содержащие значения NULL, игнорируются функцией AVG().

Функция COUNT()

Функция COUNT() подсчитывает число строк. При помощи функции COUNT() можно узнать общее число строк в таблице или число строк, удовлетворяющих определенному критерию.

Эту функцию можно использовать двумя способами:

  • В виде COUNT(*) для подсчета числа строк в таблице независимо от того, содержат столбцы значения NULL или нет.
  • В виде COUNT(column) для подсчета числа строк, которые имеют значения в указанных столбцах, причем значения NULL игнорируются.

В первом примере возвращается общее число имен клиентов, содержащихся в таблице Customers: 

SELECT COUNT(*) AS num_cust 
FROM Customers;

Вывод:

num_cust
-------- 
5

В этом примере функция COUNT(*) используется для подсчета всех строк независимо от их значений. Сумма возвращается в переменную num_cust.

В следующем примере подсчитываются только клиенты, имеющие адреса электронной почты: 

SELECT COUNT(cust_email) AS num_cust 
FROM Customers;

Вывод:

num_cust
-------- 
3

В этом выражении SELECT используется функция COUNT (cust_email) для подсчета только строк, имеющих ненулевое значение в столбце cust_email. В этом примере значение cust_email равно 3 (это означает, что только 3 из 5 клиентов имеют адрес электронной почты).

Значения NULL

Строки столбцов со значениями NULL игнорируются функцией COUNT(), если указано имя столбца, и учитываются, если используется звездочка (*).

Функция МАХ()

Функция МАХ() возвращает самое большое значение из указанного столбца. Для этой функции необходимо указывать имя столбца, как это показано ниже: 

SELECT MAX(prod_price) AS max_price 
FROM Products;

Вывод:

max_price
---------
11.9900

Здесь функция MAX() возвращает цену наиболее дорогого продукта в таблице Products.

Использование функции МАХ() с нечисловыми данными

Несмотря на то, что функция МАХ() обычно используется для поиска наибольшего числового значения или даты, многие (но не все) СУБД позволяют использовать ее для возвращения наибольшего значения из всех столбцов, включая текстовые. При использовании с текстовыми данными функция МАХ() возвращает строку, которая была бы последней, если бы данные были отсортированы по этой строке.

Значения NULL

Строки столбцов со значениями NULL игнорируются функцией МАХ().

Функция MIN()

Функция MIN() производит противоположное по отношению к МАХ() действие – она возвращает наименьшее значение в указанном столбце. Так же, как и для функции МАХ(), для MIN() требуется указать имя столбца, как показано ниже: 

SELECT MIN(prod_price) AS min_price 
FROM Products;

Вывод:

min_price 
---------
3.4900

Здесь MIN() возвращает цену самого дешевого продукта в таблице Products.

Использование функции MIN() с нечисловыми данными

Несмотря на то что функция MIN() обычно используется для поиска наименьшего числового значения или даты, многие (но не все) СУБД позволяют использовать ее для возвращения наименьшего значения из всех столбцов, включая текстовые. При использовании с текстовыми данными функция MIN() возвращает строку, которая была бы первой, если бы данные были отсортированы по этой строке.

Значения NULL

Строки столбцов со значениями NULL игнорируются функцией MIN().

Функция SUM()

Функция SUM() возвращает сумму (общую) значений в определенном столбце.

Ниже приведен пример, демонстрирующий это действие. В таблице OrderItems содержатся предметы заказа, причем каждому предмету соответствует определенное количество заказов. Общее число заказанных продуктов (сумма всех значений переменной quantity) может быть выбрана следующим образом: 

SELECT SUM(quantity) AS item_ordered
FROM OrderItems
WHERE order_item = 20005;

Вывод:

item ordered
------------
200

Функция SUM(quantity) возвращает сумму всех предметов заказа, а предложение WHERE гарантирует, что учитываться будут только необходимые продукты. 

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_item = 20005;

Вывод:

total_price
-----------
1648.0000

Функция SUM(item_price*quantity) возвращает сумму всех цен в заказе, а предложение WHERE гарантирует, что учитываться будут только необходимые продукты.

Вычисления с несколькими столбцами

Все статистические функции можно использовать для выполнения вычислений над несколькими столбцами при помощи стандартных математических операторов, как показано в примере.

Значения NULL

Строки столбцов со значениями NULL игнорируются функцией SUM().

Статистические вычисления для отдельных значений

Все пять статистических функций могут быть использованы двумя способами:

  • для вычисления во всех строках при указании аргумента ALL или без указания какого-либо аргумента (так как ALL является аргументом по умолчанию);
  • для указания отдельных значении при помощи аргумента DISTINCT.

ALL no умолчанию

Аргумент ALL не обязательно указывать, так как он является аргументом по умолчанию. Если не указан аргумент DISTINCT, то подразумевается аргумент ALL.

Не в Access

Microsoft Access не поддерживает использование аргумента DISTINCT в статистических функциях, поэтому следующий пример не будет работать в Access.

В следующем примере используется функция AVG() для возвращения средней цены продуктов, предлагаемых определенным поставщиком. Это такой же оператор SELECT, как и предыдущий, но с использованием ключевого слова DISTINCT – при вычислении среднего значения учитываются только определенные цены. 

SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'LL01';

Вывод:

avg_price
--------- 
4.2400

В этом примере вследствие использован ия ключевого слова DISTINCT значение avg_price получается более высоким, так как в таблице есть несколько предметов с одинаково низкой ценой. Не учитывая их, мы получаем более высокую среднюю стоимость.

Внимание

Ключевое слово DISTINCT можно использовать с функцией COUNT () только в том случае, если указано имя столбца. Его нельзя использовать с функцией COUNT(*). Аналогично, DISTINCT нужно использовать с именем столбца, но не с выражением.

Использование ключевого слова DISTINCT с функциями MIN() и MAX()

Несмотря на то что ключевое слово DISTINCT технически можно использовать с функциями MIN() и МАХ(), реальной необходимости в этом нет. Минимальные и максимальные значения в столбце будут теми же, независимо от того, указаны определенные значения или нет.

Предикаты

Помимо ключевых слов DISTINCT и ALL, некоторые СУБД поддерживают предикаты, такие как ТОР И ТОР PERCENT, позволяющие выполнять действия над подмножествами результатов запроса. Обратитесь к документации вашей СУБД, чтобы точно узнать, какие предикаты вы можете использовать.

Комбинирование статистических функций

Во всех примерах применения статистических функций, приведенных до сих пор, указывалась только одна функция. Но на самом деле операторы SELECT могут содержать столько статистических функций, сколько нужно. Рассмотрим пример: 

SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min, 
MAX(prod_price) AS price_max, 
AVG(prod_price) AS proce_avg
FROM Products;

Вывод:

num_items  price_min  price_max  price_avg
------------------------------------------
9          3.4900     11.9900    6.823333

В одном операторе SELECT используются сразу четыре статистические функции и возвращаются четыре значения (число элементов в таблице Products, самая высокая, самая низкая и средняя их стоимость).

Псевдонимы

При указании псевдонимов для хранения результатов статистической функции старайтесь не использовать реальных названий столбцов в таблице, поскольку во многих реализациях SQL такое поведение не приветствуется – вы получите сообщение об ошибке.

Резюме

Статистические функции используются для получения итоговых данных. В SQL поддерживается пять статистических функций, каждая из которых может использоваться несколькими способами для возвращения только необходимых в данный момент результатов. Эти функции разработаны для повышения эффективности работы, обычно они возвращают результат гораздо быстрее, чем если бы вы производили вычисления в своем клиентском приложении.