How to group data to show average price by weeks from a select query

Question

I show orders for a period 3 months from today. If today is 17.07.2015, I show data from 17.04.2015 to 17.07.2015. My problem is that I have to show this data by weeks. In 3 months there are 12 weeks. I have to show average prices of orders for all 12 weeks.

Now, in my query, I show all orders which is in this period. But I don't have to show average_prices for all orders, have to show 12 average prices for the 12 weeks. How to do that?

<?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();

My table ordersheader has structure:

idOrder(Primary key),idCustomer,createdDate,orderDueDate

My table orderitems has structure:

id(Primary key),idOrder,itemNumber,wrappingType,size,quantity,unitPrice,incomes`

One idOrder can have many orderitems.

Edited: I have to show this data in a chart. In my controller I have: How to show average price by week and interval from query instead of the array $firms to take 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

      ));
      
    }

Answer 1

To do the whole process in PHP change your select and remove the select_avg and do a normal select of unitPrice.

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

foreach($result_array as $row){
     //Initialize the index's 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 a date format will give you the number of the weeek (ie 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 probably others) for more info

PHP get start and end date of a week by weeknumber

Answer 2

Not a portable solution (it uses a MySQL function; other RDBMs have similar features but differently named), but you can group the data by WEEKOFYEAR(). The only caveat is that this way if you're in January, you will get e.g. weeks #48, #49, #50, #51, #52, #1, #2, #3 and you will need to sort them just like that (i.e. 52 comes before 1).

So you can extract

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

and then group by weekNumber but order by firstOrderDate (without displaying it).

Another problem might be if you have missing data in the interval.

Perhaps a more high-level approach would be:

1. Extract day average prices. You then get from 0 to ~91 items worth of data points (0 if you have no orders in the period; you have to consider the possibility).
2. Use some extending method (B-splines [easy on the eyes], polynomial [risky], linear average [defensible]) to get data for the days you do not have; this to be done in PHP
3. Re-group the data in PHP, from 90 days to 12 weeks.
4. Display the 12 data points.

Grouping the data

You can add missing data in the query but it's tricky. A more approachable way from PHP would be this, supposing that you have a result array 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 average the day averages. Suppose we have:

Day      Price
17       1000
17       1000
17       1000
18       2000

The day averages will be: 17 = 1000, 18 = 2000. If both days are in the same week, the average of 1000 and 2000 will give a week average of 1500. Yet in that week we had four orders, and the average of those would actually be 1250.

There is an ambiguity when you say "week average", because it is not clear whether it is the average of the prices of each day, or the average of the prices of all orders in the whole period.

If you want the second type of average, you need to group by week number, which was my initial proposal above.