Перейти на главную   
  helloworld.ru - документация и книги по программированию  
helloworld.ru - документация и книги по программированию
    главная     хостинг    
Поиск по сайту:  
Смотрите также
Couldn't connect to MySQL

Структуризированный язык запросов (SQL)

Глава 2. Запросы с использованием единственной таблицы

2.1 О предложении SELECT
2.2 Выборка без использования фразы WHERE
2.3 Выборка c использованием фразы WHERE
2.4 Выборка с упорядочением
2.5 Агрегирование данных

2.5 Агрегирование данных

В SQL существует ряд специальных стандартных функций (SQL-функций). Кроме специального случая COUNT(*) каждая из этих функций оперирует совокупностью значений столбца некоторой таблицы и создает единственное значение, определяемое так:

  • COUNT - число значений в столбце,
  • SUM - сумма значений в столбце,
  • AVG - среднее значение в столбце,
  • MAX - самое большое значение в столбце,
  • MIN - самое малое значение в столбце.

Для функций SUM и AVG рассматриваемый столбец должен содержать числовые значения.

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

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

2.5.2 Функции без использования фразы GROUP BY.

Если не используется фраза GROUP BY, то в перечень элементов_SELECT можно включать лишь SQL-функции или выражения, содержащие такие функции. Другими словами, нельзя иметь в списке столбцы, не являющихся аргументами SQL-функций.

Например, выдать данные о массе лука (ПР=10), проданного поставщиками, и указать количество этих поставщиков:

SELECT SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;

Результат:

SUM(К_во) COUNT(К_во)
220 2

Если бы для вывода в результат еще и номера продукта был сформирован запрос

SELECT ПР,SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;

то было бы получено сообщение об ошибке. Это связано с тем, что SQL-функция создает единственное значение из множества значений столбца-аргумента, а для "свободного" столбца должно быть выдано все множество его значений. Без специального указания (оно задается фразой GROUP BY) SQL не будет выяснять, одинаковы значения этого множества (как в данном примере, где ПР=10) или различны (как было бы при отсутствии WHERE фразы). Поэтому подобный запрос отвергается системой.

Правда, никто не запрещает дать запрос

SELECT 'Кол-во лука =',SUM(К_во),COUNT(К_во)
FROM Поставки
WHERE ПР = 10;

Результат:

'Кол-во лука =' SUM(К_во) COUNT(К_во)
Кол-во лука = 220 2

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

Например, для получения суммы цен, средней цены, количества поставляемых продуктов и количества разных цен продуктов, проданных коопторгом УРОЖАЙ (ПС=5), а также для получения количества продуктов, которые могут поставляться этим коопторгом, можно дать запрос

SELECT SUM(Цена),AVG(Цена),COUNT(Цена),
COUNT(DISTINCT Цена),COUNT(*)
FROM Поставки
WHERE ПС = 5;

и получить

SUM(Цена) AVG(Цена) COUNT(Цена) COUNT(DISTINCT Цена) COUNT
6.2 1.24 5 4 7

В другом примере, где надо узнать "Сколько поставлено моркови и сколько поставщиков ее поставляют?":

SELECT SUM(К_во),COUNT(К_во)
FROM Поставки
WHER ПР = 2;

будет получен ответ:

SUM(К_во) COUNT (К_во)
-0- 0

Наконец, попробуем получить сумму массы поставленного лука с его средней ценой ("Сапоги с яичницей"):

SELECT (SUM(К_во) +AVG(Цена))
FROM Поставки
WHERE ПР = 10;

Результат:

SUM(К_во)+AVG(Цена)
220.6

2.5.3 Фраза GROUP BY.

Мы показали, как можно вычислить массу определенного продукта, поставляемого поставщиками. Предположим, что теперь требуется вычислить общую массу каждого из продуктов, поставляемых в настоящее время поставщиками. Это можно легко сделать с помощью предложения

SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР;

Результат показан на рис. 2.3,а.

а)
ПР  
9 0
11 150
12 30
15 370
1 370
3 250
5 170
6 220
8 150
7 200
2 0
4 100
13 190
14 70
16 250
17 50
10 220
б)
ПС ПР Цена К_во
1 9 -0- -0-
3 9 -0- -0-
5 9 -0- -0-
1 11 1.50 50
5 11 -0- -0-
6 11 -0- -0-
8 11 1.00 100
1 12 3.00 10
3 12 2.50 20
6 12 -0- -0-
1 15 2.00 170
3 15 1.50 200
2 1 3.60 300
7 1 4.20 70
2 3 -0- -0-
7 3 4.00 250
. . . .
в)
ПР  
1 370
2 0
3 250
4 100
5 170
6 220
7 200
8 150
9 0
10 220
11 150
12 30
13 190
14 70
15 370
16 250
17 50
г)
ПР  
9 0
11 150
12 30
15 70
1 370
3 250
5 70
6 140
8 150
7 200
2 0
4 100
13 190
14 70
16 250
17 50
10 220

Рис. 2.3. Иллюстрации к фразе GROUP BY

Фраза GROUP BY (группировать по) инициирует перекомпоновку указанной во FROM таблицы по группам, каждая из которых имеет одинаковые значения в столбце, указанном в GROUP BY. В рассматриваемом примере строки таблицы Поставки группируются так, что в одной группе содержатся все строки для продукта с ПР = 1, в другой – для продукта с ПР = 2 и т.д. (см. рис. 2.3.б). Далее к каждой группе применяется фраза SELECT. Каждое выражение в этой фразе должно принимать единственное значение для группы, т.е. оно может быть либо значением столбца, указанного в GROUP BY, либо арифметическим выражением, включающим это значение, либо константой, либо одной из SQL-функций, которая оперирует всеми значениями столбца в группе и сводит эти значения к единственному значению (например, к сумме).

Отметим, что фраза GROUP BY не предполагает ORDER BY. Чтобы гарантировать упорядочение по ПР результата рассматриваемого примера (рис. 2.3,в) следует дать запрос

SELECT ПР, SUM(К_во)
FROM Поставки
GROUP BY ПР
ORDER BY ПР;

Наконец, отметим, что строки таблицы можно группировать по любой комбинации ее столбцов. Так, по запросу

SELECT Т, БЛ, COUNT(БЛ)
FROM Заказ
GROUP BY Т, БЛ;

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

Т БЛ COUNT(БЛ)
1 3 18
1 6 14
1 19 17
1 21 15
. . .

Если в запросе используются фразы WHERE и GROUP BY, то строки, не удовлетворяющие фразе WHERE, исключаются до выполнения группирования.

Например, выдать для каждого продукта его код и общий объем возможных поставок, учитывая временную недееспособность поставщика с ПС=2:

SELECT ПР, SUM(К_во)
FROM Поставки
WHERE ПС <> 2
GROUP BY ПР;

Результат, приведенный на рис. 2.3,г, отличается от результата (рис. 2.3,а) аналогичного запроса для всех поставщиков объемом поставок продуктов с кодами 15, 5 и 6.

2.5.4 Использование фразы HAVING.

Фраза HAVING (рис.2.3) играет такую же роль для групп, что и фраза WHERE для строк: она используется для исключения групп, точно так же, как WHERE используется для исключения строк. Эта фраза включается в предложение лишь при наличии фразы GROUP BY, а выражение в HAVING должно принимать единственное значение для группы.

Например, выдать коды продуктов, поставляемых более чем двумя поставщиками:

SELECT
FROM Поставки
GROUP BY ПС
HAVING COUNT(*) > 2;

Результат:

ПР
9
11
12










helloworld.ru © 2001-2021
Все права защищены