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

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

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

Обсуждение
Во многих задачах требуется нахождение наибольшего или наименьшего значения какого-то столбца, но при этом часто необходимо знать и значения других столбцов строки, содержащей такое значение. Например, вы можете использовать MAX(pop) для определения максимального населения штата в таблице states, но хотелось бы также получить ответ на вопрос о том, какой штат имеет такое население. Одним из способов получения ответа является использование переменной SQL:

mysql> SELECT @max := MAX(pop) FROM states;
mysql> SELECT * FROM states WHERE pop = @max;

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

Можно использовать и соединение.


Сначала выберем максимальное значение численности населения во временную таблицу:

mysql> CREATE TABLE tmp SELECT MAX(pop) as maxpop FROM states;

Затем объединим временную таблицу с исходной для поиска записи, соответствующей выбранному значению численности населения:mysql> SELECT states.* FROM states, tmp WHERE states.pop = tmp.maxpop;

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

Применяя описанные методы к таблицам artist и painting, вы сможете ответить на вопросы типа «Какая картина коллекции была самой дорогой, кто ее написал?». Если вы используете переменную SQL, сохраните в ней наибольшую цену, затем используйте переменную для идентификации записи, содержащей такое значение цены, с тем, чтобы извлечь из нее другие столбцы:

mysql> SELECT @max_price := MAX(price) FROM painting;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist, painting
-> WHERE painting.price = @max_price
-> AND painting.a_id = artist.a_id;

+---------+------------------+------+
| name | title | price |
+---------+------------------+------+
| Da Vinci | The Mona Lisa | 87 |
+---------+------------------+------+

То же самое можно сделать, создав временную таблицу для хранения максимальной цены, а затем объединив ее с другими таблицами:

mysql> CREATE TABLE tmp SELECT MAX(price) AS max_price FROM painting;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist, painting, tmp
-> WHERE painting.price = tmp.max_price
-> AND painting.a_id = artist.a_id;

+---------+-----------------+-------+
| name | title | price |
+---------+-----------------+-------+
| Da Vinci | The Mona Lisa | 87 |
+---------+------------------+-------+

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


Есть ли смысл применять его? Да, потому что он является основой общей техники решения более сложных задач. Предыдущие запросы выводят информацию только для одной самой дорогой картины всей таблицы painting. А если бы вопрос стоял так: «Какая картина каждого художника была самой дорогой?». Для ответа на этот вопрос нельзя использовать переменную SQL, так как необходимо найти по одному значению цены на каждого художника, а переменная может хранить только одно значение одновременно. Временная таблица может хранить несколько значений, и соединение может искать соответствия для всех таких значений сразу. Для получения ответа на последний вопрос выберите идентификатор каждого автора и максимальную цену его картины во временную таблицу. Таблица будет содержать не просто максимальную цену картины, а максимумы для каждой группы, гдегруппа – это картины определенного художника. Затем используйте хранящиеся в таблице tmp идентификаторы авторов и цены для сравнения с записями таблицы painting и объедините результат с таблицей artist для получения фамилий художников:

mysql> CREATE TABLE tmp
-> SELECT a_id, MAX(price) AS max_price FROM painting GROUP BY a_id;
mysql> SELECT artist.name, painting.title, painting.price
-> FROM artist, painting, tmp
-> WHERE painting.a_id = tmp.a_id
-> AND painting.price = tmp.max_price
-> AND painting.a_id = artist.a_id;

+------------+---------------------+-------+
| name | title | price |
+------------+---------------------+-------+
| Da Vinci | The Mona Lisa | 87 |
| Van Gogh | The Potato Eaters | 67 |
| Renoir | Les Deux Soeurs | 64 |
+------------+---------------------+------+

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


Рассмотрим таблицу driver_log, в которой перечислены водители и их поездки:

mysql> SELECT name, trav_date, miles
-> FROM driver_log
-> ORDER BY name, trav_date;

+-------+---------------+------+
| name | trav_date | miles |
+-------+---------------+------+
| Ben | 2001-11-29 | 131 |
| Ben | 2001-11-30 | 152 |
| Ben | 2001-12-02 | 79 |
| Henry | 2001-11-26 | 115 |
| Henry | 2001-11-27 | 96 |
| Henry | 2001-11-29 | 300 |
| Henry | 2001-11-30 | 203 |
| Henry | 2001-12-01 | 197 |
| Suzi | 2001-11-29 | 391 |
| Suzi | 2001-12-02 | 50 |
+-------+--------------+-------+

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

mysql> CREATE TABLE tmp
-> SELECT name, MAX(trav_date) AS trav_date
-> FROM driver_log GROUP BY name;
mysql> SELECT driver_log.name, driver_log.trav_date, driver_log.miles
-> FROM driver_log, tmp
-> WHERE driver_log.name = tmp.name
-> AND driver_log.trav_date = tmp.trav_date-> ORDER BY driver_log.name;

+------+----------------+-------+
| name | trav_date | miles |
+-------+---------------+------+
| Ben | 2001-12-02 | 79 |
| Henry | 2001-12-01 | 197 |
| Suzi | 2001-12-02 | 502 |
+-------+--------------+-------+

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Вставка записей в таблицу, включающую значения из другой
Вывод списков для записей «главная-подчиненная» и итогов
Вычисление разности между последовательными строками
Вычисление рейтинга команд
Выявление и удаление несвязанных записей