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

3. Объедините таблицы AUTO, MAN; выведите следующие данные: марка автомобиля, цвет, имя владельца; отсортируйте результаты по колонке «марка автомобиля», «цвет автомобиля» в обратном порядке.

4. Объедините таблицы CITY, MAN; выведите следующие данные: название города, имя; отсортируйте результаты по колонке «название города», используя левое объединение LEFT JOIN.

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

Язык 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, но сумма, или максимальное, или минимальное значение будут правильными.

Я видел выражение COUNT (1) в одном из примеров. Что значит COUNT (1) и почему именно 1?

Оператор COUNT подсчитывает количество элементов в группе, вместо элемента мы задаем единичку; таким образом, каждой строчке в группе будет соответствовать единица, количество строчек будет считаться как сумма единичек, строчек.

Как использовать группировки совместно с функциями для работы со строками и другими встроенными функциями языка SQL? Приведите несколько примеров, пожалуйста.

У нас уже был подобный пример, но для лучшего понимания посмотрим еще несколько примеров.

Группируем имена MAN по первой букве имени (FIRSTNAME); найдем, сколько имен начинаются с заданной буквы в таблице MAN.

Рисунок 98. Группировка по SUBSTR: таблица MAN

Сгруппируем названия городов по последним двум буквам и найдем сумму количества населения в каждой группе.

Рисунок 99. Группировка по SUBSTR: таблица MAN

GROUP BY можно использовать вместо DISTINCT?

Во множестве случаев GROUP BY действительно заменяет DISTINCT.

Например, вывести уникальные цвета и марки автомобилей из таблицы AUTO.

Результат одинаков при использовании GROUP BY и DISTINCT.

Рисунок 100. Запрос к AUTO: демонстрация DISTINCT

1. Найдите ошибку в запросе:

2. Выведите количество авто из AUTO, сгруппированных по марке (MARK) (то есть сколько BMW, сколько LADA…).

3. Выведите количество синих (COLOR) автомобилей в таблице AUTO.

4. Выведите максимальную и минимальную численность населения (PEOPLES) во всех городах (CITY).

5. Выведите средний возраст людей (MAN) и количество человек в группе, сгруппированных по ПЕРВОЙ БУКВЕ имени (FIRSTNAME).