Агрегатные функции - это функции, которые позволяют находить значения
некоторых обобщенных характеристик для набора данных, таких, как общее число
элементов в наборе /функция COUNT()/, их среднее значение /AVG()/, сумму
/SUM()/, наибольшее /MAX()/ или наименьшее /MIN()/ значения. Агрегатные
функции могут используются в целевых списках запросов ( SELECT ), но не в
условиях ( например, во фразе WHERE ).
2.1 Функция COUNT()
Синтаксис: COUNT ( { * | [DISTINCT] имя_столбца } )
Функция COUNT(*) подсчитывает число строк в наборе, если в качестве аргумента
задано имя столбца таблицы, то вычисляется количество "не пустых" значений
(NOT NULL) в этом столбце. При добавлении к имени столбца квалификатора
DISTINCT происходит подсчет неповторяющихся значений.
SELECT COUNT(*) FROM stock;
- подсчет общего числа строк в таблице stock.
SELECT COUNT(stock) FROM stock WHERE stock <> 0;
- подсчет числа ненулевых значений в столбце stock таблицы stock.
2.2 Функции SUM(), AVG(), MIN(), MAX()
Синтаксис этих функций одинаков: имя_функции ( [DISTINCT] имя_столбца )
Эти функции находят сумму, среднее арифметическое, наибольшее или наименьшее
значение. Очевидно, что тип значений обрабатываемого столбца должен
соответствовать операции, т.е. быть числовым при суммировании или нахождении
среднего арифметического.
Примеры:
SELECT AVG( cost ) FROM stock; - определение средней цены книги
на складе ( stock ).
SELECT MAX( retail ), MIN( retail ) FROM stock; - определение диапазона розничных цен книг на складе.
Агрегатные функции не могут быть использованы во фразе условия WHERE, но с их
помощью можно получить результат, который в дальнейшем можно использовать при
формулировке условия в запросе. Для этого используется техника т.н.
подзапросов.
Так, будет ошибкой пытаться вывести строку таблицы tab1, в которой значение в
столбце col1 принимает наибольшее значение с помощью запроса:
SELECT * FROM tab1 WHERE col1 = MAX( col1 );
Правильным решением будет использование результата подзапроса
SELECT MAX ( col1 ) FROM tab1
в запросе требуемой строки:
SELECT * FROM tab1 WHERE col1 = ( select max(col1) from tab1 );
2.3 Группировка.
Группировка данных - это процедура объединения в логическом порядке строк с
определенными значениями. Одним из видов группировки является сортировка
(определяемая фразой ORDER BY), кроме этого в группе условий оператора выборки
SELECT могут быть использованы фразы GROUP BY и HAVING (во вполне определенном
порядке: WHERE - GROUP BY - HAVING - ORDER BY ).
Фраза GROUP BY с синтаксисом:
GROUP BY имя_поля [ ,имя_поля ... ]
позвляет преобразовать таблицу так, что в ее отдельные строки
будет собрано содержание всех строк с одинаковыми значениями полей
группировки. Чтобы такое "сжатие" было возможно, необходимо, чтобы в списке
выводимых полей оператора SELECT и в списке полей группировки фразы GROUP BY
были перечислены одни и те же поля. Кроме этого, в списке SELECT могут быть
использованы агрегатные функции для определения соответствующих величин для
группируемых записей. Например, запрос для таблицы tab1
SELECT col1 FROM tab1 GROUP BY col1; - возвращает столбец col1,
содержащий только неповторяющиеся значения, а запрос
SELECT col1, count(*) FROM tab1 GROUP BY col1; - возвращает два
столбца, один из которых содержит неповторяюшиеся значения в столбце col1, а
другой - число строк в таблице с таким значениями.
Следует отметить, что фразы GROUP BY и ORDER BY похожи в том смысле, что
служат для целей упорядочения выходного набора, однако ORDER BY выводит все
записи в упорядоченном виде, а GROUP BY "конденсирует" информацию, поэтому при
использовании этой фразы в целевом списке SELECT могут быть только поля
группировки и агрегатные функции, т.е. использовние группировки целесообразно
в том случае, когда нужно получить обобщающие данные с помощью агрегатных
функций.
Пример:
SELECT author_id, count(*) FROM books GROUP BY author_id; - вывод
идентификационных номеров авторов книг с указанием, сколько различных книг есть
у каждого автора. Для наложения на результат группировки дополнительных условий используется фраза HAVING с синтаксисом:
HAVING условое_выражение
где условное_выражение должно быть образовано только из полей группировки
и/или агрегатных функций. Например, запрос
SELECT col1, count(col1) FROM tab1
GROUP BY col1 HAVING max(col2)>20;
выведет строки с неповторяющимися значениями col1, указанием числа строк с
одинаковыми значениями col1 и только те наборы, в которых наибольшее значение
поля col2 превысит 20.
Задания
1. Определить общее количество книг на складе (stock).
2. Определить количество книг с розничной ценой выше 30.
3. Определить выручку магазина при реализации всех книг.
4. Определить прибыль магазина при реализации всех книг.
5. Найти даты наиболее ранней и наиболее поздней публикаций (таблица editions).
6. Получить информацию о книге с наиболее ранним сроком публикации.
7. Найти среднюю цену (cost) для имеющихся книг на складе (stock). Получить
список книг, цена которых выше средней.
8. Подсчитать количество покупателей (customers) с различными фамилиями.
9. Подсчитать количество книг, выпущенных в твердой (Hard) и мягкой (Paper)
обложке.
10. Для каждого покупателя (его кода) получить количество купленных им
когда-либо книг. Список выдать в порядке уменьшения количества купленных
книг.
11. Для каждого не единожды купившего книгу покупателя (его кода) получить
количество купленных им книг.
12. Вывести список покупателей-однофамильцев с подсчетом их количества.
< Предыдущая | Следующая > |
---|