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

Я столкнулся с очень распространенной проблемой "Выбор верхних N строк для каждой группы в таблице".

Рассмотрим таблицу с столбцами id, name, hair_colour, score.

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

Чтобы решить эту проблему, я получил именно то, что мне нужно на блог-блог Рика Осборна "sql-get-top-n-rows-for-a-grouped-query"

Это решение работает не так, как ожидалось, когда мои баллы равны.

В приведенном выше примере приведен следующий результат.

 id  name  hair  score  ranknum
---------------------------------
 12  Kit    Blonde  10  1
  9  Becca  Blonde  9  2
  8  Katie  Blonde  8  3
  3  Sarah  Brunette 10  1    
  4  Deborah Brunette 9  2 - ------- - - > if
  1  Kim  Brunette 8  3

Рассмотрим строку 4 Deborah Brunette 9 2. Если у этого также есть тот же счет (10), что и у Сары, тогда ranknum будет 2,2,3 для типа волос "Брюнетка".

Какое решение для этого?

Ответ 1

Если вы используете SQL Server 2005 или новее, для достижения этой цели вы можете использовать функции ранжирования и CTE:

;WITH HairColors AS
(SELECT id, name, hair, score, 
        ROW_NUMBER() OVER(PARTITION BY hair ORDER BY score DESC) as 'RowNum'
)
SELECT id, name, hair, score
FROM HairColors
WHERE RowNum <= 3

Этот CTE будет "разбивать" ваши данные на значение столбца hair, и каждый раздел затем упорядочивается по счету (убывает) и получает номер строки; самый высокий балл для каждого раздела - 1, затем 2 и т.д.

Итак, если вы хотите TOP 3 каждой группы, выберите только те строки из CTE, у которых есть RowNum из 3 или менее (1, 2, 3) → там вы идете!

Ответ 2

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

a.name | a.score | b.name  | b.score
-------+---------+---------+--------
Sarah  | 9       | Sarah   | 9
Sarah  | 9       | Deborah | 9

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

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

Вместо сравнения с более чем или равным используйте строгое сравнение, отличное от сравнения, чтобы подсчитать количество девушек, которые строго лучше. Затем добавьте один к этому, и у вас есть свой ранг (который будет иметь дело со связями по мере необходимости). Таким образом, внутренний выбор будет:

SELECT a.id, COUNT(*) + 1 AS ranknum
FROM girl AS a
  INNER JOIN girl AS b ON (a.hair = b.hair) AND (a.score < b.score)
GROUP BY a.id
HAVING COUNT(*) <= 3

Может ли кто-нибудь увидеть какие-либо проблемы с этим подходом, которые ускользнули от моего уведомления?

Ответ 3

Используйте этот составной элемент, который правильно обрабатывает проблему OP

SELECT g.* FROM girls as g
WHERE g.score > IFNULL( (SELECT g2.score FROM girls as g2
                WHERE g.hair=g2.hair ORDER BY g2.score DESC LIMIT 3,1), 0)

Обратите внимание, что здесь нужно использовать IFNULL для обработки случая, когда у девочек таблицы меньше строк для определенного типа волос, а затем мы хотим видеть в sql-ответе (в случае OP это 3 элемента).