讨论数量:
快夸我 :smirk:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
class TestController extends Controller
{
public function __invoke(Request $request)
{
$dates = [
['2020-11-09 04:00:00', '2020-11-09 08:00:00'],
['2020-11-09 08:00:00', '2020-11-09 11:00:00'],
['2020-11-09 11:00:00', '2020-11-09 15:00:00'],
['2020-11-09 15:00:00', '2020-11-09 22:00:00'],
['2020-11-09 22:00:00', '2020-11-10 04:00:00'],
['2020-11-10 04:00:00', '2020-11-10 08:00:00'],
['2020-11-10 08:00:00', '2020-11-12 11:00:00'],
['2020-11-10 11:00:00', '2020-11-12 15:00:00'],
];
return Collection::make($dates)->map(function ($val) {
$avg = $this->getAvgItem($val[0], $val[1])->avg();
return [
'start' => $val[0],
'end' => $val[1],
'avg' => $avg
];
});
}
protected function getAvgItem(string $start, string $end)
{
$start_date = Carbon::parse($start);
$end_date = Carbon::parse($end);
$data = $this->getTemperature();
return Collection::make($data)->filter(function ($key, $val) use ($start_date, $end_date) {
$now_date = Carbon::parse($val);
return $now_date->gte($start_date) && $now_date->lte($end_date);
});
}
protected function getTemperature()
{
return [
'2020-11-09 00:00:00' => 0,
'2020-11-09 01:00:00' => 0,
'2020-11-09 02:00:00' => -1,
'2020-11-09 03:00:00' => -1,
'2020-11-09 04:00:00' => -2,
'2020-11-09 05:00:00' => -2,
'2020-11-09 06:00:00' => -2,
'2020-11-09 07:00:00' => -3,
'2020-11-09 08:00:00' => -3,
'2020-11-09 09:00:00' => 0,
'2020-11-09 10:00:00' => 3,
'2020-11-09 11:00:00' => 6,
'2020-11-09 12:00:00' => 8,
'2020-11-09 13:00:00' => 11,
'2020-11-09 14:00:00' => 13,
'2020-11-09 15:00:00' => 13,
'2020-11-09 16:00:00' => 12,
'2020-11-09 17:00:00' => 12,
'2020-11-09 18:00:00' => 9,
'2020-11-09 19:00:00' => 7,
'2020-11-09 20:00:00' => 6,
'2020-11-09 21:00:00' => 6,
'2020-11-09 22:00:00' => 6,
'2020-11-09 23:00:00' => 4,
'2020-11-10 00:00:00' => 2,
'2020-11-10 01:00:00' => 2,
'2020-11-10 02:00:00' => 1,
'2020-11-10 03:00:00' => 1,
'2020-11-10 04:00:00' => 0,
'2020-11-10 05:00:00' => 0,
'2020-11-10 06:00:00' => 0,
'2020-11-10 07:00:00' => 0,
'2020-11-10 08:00:00' => 0,
'2020-11-10 09:00:00' => 8,
'2020-11-10 10:00:00' => 7,
'2020-11-10 11:00:00' => 10,
'2020-11-10 12:00:00' => 11,
'2020-11-10 13:00:00' => 12,
'2020-11-10 14:00:00' => 13,
'2020-11-10 15:00:00' => 13,
];
}
}
select DATE_FORMAT(日期,"%Y-%m-%d") as day, avg(温度) as av from 表 group by DATE_FORMAT(日期,"%Y-%m-%d")
推荐文章: