Выберите одно значение из группы на основе порядка из других столбцов

проблема

Предположим, у меня есть эта таблица 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, Тем не менее, я не хочу никакого значения, но я хочу, чтобы значение из строки с максимальным a и из всех тех, с максимальным b, Другими словами, мой результат должен быть

| 1 |   bar |
| 2 | horse |

Текущее решение

Я знаю запрос для достижения этой цели:

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

Вопрос

Но я считаю этот запрос довольно некрасивым. Главным образом потому, что он использует зависимый подзапрос, который ощущается как настоящий убийца производительности. Поэтому мне интересно, есть ли более простое решение этой проблемы.

Ожидаемые ответы

Наиболее вероятным ответом на этот вопрос, который я ожидаю, будет какое-то дополнение или исправление для MySQL (или MariaDB), которое предоставляет такую ​​возможность. Но я также приветствую и другие полезные идеи. Все, что работает без зависимого подзапроса, будет рассматриваться как ответ.

Если ваше решение работает только для одного столбца заказа, т.е. не может различить cat а также horse не стесняйтесь предлагать этот ответ, так как я ожидаю, что он все еще будет полезен для большинства случаев использования. Например, 100*a+b было бы вероятным способом упорядочить вышеупомянутые данные по обоим столбцам, все еще используя только одно выражение.

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

Результаты тестов

Поскольку сравнивать различные ответы довольно сложно, просто взглянув на них, я провел несколько тестов по ним. Это было запущено на моем рабочем столе с использованием MySQL 5.1. Числа не будут сравниваться ни с какой другой системой, только друг с другом. Вы, вероятно, должны проводить свои собственные тесты с реальными данными, если производительность имеет решающее значение для вашего приложения. Когда приходят новые ответы, я могу добавить их в свой сценарий и перезапустить все тесты.

Так что, похоже, мое собственное решение не так уж и плохо, даже с зависимым подзапросом. Удивительно, но решение acatt, которое также использует зависимый подзапрос и которое я бы поэтому рассмотрел примерно так же, работает намного хуже. Вероятно, что-то, с чем не может справиться оптимизатор MySQL. Предлагаемое RichardTheKiwi решение, похоже, также имеет хорошую общую производительность. Два других решения сильно зависят от структуры данных. Для многих групп небольших групп подход xdazz превосходит все остальные, в то время как решение Dems работает лучше (хотя и не всегда хорошо) для нескольких больших групп.

Источник

4 ответа

Решение
SELECT g, a, b, v
  FROM (
            SELECT *, 
                   @rn := IF(g = @g, @rn + 1, 1) rn, 
                   @g := g
              FROM (select @g := null, @rn := 0) x, 
                   tab
          ORDER BY g, a desc, b desc, v
       ) X
 WHERE rn = 1;

Один проход. Все остальные решения выглядят O(n^2) для меня.

Источник

Этот способ не использует подзапрос.

SELECT t1.g, t1.v
FROM tab t1
LEFT JOIN tab t2 ON t1.g = t2.g AND (t1.a < t2.a OR (t1.a = t2.a AND t1.b < t2.b))
WHERE t2.g IS NULL

Объяснение:

LEFT JOIN работает на основе того, что, когда t1.a имеет максимальное значение, нет s2.a с большим значением, и значения строк s2 будут NULL.

Источник

Многие РСУБД имеют конструкции, которые особенно подходят для этой проблемы. MySQL не один из них.

Это приводит вас к трем основным подходам.

  • Проверьте каждую запись, чтобы увидеть, является ли она той, которую вы хотите, используя EXISTS и коррелированный подзапрос в предложении EXISTS. (ответ @acatt, но я понимаю, что MySQL не всегда оптимизирует это очень хорошо. Убедитесь, что у вас есть составной индекс на (g,a,b) прежде чем предположить, что MySQL не будет делать это очень хорошо.)

  • Сделайте половину декартового произведения, чтобы полностью заполнить тот же чек. Любая запись, которая не присоединяется, является целевой записью. Если каждая группа ("g") велика, это может быстро снизить производительность (если для каждого уникального значения 10 записей) g , это даст ~50 записей и отбросит 49. Для размера группы 100 это даст ~5000 записей и отбросит 4999), но это отлично подходит для небольших групп. (ответ @xdazz.)

  • Или используйте несколько подзапросов для определения MAX(a), а затем MAX(b)...

Несколько последовательных подзапросов...

SELECT
  yourTable.*
FROM
  (SELECT g,    MAX(a) AS a FROM yourTable GROUP BY g   ) AS searchA
INNER JOIN
  (SELECT g, a, MAX(b) AS b FROM yourTable GROUP BY g, a) AS searchB
    ON  searchA.g = searchB.g
    AND searchA.a = searchB.a
INNER JOIN
  yourTable
    ON  yourTable.g = searchB.g
    AND yourTable.a = searchB.a
    AND yourTable.b = searchB.b

В зависимости от того, как MySQL оптимизирует второй подзапрос, это может быть или не быть более производительным, чем другие параметры. Это, однако, самый длинный (и, возможно, наименее обслуживаемый) код для данной задачи.

Предполагая составной индекс по всем трем полям поиска (g, a, b) Я бы предположил, что это будет лучше для больших групп g, Но это должно быть проверено.

Для небольших групп размеров g Я бы пошел с ответом @xdazz.

РЕДАКТИРОВАТЬ

Существует также подход грубой силы.

  • Создайте идентичную таблицу, но со столбцом AUTO_INCREMENT в качестве идентификатора.
  • Вставьте свой стол в этот клон, упорядоченный по g, a, b.
  • Идентификаторы могут быть найдены с SELECT g, MAX(id),
  • Этот результат затем может быть использован для поиска v ценности, которые вам нужны.

Это вряд ли будет лучшим подходом. Если это так, то это фактически является способностью оптимизатора MySQL справляться с этим типом проблемы.

Тем не менее, у каждого двигателя есть свои слабые места. Так что лично я пробую все, пока не пойму, как себя ведет СУБД, и могу сделать свой выбор:)

РЕДАКТИРОВАТЬ

Пример использования ROW_NUMBER(), (Oracle, SQL Server, PostGreSQL и т. Д.)

SELECT
  *
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY g ORDER BY a DESC, b DESC) AS sequence_id,
    *
  FROM
    yourTable
)
  AS data
WHERE
  sequence_id = 1
Источник

Это можно решить с помощью коррелированного запроса:

SELECT g, v
FROM tab t
WHERE NOT EXISTS (
    SELECT 1
    FROM tab
    WHERE g = t.g
        AND a > t.a
        OR (a = t.a AND b > t.b)
    )
Источник
Другие вопросы по тегам