В ходе этого урока мы рассмотрим те функции SQL, которые предназначены для подведения итогов. С их помощью можно осуществлять достаточно широкий спектр математических операций с данными.
Основными на этом уроке будут следующие темы.
• Что такое функции?
• Использование функций
• Когда следует использовать функции?
• Использование итоговых функций
• Суммирование данных с помощью итоговых функций
• Результаты использования функций
Функции в SQL представляются ключевыми словами и используются для математических преобразований данных в столбце с целью соответствующего представления данных при выводе. Функция — это команда, всегда используемая в связи с именем столбца или выражением. В SQL имеется несколько типов функций. В ходе этого урока мы рассмотрим итоговые функции. Итоговая функция — это функция, используемая в операторе SQL для получения итоговой информации типа общего числа строк, сумм или среднего значения.
В ходе этого урока мы обсудим следующие итоговые функции.
• COUNT
• SUM
• MAX
• MIN
• AVG
Следующие запросы показывают данные, которые используются для большинства примеров данного урока.
SELECT *
FROM PRODUCTS_TBL;
PROD_ID PROD_DESC
COST
11235 КОСТЮМ ВЕДЬМЫ 29.99
222 ПЛАСТИКОВЫЕ ТЫКВЫ 7.75
13 ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ 1.1
90 ФОНАРИ 14.5
15 КОСТЮМЫ В АССОРТИМЕНТЕ 10
9 СЛАДКАЯ КУКУРУЗА 1.35
6 ТЫКВЕННЫЕ КОНФЕТЫ 1.45
87 ПЛАСТИКОВЫЕ ПАУКИ 1.05
119 МАСКИ В АССОРТИМЕНТЕ 4.95
1234 ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ 5.95
2345 ПОЛОМКА ИЗ ДУБА 59.99
11 строк выбраны.
Как видно из следующего запроса, не все служащие имеют номера пейджеров.
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER
FROM EMPLOYEE_TBL;
EKP_ID LAST_NAME FIRST_NAME
PAGER
311549902 STEPHENS TINA
4423468b9 PLEW LINDA
213764555 GLASS BRANDON 3175709980
313782439 GLASS JACOB 8887345678
220984332 WALLACE MARIAH
443679012 SPURGEON TIFFANY
6 строк выбраны.
Функция COUNT используется для подсчета строк или значений в столбце, отличных от значения NULL При использовании в запросах функция COUNT возвращает числовое значение. При использовании с опцией DISTINCT функция COUNT посчитает только разные строки (т. е строки без учета повторений). По умолчанию используется опция ALL (противоположность DISTINCT), поэтому указывать ALL не обязательно Повторяющиеся значения считаются, когда DISTINCT не указано Другой опцией функции COUNT является звездочка (*) При использовании со звездочкой функция COUNT возвращает число всех строк в таблице, без исключения повторяющихся, не обращая внимания на возможно имеющиеся в столбце значения NULL
Синтаксис оператора функции COUNT следующий
COUNT [ (*) | (DISTINCT | ALL) J (имя_столбца)
Команда DISTINCT не используется с COUNT(*), а используется только с
COUNT(имя_столбца)
Пример_______________________________Значение____________________
SELECT COUNT (EMP_ID) Подсчет числа табельных номеров всех слу-
FROM EMPLOYEE__PAY_TBL; жащих
SELECT COUNT (DISTINCT SALARY) Подсчет только разных строк
FROM EMPLOYEE_PAY_TBL;
SELECT COUNT (ALL SALARY; Подсчет всех строк для SALARYFROM EMPLOYEE_PAY_TBL;
SELECT COUNT (*) Подсчет всех строк таблицы EMPLOYEE_TBL
FROM EMPLOYEE_TBL;
В следующем примере COUNT (*) используется для подсчета всех записей в таблице EMPLOYEE_TBL. В ней оказывается 6 строк с данными о служащих.
SELECT COUNT(*)
FROM EMPLOYEE_TBL;
COUNT (*)
---------
6
В следующем примере используется COUNT iEMP_ID), чтобы подсчитать число всех табельных номеров служащих в таблице Результат совпадает с результатом предыдущего запроса, поскольку каждый из служащих имеет свой табельный номер
SELECT COUNT(EMP_ID)
FROM EMPLOYEE_TBL;
COUNT(EMP_ID)
------------
6
В следующем примере используется COUNT (PAGER) , чтобы подсчитать число всех служащих, имеющих номера пейджеров. Имеется только два таких служащих
SELECT COUNT(PAGER)
FROM EMPLOYEEJTBL;
COUNT(PAGER)
------------
2
В следующем примере используется таблица OPDERS_TBL.
SELECT *
FROM ORDERS_TBL;
ORD_NUM CUST_ID PROD__ID
QTY ORD_DATE
56A901 232 11235 1 22-OCT-99
56A917 12 907 100 30-SEP-99
32A132 43 222 25 10-OCT-99
16C17 090 222 2 17-OCT-99
18D778 287 90 10 17-OCT-99
23E934 432 13 20 15-OCT-99
90C461 560 1234 2
7 строк выбраны.
Подсчитаем в этой таблице число различных кодов товаров.
SELECT COUNT(DISTINCT(PROD_ID))
FROM ORDERS_TBL;
COUNT(DISTINCT(PROD_ID))
-----------------------
6
Для PROD_ID значение 222 встречается дважды, в результате подсчет различных значений возвращает б, а не 7.
Ввиду того, что функция COUNT подсчитывает строки, тип содержащихся в столбце данных роли не играет, т. е. данные в строке могут быть любого типа
Функция SUM используется для подсчета суммы значений в столбце для заданной группы строк. Функцию зим можно использовать вместе с ключевым словом DISTINCT. При использовании ключевого слова DISTINCT повторно встречающиеся значения в сумму не включаются. В этом случае итог не будет полной суммой, поскольку некоторые строки могут быть при суммировании пропущены.
Синтаксис оператора функции зим следующий.
SUM ([ DISTINCT ] имя_столбца )
При использовании функции зим тип значения в столбце предполагается числовым. Функцию зим нельзя использовать по отношению к столбцам с символьными значениями или значениями дат и времени.
Пример_______________________________Значение__________________
SELECT SUM (SALARY) Подсчет суммы зарплат всех служащих
FROM EMPLOYEE_PAY_TBL;
SELECT SUM (DISTINCT SALARY) Подсчет суммы зарплат всех служащих без
FROM EMPLOYEE_PAY_TBL; учета повторяющихся значений
Подсчитаем сумму всех значений стоимости товаров из таблицы PRODUCT S_TBL.
SELECT SUM(COST)
FROM PRODUCTSJTBL;
SUM(COST)
---------------
163.07
Функция AVG используется для подсчета среднего для значений заданной группы строк. При использовании с ключевым словом DISTINCT повторно встречающиеся значения в среднем не учитываются.
Синтаксис оператора функции AVG следующий.
AVG([ DISTINCT ] имя_столбца )
Для использования функции AVG тип значения в столбце должен быть числовым.
Пример_____________________________Значение______________________
SELECT AVG (SALARY) Подсчет средней зарплаты всех служащих
FROM EMPLOYEE_PAY_TBL;
SELECT AVG (DISTINCT SALARY) Подсчет среднего значения для зарплат всех
FROM EMPLOYEE_PAY_TBL ; служащих без учета повторяющихся значений
Подсчитаем среднее для всех значений стоимости товаров из таблицы PRODUCTS_TBL.
SELECT AVG(COST)
FROM PRODUCTS_TBL;
AVG(COST)
-----------
13.5891667
В некоторых реализациях SQL результат будет округлен до точности, заданной типом значений в столбце.
В следующем примере в одном запросе используются две функции. Поскольку одним служащим платят ставку, а другим — почасово, можно подсчитать средние значения и для столбца PAY_RATE, и для столбца SALARY.
SELECT AVG(PAY_RATE), AVG(SALARY)
FROM PRODUCTSJTBL;
AVG(PAY_RATE) AVG(SALARY)
13.5833333 30000
Функция MAX используется для подсчета максимума для значений заданной группы строк. Значения NULL при этом игнорируются. Можно использовать также ключевое слово DISTINCT, но поскольку повторно встречающиеся значения на значение максимума не влияют, это ключевое слово оказывается в данном случае бесполезным.
МАХ([ DISTINCT ] имя_столбца )
Пример______________________________Значение____________________
SELECT MAX (SALARY) Нахождение максимальной зарплаты
FROM EMPLOYEE_PAY_TBL;
SELECT MAX (DISTINCT SALARY) Нахождение максимальной зарплаты без учета
FROM EMPLOYEE_PAY_TBL; повторяющихся значений
Подсчитаем максимум всех значений стоимости товаров из таблицы PRODUCT SJTBL.
SELECT MAX(COST)
FROM PRODUCTS_TBL;
MAX(COST)
------------------
59.99
Функция MIN используется для подсчета минимума для значений заданной группы строк. Значения NULL при этом игнорируются. Можно использовать также ключевое слово DISTINCT, но поскольку повторно встречающиеся значения на значение минимума не влияют, это ключевое слово оказывается в данном случае бесполезным.
MIN([ DISTINCT ] имя_столбца )
Пример_________________________________Значение_____________________
SELECT MIN (SALARY) Нахождение минимальной зарплаты
FROM EMPLOYEE_PAY_TBL;
SELECT MIN (DISTINCT SALARY) Нахождение минимальной зарплаты без уче-
FROM EMPLOYEE_PAY_TBL; та повторяющихся значений
Подсчитаем минимум всех значений стоимости товаров из таблицы PRODUCTSJTBL.
SELECT MIN(COST)
FROM PRODUCTS_TBL;
MIN(COST)
-----------
1.05
При использовании итоговых функций с ключевым словом DISTINCT не забывайте о том, что в этом случае запрос может возвращать неверные результаты Ведь основной задачей этих функций является получение итоговых подсчетов для всех строк данных таблицы.
Наконец, рассмотрим пример комбинирования итоговых функций с арифметическими операциями.
SELECT COUNT(ORD_NUM), SUM(QTY),
SUM(QTY) / COUNT(ORD_NUM) AVG_QTY
FROM ORDERS_TBL;
COUNT(ORD_NUM) SUM(QTY) AVG_QTY
------------- ------- -------
7 160 22.85743
Здесь подсчитано число заказов, указана общая сумма стоимости всех заказов и с помощью деления второй величины на первую вычислена средняя стоимость заказа. Для представления последней создан псевдоним столбца — AVG_QTY.
Итоговые функции несложно использовать и они могут оказаться весьма полезными. Вы теперь знаете, как подсчитать число значений в столбце, число строк в таблице, как найти максимальное или минимальное из всех значений в столбце. Помните о том, что при использовании итоговых функций значение NULL не учитывается — исключением является функция COUNT в формате COUNT (*).
Итоговые функции являются первыми из рассмотренных нами функций SQL, но они не единственные и существует множество других. Итоговые функции используются также для группирования значений, что предполагается рассмотреть в ходе следующего урока. По мере изучения других функций, вы обнаружите, что в основном они имеют похожий синтаксис и что лежащие в их основе концепции достаточно просты.
Почему при использовании функций MIN и МАХ значение NULL игнорируется?
Значение NULL означает, что в поле ничего нет.
Почему при использовании функции COUNT тип данных не играет значения?
Функция COUNT просто считает строки.
Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".
1. Верно ли следующее утверждение: "Функция AVG возвращает среднее для значений всех строк, включая значения NULL?"
2. Верно ли следующее утверждение: "Функция зим используется для суммирования итоговых значений по столбцам?"
3. Верно ли следующее утверждение: ''Функция COUNT (* ) подсчитывает число всех строк в таблице?"
4. Будут ли работать следующие операторы SELECT? Если нет, то что в них следует исправить?
а. SELECT COUNT *
FROM EMPLOYEE_PAY_TBL;
6. SELECT COUNT(EMPLOYEE_ID), SALARY
FROM EMPLOYEE_PAYJFBL;
B. SELECT MIN(BONUS), MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE SALARY > 20000;
1. Рассмотрите следующую таблицу PRODUCTS_TBL.
EMP_ID POSITION DATE_HIRE PAV_RATE DATE_LAST SALARY BONUS
311549902 МАРКЕТИНГ 23-МАЙ-89 01-МАЙ-99 30000 2000
442346889 РУК.ГРУППЫ 17-ИЮН-90 14.75 01-ИЮН-99
213764555 МЕНЕДЖЕР 14-АВГ-94 01-АВГ-99 40000 3000
313782439 ПРОДАВЕЦ 28-ИЮН-97 20000 1000
220984332 ДОСТАВКА 22-ИЮЛ-96 11 01-ИЮЛ-99
443679012 ДОСТАВКА 14-ЯНВ-91 15 01-ЯНВ-99
6 строк выбраны.
Постройте операторы SQL, позволяющие найти следующее.
а. Среднюю зарплату (SALARY).
б. Максимальную премию (BONUS).
в. Сумму всех выплат по зарплате.
г. Минимальную почасовую оплату (PAY_RATE).
д. Общее число строк в таблице.