在MySQL或Rails中在特定日期范围内每天获取AVG的最佳方法

Question

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

假设我有一个products_sold模型，该模型具有“ sales_price”浮动属性。但是，如果某天没有销售（例如，model /
db中没有销售），我只想返回0。

MySQL / Rails中完成此任务的最佳方法是什么？我知道我可以做这样的事情：

（ 此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;

并获得如下结果：

| 平均| 日期|
  23 01-03-2009
  50 2009年1月5日 
  34 2009年1月7日
  ……

我想要得到的是：

| 平均| 日期|
  23 01-03-2009
   0 01-04-2009
  50 2009年1月5日
   0 01-06-2009 
  34 2009年1月7日
   0 01-08-2009
  ……

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

感谢大家的帮助。

Answer 1

是否有一个原因（除了已经提到的日期以外）为什么您不使用ActiveRecord中的内置组功能？您似乎担心“后处理”，我认为这并不是真正要担心的事情。

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

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

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

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

（我希望您对模型的描述为“ products_sold”是一种转录错误，顺便说一句-如果不是，则您对类的命名有些不满！）

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

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

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

这样就将日期的完整列表构建为一个数组。我们不需要平均日期：

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

那很多东西对我来说显得有些混乱：我认为它可能更简洁更干净。我会研究构建一个Hash或Struct，而不是停留在数组中。

[1]我并不是说不使用SQL，而是在ActiveRecord无法生成最有效的查询而您又回过头来的情况下发生find_by_sql。很好，应该是这样，但是我认为您应该尝试仅将其用作最后的手段。