Использование статистических функций
9. Использование статистических функций
В языке SQL имеются функции, которые используются с целью выборки данных для анализа и создания отчетов. Примерами таких выборок могут послужить:
· Определение числа строк в таблице, которые удовлетворяют какому-то условию или содержат определенное значение;
· Получение суммы по выбору строк в таблице;
· Поиск наибольшего, наименьшего и среднего значений из столбца таблицы из всех строк или из каких-то конкретных строк.
В этих выборках требуются какие-то итоговые данные по таблице, а не сами данные. В SQL предусмотрен набор из пяти статистических функций, которые поддерживаются в большинстве реализаций SQL. Это следующие функции:
AVG() Возвращает среднее значение столбца
COUNT() Возвращает число строк в столбце
MAX() Возвращает самое большое значение в столбце
Рекомендуемые материалы
MIN() Возвращает самое маленькое значение в столбце
SUM() Возвращает сумму значений столбца
9.1. Функция AVG
Функция возвращает среднее значение определенного столбца путем подсчета числа строк в таблице и суммирование их значений.
Эту функцию можно использовать для возвращения среднего значения всех столбцов или определенных столбцов или строк.
В примере требуется возвратить среднюю цену для всех продуктов таблицы Products.
SELECT AVG(prod_price) AS avg_price
FROM Products
--------------------------
avg_price
6.82p
В следующем примере требуется вернуть среднюю цену продуктов, предлагаемых поставщиком DLL01. В оператрое используется предложение WHERE/
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’
--------------------------
avg_price
3.87
Функция AVG можно использовать только для вычисления среднего значения определенного столбца, имя которого указывается в качестве параметра функции. Чтобы получить среднее значение нескольких столбцов, необходимо использовать несколько функций AVG.
Функция AVG игнорирует строки столбца, содержащие значения NULL.
9.2. Функция COUNT
Функция подсчитывает строк в таблице. Она позволяет подсчитать общее число строк в таблице или число строк, удовлетворяющих определенному критерию. Функцию можно использовать двумя способами:
· В виде COUNT(*) для подсчета числа строк в таблице независимо от того, содержат столбцы значения NULL или нет.
· В виде COUNT(column) для подсчета числа строк, которые имеют значения в указанных столбцах, причем значения NULL игнорируются.
Требуется вернуть общее число имен клиентов, содержащихся в таблице Customers.
SELECT COUNT(*) AS num_cust
FROM Customers
--------------------------
num_cust
5
Требуется подсчитать только клиентов, имеющих адреса электронной почты.
SELECT COUNT(cust_email) AS num_cust
FROM Customers
--------------------------
num_cust
3
Строки столбцов со значениями NULL игнорируются функцией COUNT(), если указано имя столбца, и учитываются, если используется звездочка.
9.3. Функция MAX
Функция возвращает самое большое значение из указанного столбца.
Требуется вернуть цену наиболее дорогого продукта в таблице Products.
SELECT MAX(prod_price) AS max_price
FROM Products
--------------------------
max_price
11.99p.
Функция MAX() обычно используется для поиска наибольшего числового значение или даты. Некоторые СУБД позволяют использовать ее для возвращения наибольшего значения из всех столбцов, включая и текстовые. При использовании с текстовыми данными функция MAX() возвращает строку, которая была бы последней, если бы данные были отсортированы по этой строке.
SELECT MAX(prod_name) AS max_name
FROM Products
--------------------------
max_name
Raggedy Ann
Функция MAX() игнорирует строки столбцов со значениями NULL/
9.4. Функция MIN
Функция MIN() возвращает наименьшее значение в указанном столбце.
Требуется вернуть цену самого дешевого продукта в таблице Products.
SELECT MIN(prod_price) AS min_price
FROM Products
--------------------------
min_price
3.49p.
При использовании MIN() с текстовыми данными возвращается строка, которая была бы первой, если бы данные были отсортированы по этой строке.
SELECT MIN(prod_name) AS min_name
FROM Products
--------------------------
min_name
12 inch teddy bear
Функция MIN() игнорирует строки столбцов со значениями NULL/
9.5. Функция SUM
Функция SUM() возвращает сумму значений в определенном столбце.
Требуется определить общее число заказанных продуктов в таблице OrderItems.
SELECT SUM(quantity) AS num_ordered
FROM OrderItems
WHERE order_num =20005
--------------------------
num_ordered
200
Требуется определить сумму всех цен в заказе 20005.
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num =20005
--------------------------
total_price
1648.00p.
Функция SUM() игнорирует строки столбцов со значением NULL.
9.6. Статистические вычисления для отдельных значений
Все пять статистические функции могут использоваться двумя способами:
· для вычисления во всех строках при указании аргумента ALL (по умолчанию) или без указания какого-либо аргумента;
· для указания отдельных значений при помощи аргумента DISTINCT.
В примере возвращается средняя цена продуктов, предлагаемых поставщиком DLL01.
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id =’DLL01’
--------------------------
avg_price
3.87p.
Использование ключевого слова DISTINCT значение avg_price получится более высоким, так как в таблице есть несколько предметов с одинаково низкой ценой и будет учитываться эта цена один раз.
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id =’DLL01’
--------------------------
avg_price
4.24p.
Access не поддерживает использование аргумента DISTRINCT в статистических функциях.
Ключевое слово DISTINCT можно использовать с функцией COUNT() только в том случае, если указано имя столбца, если указана *, то использовать нельзя.
9.7. Комбинирование статистических функций
Оператор SELECT могут содержать столько статистических функций, сколько нужно.
Вычислить в таблице Products самую высокую, самую низкую и среднюю стоимость продукта.
SELECT COUNT(*) AS num_items,
MIN(prod_price) AS price_min,
Лекция "1.7. Особенности защиты населения от терроризма" также может быть Вам полезна.
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM Products;
--------------------------
num_items price_min price_max price_avg
9 3.49p. 11.99p. 6.82p.
