DeepEdit!

Программирование баз данных на Oracle, техническая документация, литература, статьи и публикации

  • Увеличить размер шрифта
  • Размер шрифта по умолчанию
  • Уменьшить размер шрифта

Агрегатные функции. Группировка записей


Агрегатные функции - это функции, которые позволяют находить значения
некоторых обобщенных характеристик для набора данных, таких, как общее число
элементов в наборе /функция 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. Вывести список покупателей-однофамильцев с подсчетом их количества.


 









jAntivirus