Rails3/ActiveRecord：将现有查询更改为按月分组

Question

model Entry.rb

def self.calculate(year, month, id)
     where(':id = entries.user_id', {    
          :id => id
     }).
     where('entries.date <= :last_day', { 
          :last_day => Date.new(year, month, 1).at_end_of_month
     }).
     select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
            'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
            'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation')
  end

该查询为特定用户提供三个总和以及截至给定月份的所有条目。到目前为止工作正常。

接下来我真正需要的是完全相同的，但每个月都有一个值（不同年份的月份必须位于不同的组中）。 该代码需要与 SQLite 和 PostgreSQL 一起使用。

{ 附加问题：是否可以像上面在一个查询中的原始代码一样获得分组和总和？我相信这是不可能的...}

Answer 1

SQLite

def self.calculate(year, month, id)
 where(':id = entries.user_id', {    
      :id => id
 }).
 where('entries.date <= :last_day', { 
      :last_day => Date.new(year, month, 1).at_end_of_month
 }).
 select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
        'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
        'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation').
 group("strftime('%Y-%m', created_at)")
end

Postgre 像这样（我从未使用过这个数据库）

def self.calculate(year, month, id)
 where(':id = entries.user_id', {    
      :id => id
 }).
 where('entries.date <= :last_day', { 
      :last_day => Date.new(year, month, 1).at_end_of_month
 }).
 select('sum(case when joint = "f" then amount_calc else 0 end) as sum_single,' + 
        'sum(case when joint = "t" then amount_calc else 0 end) as sum_joint,' + 
        'sum(case when compensation = "t" then amount_calc else 0 end) as sum_compensation').
 group("EXTRACT(YEAR FROM TIMESTAMP created_at)||EXTRACT(MONTH FROM TIMESTAMP created_at)")
end

所以你应该首先检查使用了什么适配器，然后使用这些查询之一