将聚合函数 SUM 与 GROUP BY 一起应用 [英] Applying aggregate function SUM together with GROUP BY

问题描述

在 Active Record/Ruby on Rails 中执行此查询的最佳方法是什么

What is the best way to execute this query in Active Record / Ruby on Rails

SELECT sum(amount) as total_amount FROM payment GROUP BY person_id, product_id

我似乎无法将 sum 与 group 方法链接起来.我必须在这里使用 find_by_sql 吗?

I can't seem to chain sum with group method. Do I have to resort to find_by_sql here?

推荐答案

类似:

Payment.group(:person_id).group(:product_id).sum(:amount)

应该给你一个散列，其键是 [person_id, product_id] 数组，其值是总和.你可能不想:

should give you a hash whose keys are [person_id, product_id] arrays and whose values are the sums. You probably don't want to:

Payment.group('person_id, product_id').sum(:amount)

虽然，这可能会给你正确的总和，但哈希的键没有多大意义.

though, that might give you the right sums but the Hash's keys won't make much sense.

你也可以说:

Payment.sum(:amount, :group => 'person_id, product_id')

但这将在密钥中遭受与 Payment.group('person_id, product_id').sum(:amount) 相同的脑损伤；但是，:group:

but that will suffer the same brain damage in the keys as Payment.group('person_id, product_id').sum(:amount) does; however, an array for :group:

Payment.sum(:amount, :group => [ :person_id, :product_id ])

应该产生与双 .group 版本相同的带有 Array 键的哈希.但是，:group 形式的 sum 在 Rails4 中已被弃用(感谢 Ganeshwara Herawan Hananda Put 注意到这一点.

should produce the same Hash with Array keys as the double .group version. However, the :group form of sum is deprecated in Rails4 (thanks to Ganeshwara Herawan Hananda Put for noting this).

以上在 Rails3 中运行良好，我认为没有理由在 Rails4 中不起作用.我手边没有 Rails4 设置，所以无法验证.

The above works fine in Rails3 and I see no reason that it wouldn't work in Rails4. I don't have a Rails4 setup handy so I can't verify that.

这篇关于将聚合函数 SUM 与 GROUP BY 一起应用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！