Best way to get average values within a specific set of time say date or year using MySQL or Rails

Question

I'm plotting a graph in Rails for the avg sales amount per day within a given range of dates with a products_sold model having a "sales_price" float attribute. And, when a specific day makes no sale (say, none in the model/db), a 0 gets returned. Can anybody suggest the best way to do this using MySQL/Rails? Here's what I've done so far but it might seem a little indirect:

SELECT avg(sales_price) AS avg, DATE_FORMAT(created_at, '%m-%d-%Y') AS date
    FROM products_sold WHERE merchant_id = 1 GROUP BY date;

Plus, I get my results like this:

| avg |    date    |
  23    01-03-2009
  50    01-05-2009 
  34    01-07-2009
  ...       ...

But, this is how I actually need it to be:

| avg |    date    |
  23    01-03-2009
   0    01-04-2009
  50    01-05-2009
   0    01-06-2009 
  34    01-07-2009
   0    01-08-2009
  ...       ...

Can I somehow do this with SQL or else I'll have to post-process this result and then find the dates missing in my SQL result set? Like maybe using sub-selects or IF statements? Please advice, TIA.

Answer 1

I don't think post-process is what you should be worrying about. And, its not like I'm asking you to not use SQL as there will be times when ActiveRecord won't be able to generate efficient queries, and you'd have to resort to find_by_sql (but, only as a last resort). So, why aren't you using the built-in group function capabilities in ActiveRecord (other than just the date one already mentioned)? And, you should actually be trying a solution in Rails itself in the first place. Say, something like the following:

Product.average(:sales_price, :group => "DATE(created_at)", :conditions => ["merchant_id=?", 1])

So, ActiveRecord turns into it the same SQL that you described. And, if there's a declared has_many association between Merchant and Product, then you'd be preferably using that, like say:

ave_prices = Merchant.find(1).products.average(:sales_price, :group => "DATE(created_at)")

Also, your description of the model as "products_sold" could either be a transcription error or you're somehow off-message with your class naming. Now, you're back to where you started, all that's required is to fill in all the gaps. And, assuming you're aware of your date range and your limit is defined as all the dates within from_date to to_date:

date_aves = (from_date..to_date).map{|dt| [dt, 0]}

So, the entire list of dates gets built into an array. And, if we don't need the dates with average, this should do:

ave_price_dates = ave_prices.collect{|ave_price| ave_price[0]} # build an array of dates
date_aves.delete_if { |dt| ave_price.dates.index(dt[0]) } # remove zero entries for dates retrieved from DB
date_aves.concat(ave_prices)     # add the query results
date_aves.sort_by{|ave| ave[0] } # sort by date

It might look a little messy and there is scope to make it cleaner. So, I've been trying to build a Hash or Struct instead of the arrays. But, more on that later!