混合不同类别的结果，按MySQL中的分数排序 [英] Mixing different categories results, ordered by score in MySQL

问题描述

在我的PHP应用程序中，我有一个mysql表，其中包含以下各列:

In my PHP application, I have a mysql table of articles which has the following columns:

article_id    articletext    category_id    score

每篇文章的得分都是根据其受欢迎程度计算得出的，并且属于特定类别(大约有10个类别)

Each article has a score which is calculated based on how popular it is, and belongs to a specific category (there are around 10 categories available)

我的问题是:如何执行查询以返回得分最高的文章，同时按类别交替显示，以便在可能的情况下，不会连续返回相同类别的文章. 例如，如果得分最高的文章得分为:100，则返回的结果将类似于:

My question is: how can I perform a query that returns the highest scored articles while alternating them by categories so that if possible, no same-category articles are returned consecutively. For example if the highest scored article has score: 100 the returning set would be something like:

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级别上，在每个返回的数据集之间交替，一次选择一个结果，然后将它们组合在一起形成一个新数组.

The first (naive) solution that comes in mind is performing 10 selects (1 for each category), ordering them by score descending, and then, in PHP level, alternating between each returned dataset, picking one result at a time and combining them together in a new array.

有没有更有效的方法来实现这一目标?如果可能的话，在MySQL级别

Is there any more efficient way to achieve this? If possible at the MySQL level

推荐答案

进入前20名.如果他们不满足要求，请执行其他查询以获取缺少的部分.您应该能够在查询数量和每次返回的行数之间取得一定的平衡.

Go get the top 20. If they don't satisfy the requirements, do an additional query to get the missing pieces. You should be able to come up with some balance between number of queries and number of rows each returns.

我获得了前100名，它可能在90％的时间内满足要求，并且比10个单独的查询更便宜，更快.

I you got the top 100 it might satisfy the requirements 90% of the time and would be cheaper and faster than 10 separate queries.

如果是SQL Server，我可以提供更多帮助...

If it was SQL Server I could help more...

实际上，我有另一个想法.每5分钟运行一次计算列表并将其缓存在表中的过程.使针对相关表的DML使缓存无效，因此直到重新填充(也许删除了文章)后才使用它.如果缓存无效，则可以立即进行计算...并可以使用它来重新填充缓存.

Actually, I have another idea. Run a process every 5 minutes that calculates the list and caches it in a table. Make DML against related tables invalidate the cache so it is not used until repopulated (perhaps an article was deleted). If the cache is invalid, you would fall back to calculating it on the fly... And could use that to repopulate the cache anyway.

可能有策略地更新缓存列表而不是重新计算它.但这可能是一个真正的挑战.

It might be possible to strategically update the cached list rather than recalculate it. But that could be a real challenge.

这应该有助于提高查询速度并减少数据库负载.如果您的文章列表过时了5分钟，那没关系.哎呀，甚至一分钟也可能有效.

This should help both with query speed and reducing load on your database. It shouldn't matter much if your article list is 5 minutes out of date. Heck, even 1 minute might work.

这篇关于混合不同类别的结果，按MySQL中的分数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！