选择X组中最大的N条记录 [英] Selecting Greatest N records in X groups

问题描述

因此，我一直在按每人最多"标签进行所有问题的解答，或者我不明白自己正在阅读的内容，或者到目前为止没有任何内容可以满足我的需求. 该链接还提供了许多有用的信息，但仍然没有答案.

So I've been running through all the questions under the greatest-n-per-group tag, and either I don't understand what I'm reading, or nothing has fit my needs so far. This link has also provided a lot of useful information, but still no answer.

所以我有一个包含以下字段的表:

So I've got a table with the following fields:

• id(唯一整数)
• user_id(int)
• 类别(varchar)
• 得分(int)
• 兴趣(int)

我认为我的问题与常见的每组最多n个问题相去甚远，因为我不需要每个组中最大的n.我需要X组最多的N条记录.

I believe my problem strays from the common greatest-n-per-group question, in that I don't need the greatest N for every group. I need the greatest N records for X groups.

因此，我需要获得兴趣最高的X个类别(简单，GROUP BY类别ORDER BY兴趣，LIMIT X).然后我需要获得N个记录，其中每个类别的得分最高.

So, I need to get X categories with the highest interest (simple, GROUP BY category ORDER BY interest, LIMIT X). And then I need to get N records with the highest score for each of those categories.

我的查询将如何查找这样的内容?

How would my query look for something like this?

很抱歉，如果这确实是其他每组每人最多一次的问题的重复，我只是不了解它们的工作原理.可以的话，请随时关闭它.

根据@tehshrike的建议，这里有更多信息.

Per @tehshrike's prodding, here's a bit more information.

真正发生的是我的用户在许多不同的categories中都有interest.我想生成原始用户所在的categories中具有最高categories的用户列表.

Really what's happening is that my user's have interest in lots of different categories. I would like to generate a list of users with the highest score in the categories that the original user is interested in.

所以我要寻找的一件事可能是:

So one possible thing that I'm looing for is:

在4个categories中，score最高的15个用户是用户1在interested中的最高位置.

The 15 users with the highest score in the 4 categories that user 1 is most interested in.

对于特定用户ID ，请查找排名前X的类别(对该特定用户最感兴趣)，对于那些(X类别)，请查找排名前N的用户(这些类别得分最高).

For a specific user_id find Top X categories (with highest interest for that specific user) and for those (X categories) find Top N users (with highest score for those categories).

推荐答案

SELECT catX.category
       catX.interest
       t1.user_id
       t1.score
FROM 
    ( SELECT category 
           , interest 
      FROM tableX 
      WHERE user_id = @user_id_we_are_interested_in     --- specific user 
      ORDER BY interest DESC
      LIMIT @X                         --- top @X categories per specific user 
    ) AS catX 
  JOIN 
    tableX AS t1 
      ON t1.category = catX.category 
  LEFT JOIN 
    tableX AS t2 
      ON  t2.category = t1.category 
      AND t2.score > t1.score 
  GROUP BY t1.category
         , t1.user_id
  HAVING COUNT(t2.score) < @N                      --- top @N users per category 
  ORDER BY catX.interest DESC 
         , t1.score DESC 

这篇关于选择X组中最大的N条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！