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

Язык SQL позволяет гармонично сочетать операции группировки данных GROUP BY с операторами объединения JOIN.

Конструкция GROUP BY оперирует с результатами запроса, поэтому прекрасно работает с запросами на объединение данных JOIN.

Приведем несколько примеров.

Вывести сведения: список марок автомобиля, а также сколько людей приобрели авто указанной марки; объединить таблицы MAN и AUTO по колонке PHONENUM.

Рисунок 101. Группировка по нескольким таблицам

Вывести наименование города, код города, а также количество людей и средний возраст людей из таблицы MAN, проживающих в данном городе, объединив таблицы MAN и CITY по колонке CITYCODE.

Рисунок 102. Группировка по нескольким таблицам MAN, CITY

Сортировка результатов запросов

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

Вывести сведения: список марок автомобиля, а также сколько людей приобрели авто указанной марки, объединив таблицы MAN и AUTO по колонке PHONENUM, отсортировать по названию марок авто.

Рисунок 103. Группировка по таблицам AUTO, MAN

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

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

Рисунок 104. Группировка по таблицам MAN, CITY

А можно ли с помощью агрегатной функции COUNT подсчитать количество уникальных элементов?

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

Предположим, что нам необходимо вывести список марок автомобиля, а также сколько людей с уникальным именем приобрели авто указанной марки, объединив таблицы MAN и AUTO по колонке PHONENUM.

Обратите внимание: мы использовали конструкцию DISTINCT MAN.FIRSTNAMEв агрегатной функции COUNT.

Рисунок 105. Группировка объединенных данных по MARK таблиц AUTO, MAN

1. Выведите марки автомобилей из таблицы AUTO и найдите количество людей, купивших одну и ту же марку авто, не проживающих в одном и том же городе.

2. Выведите названия городов и сумму возрастов, средний возраст жителей этого города из таблицы MAN, отсортируйте данные по количеству жителей из таблицы MAN в каждом из городов.

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

Перед нами неизбежно возникает необходимость ограничить результаты вывода групп.

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

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

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

Синтаксис

Здесь после HAVING условие — это условие ограничения на вывод групп.

Примеры

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

Рисунок 106. Группировка по цвету: таблица AUTO

Сгруппировать данные в таблице AUTO по марке, найти количество в группе, выбрать только группы, где больше одного авто.

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

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

Рисунок 108. Использование HAVING: запрос

В ORACLE SQL существует специальный идентификатор для каждой строки таблицы — это колонка ROWID, уникальный идентификатор строки.

Что такое ROWID? Уникальный идентификатор: состоит из номера объекта (32 бита), относительного номера файла (10 бит), номера блока (22 бита) и номера строки (16 бит).

Как использовать ROWID? Если нет другого уникального идентификатора, первичного ключа, то можно опираться на ROWID.

Пример работы с ROWID — находим дубли.

Пример работы с ROWID — находим количество повторенийю

Пример работы с ROWID — сортировка.

Для удаления дубликатов строк в таблице без первичного ключа можно обратиться именно к ROWID-колонке.

Пример удаления дублей из таблицы AUTO

В этом примере группируем таблицу AUTO по всем колонкам, находим MAX (ROWID) сгруппированных записей и удаляем те записи, ROWID которых нет в сгруппированном наборе данных.

• Оператор HAVING нельзя использовать в запросах без GROUP BY.

• Оператор HAVING возможно также применять вместе c сортировкой ORDER BY.

Синтаксис

Примеры

Сгруппировать данные в таблице AUTO по цвету, найти количество авто в каждой группе, отсортировать по цвету.

Сгруппировать данные в таблице AUTO по цвету, найти количество авто в каждой группе.

Отсортировать количество авто в каждой группе.

Есть ли другие способы удаления дублей из таблиц?

Да, например с использованием EXIST, с использованием аналитических функций.

Используем EXIST:

Будет ли работать HAVING в запросах с несколькими таблицами?

Да, ограничений нет.

Пример запроса:

Ранее мы уже встречались с оператором IN в запросах SQL в инструкции WHERE, в качестве фильтра по перечислению значений.

Сейчас мы познакомимся с еще одним способом использования IN, который достаточно часто применяется на практике.

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

Вспомните синтаксис запроса с инструкцией IN, который мы проходили ранее.

SELECT * или колонки через запятую FROM таблиц WHERE имяколонки IN (перечень значений).