Optimized and efficient SQL query with Rails 4 ActiveRecord on postgreSQL database?

Question

I'm not sure how to do this without hacking something completely ridiculous and experience has thought me that someone can probably suggest 1 or 2 lines of code that will do exactly what I want. I'm not sure where to look, so here's my question:

Say I want to display a chart of month to date daily signups. So I need an efficient sql statement.

I want this to return a hash like this:

{ "2014-08-01"=>83, "2014-08-02"=>67, "2014-08-03"=>109, ... }

Which I can then use to generate a wicked cool chart! =)

So I have no idea how to write this query. Can someone help me please? Thanks...

Here is my "hacked" attempt:

h = User.group("created_at").count
   (0.7ms)  SELECT COUNT(*) AS count_all, created_at AS created_at FROM "users"  GROUP BY created_at
 #=> {nil=>5, 2014-07-27 17:19:41 UTC=>1, 2014-07-19 00:23:54 UTC=>1, 2014-08-21 18:02:58 UTC=>1, 2014-08-21 18:39:31 UTC=>1, 2014-08-21 17:32:48 UTC=>1, 2014-08-21 17:21:46 UTC=>1, 2014-08-21 18:36:21 UTC=>1, 2014-08-21 16:29:37 UTC=>1, 2014-08-21 18:54:34 UTC=>1, 2014-08-21 17:56:24 UTC=>1, 2014-08-21 18:33:20 UTC=>1, 2014-08-21 16:27:17 UTC=>1, 2014-08-21 19:16:11 UTC=>1, 2014-08-21 18:41:14 UTC=>1, 2014-08-21 18:14:12 UTC=>1, 2014-08-21 18:30:46 UTC=>1, 2014-08-21 18:28:04 UTC=>1} 

dates = h.keys.compact.map {|j| j.to_s.match(/\d*-\d*-\d*/)[0] }

 #=> ["2014-07-27", "2014-07-19", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21", "2014-08-21"] 

signUpHash = Hash.new 0
 #=> {} 
dates.map do |date|
signUpHash[date] += 1
end
 #=> [1, 1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15] 
signUpHash
 #=> {"2014-07-27"=>1, "2014-07-19"=>1, "2014-08-21"=>15} 
signUpHash.sort.to_h
 #=> {"2014-07-19"=>1, "2014-07-27"=>1, "2014-08-21"=>15}

So though my way works (somewhat because it doesn't actually take into account month to date which would require more hacking) so... (I know there is a better, more efficient, more legible, more reliable, preferred, recommended, ideal ... (you get the point) ... way to do this. Please advise. Thanks! =)

This is an awesome question because the knowledge learned here can be applied in many useful ways...! — MrPizzaFace
– MrPizzaFace, Commented Aug 22, 2014 at 0:45
You appear to want Rails code, not SQL, though. Making it rather less interesting :p — Craig Ringer
– Craig Ringer, Commented Aug 22, 2014 at 1:21
@Craig no no no... Please spice it up... Make it so interesting that my head spins off my body... Pour that wonderful knowledge on me please -- I am NOT being sarcastic.... Seriously I want to know what you are hinting at... Don't deprive me of ANY fun!!! [Eagerly waiting] =D — MrPizzaFace
– MrPizzaFace, Commented Aug 22, 2014 at 1:23

Craig Ringer · Accepted Answer · 2014-08-22 01:31:22Z

1

If writing SQL directly, you'd use date_trunc.

You haven't shown table definitions or sample data so I can't really test it or write a full query, but the general principle is:

SELECT date_trunc('month', created_at), count(created_at)
FROM mytable
GROUP BY date_trunc('month', created_at)
ORDER BY 1;

to produce a resultset of first-day-of-month to count pairs that're then trivially consumed and aggregated into a hash in the client. You could build the hash in the SQL query, but there's not really any benefit to doing so.

Note that an index on created_at cannot be used for this query. If you want to limit it to certain dates, do so with a WHERE clause on created_at, not using the resulting date_trunc'd values.

If you run this a lot and need it to be fast, and don't mind the additional write overhead of an extra index, you can create a composite index on the month:

create index blahwhateverindexname on mytablename( (date_trunc('month', created_at) );

answered Aug 22, 2014 at 1:31

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

MrPizzaFace Over a year ago

date_trunc Wouldn't have found this on my own. Going to do some reading now... Using the Rails "magic" is always easy and fun but actually knowing the raw sql query, well that just arousing... Thanks dude!

MrPizzaFace Over a year ago

Seriously so much information in such a short chuck of text. Nice answer +1 Leaving it open to see if anyone else is going to top you... =)

Collectives™ on Stack Overflow

Optimized and efficient SQL query with Rails 4 ActiveRecord on postgreSQL database?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related