Сортировка и группировка в SQL

Сортировка и группировка данных в SQLПронина Ю.Э.

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

заключается к приведению последовательности к определенному порядку.
Сортировка может быть выполнена по любым полям с любым типом данных. Это может быть сортировка по возрастанию или убыванию для числовых полей. Для символьных (текстовых) полей это может быть сортировка в алфавитном порядке, хотя по сути, она так же является сортировкой по возрастанию или убыванию. Она так же может быть выполнена в любых направлениях – от А, до Я, и наоборот от Я, до А.

Группировка данных

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

Операторы сравнения в SQL

• «>» – больше чего-либо;
• «<» – меньше чего-нибудь;
• «=» – равно;
• «<>» – не равно;
• «>=» – больше или равно;
• «<=» – меньше или равно;
• ключевое слово BETWEEN - значение в определенном промежутке

В SQL очень полезные так называемые агрегирующие функции, а именно:

• COUNT — количество значений в указанном столбце;
• SUM — сумма значений в указанном столбце;
• AVG — среднее значение в указанном столбце;
• MIN — минимальное значение в указанном столбце;
• MAX — максимальное значение в указанном столбце.

Направление сортировки:

• ASC - по возрастанию;
• DESC - по убыванию.

Условие выборки – оператор WHERE

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


где, WHERE и есть условие, т.е. мы отображаем только те строки, которые соответствуют нашему условию (цена больше 100).

Очень часто внутри элемента where используются IN / NOT IN для фильтрации столбца по нескольким значениям

Фильтрация по одному условию и одному значению:
select * from Customers WHERE City = 'London‘

Фильтрация по одному условию и нескольким значениям с применением IN (включение) или NOT IN (исключение):
select * from Customers where City IN ('London', 'Berlin')
select * from Customers where City NOT IN ('Madrid', 'Berlin','Bern')

Очень часто внутри элемента where используются AND / OR для фильтрации таблицы по нескольким столбцам.

Фильтрация по нескольким условиям с применением AND (выполняются все условия) или OR (выполняется хотя бы одно условие) и нескольким значениям:
select * from Customers where Country = 'Germany' AND City not in ('Berlin', 'Aachen') AND CustomerID > 15
select * from Customers where City in ('London', 'Berlin') OR CustomerID > 4

Сортировка в запросе SQLПорядок команды ORDER BY в запросе

Для выполнения сортировки в строку запроса нужно добавить команду ORDER BY. После этой команды указывается поле, по которому производится сортировка.

Сортировка строк чаще всего проводится вместе с условием на выборку данных. Команда ORDER BY ставится после условия выборки WHERE. Например, выбираем товары с ценой меньше 100 рублей, упорядочив по названию в алфавитном порядке:
SELECT * FROM goods WHERE price < 100 ORDER BY title ASC

Для примера используем таблицу товаров goods:

Данные здесь уже упорядочены по столбцу "num". Теперь, построим запрос, который выведет таблицу с товарами, упорядоченными в алфавитном порядке:

SELECT * FROM goods ORDER BY title
- SELECT * FROM goods – указывает выбрать все поля из таблицы goods;
- ORDER BY – команда сортировки;
- title – столбец, по которому будет выполняться сортировка.
Результат выполнения такого запроса следующий:

Направление сортировки

По умолчанию, команда ORDER BY выполняет сортировку по возрастанию. Чтобы управлять направлением сортировки вручную, после имени столбца указывается ключевое слово ASC (по возрастанию) или DESC (по убыванию).
Таким образом, чтобы вывести нашу таблицу в порядке убывания цен, нужно задать запрос так:
SELECT * FROM goods ORDER BY price DESC
Сортировка по возрастанию цены будет:
SELECT * FROM goods ORDER BY price ASC

Сортировка по нескольким полям

SQL допускает сортировку сразу по нескольким полям.
Для этого после команды ORDER BY необходимые поля указываются через запятую. Порядок в результате запроса будет настраиваться в той же очередности, в которой указаны поля сортировки.

Отсортируем таблицу по следующим правилам:SELECT * FROM mytable ORDER BY column1 ASC, column2 DESC, column3 ASC

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

Группировка данных осуществляется внутри SQL-запросов через конструкции GROUP BY И HAVING

Большинство информации в базах данных хранятся в детализированном виде. Однако, часто возникает необходимость получить сводки. Например, узнать общее число комментариев пользователей или быть может количество товара на складах. Поэтому в языке SQL специально для таких случаев предусмотрены конструкции group by и having, позволяющие, соответственно, группировать и отфильтровывать полученные группы данных.

GROUP BY

GROUP BY — необязательный элемент запроса, с помощью которого можно задать агрегацию по нужному столбцу (например, если нужно узнать какое количество клиентов живет в каждом из городов).При использовании GROUP BY обязательно:
перечень столбцов, по которым делается разрез, был одинаковым внутри SELECT и внутри GROUP BY,
агрегатные функции (SUM, AVG, COUNT, MAX, MIN) должны быть также указаны внутри SELECT с указанием столбца, к которому такая функция применяется.

HAVING

HAVING — необязательный элемент запроса, который отвечает за фильтрацию на уровне сгруппированных данных (по сути, WHERE, но только на уровень выше).
Фильтрация агрегированной таблицы с количеством клиентов по городам, в данном случае оставляем в выгрузке только те города, в которых не менее 5 клиентов:select City, count(CustomerID) from Customers group by City HAVING count(CustomerID) >= 5

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

Где,
user_name - имя пользователя
forum_group - имя группы
raiting - итоговый рейтинг пользователя
mess_count - количество сообщений
is_have_social_profile - указан ли в профиле форуме ссылка на страничку в социальной сети

Чистая группировка с помощью GROUP BY

Представим, что нам необходимо узнать ценность каждой группы, а именно среднее значение рейтинга пользователей в группе и общее число сообщений, оставленных в форуме.
Вначале, небольшое словесное описание, чтобы легче было понимать SQL-запрос. Итак, нам нужно найти вычисляемые значения по группам форума. Соответственно, вам нужно поделить все эти десять строк на три разные группы: admin, moder, user. Чтобы это сделать, нужно в конце запроса добавить группировку по значениям поля forum_group. А так же добавить в select вычисляемые выражения с использованием так называемых агрегатных функций.
Вот как будет выглядеть SQL-запрос

Чистая группировка с помощью GROUP BY

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

1. Вначале все строки исходной таблицы были разбиты на три группы по значениям поля forum_group. Например, внутри группы admin было три пользователя. Внутри moder так же 3 строки. А внутри группы user было 4 строки (четыре пользователя).
2. Затем для каждой группы применялись агрегатные функции. Например, для группы admin средний рейтинг вычислялся так (2 + 5 + 5)/3 = 4. Количество сообщений вычислялось так (10 + 15 + 25) = 50.

AVG — среднее значение в указанном столбце;

Группировка с помощью GROUP BY и фильтрацией групп с HAVING

Допустим нам нужно оценить эффективность действий по привлечению пользователей к социальной деятельности. Если по-простому, то узнать сколько пользователей в группах оставило ссылки на свои профили, а сколько проигнорировало рассылки и прочее. Однако, в реальной жизни таких групп может быть много, поэтому нам нужно отфильтровать те группы, которыми можно пренебречь (к примеру, слишком мало людей не оставило ссылку; зачем захламлять полный отчет). В примере это группы, где всего один пользователь.
Вначале, словесно опишем что необходимо сделать в SQL-запросе. Нам нужно все строки исходной таблицы userstat разделить по следующим признакам: имя группы и наличие социального профиля. Соответственно, необходимо группировать данные таблицы по полям forum_group и is_have_social_profile. Однако, нас не интересуют те группы, где всего один человек. Следовательно такие группы нужно отфильтровать.
Напоминание важного момента!!! Фильтровать с помощью having можно только при применении агрегатных функций, а не по отдельным полям. Другими словами, это не конструкция where, это фильтр именно групп строк, а не отдельных записей. Хотя условия внутри задаются аналогичным образом с помощью "or" и "and".
Вот как будет выглядеть SQL-запрос

Группировка с помощью GROUP BY и фильтрацией групп с HAVING

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

1. Вначале было получено 6 групп. Каждая из групп по forum_group была разбита на две подгруппы по значениям поля is_have_social_profile. Другими словами группы: [admin, 1], [admin, 0], [moder, 1], [moder, 0], [user, 1], [user, 0].

Примечание: Кстати, групп не обязательно должно было бы получится 6. Так, к примеру, если бы все администраторы заполнили профиль, то групп было бы 5, так как поле is_have_social_profile имело бы только одно значение у пользователей группы admin.

2. Затем для каждой группы было применено условие фильтрации в having. Поэтому были исключены следующие группы: [admin, 0], [moder, 0], [user, 1]. Так как внутри каждой такой группы присутствовала всего одна строка исходной таблицы.

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

COUNT — количество значений в указанном столбце;

Вопросы для самоконтроля:

1. При каком условии можно фильтровать с помощью having?
2. В каком порядке будут выстраиваться результаты запроса при сортировке?
3. Сортировка в запросе SQL осуществляется через команду?
4. Какие существуют направления сортировки?
5. На какой позиции ставится команда ORDER BY?