Выберите одно значение из группы на основе порядка из других столбцов
Предположим, у меня есть эта таблицаtab
(играть на скрипке имеется в наличии).
| g | a | b | v |
---------------------
| 1 | 3 | 5 | foo |
| 1 | 4 | 7 | bar |
| 1 | 2 | 9 | baz |
| 2 | 1 | 1 | dog |
| 2 | 5 | 2 | cat |
| 2 | 5 | 3 | horse |
| 2 | 3 | 8 | pig |
Я группирую строки поg
и для каждой группы я хочу одно значение из столбцаv
, Однако я не хочуany значение, но я хочу значение из строки с максимальнымa
и из всех тех, с максимальнымb
, Другими словами, мой результат должен быть
| 1 | bar |
| 2 | horse |
Current solution
Я знаю запрос для достижения этой цели:
SELECT grps.g,
(SELECT v FROM tab
WHERE g = grps.g
ORDER BY a DESC, b DESC
LIMIT 1) AS r
FROM (SELECT DISTINCT g FROM tab) grps
Question
Но я считаю этот запрос скорееugly, Главным образом потому, что он используетdependant subquery, который чувствует себя как настоящий убийца производительности. Поэтому мне интересно, есть ли более простое решение этой проблемы.
Expected answersНаиболее вероятным ответом на этот вопрос, который я ожидаю, будет какое-то дополнение или исправление для MySQL (или MariaDB), которое предоставляет такую возможность. Но я приветствую и другие полезные источники вдохновения. Все, что работает без зависимого подзапроса, будет рассматриваться как ответ.
Если ваше решение работает только для одного столбца заказа, т. Е. Нельзя различитьcat
а такжеhorse
не стесняйтесь предлагать этот ответ, так как я ожидаю, что он все еще будет полезен для большинства случаев использования. Например,100*a+b
было бы вероятным способом упорядочить вышеуказанные данные по обоим столбцам, при этом все еще используя только одно выражение.
Я имею в виду несколько довольно хакерских решений и могу добавить их через некоторое время, но сначала я посмотрю и посмотрим, появятся ли какие-нибудь приятные новые.
Benchmark resultsПоскольку сравнивать различные ответы довольно сложно, просто взглянув на них, я проверил некоторые тесты для них. Это было запущено на моем рабочем столе с использованием MySQL 5.1. Числа не будут сравниваться с любой другой системой, только друг с другом. Вы, вероятно, должны проводить свои собственные тесты с реальными данными, если производительность имеет решающее значение для вашего приложения. Когда приходят новые ответы, я могу добавить их в свой сценарий и перезапустить все тесты.
100,000 items, 1,000 groups to choose from, InnoDb: 0.166s for MvG (from question) 0.520s for RichardTheKiwi 2.199s for xdazz 19.24s for Dems (sequential sub-queries) 48.72s for acatt 100,000 items, 50,000 groups to choose from, InnoDb: 0.356s for xdazz 0.640s for RichardTheKiwi 0.764s for MvG (from question) 51.50s for acatt too long for Dems (sequential sub-queries) 100,000 items, 100 groups to choose from, InnoDb: 0.163s for MvG (from question) 0.523s for RichardTheKiwi 2.072s for Dems (sequential sub-queries) 17.78s for xdazz 49.85s for acattТаким образом, кажется, что мое собственное решение пока не так уж плохо, даже с зависимым подзапросом. Удивительно, но решение acatt, которое также использует зависимый подзапрос и которое я бы поэтому рассмотрел примерно так же, работает намного хуже. Вероятно, с этим не справится оптимизатор MySQL. Предлагаемое RichardTheKiwi решение, похоже, также имеет хорошую общую производительность. Два других решения сильно зависят от структуры данных. Для многих групп небольшие группы, xdazz & apos; Подход превосходит все остальные, в то время как решение Dems работает лучше (хотя все еще не исключительно хорошо) для нескольких больших групп.