コード例 #1
0
 /**
  * Core data aggregation
  *
  * @param  int 	  $channel_id  id of channel to perform aggregation on
  * @param  string $interpreter interpreter class name
  * @param  string $mode        aggregation mode (full, delta)
  * @param  string $level       aggregation level (day...)
  * @param  int 	  $period      delta days to aggregate
  * @return int    number of rows
  */
 protected function aggregateChannel($channel_id, $interpreter, $mode, $level, $period)
 {
     $format = self::getAggregationDateFormat($level);
     $type = self::getAggregationLevelTypeValue($level);
     $weighed_avg = $interpreter == 'Volkszaehler\\Interpreter\\SensorInterpreter';
     $sqlParameters = array($type);
     $sql = 'REPLACE INTO aggregate (channel_id, type, timestamp, value, count) ';
     if ($weighed_avg) {
         // get interpreter's aggregation function
         $aggregationFunction = $interpreter::groupExprSQL('agg.value');
         // SQL query similar to MySQLOptimizer group mode
         $sql .= 'SELECT channel_id, ? AS type, ' . 'MAX(agg.timestamp) AS timestamp, ' . 'COALESCE( ' . 'SUM(agg.val_by_time) / (MAX(agg.timestamp) - MIN(agg.prev_timestamp)), ' . $aggregationFunction . ') AS value, ' . 'COUNT(agg.value) AS count ' . 'FROM ( ' . 'SELECT channel_id, timestamp, value, ' . 'value * (timestamp - @prev_timestamp) AS val_by_time, ' . 'GREATEST(0, IF(@prev_timestamp = NULL, NULL, @prev_timestamp)) AS prev_timestamp, ' . '@prev_timestamp := timestamp ' . 'FROM data ' . 'CROSS JOIN (SELECT @prev_timestamp := NULL) AS vars ' . 'WHERE ';
     } else {
         // get interpreter's aggregation function
         $aggregationFunction = $interpreter::groupExprSQL('value');
         $sql .= 'SELECT channel_id, ? AS type, MAX(timestamp) AS timestamp, ' . $aggregationFunction . ' AS value, COUNT(timestamp) AS count ' . 'FROM data WHERE ';
     }
     // selected channel only
     if ($channel_id) {
         $sqlParameters[] = $channel_id;
         $sql .= 'channel_id = ? ';
     }
     // since last aggregation only
     if ($mode == 'delta') {
         if ($channel_id) {
             // selected channel
             $sqlTimestamp = 'SELECT UNIX_TIMESTAMP(DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $format . '), ' . 'INTERVAL 1 ' . $level . ')) * 1000 ' . 'FROM aggregate ' . 'WHERE type = ? AND channel_id = ?';
             if ($ts = $this->conn->fetchColumn($sqlTimestamp, array($type, $channel_id), 0)) {
                 $sqlParameters[] = $ts;
                 $sql .= 'AND timestamp >= ? ';
             }
         } else {
             // all channels
             $sqlParameters[] = $type;
             $sql .= 'AND timestamp >= IFNULL((' . 'SELECT UNIX_TIMESTAMP(DATE_ADD(' . 'FROM_UNIXTIME(MAX(timestamp) / 1000, ' . $format . '), ' . 'INTERVAL 1 ' . $level . ')) * 1000 ' . 'FROM aggregate ' . 'WHERE type = ? AND aggregate.channel_id = data.channel_id ' . '), 0) ';
         }
     }
     // selected number of periods only
     if ($period) {
         $sql .= 'AND timestamp >= (SELECT UNIX_TIMESTAMP(DATE_SUB(DATE_FORMAT(NOW(), ' . $format . '), INTERVAL ? ' . $level . ')) * 1000) ';
         $sqlParameters[] = $period;
     }
     // up to before current period
     $sql .= 'AND timestamp < UNIX_TIMESTAMP(DATE_FORMAT(NOW(), ' . $format . ')) * 1000 ';
     if ($weighed_avg) {
         // close inner table
         $sql .= 'ORDER BY timestamp ' . ') AS agg ';
     }
     $sql .= 'GROUP BY channel_id, ' . Interpreter\Interpreter::buildGroupBySQL($level);
     if (Util\Debug::isActivated()) {
         echo Util\Debug::getParametrizedQuery($sql, $sqlParameters) . "\n";
     }
     $rows = $this->conn->executeUpdate($sql, $sqlParameters);
     return $rows;
 }