Группировка, оператор GROUP BY

Давайте выполним запрос:

MySQL
SELECT id, home_type, has_tv, price FROM Rooms;

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

Например, такими группами могут выступать записи разбитые по типу жилья:

• Shared room (аренда комнаты на несколько человек)
• Private room (аренда целой комнаты)
• Entire home/apt (аренда целой квартиры)

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

Такой полезной информацией о группах может быть:

• средняя стоимость аренды комнаты или целого жилого помещения
• количество сдаваемых жилых помещений каждого типа

Для ответов на все эти и многие другие вопросы есть оператор GROUP BY.

Общая структура запроса с GROUP BY

MySQL
SELECT [литералы, агрегатные_функции, поля_группировки]
FROM имя_таблицы
GROUP BY поля_группировки;

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

MySQL
SELECT home_type FROM Rooms
GROUP BY home_type

Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу

При использовании оператора GROUP BY мы перешли от работы с отдельными записями на работу с образовавшимися группами. В связи с этим мы не можем просто вывести любое поле из записи (например, has_tv или price), как мы это могли делать раньше. Так как в каждой группе может быть несколько записей и в каждой из них в этом поле может быть разное значение.

При использовании GROUP BY мы можем выводить только:

• литералы, т.е. указанное явным образом фиксированные значения.

  Мы можем их выводить, так как это фиксированные значения, которые ни от чего не зависят.
  Например,

  MySQL
  SELECT home_type, "literal" FROM Rooms
  GROUP BY home_type
  

  home_type	literal
  Private room	literal
  Entire home/apt	literal
  Shared room	literal

• результаты агрегатных функций, т.е. вычисленные значения на основании набора значений.

  Более детальную информацию об агрегатных функциях мы затронем на следующем уроке. Но для примера рассмотрим агрегатную функцию AVG.
  Функция AVG принимает в качестве аргумента название поля, по которому мы хотим вычислить среднее значение для каждой группы.

  MySQL
  SELECT home_type, AVG(price) as avg_price FROM Rooms
  GROUP BY home_type
  

  home_type	avg_price
  Private room	89.4286
  Entire home/apt	148.6667
  Shared room	40

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

• поля группировки.

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

Группировка по 2 и более полям

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

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

Пример группировки по home_type и has_tv:

Давайте проверим себя? При использовании оператора GROUP BY в SELECT выражение мы можем выводить: