Смешивание результатов разных категорий, упорядоченных по баллам в MySQL
В моем PHP-приложении у меня есть таблица статей mysql, в которой есть следующие столбцы:
article_id articletext category_id score
У каждой статьи есть оценка, которая рассчитывается на основе того, насколько она популярна, и относится к определенной категории (доступно около 10 категорий)
Мой вопрос: как я могу выполнить запрос, который возвращает статьи с наибольшим количеством баллов, чередуя их по категориям, чтобы, если возможно, статьи одной категории не возвращались последовательно. Например, если статья с наивысшим баллом имеет оценку: 100 возвращаемый набор будет примерно таким:
article_id articletext category_id score
-----------------------------------------------------
142 <.....> 5 100
153 <.....> 3 97
119 <.....> 5 99
169 <.....> 2 93
121 <.....> 7 89
197 <.....> 2 92
.
.
.
Первое (наивное) решение, которое приходит на ум, - это выполнить 10 выборок (по 1 для каждой категории), упорядочить их по убыванию баллов, а затем на уровне PHP чередовать каждый возвращаемый набор данных, выбирая по одному результату за раз и объединяя их вместе в новый массив.
Есть ли более эффективный способ добиться этого? Если возможно на уровне MySQL
3 answers
Иди и выбери 20 лучших. Если они не удовлетворяют требованиям, выполните дополнительный запрос, чтобы получить недостающие части. Вы должны быть в состоянии найти некоторый баланс между количеством запросов и количеством строк, возвращаемых каждым из них.
Если вы попали в топ-100, это может удовлетворить требованиям в 90 % случаев и будет дешевле и быстрее, чем 10 отдельных запросов.
Если бы это был SQL Server, я мог бы помочь больше...
На самом деле, у меня есть другая идея. Запускайте процесс каждые 5 минут, чтобы вычисляет список и кэширует его в таблице. Сделайте DML по отношению к связанным таблицам недействительным, чтобы кэш не использовался до повторного заполнения (возможно, статья была удалена). Если кэш недействителен, вы вернетесь к его вычислению на лету... И в любом случае мог бы использовать это для повторного заполнения кэша.
Возможно, можно стратегически обновить кэшированный список, а не пересчитывать его. Но это может стать настоящим испытанием.
Это должно помочь как с ускорением запросов, так и с уменьшением загрузите в свою базу данных. Это не должно иметь большого значения, если ваш список статей устарел на 5 минут. Черт возьми, даже 1 минута может сработать.
Только для целей обучения. Я сделал тест с 3 категориями. Я понятия не имею, как этот запрос может выполняться на большом наборе записей.
select * from (
(select @r:=@r+1 as rownum,article_id,articletext,category_id,score
from articles,(select @r:=0) as r
where category_id = 1
order by score desc limit 100000000)
union all
(select @r1:=@r1+1,article_id,articletext,category_id,score
from articles,(select @r1:=0) as r
where category_id = 2
order by score desc limit 100000000)
union all
(select @r2:=@r2+1,article_id,articletext,category_id,score
from articles,(select @r2:=0) as r
where category_id = 3
order by score desc limit 100000000)
) as t
order by rownum,score desc
Ваше наивное решение - это именно то, что я бы сделал.