计算月统计 [英] Calculate Month Statistics

问题描述

我有一个捐款表，其中我试图计算出总金额为每月。几个月来没有没有任何捐款，我想结果返回0。

I have a donations table where I'm trying to calculate the total amount for each month. For months without without any donations, I'd like the result to return 0.

下面是我当前的查询：

Donation.calculate(:sum, :amount, :conditions => { 
  :created_at => (Time.now.prev_year.all_year) }, 
  :order => "EXTRACT(month FROM created_at)", 
  :group => ["EXTRACT(month FROM created_at)"])

返回：

{7=>220392, 8=>334210, 9=>475188, 10=>323661, 11=>307689, 12=>439889}

任何想法如何抢空个月？

Any ideas how to grab the empty months?

推荐答案

通常你会离开加入到日历表（或的 generate_series PostgreSQL中）来获取丢失的个月，但最简单的事情在Rails将你的结果合并到零的哈希值;是这样的：

Normally you'd left join to a calendar table (or generate_series in PostgreSQL) to get the missing months but the easiest thing with Rails would be to merge your results into a Hash of zeroes; something like this:

class Donation
  def self.by_month
    h = Donation.calculate(:sum, :amount, :conditions => { 
      :created_at => (Time.now.prev_year.all_year) }, 
      :order => "EXTRACT(month FROM created_at)", 
      :group => ["EXTRACT(month FROM created_at)"])
    Hash[(1..12).map { |month| [ month, 0 ] }].merge(h)
  end
end

然后就调用类的方法， H = Donation.by_month ，才能给你结果。

这篇关于计算月统计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！