MySQL / 7. Формирование итогов

Поиск значений, связанных с минимальным и максимальным значениями

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

Решение
Используйте два запроса и переменную SQL. Или «прием MAX-CONCAT». Или соединение (join).

Обсуждение
Функции MIN() и MAX() находят границы диапазона значений. Бывают ситуации, когда после нахождения минимального или максимального значения вам нужно получить и другие значения той строки, в которой оно встретилось. Например, наибольшее население штата можно получить так:

mysql> SELECT MAX(pop) FROM states;

+------------+
| MAX(pop) |
+------------+
| 29760021 |
+------------+

Но при этом вы не знаете, какой именно штат имеет такое население. Кажется, что можно вывести необходимую информацию так:

mysql> SELECT name, MAX(pop) FROM states WHERE pop = MAX(pop);
ERROR 1111 at line 1: Invalid use of group function

Практически каждый рано или поздно пробует сделать нечто подобное, но ничего не получается, поскольку агрегирующие функции, такие как MIN() и MAX(), нельзя использовать в инструкциях WHERE. В приведенном выше предложении мы пытались сначала определить, какая запись содержит макси-мальное значение для численности населения, а затем вывести название соответствующего штата. Проблема в том, что мы-то хорошо понимаем, что имели в виду, а вот для MySQL все это не имеет никакого смысла. Запрос не удается выполнить, так как MySQL использует инструкцию WHERE для того, чтобы определить, какие записи выбрать, но при этом узнаёт значение агрегирующей функции только после того, как выбраны записи, по которым определяется значение функции! То есть предложение является внутренне противоречивым. Можно было бы справиться с этим, используя подзапрос, однако MySQL будет поддерживать их только начиная с версии 4.1. Между тем, можно разбить решение задачи на два этапа: первый запрос будет извлекать максимальное значение в переменную SQL, а второй – ссылаться на эту переменную в своей инструкции WHERE:

mysql> SELECT @max := MAX(pop) FROM states;
mysql> SELECT @max AS 'highest population', name FROM states WHERE pop = @max;

+----------------------+-----------+
| highest population | name       |
+----------------------+-----------+
| 29760021              | California |
+----------------------+-----------+

Этот прием работает даже тогда, когда минимальное или максимальное значение не содержится непосредственно в строке, а получается из нее. Если вы хотите узнать длину самого короткого стиха в King James Version, ее легко можно вычислить так:

mysql> SELECT MIN(LENGTH(vtext)) FROM kjv;

+-------------------------+
| MIN(LENGTH(vtext)) |
+-------------------------+
| 11                              |
+-------------------------+

Если же вы хотите получить ответ на вопрос: «Что это за стих?», выполните следующий запрос:

mysql> SELECT @min := MIN(LENGTH(vtext)) FROM kjv;
mysql> SELECT bname, cnum, vnum, vtext FROM kjv WHERE LENGTH(vtext) = @min;

+--------+-------+-------+---------------+
| bname | cnum | vnum | vtext            |
+--------+-------+-------+---------------+
| John    | 11     | 35      | Jesus wept. |
+--------+-------+-------+---------------+

Есть и другой способ, который можно использовать для нахождения значений, связанных с минимумом или максимумом. В справочном руководстве по MySQL он называется «прием MAX-CONCAT» («MAX-CONCAT trick»).

Способ не очень изящен, но может оказаться полезным тем, кто работает с версиями MySQL, не допускающими использования переменных SQL. Технология такова: используя CONCAT(), добавляем столбец к столбцу суммирования, находим максимум получившихся значений при помощи MAX() и из-влекаем несуммируемую часть значения результата. Например, чтобы вывести название штата с наибольшей численностью населения, можно выбрать максимальное составное значение столбцов pop и name, а затем извлечь из него составляющую name. Давайте будем действовать последовательно.

Сначала определим максимальную численность населения, чтобы узнать размер этого значения:

mysql> SELECT MAX(pop) FROM states;

+------------+
| MAX(pop) |
+------------+
| 29760021 |
+------------+

Восемь символов. Такая информация необходима для того, чтобы соответствующим образом разместить составляющую названия штата в комбинированном значении население-штат, обеспечив тем самым его корректное извлечение. Составляющая названия должна начинаться с фиксированной позиции. Зная, что максимальное значение численности населения состоит из восьми символов, дополним столбец pop пробелами до восьми символов, тогда значения name всегда будут начинаться с девятой позиции. Но будьте внимательны при дополнении значений численности населения пробелами. Значения, порождаемые функцией CONCAT(), являются строками, поэтому функция MAX() при сортировке будет рассматривать значения население-штат как строки. Если выровнять значения pop по левому краю, дополнив их пробелами справа с помощью RPAD(), то составные значения будут такими:

mysql> SELECT CONCAT(RPAD(pop,8,' '),name) FROM states;

+---------------------------------------+
| CONCAT(RPAD(pop,8,' '),name) |
+---------------------------------------+
| 4040587 Alabama                        |
| 550043 Alaska                             |
| 3665228 Arizona                          |
| 2350725 Arkansas                       |
...

Эти значения будут упорядочиваться в лексическом порядке, что удобно для нахождения наибольшего строкового значения с помощью функции MAX().

Но значения pop – это числа, и хотелось бы, чтобы значения упорядочивались как числа. Чтобы лексическое упорядочивание соответствовало числовому, необходимо выровнять значения численности населения вправо, дополнив их пробелами слева при помощи LPAD():

mysql> SELECT CONCAT(LPAD(pop,8,' '),name) FROM states;

+--------------------------------------+
| CONCAT(LPAD(pop,8,' '),name) |
+--------------------------------------+
| 4040587Alabama                        |
| 550043Alaska                             |
| 3665228Arizona                         |
| 2350725Arkansas                       |
...

Теперь используем выражение CONCAT() в сочетании с MAX() для нахождения значения с наибольшей составляющей численности населения:

mysql> SELECT MAX(CONCAT(LPAD(pop,8,' '),name)) FROM states;

+---------------------------------------------+
| MAX(CONCAT(LPAD(pop,8,' '),name)) |
+---------------------------------------------+
| 29760021California                              |
+---------------------------------------------+

Чтобы получить конечный результат (название штата с наибольшим населением), извлеките из максимального составного значения подстроку, начинающуюся с девятого символа:

mysql> SELECT SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) FROM states;

+----------------------------------------------------------------+
| SUBSTRING(MAX(CONCAT(LPAD(pop,8,' '),name)),9) |
+----------------------------------------------------------------+
| California                                                                       |
+----------------------------------------------------------------+

Естественно, для хранения промежуточного результата гораздо удобнее использовать переменную SQL. Это и более эффективно, так как не приходится заниматься объединением значений столбцов для сортировки и разбиением результата на составляющие для вывода.

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

mysql> CREATE TEMPORARY TABLE t
-> SELECT MAX(pop) as maxpop FROM states;
mysql> SELECT states.* FROM states, t WHERE states.pop = t.maxpop;

+-----------+---------+--------------+------------+
| name       | abbrev | statehood   | pop          |
+-----------+---------+--------------+------------+
| California | CA       | 1850-09-09 | 29760021 |
+-----------+---------+--------------+-------------+

Статьи по MySQL на эту тему:

Выбор групп только с определенными характеристиками
Группирование по результатам выражения
Итоги и значения NULL
Итоги по датам
Классификация некатегориальных данных

Вернуться в раздел: MySQL / 7. Формирование итогов