Clever Geek Handbook

How to group data to show the average price by week from a selected query

I am showing orders for 3 months from today. If today is 07/17/2015, I show the data from 04/17/2015 to 07/17/2015. My problem is that I have to show this data by week. After 3 months 12 weeks. I have to show the average order prices for all 12 weeks.

Now, in my request, I am showing all the orders that are in this period. But I don't need to show average_prices for all orders, I should show 12 average prices over 12 weeks. How to do it?

<?php
public function average_price_by_week() {
$date = new DateTime("now");
$date->modify('-3 month');
$current =$date->format('Y-m-d');

$this->db->select('DATE_FORMAT(DATE_SUB(ordersheader.createdDate, INTERVAL DAYOFWEEK(ordersheader.createdDate)-2 DAY), "%Y-%m-%d") AS "interval"',FALSE);
	    	
$this->db->select_avg('unitPrice');
$this->db->from('orderitems');
$this->db->join('ordersheader', 'ordersheader.idOrder=
orderitems.idOrder');
			
$this->db->where('ordersheader.createdDate > ',$current);
			
			
$this->db->order_by('interval');
$this->db->group_by('interval');
$query=$this->db->get();
return $query->result_array();
Run codeHide result


My table ordersheader

has a structure:

idOrder

(primary key) idCustomer

, createdDate

,orderDueDate

My table orderitems

has a structure:

id

(primary key), idOrder

, itemNumber,

wrappingType ,

size ,

number ,

unitPrice ,

income

One idOrder can have many sequence numbers.

Edited: I have to show this data in a chart. In my controller, I have: How to show the average price by weeks and intervals from a query instead of the $ firm array to get the values ​​from the above arrays?

<?php
 public function column_chart() {   
        
      $size = $this->uri->segment(3);
      $interval = $this->uri->segment(4);
      $firms = $this->Receivedorders_model->average_price_by_week($size,$interval);

      $new_result_array=[];
      $average_prices=[];

      foreach($firms as $row){
          
         if(!isset($new_result_array[date('W',strtotime($row['interval']))])){
           $new_result_array[date('W',strtotime($row['interval']))]=[];
           $new_result_array[date('W',strtotime($row['interval']))]['weekly_order_total']=0;
           $new_result_array[date('W',strtotime($row['interval']))]['orders']=[];
         }
        
         $new_result_array[date('W',strtotime($row['interval']))]['weekly_order_total']+=$row['unitPrice'];
         $new_result_array[date('W',strtotime($row['interval']))]['orders'][]=$row;
      }

      print_r($new_result_array);

      foreach($new_result_array as $week=>$orders){
         $average_prices[$week]=$orders['weekly_order_total']/count($orders['orders']);
      }

      print_r($average_prices);



/* How to show average price by week and interval from query in the following array -instead of $firms to take values from the above array  */
     
      $p = array();
      foreach ($firms as $key => $firm) {
        $p[$key] = array('c' => array(array(
          'v' =>  $firm['interval'],  
        ),
        
     
        array(
          'v' =>  round($firm['unitPrice'],2), 
        )));
      }
      echo json_encode(array(
      'cols' => array(
          array('id' => 'name',  'label' => lang("customer"),  'type' => 'string'),
          array('id' => 'incomes', 'label' => lang("chart_average_price"),  'type' => 'number'),

        ),
        'rows' => $p

      ));
      
    }
Run codeHide result


+3


source to share


2 answers


To do the whole process in PHP, change your selection and delete select_avg

and make your normal selection unitPrice

.

$new_result_array=[];
$average_prices=[];

foreach($result_array as $row){
     //Initialize the index if they dont exist
   if(!isset($new_result_array[date('W',strtotime($row['createdDate']))]){
     $new_result_array[date('W',strtotime($row['createdDate'])]=[];
     $new_result_array[date('W',strtotime($row['createdDate'])]['weekly_order_total']=0;
     $new_result_array[date('W',strtotime($row['createdDate'])]['orders']=[];
   }
   //Edited to wrap $row['createdDate'] in strtotime() as all dates are returning week 1 
   $new_result_array[date('W',strtotime($row['createdDate']))]['weekly_order_total']+=$row['unitPrice'];
   $new_result_array[date('W',strtotime($row['createdDate']))]['orders'][]=$row;
}

print_r($new_result_array);

foreach($new_result_array as $week=>$orders){
   $average_prices[$week]=$orders['weekly_order_total']/count($orders['orders']);
}

print_r($average_prices);


W

as the date format will give you the character number (i.e. 1-52). If you need to show the start and end date of each weekly period, you will need to parse that number. See this question (and possibly others) for more information

PHP get start and end date of week by week number

+3


source


Not a portable solution (it uses a MySQL function, other RDBMs have similar functionality but different names), but you can group the data with WEEKOFYEAR()

. The only caveat is that this way, if you are in January, you will receive, for example. weeks # 48, # 49, # 50, # 51, # 52, # 1, # 2, # 3 and you will need to sort them exactly the same (e.g. 52 to 1).

So you can extract

MIN(ordersheader.createdDate) AS firstOrderDate,
WEEKOFYEAR(ordersheader.createdDate) AS weekNumber,

and then the group weekNumber

and the order firstOrderDate

(no display).

Another problem may be lack of data in the interval.

Perhaps a higher level approach:

  • Average prices per day. You then get between 0 and ~ 91 data point value points (0, if you have no orders for this period, you should consider).
  • Use some extension method (B-splines [easy on the eyes], polynomial [risky], linear means [defensive]) to get data for days you don't have; it needs to be done in PHP
  • Rearrange data in PHP, 90 days to 12 weeks.
  • Display 12 data points.

Data grouping

You can add missing data to the query, but this is difficult. A more accessible way for PHP would be this, assuming you have an array of results like



$results = array(
    0 => array(
        'createdDate'   => '2015-07-17',
        'value'         => 2500, // This is the DAY AVERAGE for that day
        ...
    ),

Then you can do something like

$weeks = array();
foreach ($results as $row) {
    $weekNumber = date('W', $row['createdDate']);
    if (array_key_exists($weekNumber, $weeks)) {
        $week = $weeks[$weekNumber];
    } else {
        $week = array( 'value' => 0, 'count' => 0 );
    }
    $week['count'] ++;
    $week['value'] += $row['value'];
}
// Now get the average
foreach ($weeks as $n => $week) {
   $weeks[$n]['value'] /= $weeks[$n]['count'];
}
// Now we have $weeks, with "holes" for periods with no data:
/*
    $weeks = array(
        51 => [ 'value' => 172.371, 'count' => 3 ],
         2 => [ 'value' => 211.952, 'count' => 7 ],
         ...
    );
*/
// To fill holes, we first need to choose how to do that (see above).

Note : There is a (possibly acceptable) conceptual error in the above approach where we are averaging daily averages. Let's pretend that:

Day      Price
17       1000
17       1000
17       1000
18       2000

Average daily averages: 17 = 1000, 18 = 2000. If both days are in the same week, the average 1000 and 2000 will give a weekly average of 1500. However, this week we had four orders and the average of them will actually be 1250.

There is ambiguity when you say "weekly average" because it is not clear if this is the average price of each day or the average price of all orders over the entire period.

If you want the second type of mean, you need to provide the group number, which was my initial suggestion above.

0


source






More articles:


All Articles