Xpoint
   [напомнить пароль]

Выборка конкретного количества записей, но в выбоке не более 2х записей с 1м user_id

Метки: [без меток]
2010-07-27 09:49:54 [обр] Шмер Михаил aka Topepo[досье]

Привет.
Вот такая задача на мой малоопытный взгляд, нетривиальная встала передо мной.

Есть таблица messages Для наглядности пусть это будет такая структура

iduser_idmessagerating
145привет100
24Как дел34
322Естьчё?748

и еще тысячи таких же записей.
Нужно выбрать 50 записей с наивысшим рейтингом (сортировка по рейтингу по убыванию) , но в выборке должно встречаться не более двух записей с одним user_id. В этом то вся и сложность, незнаю как в SQL ограничить максимальный порог записей с одинаковым значением одно поля.

Буду очень благодарен за помощь.

спустя 8 часов [обр] Дмитрий Кучкин(3/236)[досье]
Если СУБД поддерживает оконные функции стандарта SQL:2003, можно решить задачу используя их.
спустя 38 минут [обр] Thirteensmay(0/157)[досье]

вроде же SQL:1999 (Oracle, PostgreSQL, MSSQL)

select *
from
(select id,
 count(*) over (partition by user_id
                order by rating desc
                range unbounded preceding) as cnt from mytable)
where cnt < 3

(проверено в Oracle)

спустя 3 часа [обр] Дмитрий Кучкин(3/236)[досье]

Thirteensmay[досье]
Про SQL:2003 прочитал в вики http://en.wikipedia.org/wiki/SQL:2003

Не хотел писать решение, прежде чем будет ответ по поводу СУБД, плюс хотел дать возможность автору разобраться самостоятельно, но коли уж...

Насчет Oracle не скажу, но в PostgreSQL при использовании count(*) выдается неверный результат, если есть несколько сообщений от одного пользователя с одинаковым рейтингом.
Вот на таких данных

iduser_idmessagerating
145привет100
24Как дела34
322Естьчё?748
422Чоза грибы100500
522стотыщпицот333
622кагдила748

из выборки исключаются записи с id 3 и 6, но одна из них обязательно должна присутствовать.

В PostgreSQL (начиная с 8.4) запрос, учитывающий всякие нюансы, такой:

select id,user_id,message,rating
 from
  (select id,user_id,message,rating,
          row_number() over (partition by user_id order by rating desc, id desc) as pos
    from messages) as ss
 where pos < 3
 order by rating desc
 limit 50;
спустя 4 часа [обр] Thirteensmay(0/157)[досье]

В 99 появилась основа (http://www.rsdn.ru/article/db/WindowFunctions.xml 2 абзац), в 2003 список функций был существенно расширен, а в 2008 скорректирован ;)

По поводу count(*) в Oracle также, из 2 значений с одинаковым рейтингом выдается лишь одно, но дело не в count, а в том что в моем куске учет идет только по рейтингу (order by rating), и соответственно конструкция "range unbounded preceding" (диапазон всех предыдущих) не может определить какое из двух одинаковых значений является предыдущим, обратите внимание у меня окно не по строкам а по диапазону значений, впрочем у вас окно явно не указано и по умолчанию как я понимаю аналогично, но в вашем случае спасает что сортировка дополнительно ведется и по id (становится возможно определить какое значение предыдущее). Если добавить это к моему запросу поведение становится аналогичным, вместо добавления можно задать окно не по значениям а по строкам - rows unbounded preceding. По идее решение на основе rows и row_number без дополнительной сортировки по id будет производительнее count без той же сортировки.

Далее можно заметить что ограничение limit не поддерживается ни ораклом ни MSSQL, однако обе они и постгрес поддерживают row_number(), в принципе для ограничения этого достаточно, таким образом для "большой тройки" похоже есть кроссплатформенное решение ;) О MySQL в данном случае говорить не приходится ибо не поддерживает в принципе.

Есть предположение что мы не дали готового решения ;)

Толковое и подробное описание аналитических (оконных) функций можно найти в книге Тома Кайта "Oracle для профессионалов" Книга 2, Глава 12. (есть в сети в электронном виде)

спустя 1 час 19 минут [обр] Дмитрий Кучкин(3/236)[досье]

По поводу того, что дело все же не в count мысль пришла уже после отправки сообщения :)

Решение использовать сортировку по id было принято сознательно, чтобы гарантировать повторяемость выборки. rows unbounded preceding такого не гарантирует.
Сортировка id выбрана по убыванию, потому что, IMHO, справедливо отдать предпочтение более новой записи, поскольку у неё было меньше времени для набора такого же рейтинга, как у записи старой.

Далее можно заметить что ограничение limit не поддерживается ни ораклом ни MSSQL, однако обе они и постгрес поддерживают row_number(), в принципе для ограничения этого достаточно, таким образом для "большой тройки" похоже есть кроссплатформенное решение ;)

И там и там есть простые конструкции с той же функциональностью. Не уверен, стоят ли кроссплатформенность и стандартность еще одного уровня подзапроса.

О MySQL в данном случае говорить не приходится ибо не поддерживает в принципе.

Есть предположение что мы не дали готового решения ;)

Боюсь, что дали - тема не в MySQL. Хотя есть и еще СУБД :)

За наводку на книгу спасибо. У меня это первый опыт использования аналитических функций, подробности интересны.
Даже поставил наконец-то постгрес 8.4 :)

спустя 3 часа 57 минут [обр] Шмер Михаил aka Topepo[досье]
Спасибо, нужный ответ дали здесь http://www.sql.ru/forum/actualthread.aspx?tid=777089 , ссылка на ответ(faq по top-N query) http://www.sql.ru/forum/actualthread.aspx?tid=684431#7489069. спасибо всем за участие
Powered by POEM™ Engine Copyright © 2002-2005