Тема "Язык SQL", Урок 6 "Группировка данных в SELECT. Аггрегатные функции. GROUP BY."

Создание таблицы для сегодняшней работы

Зайдите в базу данных и выполните такой запрос:

Небольшая теория, которая будет вставлена сюда, так как она не была дана

Довольно часто в запросе SELECT бывает выгодно выводить не просто значения данных в столбцах, а результаты каких-либо действий. Вот пример запроса с арифметическим действием:

Действия не обязательно бывают арифметическими. Вот пример действия, которое выделяет месяц затраты:

Стоит заметить, что есть ещё много других функций для работы с датой. Вот вам дополнительное домашнее задание: погуглите, какие бывают функции для работы с датой и временем в MySQL.

Вот ещё интересный пример арифметического действия. Он включает в себя оператор AS, который вы раньше не учили. Суть оператора AS -- в именовании столбца:

Теория (прочитать).

Постановка задачи для этого урока

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

Но в реальной жизни нам бы очень часто требовалось вывести из этой таблицы примерно вот такую информацию:

Мои затраты за весь период

Категория Сумма
Работа 30 грн
Развлечения 40 грн
Еда 120 грн

Такая операция называется "группировка данных". Именно её мы и будем сегодня учить. А поскольку "группировка" по-латыни будет "аггрегация", то и функции, которые мы будем сегодня учить, называются "аггрегатные".

Аггрегатные функции и их использование

Мы будем рассматривать пять аггрегатных функций из SQL: COUNT,SUM,MAX,MIN,AVG. Смысл первых четырёх функций понятен из названия. Название пятой функции происходит от слова AVERAGE -- среднее арифметическое.

Использование аггрегатных функций мы рассмотрим на примере:

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

и

Оператор GROUP BY

Итак, теперь понятно, что если сделать SELECT SUM(чего-то там), то выведется сумма всех значений из таблицы. Но как сделать, чтобы вывелось несколько сумм по каждой категории отдельно? А вот как:

Итак: что делает GROUP BY?

Оператор HAVING

Представьте себе, что вам нужно вывести сумму по каждым затратам, но только за 2015-08-31. Решение, казалось бы, очевидно: в конце предыдущего запроса дописать «WHERE `when`='2015-08-31'». Но не тут-то было!

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

Практическое задание

  1. Допишите в таблицу две затраты на второе сентября;
  2. Выведите суммарную затрату на: 1) Каждое наименование (например, "поездка в метро"); 2) Каждый день; 3) Каждый месяц.
  3. Выведите максимальную, минимальную, среднюю цену из таблицы.
  4. Проверьте, работает ли такой запрос, и объясните, почему он не работает:
  5. Подумайте, как выполнить группировку и по дате, и по категории затраты. Проверьте мысль.

Домашнее задание:

  1. Создайте текстовый файлик.
  2. Не подглядывая в теорию, опишите (коротко), какие операторы вы сегодня выучили и что они делают.
  3. Представьте себе, что вы -- начальник продавца в киоске. У продавца есть кассовый аппарат, который при продаже каждого товара оставляет соответствующую запись в SQL-таблице. Напишите в блокнотовский файл скрипт по созданию такой таблицы (подумайте над полями и типами данных) и внесению в неё 30-ти записей на протяжении одного рабочего дня.
  4. Напишите запрос, который покажет суммарную продажу за каждый час. Напишите запрос, который покажет суммарную продажу по каждому товару.
  5. Изложите письменно мысли, как вы можете увеличить свою прибыль, используя вышеуказанные запросы.
  6. Наберите (или скопируйте, если сможете) вышеуказанную таблицу в Excel. Попробуйте найти или сделать в Excele функцию, которая бы показала суммарную продажу за каждый час (можно пользоваться Гуглом). Напишите в блокнотовском файлике либо описание того, как вы это сделали, либо надпись "сидел (тут будет указание времени, но не меньше получаса), задолбался, так и не сделал".
  7. Прочитайте страницы 47--55 из книги Макса Грубера "Понимание SQL".