Выбор верхних 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 элемента).
