SQL — Урок 11. Итоговые функции, вычисляемые столбцы и представления
Теперь, мы хотим узнать, на какую сумму нам привез товар поставщик «Дом печати» (id=2). Составить такой запрос не так просто. Давайте поразмышляем, как его составить:
1. Сначала надо из таблицы Поставки (incoming) выбрать идентификаторы (id_incoming) тех поставок, которые осуществлялись поставщиком «Дом печати» (id=2):
SELECT id_incoming FROM incoming WHERE id_vendor=2;
2. Теперь из таблицы Журнал поставок (magazine_incoming) надо выбрать товары (id_product) и их количества (quantity), которые осуществлялись в найденных в пункте 1 поставках. То есть запрос из пункта 1 становится вложенным:
SELECT id_product, quantity FROM magazine_incoming WHERE id_incoming=(SELECT id_incoming FROM incoming WHERE id_vendor=2);
3. Теперь нам надо добавить в результирующую таблицу цены на найденные товары, которые хранятся в таблице Цены (prices). То есть нам понадобится объединение таблиц Журнал поставок (magazine_incoming) и Цены (prices) по столбцу id_product:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
4. В получившейся таблице явно не хватает столбца Сумма, то есть вычисляемого столбца . Возможность создания таких столбцов предусмотрена в MySQL. Для этого надо лишь указать в запросе имя вычисляемого столбца и что он должен вычислять. В нашем примере такой столбец будет называться summa, а вычислять он будет произведение столбцов quantity и price. Название нового столбца отделяется словом AS:
SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
5. Отлично, нам осталось лишь просуммировать столбец summa и наконец-то узнаем, на какую сумму нам привез товар поставщик «Дом печати». Синтаксис для использования функции SUM() следущий:
SELECT SUM(имя_столбца) FROM имя_таблицы;
Имя столбца нам известно — summa, а вот имени таблицы у нас нет, так как она является результатом запроса. Что же делать? Для таких случаев в MySQL существуют Представления . Представление — это запрос на выборку, которому присваивается уникальное имя и который можно сохранять в базе данных, для последующего использования.
Синтаксис создания представления следующий:
CREATE VIEW имя_представления AS запрос;
Давайте сохраним наш запрос, как представление с именем report_vendor:
CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
6. Вот теперь можно использовать итоговую функцию SUM() :
SELECT SUM(summa) FROM report_vendor;
Вот мы и достигли результата, правда для этого нам пришлось использовать вложенные запросы, объединения, вычисляемые столбцы и представления. Да, иногда для получения результата приходится подумать, без этого никуда. Зато мы коснулись двух очень важных тем — вычисляемые столбцы и представления. Давайте поговорим о них поподробнее.
Вычисляемые поля (столбцы)
На примере мы рассмотрели сегодня математическое вычисляемое поле. Здесь хотелось бы добавить, что использовать можно не только операцию умножения (*), но и вычитание (-), и сложение (+), и деление (/). Синтаксис следующий:
SELECT имя_столбца_1, имя_столбца_2, имя_столбца_1*имя_столбца_2 AS имя_вычисляемого_столбца FROM имя_таблицы;
Второй нюанс — ключевое слово AS, мы его использовали для задания имени вычисляемого столбца. На самом деле с помощью этого ключевого слова задаются псевдонимы для любых столбцов. Зачем это нужно? Для сокращения и читаемости кода. Например, наше представление могло бы выглядеть так:
CREATE VIEW report_vendor AS SELECT A.id_product, A.quantity, B.price, A.quantity*B.price AS summa FROM magazine_incoming AS A, prices AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM incoming WHERE id_vendor=2);
Согласитесь, что так гораздо короче и понятнее.
Представления
Синтаксис создания представлений мы уже рассматривали. После создания представлений, их можно использовать так же, как таблицы. То есть выполнять запросы к ним, фильтровать и сортировать данные, объединять одни представления с другими. С одной стороны это очень удобный способ хранения частоприменяемых сложных запросов (как в нашем примере).
Но следует помнить, что представления — это не таблицы, то есть они не хранят данные, а лишь извлекают их из других таблиц. Отсюда, во-первых, при изменении данных в таблицах, результаты представления так же будут меняться. А во-вторых, при запросе к представлению происходит поиск необходимых данных, то есть производительность СУБД снижается. Поэтому злоупотреблять ими не стоит.
Научись программировать на Python прямо сейчас!
- Научись программировать на Python прямо сейчас
- Бесплатный курс
Если этот сайт оказался вам полезен, пожалуйста, посмотрите другие наши статьи и разделы.
Функция SQL SUM()
Оператор SQL SUM() — функция, возвращающая сумму значений столбца таблицы. Используется только для числовых столбцов.
Функция SQL SUM() имеет следующий синтаксис:
SUM ( [ALL | DISTINCT] expression )
Параметр ALL — является параметром по умолчанию. Считается сумма всех строк.
При указании параметра DISTINCT — происходит подсчет только уникальных значений.
Примеры оператора SQL SUM: Имеется следующая таблица Universities :
| ID | UniversityName | Students | Faculties | Professores | Location | Site |
| 1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
| 2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
| 3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
| 4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
| 5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
| 6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
| 7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1. Используя оператор SQL SUM найти количество студентов (Students) всех университетов в таблице:
SELECT SUM(Students) FROM Universities
Пример 2. Используя оператор SQL SUM найти количество преподавателей московских университетов:
SELECT SUM(Professores) FROM Universities WHERE Location = 'Moscow'
Пример 3. Используя оператор SQL SUM произвести подсчет уникальных значений столбца Faculties:
SELECT SUM(DISTINCT Faculties) FROM Universities
Ответ: 117 (примечание: в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)
Получение суммарных или статистических значений для всех строк в таблице (визуальные инструменты для баз данных)
Агрегатные функции позволяют вычислять сумму всех значений, содержащихся в таблице. Например, можно создать запрос наподобие того, что приведен ниже, для отображения общей стоимости всех книг, содержащихся в таблице titles :
SELECT SUM(price) FROM titles Создайте несколько агрегатов в одном запросе, используя агрегатные функции для одного или нескольких столбцов. Например, можно создать запрос, вычисляющий общую сумму по столбцу price и среднеарифметическое значение столбца discount .
В отдельном запросе для одного и того же столбца можно определять несколько статистических операций (подведение общей суммы, подсчет, усреднение). Например, следующий запрос определяет среднеарифметическое значение и общую сумму столбца price в таблице titles :
SELECT AVG(price), SUM(price) FROM titles Если добавить условия поиска, можно вычислять статистические значения на основе подмножества строк, удовлетворяющих заданному условию.
Можно также подсчитать, сколько всего в таблице содержится строк, или строк, удовлетворяющих определенному условию. Дополнительные сведения см. в разделе Подсчет строк в таблице (визуальные инструменты для баз данных).
Если в запросе указано статистическое выражение, то для всех строк в таблице отображается только само это статистическое значение. Например, при подсчете итогового значения столбца price в таблице titles названия, имена издателей и другие столбцы не отображаются.
! При создании подытогов (например, групп) можно выводить значения столбцов для каждой группы. Дополнительные сведения см. в разделе Группирование строк в результатах запроса (визуальные инструменты для баз данных).
Статистическая обработка значений по всем строкам
- Убедитесь, что таблица, в которой необходимо вычислить статистическую величину, присутствует на панели диаграмм
- Щелкните правой кнопкой мыши фон панели диаграммы, а затем в контекстном меню выберите пункт Группировать . Конструктор запросов и представлений добавляет столбец Группировать в сетку на панели критериев.
- Добавьте на панель критериев столбец, по которому необходимо вычислить статистическую величину. Убедитесь, что столбец помечен для вывода. Конструктор запросов и представлений автоматически назначает суммируемому столбцу псевдоним, который можно заменить более понятным. Дополнительные сведения см. в разделе Создание псевдонимов столбцов (визуальные инструменты для баз данных).
- В столбце сетки Группировать выберите нужную агрегатную функцию, например Sum, Avg, Min, Max, Count. Если необходимо вычислить статистические значения только для уникальных строк результирующего набора, выберите агрегатную функцию с параметром DISTINCT, например Min Distinct. Не выбирайте параметры Группировать, Выражениеили Где, так как они не применяются при статистической обработке всех строк. Конструктор запросов и представлений заменяет указанной агрегатной функцией имя столбца в инструкции, представленной на панели SQL . Например, инструкция SQL может иметь такой вид:
SELECT SUM(price) FROM titles При выполнении запроса на панели результатов отображаются указанные статистические выражения.
Конструктор запросов и представлений обслуживает агрегатные функции в инструкции, отображаемой на панели SQL до тех пор, пока режим «Группировать по» явно не будет выключен. Поэтому при изменении типа запроса или изменении состава таблиц и возвращающих табличное значение объектов, присутствующих на панелях диаграмм, конечный запрос может содержать недопустимые агрегатные функции.
Заметки разработчика
Я думаю, каждый знает такую статистическую функцию SQL, как SUM(). Она позволяет посчитать сумму всех значений по определённому столбцу (если не задано дополнительных условий). Но что, если нам вдруг понадобиться посчитать не сумму, а произведение всех значений?
Как ни странно, функция SUM() нам тоже пригодится. Как нам от произведения перейти к сумме? Вспомним начала матанализа: логарифм произведения равен сумме логарифмов. Будем использовать натуральный логарифм в паре с функцией возведения экспоненты в степень EXP(). Привожу пример для SQL SERVER, но должно работать и в других СУБД:
SELECT EXP(SUM(LOG(FIELD))) FROM TABLE
То есть от каждого значения в некотором поле таблицы берется натуральный логарифм, затем считается сумма этих логарифмов и затем экспонента возводится в степень, равную этой сумме.
Вот так мы от суммы переходим к произведению в рамках одного изящного SQL запроса. С ходу не могу сказать, может ли это пригодиться в реальных проектах, но согласитесь — выглядит впечатляюще.
То же самое, только уже средствами LINQ to Objects:
// произведение этих чисел равно 120
int[] array = new int[] < 1, 2, 3, 4, 5 >;
var result = Math.Exp((from a in array
select Math.Log(a)).Sum());
Console.WriteLine(«Произведение чисел равно: «, result);
Console.ReadKey();
В результате программа выдаст 120.