Максим Чалышев - Oracle SQL. 100 шагов от новичка до профессионала

Группировки являются одной из самых сложных для понимания тем, поэтому я прошу вас отнестись к этому шагу с максимальным вниманием.

Язык SQL позволяет делить данные, полученные в результате выборки, на группы, объединенные по набору колонок — признаков группы.

Например, мы видим, что в запросе по таблице AUTO есть автомобили КРАСНОГО, ЗЕЛЕНОГО и СИНЕГО цветов.

То есть в данной таблице можно определить группы КРАСНЫХ, ЗЕЛЕНЫХ, СИНИХ автомобилей.

Мы сгруппировали автомобили по признаку цвета — колонке таблицы COLOR, и мы также можем сгруппировать авто по марке LADA или BMW, колонка MARK.

Для группировки данных, работы с группами в языке SQL используется специальный оператор GROUP BY.

Синтаксис

Здесь группировки 1—n, колонка группировки — это названия колонок таблицы, по которой мы группируем данные.

Примеры

Группируем автомобили AUTO по марке (MARK).

Рисунок 85. Демонстрация работы GROUP BY: таблица AUTO MARK

Группируем автомобили AUTO по цвету (COLOR).

Рисунок 86. Группировка по цвету: запрос

Какие цвета автомобилей COLOR есть в таблице AUTO?

Группируем имена MAN по первой букве имени (FIRSTNAME).

В этом примере данные группируются не по колонке, а по результату выражения.

Рисунок 87. Группировка по первой букве имени

Агрегатные функции

Определение группировки было бы неполным без понимания агрегатных функций.

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

Основные агрегатные функции:

Примеры

Вывести наименование города (CITYNAME), сумму и среднее население (PEOPLES) в группах городов CITY.

Рисунок 88. Группировка CITY по CITYNAME, CITYCODE

Один самых часто используемых на практике примеров — подсчет количества элементов в группе.

Вывести количество авто (AUTO), сгруппированных по цвету (COLOR, то есть сколько синих, сколько красных…).

Рисунок 89. Группировка по цвету: запрос

Вывести максимальный и минимальный возраст (YEAROLD) людей из таблицы MAN, сгруппированных по имени.

Рисунок 90. Запрос: группировка по FIRSTNAME MAN

Вывести средний возраст (YEAROLD) людей (MAN) и количество человек в группе, сгруппированных по имени.

Рисунок 91. Таблица MAN: запрос группировки по FIRSTNAME

Вывести количество авто (AUTO) марки BMW, сгруппированных по цвету (COLOR, то есть сколько синих, сколько красных…).

Рисунок 92. Группировка по цвету: запрос к AUTO

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

Ошибочный запрос:

Правильный запрос:

Рисунок 93. Агрегатная функция COUNT: запрос к таблице MAN

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

Примеры

— результат 9.

Количество красных автомобилей (COLOR) в таблице AUTO:

— результат 2.

Количество людей (MAN), которым больше 27 лет (YEAROLD):

— результат 6.

Максимальный и минимальный возраст людей, которым больше 27 лет:

Рисунок 94. Агрегатные функции MIN, MAX: запрос к MAN

Максимальная и минимальная дата выпуска (RELEASEDT) машин BMW:

Рисунок 95. Агрегатные функции MIN, MAX: запрос к AUTO

Сумма населения (PEOPLES) во всех городах таблицы CITY:

Рисунок 96. Агрегатная функция SUM: запрос к CITY

Сумма возраста (YEAROLD) людей MAN, которым больше 30 лет:

Рисунок 97. Агрегатная функция SUM: запрос к MAN

Агрегатные функции и пустые значения

Особенно аккуратно необходимо относиться к наличию пустых значений (NULL) в ячейках таблицы при применении агрегатных функций.

Рассмотрим два запроса:

— результат 7.

— результат 9.

Так произошло, потому что агрегатная функция COUNT подсчитает количество элементов в заданной колонке без учета пустых значений в ячейках этой колонки, без учета NULL-значений.

Агрегатные функции SUM, MAX, MIN по пустым значениям NULL вернут нам пустое значение в качестве результата — NULL, но сумма, или максимальное, или минимальное значение будут правильными.