Laravel DB :: Raw with Average [英] Laravel DB::Raw with Average

问题描述

我有一个laravel应用程序，其中有一个订单模型。

该模型包含：

  id 
 order_date 
价值
  

我希望返回某个时期的平均每日总销售价值。 / p>

例如，请参见下面的示例数据集

  1 | 2017-11-01 | 10.00 
 2 | 2017-11-01 | 10.00 
 3 | 2017-11-01 | 11.00 
 4 | 2017-11-02 | 14.00 
 5 | 2017-11-03 | 1.00 
 6 | 2017-11-03 | 18.00 
 7 | 2017-11-03 | 10.00 
 8 | 2017-11-03 | 10.00 
 9 | 2017-11-04 | 105.00 
 10 | 2017-11-04 | 10.00 
  

我希望返回每天的总价值，然后返回两天的平均值。 / p>

  1/11/2017 = 31 
 2/11/2017 = 14 
 3/11/2017 = 39 
 2017/4/11 = 115 
  

得出上述答案： 49.75

我尝试了以下雄辩的查询，但未能获得正确的值，这几乎就像忽略了group by并平均了订单值，而不是当天的总订单价值，有人可以指示我正确的方向吗？

  $ avg = Order :: whereBetween（' order_date'，array（Carbon :: now（）-> startOfMonth（），Carbon :: now（）-> endOfMonth（））-> groupBy（'order_date'）-> avg（'value'） ; 
  

解决方案

您已经注意到，当前查询的平均值每个日期的值，而不是天数。您必须调整查询以使用某些除法：

  $ avgValue = Order :: whereBetween（'order_date'，array （碳:: now（）-> startOfMonth（），碳:: now（）-> endOfMonth（）））
-> selectRaw（'SUM（value）/ COUNT（DISTINCT order_date）AS order_average '）
-> first（）; 
  

您应该能够使用 $ avgValue->获取值。 order_average

I have a laravel application which has the a model for orders.

The model contains:

id
order_date
value

I'm looking to return the average total daily sales value across a period.

For example, see below sample dataset

1 | 2017-11-01 | 10.00
2 | 2017-11-01 | 10.00
3 | 2017-11-01 | 11.00
4 | 2017-11-02 | 14.00
5 | 2017-11-03 | 1.00
6 | 2017-11-03 | 18.00
7 | 2017-11-03 | 10.00
8 | 2017-11-03 | 10.00
9 | 2017-11-04 | 105.00
10 | 2017-11-04 | 10.00

I'm looking to return the total value of each day but then average across the days.

1/11/2017 = 31
2/11/2017 = 14
3/11/2017 = 39
4/11/2017 = 115

resulting in the answer for the above: 49.75

I've tried the following eloquent query but failed to get the right value, it's almost like the group by is being ignored and its averaging the order value rather than the total order value for the day, can some one point me in the right direction?

$avg = Order::whereBetween('order_date',array(Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()))->groupBy('order_date')->avg('value');

解决方案

As you've noticed, your current query will average the value per date, instead of by the number of days. You'll have to tweak the query to use some division:

$avgValue = Order::whereBetween('order_date',array(Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()))
->selectRaw('SUM(value) / COUNT(DISTINCT order_date) AS order_average')
->first();

You should the be able to get the value with $avgValue->order_average

这篇关于Laravel DB :: Raw with Average的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！