Rails通过：created_at对表进行分组，返回：status列的计数，然后对：status列进行子分组并包含每个唯一值的计数 [英] Rails group a table by :created_at, returning a count of the :status column, and then sub-group the :status column with count of each unique value

问题描述

我有一个通知模型，我想知道每天创建多少个通知以及每种状态有多少个。 状态是通知模型的字符串键。

I have a model Notification and I would like to know how many notifications are created per days and how many with each status. status is a string key of the model Notification.

Notification.where(user: users).group('DATE(created_at)').count('created_at')

给我：

{Mon, 05 Oct 2015=>2572,
 Tue, 06 Oct 2015=>555,
 Wed, 07 Oct 2015=>2124,
 Thu, 08 Oct 2015=>220,
 Fri, 09 Oct 2015=>36}

但是我想要这样的东西：

But I would like something like :

{Mon, 05 Oct 2015=>{total=>2572, error=>500, pending=>12},
 Tue, 06 Oct 2015=>{total=>555, sent=>50, pending=>12}}

或类似。活动记录是否有可能？也许 group_by 对我来说是一件好事。

Or similar. Is it possible with active record ? Maybe group_by was something good for me. But it's deprecated.

目前我已经尝试过：

Notification.where(user: users).group('DATE(created_at), status').count('created_at') 
# => {"error"=>2, "sent"=>42}

不是结果我想要。

推荐答案

我添加了第二种答案，采用了另一种方法，我认为这样做会更好。

I'm adding a second answer with a different approach that I believe to be much better in that it is efficient and can be translated into a DB view.

每次我最终遇到很多重复的问题时，要么数据库上的重复命中，要么大型，复杂的查询无法很好地翻译，我希望使用纯SQL，因为这样可以用作数据库中的视图。我问了这个问题，因为我SQL很差。我认为这可以适应您的需求，尤其是在状态字段是一组已知的可能值的情况下。这是我最初尝试的方式：

Any time I end up with lots of repeated hits on the DB or large, complex queries that don't translate well, I look to use pure SQL as that can then be used as a view in the DB. I asked this question because my SQL is poor. I think this can be adapted to your needs, especially if the "status" field is a know set of possible values. Here's how I would try it initially:

构造一个有效的SQL查询。您可以在psql中对其进行测试。

Construct a SQL query that works. You can test this in psql.

SELECT created_at, count(status) AS total,
sum(case when status = 'error' then 1 end) AS errors,
sum(case when status = 'pending' then 1 end) AS pending,
sum(case when status = 'sent' then 1 end) AS sent
FROM notifications
GROUP BY created_at;

这应该返回一个数据透视表，例如：

This should return a pivot table like:

| created_at       |total|errors|pending|sent|
----------------------------------------------
| Mon, 05 Oct 2015 |2572 |500   |12     |null|
| Tue, 06 Oct 2015 |555  |null  |12     |50  |

很好，任何单个表在Rails中都是简单的查询，它将以对象数组的形式加载。这些对象中的每一个都有对应于每一列的方法。如果该行的列为空，Rails将通过 nil 作为值。

Great, any single table is an easy query in Rails that will load it up as an array of objects. Each of those objects will have a method that corresponds to each column. If the column is null for that row Rails will pass nil as the value.

在其中进行测试Rails

@stats = Notification.where(user: users).find_by_sql("SELECT created_at, count(status) 
  AS total,
  sum(case when status = 'error' then 1 end) AS errors,
  sum(case when status = 'pending' then 1 end) AS pending,
  sum(case when status = 'sent' then 1 end) AS sent
  FROM notifications
  GROUP BY created_at;")

哪个将返回 Notification 对象的数组...

Which will return an array of Notification objects...

=> [#< Notification id: nil, created_at: "2014-02-07 22:36:30">
#< Notification id: nil, created_at: "2014-06-26 02:07:51">,
#< Notification id: nil, created_at: "2015-04-26 21:37:09">,
#< Notification id: nil, created_at: "2014-02-07 22:48:29">,
#< Notification id: nil, created_at: "2014-11-04 23:39:07">,
#< Notification id: nil, created_at: "2015-01-27 17:46:50">,...]

请注意，通知ID：为nil。这是因为这些对象并不代表数据库中的实际对象，而是查询所产生的表中的一行。但是现在您可以执行以下操作：

Note that the Notification id: is nil. That's because these objects do not represent the actual objects in the DB, but a row in the table produced by your query. But now you can do something like:

@stats.each do |daily_stats|
  puts daily_stats.attributes
end

#{"created_at" => "Mon, 05 Oct 2015", "total" = 2572, "errors" => 500, "pending" => 12, "sent" => nil}
#{"created_at" => "Tue, 06 Oct 2015", "total" = 555, "errors" => nil, "pending" => 12, "sent" => 50}

等。.您的 @stats 变量很容易传递到视图，在该视图中它很容易以表格形式打印在 .html.erb 文件中。您可以访问数组中任何Notification对象的属性，例如：

and so on.. Your @stats variable is easily passed to a view where it is easily printed as a table in an .html.erb file. You can access the attributes of any Notification object in the array like:

@stats[0].created_at
  #=> "Mon, 05 Oct 2015"

@stats[1].pending
  #=> 12

总的来说，您已经使用一个查询来获取整个数据集。

The overall point is you have used one query to get your entire dataset.

将其存储为视图
登录数据库上的SQL控制台并执行

Store it as a view Log into the SQL console on your DB and do

CREATE VIEW daily_stats AS
SELECT user_id, created_at, count(status) AS total,
   sum(case when status = 'error' then 1 end) AS errors,
   sum(case when status = 'pending' then 1 end) AS pending,
   sum(case when status = 'sent' then 1 end) AS sent
FROM notifications
GROUP BY user_id, created_at;

现在，您可以使用

Select * FROM daily_stats;

请注意，由于您在原始问题中并没有将user_id添加到SELECT。我们正在直接在数据库中工作，它应该可以轻松地从该视图生成带有每个日期的所有用户统计信息的表。这是您正在做的非常强大的数据集。现在，您可以在Rails中设置虚拟模型，轻松地获得所有数据，而不会扭曲Rails查询。

Note that I have purposefully not limited this by user as you are in your original question and added user_id to the SELECT. We are working in the DB directly and it should easily handle generating a table from this view with ALL users stats for every date. This is a very powerful dataset for what you are doing. Now you can set up a dummy model in Rails and easily have all of your data available without contorted Rails queries.

添加虚拟模型
app / models / daily_stat.rb：

Add a dummy model app/models/daily_stat.rb:

class DailyStat < ActiveRecord::Base
  belongs_to :user
  #this is a model for a view in the DB called dash_views
  #class name is singular and will automatically look for the table "daily_stats" which his snake_case and plural.
end

将相应的关系添加到您的 User 模型：

add the corresponding relation to your User model:

class User < ActiveRecord::Base
  has_many :daily_stats
end

现在您可以访问您的用户统计信息的使用方式非常混乱。

Now you have access to your stats by user in a very rail-ish way.

users = [2]
DailyStat.where(user: users)
   => AllStat Load (2.8ms)  SELECT "all_stats".* FROM "all_stats" WHERE "all_stats"."category_id" = 2
   => [ #<AllStat user_id: 2, created_at: "2014-02-14 00:30:24", total: 300, errors: 23, pending: nil, sent: 3>,
        #<AllStat user_id: 2, created_at: "2014-11-29 00:18:28", total: 2454, errors: 3, pending: 45, sent: 323>,
        #<AllStat user_id: 2, created_at: "2014-02-07 22:46:59", total: 589, errors: 33, pending: 240, sent: 68>...]

以及另一个方向：

user = User.first
user.daily_stats
 #returns array of that users DailyStat objects.

关键是以最低水平解决问题。解决数据库中的数据查询问题，然后使用Rails进行操作并显示出来。

The key is to "solve things at the lowest level". Solve a data query problem in the database, then use Rails to manipulate and present it.

这篇关于Rails通过：created_at对表进行分组，返回：status列的计数，然后对：status列进行子分组并包含每个唯一值的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！