Итоговые функции, вычисляемые столбцы и представления

Итоговые функции еще называют статистическими, агрегатными или суммирующими. Эти функции обрабатывают набор строк для подсчета и возвращения одного значения. Таких функций всего пять:

· AVG(). Функция возвращает среднее значение столбца.

· COUNT(). Функция возвращает число строк в столбце.

· MAX(). Функция возвращает самое большое значение в столбце.

· MIN(). Функция возвращает самое маленькое значение в столбце.

· SUM(). Функция возвращает сумму значений столбца.

Пусть необходимо узнать минимальную, максимальную и среднюю цену на книги в нашем магазине. Тогда из таблицы «Цены» (prices) надо взять минимальное, максимальное и среднее значения по столбцу price. Запрос простой:

SELECT MIN(price), MAX(price), AVG(price) FROM prices;

Пусть теперь необходимо узнать, на какую сумму привез товар поставщик «Дом печати» (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;