Ruby loop to create an array of DISTINCT counts

Question

I'm trying to create an array of counts per day. I want the counts to be only of distinct uid's (what uid's are "distinct" shouldn't be reset each day).

Before, I had:

 @unique_count_array_by_day = []
    15.times { |i| 
      bar = Model.select("DISTINCT(uid)").where(:created_at => (Time.now.beginning_of_day - i.days)..(Time.now.beginning_of_day - (i-1).days)).count()
      @unique_count_array_by_day << bar
    }

This wasn't giving me distinct uid's overall, it was giving me the count of unique uid's within a day. So I pulled the code selecting the distinct uid's out of the loop:

 @unique_count_array_by_day = []
  foo = Model.select("DISTINCT(uid)")
    15.times { |i| 
      bar = foo.where(:created_at => (Time.now.beginning_of_day - i.days)..(Time.now.beginning_of_day - (i-1).days)).count()
      @unique_count_array_by_day << bar
    }

However, this still produces a count of distinct uid's per day instead of distinct uid's on their first occurrence in the data table.

Any thoughts on how to finagle this?

What does "distinct uid's overall" mean? You got me confused about what you want and what are you getting. Can you give us example? — Mladen Jablanović
– Mladen Jablanović, Commented Aug 31, 2011 at 16:03
Ok sure. Let's say there are two records, both with the same 'uid'. One record was from yesterday, the the second is from today. With this code, the output for the array would be [1,1] instead of [1,0] (with a 2.times loop). It should be [1,0] because the 'uid' was distinct yesterday, but it isn't today. — Galen
– Galen, Commented Aug 31, 2011 at 16:39
Oh, then the question would be more like "how to get daily counts of UIDs that have never been seen previously"? And it's not enough to look just 15 days back as you don't account for the uids that appeared days before. — Mladen Jablanović
– Mladen Jablanović, Commented Aug 31, 2011 at 16:58
Good point. That's the real question and yes, you're right, it'll need to look back. — Galen
– Galen, Commented Aug 31, 2011 at 18:18
This looks like it's about Rails. Let me know if it was using some other framework. — Andrew Grimm
– Andrew Grimm, Commented Aug 1, 2012 at 6:26

Matthew Rathbone · Accepted Answer · 2011-08-31 21:07:33Z

1

If you just want a list of distinct ID's you should just remove the loop:

@unique_uids = Model.select("DISTINCT(uid)").all

If you want to get the date that a uid first occurs, you could do something like this:

@unique_uids_with_first_dates = Model.find(:select => 'uid, min(created_at)', :group => 'uid')

(untested, so not sure if that works as-is, but that's basically the way to do it)

Not sure if that totally answers your question, I was a little confused by "overall distincts"

edited Aug 31, 2011 at 21:07

answered Aug 31, 2011 at 16:41

Matthew Rathbone

8,2897 gold badges56 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ruby loop to create an array of DISTINCT counts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related