下面是擷取的部分程式碼
優化前程式碼
/**
* 柱狀圖 面試趨勢及通過人數
* @param TrendRequestAlias $request
* @return array
*/
public function trend(TrendRequestAlias $request)
{
$date = $request->input('date');
try {
$data = $this->getTrendData($date[0], $date[1]);
$this->result->succeed($data);
} catch (\Exception $exception) {
$this->result->failed($exception->getMessage());
}
return $this->result->toArray();
}
/**
* 招聘渠道及通過率柱狀圖
* @param ChannelRequest $request
* @return array
*/
public function channel(ChannelRequest $request)
{
$type = $request->input('type');
$date = $request->input('date');
try{
$data = $this->disposeByType($type,$date);
$this->result->succeed($data);
}catch (\Exception $exception){
$this->result->failed($exception->getMessage());
}
return $this->result->toArray();
}
/**
* 查詢表格資料
* @param string $start_time
* @param string $end_time
* @return array
*/
private function getTrendData(string $start_time, string $end_time)
{
$dates = $this->getEveryDayDate($start_time, $end_time); // 日期集合
$intCount = [];
$passCount = [];
foreach ($dates as $date) {
$query = InterviewRecord::query();
$query = $query->whereDate('interview_time', $date)
->whereIn('type', [
InterviewRecord::TYPE_FIRST,
InterviewRecord::TYPE_SECOND,
InterviewRecord::TYPE_THIRD,
InterviewRecord::TYPE_OFFER,
]);
## 面試人數
$intCount[] = $query->count();
## 通過人數
$passCount[] = $query->where('result', InterviewInvitation::ABANDON_Y)->count();
}
return [
'date' => $dates,
'interviewCount' => $intCount,
'passCount' => $passCount,
];
}
/**
* 生成日/周/月 資料
* @param string $type
* @param array $date
* @return array
*/
private function disposeByType(string $type, array $date)
{
$channels = config('business.option.channel.items'); // 渠道集合
$channels_value = array_column($channels,'value');
$data = [];
$result = [];
switch ($type){
case 'week':
$dates = $this->getEveryWeekDate($date[0], $date[1]);
$result['date'] = array_column($dates,'number');
break;
case 'month':
$dates = $this->getEveryMonthDate($date[0], $date[1]);
$result['date'] = array_column($dates,'number');
break;
case 'day':
$dates = $this->getEveryDayDate($date[0], $date[1]);
$result['date'] = $dates;
}
// 迴圈渠道
foreach ($channels_value as $value){
$data[] = $type == 'day' ? $this->getDayDataByDate($value,$dates) :
$this->getWeekOrMonthDataByDate($value,$dates);
}
$result['channel'] = array_column($channels,'label');
$data_tmp = [];
foreach ($channels as $key => $channel){
$data_tmp[] = [
'name' => $channel['label'],
'data' => $data[$key]
];
}
$result['data'] = $data_tmp;
return $result;
}
/**
* 按日/月取資料
* @param $channel
* @param $dates
* @return array
*/
private function getDayOrMonthDataByDate($channel, $dates)
{
$tmp = [];
foreach ($dates as $date) {
$tmp[] = DashboardChannel::where('channel',$channel)->whereDate('date' ,$date)->sum('pass_count');
}
return $tmp;
}
/**
* 按周取資料
* @param \Illuminate\Database\Eloquent\Builder $query
* @param array $weeks
* @return array
*/
private function getWeekDataByDate($channel, array $weeks)
{
$tmp = [];
foreach ($weeks as $week) {
$tmp[] = DashboardChannel::where('channel',$channel)
->whereDate('date','>=' ,$week['start'])
->whereDate('date','<' ,$week['end'])
->sum('pass_count');
}
return $tmp;
}其實寫的時候知道這個問題,想著之後優化
對接完之後,時間引數選2016-12-06 至 2020-01-23, 資料庫基本無資料的情況下,響應時間3.57s
意識到問題嚴重性之後..
/**
* 查詢表格資料
* @param string $start_time
* @param string $end_time
* @return array
*/
private function getTrendData(string $start_time, string $end_time)
{
$dates = $this->getEveryDayDate($start_time, $end_time); // 日期集合
// 邀請人數
$interviews = InterviewRecord::whereIn('type',[
InterviewRecord::TYPE_FIRST,
InterviewRecord::TYPE_SECOND,
InterviewRecord::TYPE_THIRD,
InterviewRecord::TYPE_OFFER,
])->select('type','interview_time')
->whereDate('interview_time','>=',$start_time)
->whereDate('interview_time','<=',$end_time)
->pluck('interview_time')->toArray();
// 通過人數
$pass = InterviewRecord::whereIn('type',[
InterviewRecord::TYPE_FIRST,
InterviewRecord::TYPE_SECOND,
InterviewRecord::TYPE_THIRD,
InterviewRecord::TYPE_OFFER,
])->select('type','interview_time','result')
->whereDate('interview_time','>=',$start_time)
->whereDate('interview_time','<=',$end_time)
->where('result', InterviewInvitation::ABANDON_Y) //結果為通過
->pluck('interview_time')->toArray();
// 面試時間格式化
foreach ($interviews as &$item){
$item = date('Y-m-d',strtotime($item));
}
// 統計每日面試人數
$interviews = array_count_values($interviews);
// 面試時間格式化
foreach ($pass as &$item){
$item = date('Y-m-d',strtotime($item));
}
$pass = array_count_values($pass);
foreach ($dates as $date){
$intCount[] = $interviews[$date] ?? 0;
$passCount[] = $pass[$date] ?? 0;
}
return [
'date' => $dates,
'interviewCount' => $intCount,
'passCount' => $passCount,
];
}
/**
* 生成日/周/月 資料
* @param string $type
* @param array $date
* @return array * @throws \Exception */
private function disposeByType(string $type, array $date)
{
$channels = config('business.option.channel.items'); // 渠道集合
$start_date = $date[0];
$end_date = $date[1];
$result = []; //返回值
$data = DashboardChannel::select('channel','date','pass_count')
->whereDate('date','>=',$start_date)
->whereDate('date','<=',$end_date)
->get()
->toArray();
switch ($type){
case 'day':
$dates = $this->getEveryDayDate($start_date, $end_date);
$result['date'] = $dates;
break;
case 'week':
$dates = $this->getEveryWeekDate($start_date, $end_date);
$result['date'] = array_column($dates,'number');
break;
case 'month':
$dates = $this->getEveryMonthDate($start_date, $end_date);
$result['date'] = array_column($dates,'number');
break;
}
$data = $this->disposeByDate($data,$dates,$channels,$type);
$result['channel'] = array_column($channels,'label');
$result['data'] = $data;
return $result;
}
/**
* 根據日期型別組裝資料(日/月/周/無型別(招聘通過率))
* @param $data
* @param array $dates
* @param array $channels
* @param null $type
* @return array
* @throws \Exception
*/
private function disposeByDate($data,array $dates,array $channels,$type = null)
{
if ($type && $type != 'day'){
$numbers = array_column($dates,'number');
$dates = array_combine($numbers,$dates);
}
// 拼裝初始資料框架
$tmpStruct = [];
foreach ($channels as $channel){
$tmpStruct[$channel['value']] = [
'name' => $channel['label'],
'data' => []
];
foreach ($dates as $date){
$key = $date['number'] ?? $date;
$tmpStruct[$channel['value']]['data'][$key] = 0;
}
}
// 資料組裝
foreach ($data as $item){
if (!$type || $type == 'day'){
// 日
$number = $item['date'];
}elseif ($type == 'week'){
// 周
$number = date('Y',strtotime($item['date'])).'年第'.date('W',strtotime($item['date'])).'周';
}elseif ($type == 'month'){
// 月
$number = date('Y-m',strtotime($item['date']));
}else{
throw new \Exception('type不合法');
}
if ($type)
$tmpStruct[$item['channel']]['data'][$number] += $item['pass_count'];
else
$tmpStruct[$item['channel']]['data'][$number] = $item['pass_rate']; // 通過率(招聘通過率介面)
}
// 去除日期key
foreach ($tmpStruct as &$item){
$item['data'] = array_values($item['data']);
}
return array_values($tmpStruct);
}
修改完之後測了一下效果
寫在最後
昂,菜鳥第一次發部落格,技術很渣渣,程式碼寫得也比較亂,請大家諒解...
雖然是個菜鳥,但是還是希望自己能寫出高質量,高效能,能獲得大家認同的程式碼
本作品採用《CC 協議》，轉載必須註明作者和本文連結