mysql - 如何获得与可排名数据模型关联的 TOP 10 标签？ -- 高级 MySQL 或 ActiveRecord 查询问题

Question

我遇到了与标签、排名等有关的高级 ActiveRecord 和/或 SQL 查询的绊脚石。希望各位 MySQL 或 Rails 3 高手帮我解决。

我提前为一篇很长的帖子道歉:)

首先是一段描述我的数据模型的片段

数据模型

AggregateData -- 模型包含一组来自不同数据提供者的数据属性(provider_datas)，并使用了计算得出的score属性用于排名

class AggregateData < ActiveRecord::Base
  # has a pre-populated integer attribute 'score'
  has_many :provider_datas

  # find profiles with top 10 score for specified tag      
  # this is mainly used to determine top 10 scores for later comparison
  # Since this is grouped by score, the actual number of profiles 
  # that have these score may be larger
  def self.find_top_10_by_tag(tag)
    joins(:provider_data_tags)                          \
    .where(:provider_data_tags=>{:tag_id => tag.id})    \
    .group('aggregate_data.score')                      \
    .order('aggregate_data.score DESC')                 \
    .limit(10)
  end

  # simple ranking algorithm, 
  # tells you how many AggregateDatas have better score than this one
  def ranking
    self.connection.select_value("SELECT COUNT(*) + 1 AS ranking \
      FROM aggregate_datas              \
      WHERE aggregate_datas.score > \
      (SELECT aggregate_datas.score FROM aggregate_data         \
        WHERE aggregate_datas.id = #{self.id})").to_i
  end
end

ProviderData 包含来自此 ProviderData 实例所代表的特定数据提供者的各种数据属性，最重要的是有许多与之关联的标签通过*provider_data_tags*多对多映射表

class ProviderData < ActiveRecord::Base
  belongs_to :aggregate_data
  has_many :provider_data_tags
  has_many :tags, :through => :provider_data_tags
end

Tag 是一个简单的模型，包含 name 属性和与 ProviderData 的 many_to_many 关联。请注意 finder 函数以获取与提供的 AggregateData

关联的所有标签

class Tag < ActiveRecord::Base  
  has_many :provider_data_tags
  has_many :provider_datas, :through => :provider_data_tags

  def self.find_by_aggregate_data(ag_data)
    joins(:provider_datas).where(:provider_datas =>{:aggregate_data_id => ag_data.id})
  end
end

问题:指定 AggregateData 的前 10 个标签

所以在我的数据模型中，AggregateData 有一个分数，您可以使用范围或< strong>Tag.find_by_aggregate_data 上面的函数

我需要获得每个 AggregateData 的 TOP 10 TAGS。

这意味着我需要获取与 AggregateData 相关联的所有标签的子集，该 AggregateData 的得分在所有 AggregateData 的前 10 名中与该特定标签关联的 strong>AggregateDatas。

所以如果这个 AggregateData 有标签 "java", "ruby", "javascript", < em>“html”、“css” 和这个 AggregateData 在所有带有标签 AggregateData 中得分最高>“ruby” 和所有带有标签 “javascript” 的 AggregateDatas 的最高分，但不是 “java” 或 “html” 或 “css”，那么此函数/作用域/查询将返回标签 “ruby” 和 ” javascript”

该解决方案最好使用 ActiveRecord/AREL 表示法，但我愿意接受 SQL 建议，我可以自己适应 AR。

Answer 1

您需要在子查询中使用 Mysql 中的“排名技巧”，如 How to perform grouped ranking in MySQL 所述.然后，在您的包含查询中，加入 te 子查询的结果并添加排名需要为 #1 并且它必须映射当前 AggregateData 项目。这是假设我的解释正确，您只想返回当前 AggregateData 对象所在的标签所在的标签用于 #1 标签。

这是您可以在 MySQL 中使用的脚本的想法。它可能不完全正确，但我认为它传达了这个想法。您基本上需要使用一些用户变量来对项目进行排名。

SET @last_tag_id = 0;

SELECT tag_id
FROM (
    SELECT tags.id AS tag_id, aggregate_datas.id AS agg_data_id, aggregate_datas.score, 
        (@rank := if(@last_tag_id = tags.id, @rank + 1, 0)) AS rank, 
        @last_tag_id := tags.id
    FROM aggregate_datas
    INNER JOIN provider_data_tags
    ON provider_data_tags.aggregate_data_id = aggregate_datas.id
    INNER JOIN tags
    ON tags.id = provider_data_tags.tag_id
    ORDER BY tags.id, aggregate_datas.score
) tag_ranks
WHERE tag_ranks.rank <= 10
AND agg_data_id = ?;