按列分组、结果限制和按其他列轨道排序

Question

假设我们有模型ItemStatistic和Book。ItemStatistic示例记录：

item_id: 15,
book_id: 3,
score: 0.25192368e4,

书籍示例：

id: 3,
title: 'Harry Potter',

我们需要按item_id对ItemStatistic记录进行分组，然后按score对每个item_id密钥的结果进行排序，并按3限制每个item_id密钥的值的数量，并且返回的不是ItemStatistic记录而是Book。示例：

{15: [{id: 3, title: 'Harry Potter'}, {id: 4, title: 'The Chronicles of Narnia'},...], 2: [...]}

或

{1: [{},{},{}], 2: [{},{},{}], 3:[{},{},{}]}

Answer 1

如果我正确理解了这个愿望，下面的内容应该可以

item_statistics_table = ItemStatistic.arel_table 
filter = Arel::Table.new('filtered_results')
sub_query = item_statistics_table.project(
item_statistics[Arel.star],
Arel.sql('ROW_NUMBER() OVER(
PARTITION BY item_statistics.item_id 
ORDER BY item_statistics.score DESC
)').as('row_num')
)
query = Arel::Nodes::As.new(sub_query, Arel.sql(filter.name))
join_clause = Arel::Nodes::InnerJoin.new(
query,
Arel::Nodes::On.new(
Book.arel_table[:id].eq(filter[:book_id])
.and(filter[:row_num].lteq(3))
)
)
Book
.select(Book.arel_table[Arel.star],filter[:item_id])
.joins(join_clause)
.group_by(&:item_id)

这将导致以下SQL:

SELECT 
books.*,
filtered_results.item_id
FROM 
books 
INNER JOIN (
SELECT 
item_statistics.*,
ROW_NUMBER() OVER(
PARTITION BY item_statistics.item_id 
ORDER BY item_statistics.score DESC
) as row_number
FROM 
item_statistics
) AS filtered_results ON books.id = filtered_results.book_id 
AND filtered_results.row_number <= 3

然后我们按照item_id(虚拟属性(对所有书籍进行分组，结果是

{1 => [Book,Book,Book], 2 => [Book,Book,Book]}

数组的结果大小应为<=3(在item_id具有少于3个记录的情况下(不考虑相同的分数。