rails COUNT SELECT DISTINCT [英] rails COUNT SELECT DISTINCT

问题描述

我记录了用户观看一系列视频的次数。

  UserVideoWatching.where（ created_at> =？AND user_id！=？，1.month.ago，User.elephant.id）.group（DATE（created_at））。reorder（'created_at'）。count 
  

产生sql

  SELECT COUNT（*）AS count_all，DATE（created_at）AS date_created_at FROM`user_video_watchings` WHERE（created_at> ='2013-01-27 10:43:24'AND user_id！= 7）GROUP BY DATE（created_at）ORDER BY created_at 
  

可为每天观看的所有影片产生正确的结果，但正如我所说，每个用户一次。

我想要的sql是

  SELECT COUNT （DISTINCT user_id）AS count_all，DATE（created_at）AS date_created FROM`user_video_watchings` WHERE（created_at> ='2013-01-27 10:33:18'AND user_id！= 7）GROUP BY DATE（created_at）ORDER BY created_at 
  

所以我认为

  UserVideoWatching.where（created_at> =？ AND user_id！=？，1.month.ago，User.elephant.id）.group（DATE（created_at））。reorder（'created_at'）。select（'COUNT（DISTINCT user_id）AS count_all，DATE created_at）AS date_created'）
  

会做我想要的，但这会给

  [＃< UserVideoWatching>，＃< UserVideoWatching>] 
  

任何想法？

我使用rails 3.1和mysql

解决方案

可以使用 distinct.count（：attribute_name）。

（在Rails 3中使用： count（：user_id，distinct：true） ）

因此：

  UserVideoWatching.where（created_at> ; =？AND user_id！=？，1.month.ago，User.elephant.id）
 .group（DATE（created_at））。reorder（'created_at'）。distinct.count（：user_id ）
  

无法测试，但我认为会生成您所用的SQL。 p>

I'm recording the number of times users watch a series of videos. Now I'm trying to make a graph of the number of users who watch any video each day.

UserVideoWatching.where("created_at >= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').count

produces the sql

SELECT COUNT(*) AS count_all, DATE(created_at) AS date_created_at FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:43:24' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at

which produces the correct results for all videos watched each day, but as I said I want to only show each user once.

The sql I want is

SELECT COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created FROM `user_video_watchings` WHERE (created_at >= '2013-01-27 10:33:18' AND user_id != 7) GROUP BY DATE(created_at) ORDER BY created_at

so i thought

UserVideoWatching.where("created_at >= ? AND user_id != ?",1.month.ago, User.elephant.id).group("DATE(created_at)").reorder('created_at').select('COUNT(DISTINCT user_id) AS count_all, DATE(created_at) AS date_created')

would do what I wanted. But this gives

[#<UserVideoWatching >, #<UserVideoWatching >]

rather than a hash.

Any ideas?

I'm using rails 3.1 and mysql

解决方案

You can use distinct.count(:attribute_name).

(In Rails 3 use: count(:user_id, distinct: true) instead)

Thus:

UserVideoWatching.where("created_at >= ? AND user_id != ?", 1.month.ago, User.elephant.id)
.group("DATE(created_at)").reorder('created_at').distinct.count(:user_id)

Not able to test but I think that'll produce the SQL you're after.

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