Laravel 复杂聚合查询 sql 构建，备忘小例子

在实际开发过程中时不时的会遇到一些统计查询的需求，面对复杂的查询需求可以直接手写原生 sql 之后直接 DB::select,当然也可以使用 Illuminate\Database\Query\Builder 组织查询程序，为了使代码更具可读性还是推荐使用 Query\Builder 较为优雅。
下面是备忘小例子

$query = DB::table('erp_cutover_statistic0')->whereBetween('statistic_at',[$this->start_date,$this->end_date])  
    ->select('qty_hs_new_r', 'qty_hs_new_s', 'qty_cu_new_r', 'qty_cu_new_s', 'qty_hs_follow', 'qty_cu_follow','qty_sh_seen_r','qty_sh_cu_seen_r','qty_sh_seen_s','qty_sh_cu_seen_s','qty_nh_seen','emp_id');  
$queryToday = DB::table('erp_cutover_statistic0_today')->whereBetween('statistic_at',[$this->start_date,$this->end_date])  
    ->select('qty_hs_new_r', 'qty_hs_new_s', 'qty_cu_new_r', 'qty_cu_new_s', 'qty_hs_follow', 'qty_cu_follow', 'qty_sh_seen_r','qty_sh_cu_seen_r','qty_sh_seen_s','qty_sh_cu_seen_s','qty_nh_seen','emp_id');  
$query->unionAll($queryToday);  
$unionAllSql = $query->toSql();  
$start = $this->dateToKey($this->start_date);  
$end = $this->dateToKey($this->end_date);  
$data = DB::table('employee_dim')->join('department_tree',function ($join){  
    $join->on('employee_dim.dept_id','department_tree.dept_id')  
        ->where($this->dept_filed,$this->dept_id);  
})->leftJoin(DB::raw('('.$unionAllSql.') sub'),'sub.emp_id','employee_dim.emp_key')  
    ->addBinding($query->getBindings())  
    ->whereIn('position_id',$this->statPositionIds)  
    ->groupBy('employee_dim.emp_key')  
    ->orderBy('dept_name','ASC')  
    ->orderBy('emp_name','ASC')  
    ->selectRaw('emp_name,dept_name,SUM(qty_hs_new_r) qty_hs_new_r,SUM(qty_hs_new_s) qty_hs_new_s,SUM(qty_cu_new_r) qty_cu_new_r,SUM(qty_cu_new_s) qty_cu_new_s,SUM(qty_hs_follow) qty_hs_follow, SUM(qty_cu_follow) qty_cu_follow,SUM(qty_sh_seen_r) qty_sh_seen_r,SUM(qty_sh_cu_seen_r) qty_sh_cu_seen_r, SUM(qty_nh_seen) qty\_nh_seen,fn_get_stereo_visit_r(emp_key,?,?,\'e\') as stereo_visit_r,  
 fn_get_stereo_visit_s(emp_key,?,?,\'e\') as stereo_visit_s',[$start,$end,$start,$end])  
    ->skip($this->offset)->take($this->rows)->get();
​

实际查询时生成 sql

SELECT
    emp_name,
	dept_name,
	SUM(qty_hs_new_r) qty_hs_new_r,
	SUM(qty_hs_new_s) qty_hs_new_s,
	SUM(qty_cu_new_r) qty_cu_new_r,
	SUM(qty_cu_new_s) qty_cu_new_s,
	SUM(qty_hs_follow) qty_hs_follow,
	SUM(qty_cu_follow) qty_cu_follow,
	SUM(qty_sh_seen_r) qty_sh_seen_r,
	SUM(qty_sh_cu_seen_r) qty_sh_cu_seen_r,
	SUM(qty_nh_seen) qty_nh_seen,
	fn_get_stereo_visit_r (
		emp_key,
		20160101,
		20180909,
		'e'
	) AS stereo_visit_r,
	fn_get_stereo_visit_s (
		emp_key,
		20160101,
		20180909,
		'e'
	) AS stereo_visit_s
FROM
	`employee_dim`
INNER JOIN `department_tree` ON `employee_dim`.`dept_id` = `department_tree`.`dept_id`
AND `dept1_id` = 394
LEFT JOIN (
	(
		SELECT
			`qty_hs_new_r`,
			`qty_hs_new_s`,
			`qty_cu_new_r`,
			`qty_cu_new_s`,
			`qty_hs_follow`,
			`qty_cu_follow`,
			`qty_sh_seen_r`,
			`qty_sh_cu_seen_r`,
			`qty_sh_seen_s`,
			`qty_sh_cu_seen_s`,
			`qty_nh_seen`,
			`emp_id`
		FROM
			`erp_cutover_statistic0`
		WHERE
			`statistic_at` BETWEEN 2016 - 01 - 01
		AND 2018 - 09 - 09
	)
	UNION ALL
		(
			SELECT
				`qty_hs_new_r`,
				`qty_hs_new_s`,
				`qty_cu_new_r`,
				`qty_cu_new_s`,
				`qty_hs_follow`,
				`qty_cu_follow`,
				`qty_sh_seen_r`,
				`qty_sh_cu_seen_r`,
				`qty_sh_seen_s`,
				`qty_sh_cu_seen_s`,
				`qty_nh_seen`,
				`emp_id`
			FROM
				`erp_cutover_statistic0_today`
			WHERE
				`statistic_at` BETWEEN 2016 - 01 - 01
			AND 2018 - 09 - 09
		)
) sub ON `sub`.`emp_id` = `employee_dim`.`emp_key`
WHERE
	`position_id` IN (
		5307,
		1926,
		1678,
		5308,
		5311,
		5312,
		5313,
		5314,
		5315,
		1791,
		1726,
		1728,
		5310,
		5316,
		5317,
		5318,
		5319
	)
GROUP BY
	`employee_dim`.`emp_key`
ORDER BY
	`dept_name` ASC,
	`emp_name` ASC
LIMIT 20 OFFSET 0```
​