Тема 8) Выборка с упорядочением и агрегированием данных

Таблицы - это неупорядоченные наборы данных, и данные которые выходят из них, не обязательно появляются в какой-то определенной последовательности. SQL использует команду ORDER BY чтобы позволить упорядочивать ваш вывод. Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого и вы можете определять возрастание (ASC) или убывание (DESC) для каждого столбца. По умолчанию установлено - возрастание.

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

SELECT *

FROM Движение товаров

ORDERBYКоличествоDESC;

Дата	№ накладной	Код поставщика	Код товара	Количество	Цена
10.03.06	6	4	3	200	20
01.01.06	1	1	2	100	10
20.01.06	3	1	2	100	10
01.03.06	5	3	4	100	25
20.10.06	11	1	2	100	12
10.01.06	2	5	3	50	20
30.01.06	4	2	1	50	15
30.03.06	8	2	1	50	15
10.10.06	10	5	3	20	20
20.03.06	7	1	2	10	12
01.10.06	9	3	4	10	25
30.10.06	12	2	1	5	15

При включении в список ORDER BY нескольких столбцов СУБД сортирует строки результата по значениям первого столбца списка пока не появится несколько строк с одинаковыми значениями данных в этом столбце. Такие строки сортируются по значениям следующего столбца из списка ORDER BY и т.д.

Например, выдать содержимое таблицы Движение товара, отсортировав ее строки в порядке убывания по количеству товара и по цене:

SELECT *

FROM Движение товаров

ORDERBYКоличество, ЦенаDESC;

Дата	№ накладной	Код поставщика	Код товара	Количество	Цена
10.03.06	6	4	3	200	20
01.03.06	5	3	4	100	25
20.10.06	11	1	2	100	12
01.01.06	1	1	2	100	10
20.01.06	3	1	2	100	10
10.01.06	2	5	3	50	20
30.01.06	4	2	1	50	15
30.03.06	8	2	1	50	15
10.10.06	10	5	3	20	20
01.10.06	9	3	4	10	25
20.03.06	7	1	2	10	12
30.10.06	12	2	1	5	15

Кроме того, в список ORDER BY можно включать не только имя столбца, а его порядковую позицию в перечне SELECT. Благодаря этому возможно упорядочение результатов на основе вычисляемых столбцов, не имеющих имен.

Например, запрос

SELECT № накладной, (Количество*Цена)

FROM Движение товара;

ORDER BY 2;

позволит получить список накладных переупорядоченный по возрастанию значений стоимости товара

Результат выборки:

№ накладной	Количество*Цена
12	75
2	100
7	120
9	250
10	400
4	750
8	750
1	1000
3	1000
11	1200
5	2500
6	4000

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

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

COUNT - число значений в столбце,

SUM - сумма значений в столбце,

AVG - среднее значение в столбце,

MAX - самое большое значение в столбце,

MIN - самое малое значение в столбце.

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

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

Агрегатные функции используются подобно именам полей в предложении SELECT запроса, но с одним исключением, они берут имена полей как аргументы.

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

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

Например, выдать данные о количестве поставленного товара и его средней цене на товар с кодом=1:

SELECT SUM(Количество),AVG(Цена)

FROM Движение товара

WHERE Код товара=1;

Результат запроса

SUM(Количество)	AVG(Цена)
105	15

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

SELECT MAX (Количество*Цена)

FROM Движение товара;

Результат выборки:

Количество*Цена

4000

Фраза GROUP BY

Предложение GROUP BY позволяет вам определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает вам возможность объединять поля и агрегатные функции в едином предложении SELECT.

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

SELECT Код товара, SUM(Количество)

FROM Движение товара

GROUP BY Код товара;

Результат выборки:

Код товара	SUM(Количество)
2	310
3	270
1	105
4	110

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

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

SELECT Код товара, SUM(Количество)

FROM Движение товара

GROUP BY Код товара;

ORDER BY Код товара;

Код товара	SUM(Количество)
1	105
2	310
3	270
4	110

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

SELECT Количество, Цена, COUNT(№ накладной)

FROM Движение товара

GROUP BY Количество, цена;

Результат выборки

Количество	Цена	COUNT(№ накладной)
200	20	1
100	10	2
100	25	1
100	12	1
50	20	1
50	15	2
10	12	1
10	25	1
20	20	1
5	15

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

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

SELECT Код товара, SUM(Количество)

FROM Движение товара

WHERE Код поставщика <> 1

GROUP BY Код товара;

Результат выборки:

Код товара	SUM(Количество)
3	270
1	105
4	110

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

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

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

SELECTКод поставщика,COUNT(№ накладной)

FROMДвижение товара

GROUPBYКод поставщика

HAVING COUNT(№ накладной)>2;

Код поставщика	COUNT(№ накладной)
1	4
2	3

Задание к теме 7:

Исходные данные: Пусть имеются таблицы БД со следующей структурой

Продавцы (Таб.номер, ФИО, город)

Товары (Номер товара, Наименование)

Движение товаров (дата, таб.номер продавца, код товара, количество товара, цена, ФИО покупателя)

1. Напишите запрос который сосчитал бы общую сумму приобретений на I-ю дата.

2. Напишите запрос который сосчитал бы число различных не-NULL значений поля город в таблице Продавцы.

3. Напишите запрос который выбрал бы наименьшую сумму для каждого покупателя.

4. Напишите запрос который бы выбирал покупателей в алфавитном порядке бех повторений их фамилий.

5. Предположим что каждый продавец имеет 12% комиссионных. Напишите запрос к таблице Движение товаров который мог бы вывести, номер продавца, сумму комиссионных продавца для каждой строки таблицы

6. Выведите информацию о том, сколько раз совершал покупки каждый из покупателей