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

0 votes

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.

Nov 8, 2018 in Others by Bharani
• 4,550 points
87 views

1 answer to this question.

0 votes

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!

answered Nov 8, 2018 by DataKing99
• 8,130 points

Related Questions In Others

0 votes
1 answer

How to get the URL of the current tab in Google Chrome?

There are different ways of doing this:- You ...READ MORE

answered Dec 21, 2018 in Others by Nabarupa
81 views
0 votes
1 answer

How to do Installation of Ruby on Rails on Linux (Ubuntu)?

Hi Pratibha, installing ROR on ubuntu is ...READ MORE

answered Mar 1 in Others by Anvi
• 13,660 points
83 views
0 votes
0 answers

how to list the contents of a asset into an event

May 29 in Others by anonymous
29 views
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24 in Big Data Hadoop by Gitika
• 25,340 points
21 views
0 votes
1 answer

Want a command to be executed in Salt only if a directory is empty

You should consider using this inside your ...READ MORE

answered Jun 12, 2018 in DevOps Tools by Damon Salvatore
• 5,510 points
41 views
0 votes
1 answer

Creating A New MySQL User In Amazon RDS Environment

AWS RDS security groups documentation (a common ...READ MORE

answered Jul 18, 2018 in AWS by Priyaj
• 56,900 points
183 views
0 votes
1 answer

How to check if array is multidimensional or not?

Since the 'second dimension' could be just ...READ MORE

answered Nov 5, 2018 in Others by DataKing99
• 8,130 points
273 views
0 votes
1 answer

Using/Handling colon inside a JSF generated HTML element ID in CSS selector

Yes, you can.  Just Backslash (\) the colon.  Like ...READ MORE

answered Nov 14, 2018 in Others by DataKing99
• 8,130 points
309 views