mysql - Rails+ActiveRecords : How to group records by date and show their count based on a condition?

Question

我的数据库表中有这样的数据

ID | numbers | created_at
1  |   10    | 2016-04-01
2  |   20    | 2016-04-01 
3  |   -8    | 2016-04-01
4  |    1    | 2016-04-02
5  |   81    | 2016-04-03
6  |  -12    | 2016-04-03
7  |    0    | 2016-04-03

以及我想要得到的期望输出:

Date       | Greater (or even) than 0 | Smaller than 0
2016-04-01 | 2                        | 1 
2016-04-02 | 1                        | 0
2016-04-03 | 2                        | 1 

有没有一种方法可以从一个查询加载这些数据？我能想到的只是我们的子查询以及在 Rails View 中执行查询，我想在其中呈现获取的数据。

Answer 1

我会尝试这样的事情:

lines = Model.
  select('date, SUM(IF(number > 0, 1, 0)) as greater_count', SUM(IF(number < 0, 1, 0)) as smaller_count').
  group(:date).
  order(:date)

lines.each do |line|
  puts [line.date, line.greater_count, line.smaller_count]
end