Вычисления и подведение итогов в запросах
1.7. Вычисления и подведение итогов в запросах:
Описывается использование арифметических операторов и построение вычисляемых столбцов. Рассматриваются итоговые (агрегатные) функции COUNT, SUM, AVG, MAX, MIN. Дается пример использования оператора GROUP BY для группировки в запросах выборки данных. Описывается применение предложения HAVING.
1.7.1. Построение вычисляемых полей
В общем случае для создания вычисляемого (производного) поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.
Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS.
Пример 33. Рассчитать общую стоимость для каждой сделки. Этот запрос использует расчет результирующих столбцов на основе арифметических выражений.
SELECT Tovar Nazvonie, Tovar Cena,
Sdelka.Kolichestvo,
Tovar Cena*Sdelka Kolichestvo AS Stoimosti
Рекомендуемые материалы
FROM Tovar INNER JOIN Sdelka
ON Tovar.KodTovar=Sdelka.Kod.Tovar
Пример 34. Получить список фирм с указанием фамилии и инициалов клиентов.
SELECT Firma.Familiya+"."+
Left(ИМЯ,l)+ "."+Left(OTЧЕСTBO,l)+"."AS ФИО
FROM Klient
В запросе использована встроенная функция Left, позволяющая вырезать в текстовой переменной один символ слева в данном случае.
Пример 35. Получить список товаров с указанием года и месяца продажи.
SELECT Tovar.Nazvanie, Уеаг(Sdelka.Data)
AS Уеаг.Month(Sdelka.Data)
AS Mesyac
FROM Tovar INNER JOIN Sdelka
ON Tovar.KodTovar=Sdelka.Kod.Tovar
Пример 36. Определить товары, которые покупают клиенты из Москвы.
SELECT DISTINCT Tovar.Nazvanie, Klient. Gorod.Klientа
FROM Tovar INNER JOIN
(Klient INNER JOIN Sdelka ON Klient.KodKlientа=Sdelka.KodKlientа) ON Tovar.KodTovarа=Sdelka.KodTovarа
WHERE Klient.GorodKlientа – “Москва”
Пример 37. Определить фирмы, покупающие товары местного производства.
SELECT DISTINCT Klient.Firma, Klient.GorodKlientа,Tovar. Gorod Tovarа
FROM Tovar INNER JOIN
(Klient INNER JOIN Sdelka
ON Klient.KodKlientа.Sdelka.KodKlientа)
ON Tovar.К.одTovarа=Sdelka.KodTovarа
WHERE Klient.GorodKlientа=Tovar.GorodTovarа
Пример 38. Определить среднюю цену проданного товара.
SELECT Аvg(Tovar.Cena) AS Avg_Cena
FROM Tovar INNER JOIN Sdelka
ON Tovar. Kod Tovara=Sdelka Kod Tovara
Пример 39. Подсчитать общую стоимость проданных товара.
SELECT Sum(Tovar.Cena*Sdelka Kolichestvo)
AS Stoimosti
FRCM Tovar INNER JOIN Sdelka ON
Tovar. Kod Tovara.Sdelka Kod Tovara
В запросе использованы встроенные функции Year и Month для выделения года и месяца из даты.
1.7.2. Использование итоговых функций
С помощью итоговых (агрегатных) функций в рамках SQL – запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора.
Пользователю доступны следующие основные итоговые функции:
- Count (Выражение) – определяет количество записей в выходном наборе SQL-запроса;
- Min/Max (Выражение) – определяют наименьшее и наибольшее из множества значение в некотором поле запроса;
- Avg (Выражение) – эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей.
Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество.
- Sum (Выражение) – вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.
Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц.
Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Функции COUNT, MIN и МАХ применимы как к числовым, так и к нечисловым полям, тогда как функции SUM и AVG могут использоваться только в случае числовых полей, за исключением COUNTY*. При вычислении результатов любых функций сначала исключаются все пустые значения, после чего требуемая операция применяется только к оставшимся конкретным значениям столбца. Вариант COUNT(*) – особый случай использования функции COUNT, его назначение состоит в подсчете всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения.
Если до применения обобщающей функции необходимо исключить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT. Оно не имеет смысла для функций MIN и МАХ, однако его использование может повлиять на результаты выполнения функций SUM и AVG, поэтому необходимо заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT может быть указано в любом запросе не более одного раза.
Очень важно отметить, что итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING. Во всех других случаях это недопустимо. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких – либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций.
Пример 40. Определить количество сделок.
В лекции "6. Вибрация и ее воздействие на организм человека" также много полезной информации.
SELECT Count(*) AS Kolichestvo sdelok
FROM Sdelka
Пример 41. Определить суммарное количество проданного товара.
SELECT Sum(Sdelka. Kolichestvo)
AS Kolichestvo Tovara
FROM Sdelka
