在MySQL或Rails中在特定日期范围内每天获取AVG的最佳方法 [英] Best way in MySQL or Rails to get AVG per day within a specific date range

问题描述

我正在尝试在Rails中绘制图形，例如在给定日期范围内每天的平均销售量

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

说，我有一个products_sold模型，该模型具有"sales_price"浮动属性.但是，如果特定日期没有销售量(例如，model/db中没有销售量)，我只想返回0.

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.

在MySQL/Rails中完成此操作的最佳方法是什么?我知道我可以做这样的事情:

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

(此SQL查询可能是完全错误的方式来获取我想要的东西)

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;

并获得如下结果:

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

我想得到的是这样:

| 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
  ...       ...

我可以使用SQL进行此操作，还是必须对结果进行后处理以查找日期范围中的哪些日期不在SQL结果集中?也许我需要一些子选择或IF语句?

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?

感谢大家的帮助.

推荐答案

是否有一个原因(除了已经提到的日期)，为什么您不使用ActiveRecord中的内置组函数功能?您似乎担心后处理"，我认为这并不是真正要担心的事情.

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.

您正在使用Rails，因此您可能应该首先寻找Rails解决方案[1].我首先想到的是做类似的事情

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])

哪个ActiveRecord变成了您描述的SQL.假设商家和产品之间存在已声明的has_many关联，那么使用它可能会更好，所以类似:

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)")

(我希望您将模型描述为"products_sold"是一种转录错误，顺便说一句-否则，您的类命名有些不正确了！)

毕竟，您回到了起点，但是您以更传统的Rails方式到达了那里(Rails确实重视约定！).现在我们需要填补空白.

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.

我假设您知道您的日期范围，假设它定义为从from_date到to_date的所有日期.

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]我并不是说不使用SQL-确实发生了ActiveRecord无法生成最有效查询并且您退回到find_by_sql的情况.很好，应该是这样，但是我认为您应该尝试仅将其用作最后的选择.

[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.

这篇关于在MySQL或Rails中在特定日期范围内每天获取AVG的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！