Best way in MySQL or Rails to get AVG per day within a specific date range

Question

I'm trying to make a graph in Rails, for example the avg sales amount per day for each day in a given date range

Say I have a products_sold model which has a "sales_price" float attribute. But if a specific day has no sales (e.g none in the model/db), I want to return simply 0.

What's the best way in MySQL/Rails to get this done? I know I can do something like this:

(This SQL query might be the completely wrong way to get what I'm wanting too)

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;

And get results like this:

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

What I'd like to get is this:

| 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 do this with SQL or will I have to post-process the results to find what dates in the daterange aren't in the SQL result set? Perhaps I need some sub-selects or IF statements?

Thanks for any help everyone.

Answer 1

Is there a reason (other than the date one already mentioned) why you wouldn't use the built-in group function capabilities in ActiveRecord? You seem to be concerned about "post-processing", which I don't think is really something to worry about.

You're in Rails, so you should probably be looking for a Rails solution first[1]. My first thought would be to do something like

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

which ActiveRecord turned into pretty much the SQL you described. Assuming there's a declared has_many association between Merchant and Product, then you'd probably be better using that, so something like:

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

(I'm hoping that your description of the model as "products_sold" is some kind of transcription error, btw - if not, you're somewhat off-message with your class naming!)

After all that, you're back where you started, but you got there in a more conventional Rails way (and Rails really values conventions!). Now we need to fill in the gaps.

I'll assume you know your date range, let's say it's defined as all dates from from_date to to_date.

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

That builds the complete list of dates as an array. We don't need the dates where we got an average:

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

That lot looks a bit cluttered to me: I think it could be terser and cleaner. I'd investigate building a Hash or Struct rather than staying in arrays.

---

[1] I'm not saying don't use SQL - situations do occur where ActiveRecord can't generate the most efficient query and you fall back on find_by_sql. That's fine, it's supposed to be like that, but I think you should try to use it only as a last resort.