按日期时间的MYSQL / PHP加权平均值

Question

Hi I have a DB with following data:

Table: "history_data"

VALUE , DATE
100   , 2010-10-01 00:00:00
105   , 2010-10-01 00:00:05
106   , 2010-10-01 00:00:08
103   , 2010-10-01 00:00:10

If I do a simple AVG with MYSQL ( SELECT AVG('VALUE') AS CURRENT_AVG FROM "history_data") it results "103.5" that it's wrong because I need a weighted average based on datetime (from 2010-10-01 00:00:05 to 2010-10-01 00:00:07 the value still equal to 105)

The right operation to do this in math is:

$values = [100,100,100,100,105,105,105,106,106,103];

echo array_sum($values) / count($values);

results: 103 that is right....

I have created two array one with Value and Date index, another only with dateinterval in php. Than I merge it and after process it... but it's to resources expensive.... ( in production I need to make an weighted average of 12 hours. 43 200 seconds...)

Where is the most efficiently method to do this?

Answer 1

At least the most efficient method is do it in PHP

    $current_avg = $current_avg->fetchAll(PDO::FETCH_ASSOC);
    $last_util_value = "";
    $last_util_date = "";
    $final_avg_array = array();
    foreach ($current_avg as $key => $value) {
        if(empty($last_util_value)){
            $last_util_value = $value["val_ask"];
            $last_util_date = strtotime($value["Date"]);
            $second_time = $last_util_date - $past_time_second;
        }else{
            $second_time = strtotime($value["Date"]) - $last_util_date;
        }

        if($second_time > 0){
            for ($i = 1; $i <= $second_time; $i++) {
                array_push($final_avg_array,$value["val_ask"]);
            }
        }
        $last_util_value = $value["val_ask"];
        $last_util_date = strtotime($value["Date"]);
    }
   }
  //6366.26937251
  $avg = array_sum($final_avg_array) / count($final_avg_array);