对子集或记录进行ActiveRecord计算 [英] ActiveRecord calculations on subsets or records

问题描述

我有一个 Game 模型，该模型具有点属性，我想计算前20名的总和得分。

I have a Game model with a points attribute, and I want to compute the sum of the top 20 scores.

我可以摘取积分并让ruby计算总和，例如：

I can pluck the points and have ruby compute the sum, as in:

Game.order('points desc').limit(20).pluck(:points).sum

但是我对是否有一种直接的方法让AR生成SQL聚合计算来完成同样的工作感到好奇。以下天真尝试不起作用：

But I am curious as to whether there is a straightforward way to have AR produce an SQL aggregate calculation to accomplish the same. The following naive attempt does not work:

Game.sum(:points, order: 'points desc', limit: 20)
SELECT SUM(`games`.`points`) FROM `games`

谢谢

推荐答案

我将回答自己的问题，但很乐意为您提供更好的解决方案。

I am going to answer my own question, but will gladly award a better solution.

sum 函数实际上只接受一个参数，而AR 4.x过去只是简单地忽略任何额外的参数，而新的AR 5.0.0则引发了错误。

The sum function really only takes one argument, and while AR 4.x used to simply ignore any extra args, the new AR 5.0.0 raises an error. The original attempt was indeed naive.

根据@pitabas的建议，然后我尝试：

Following @pitabas' suggestion, I then tried:

Game.order('points desc').limit(20).sum(:points)
SELECT SUM("games"."points") FROM "games" LIMIT ?  [["LIMIT", 20]]

从AR构造的SQL可以看出，也不起作用，因为 LIMIT 子句作用于查询结果，该结果是单个值。此外，完全忽略了 ORDER 子句。上面的表达式的结果是所有记录的points列的总和。

As you can tell from the SQL constructed by AR, this does not work either, because the LIMIT clause acts on the result of the query, which is a single value. Furthermore, the ORDER clause is ignored altogether. The result of the above expression is the total sum of the points column, for all records.

实际有效的SQL查询类似于：

An SQL query that actually works is something like:

SELECT SUM(points) FROM (SELECT points FROM games ORDER BY points DESC LIMIT 20)

我最接近的是：

sq = Game.order('points desc').limit(20)
Game.from(sq).sum('"points"')

其结果是：

SELECT SUM("points") FROM (SELECT  "games".* FROM "games" ORDER BY points desc LIMIT ?) subquery  [["LIMIT", 20]]

Hack：注意点周围的双引号。这就是我设法对 sum 参数执行字面解释的方式。没有它们，查询将引发无此列错误。

Hack: note the double set of quotes around the points. That's how I managed to enforce a literal interpretation of the sum argument. Without them, the query raises a "no such column" error.

这篇关于对子集或记录进行ActiveRecord计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！