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

本贴最后更新于 2358 天前,其中的信息可能已经东海扬尘

在实际开发过程中时不时的会遇到一些统计查询的需求,面对复杂的查询需求可以直接手写原生 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```
  • B3log

    B3log 是一个开源组织,名字来源于“Bulletin Board Blog”缩写,目标是将独立博客与论坛结合,形成一种新的网络社区体验,详细请看 B3log 构思。目前 B3log 已经开源了多款产品:SymSoloVditor思源笔记

    1063 引用 • 3453 回帖 • 198 关注
  • PHP

    PHP(Hypertext Preprocessor)是一种开源脚本语言。语法吸收了 C 语言、 Java 和 Perl 的特点,主要适用于 Web 开发领域,据说是世界上最好的编程语言。

    179 引用 • 407 回帖 • 494 关注
  • Laravel

    Laravel 是一套简洁、优雅的 PHP Web 开发框架。它采用 MVC 设计,是一款崇尚开发效率的全栈框架。

    20 引用 • 23 回帖 • 725 关注
  • 分享

    有什么新发现就分享给大家吧!

    248 引用 • 1794 回帖 • 2 关注

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • RabbitMQ

    RabbitMQ 是一个开源的 AMQP 实现,服务器端用 Erlang 语言编写,支持多种语言客户端,如:Python、Ruby、.NET、Java、C、PHP、ActionScript 等。用于在分布式系统中存储转发消息,在易用性、扩展性、高可用性等方面表现不俗。

    49 引用 • 60 回帖 • 365 关注
  • WiFiDog

    WiFiDog 是一套开源的无线热点认证管理工具,主要功能包括:位置相关的内容递送;用户认证和授权;集中式网络监控。

    1 引用 • 7 回帖 • 589 关注
  • 一些有用的避坑指南。

    69 引用 • 93 回帖
  • frp

    frp 是一个可用于内网穿透的高性能的反向代理应用,支持 TCP、UDP、 HTTP 和 HTTPS 协议。

    20 引用 • 7 回帖
  • 爬虫

    网络爬虫(Spider、Crawler),是一种按照一定的规则,自动地抓取万维网信息的程序。

    106 引用 • 275 回帖
  • Gzip

    gzip (GNU zip)是 GNU 自由软件的文件压缩程序。我们在 Linux 中经常会用到后缀为 .gz 的文件,它们就是 Gzip 格式的。现今已经成为互联网上使用非常普遍的一种数据压缩格式,或者说一种文件格式。

    9 引用 • 12 回帖 • 136 关注
  • 正则表达式

    正则表达式(Regular Expression)使用单个字符串来描述、匹配一系列遵循某个句法规则的字符串。

    31 引用 • 94 回帖
  • Node.js

    Node.js 是一个基于 Chrome JavaScript 运行时建立的平台, 用于方便地搭建响应速度快、易于扩展的网络应用。Node.js 使用事件驱动, 非阻塞 I/O 模型而得以轻量和高效。

    139 引用 • 269 回帖 • 33 关注
  • V2EX

    V2EX 是创意工作者们的社区。这里目前汇聚了超过 400,000 名主要来自互联网行业、游戏行业和媒体行业的创意工作者。V2EX 希望能够成为创意工作者们的生活和事业的一部分。

    17 引用 • 236 回帖 • 319 关注
  • JetBrains

    JetBrains 是一家捷克的软件开发公司,该公司位于捷克的布拉格,并在俄国的圣彼得堡及美国麻州波士顿都设有办公室,该公司最为人所熟知的产品是 Java 编程语言开发撰写时所用的集成开发环境:IntelliJ IDEA

    18 引用 • 54 回帖 • 2 关注
  • Latke

    Latke 是一款以 JSON 为主的 Java Web 框架。

    71 引用 • 535 回帖 • 789 关注
  • 链书

    链书(Chainbook)是 B3log 开源社区提供的区块链纸质书交易平台,通过 B3T 实现共享激励与价值链。可将你的闲置书籍上架到链书,我们共同构建这个全新的交易平台,让闲置书籍继续发挥它的价值。

    链书社

    链书目前已经下线,也许以后还有计划重制上线。

    14 引用 • 257 回帖
  • OpenStack

    OpenStack 是一个云操作系统,通过数据中心可控制大型的计算、存储、网络等资源池。所有的管理通过前端界面管理员就可以完成,同样也可以通过 Web 接口让最终用户部署资源。

    10 引用
  • Markdown

    Markdown 是一种轻量级标记语言,用户可使用纯文本编辑器来排版文档,最终通过 Markdown 引擎将文档转换为所需格式(比如 HTML、PDF 等)。

    167 引用 • 1518 回帖
  • 分享

    有什么新发现就分享给大家吧!

    248 引用 • 1794 回帖 • 2 关注
  • Maven

    Maven 是基于项目对象模型(POM)、通过一小段描述信息来管理项目的构建、报告和文档的软件项目管理工具。

    186 引用 • 318 回帖 • 289 关注
  • C

    C 语言是一门通用计算机编程语言,应用广泛。C 语言的设计目标是提供一种能以简易的方式编译、处理低级存储器、产生少量的机器码以及不需要任何运行环境支持便能运行的编程语言。

    85 引用 • 165 回帖
  • Thymeleaf

    Thymeleaf 是一款用于渲染 XML/XHTML/HTML5 内容的模板引擎。类似 Velocity、 FreeMarker 等,它也可以轻易的与 Spring 等 Web 框架进行集成作为 Web 应用的模板引擎。与其它模板引擎相比,Thymeleaf 最大的特点是能够直接在浏览器中打开并正确显示模板页面,而不需要启动整个 Web 应用。

    11 引用 • 19 回帖 • 359 关注
  • 七牛云

    七牛云是国内领先的企业级公有云服务商,致力于打造以数据为核心的场景化 PaaS 服务。围绕富媒体场景,七牛先后推出了对象存储,融合 CDN 加速,数据通用处理,内容反垃圾服务,以及直播云服务等。

    27 引用 • 225 回帖 • 171 关注
  • 新人

    让我们欢迎这对新人。哦,不好意思说错了,让我们欢迎这位新人!
    新手上路,请谨慎驾驶!

    52 引用 • 228 回帖
  • 外包

    有空闲时间是接外包好呢还是学习好呢?

    26 引用 • 232 回帖 • 3 关注
  • 又拍云

    又拍云是国内领先的 CDN 服务提供商,国家工信部认证通过的“可信云”,乌云众测平台认证的“安全云”,为移动时代的创业者提供新一代的 CDN 加速服务。

    21 引用 • 37 回帖 • 546 关注
  • 心情

    心是产生任何想法的源泉,心本体会陷入到对自己本体不能理解的状态中,因为心能产生任何想法,不能分出对错,不能分出自己。

    59 引用 • 369 回帖 • 3 关注
  • Ruby

    Ruby 是一种开源的面向对象程序设计的服务器端脚本语言,在 20 世纪 90 年代中期由日本的松本行弘(まつもとゆきひろ/Yukihiro Matsumoto)设计并开发。在 Ruby 社区,松本也被称为马茨(Matz)。

    7 引用 • 31 回帖 • 218 关注
  • DevOps

    DevOps(Development 和 Operations 的组合词)是一组过程、方法与系统的统称,用于促进开发(应用程序/软件工程)、技术运营和质量保障(QA)部门之间的沟通、协作与整合。

    50 引用 • 25 回帖
  • DNSPod

    DNSPod 建立于 2006 年 3 月份,是一款免费智能 DNS 产品。 DNSPod 可以为同时有电信、网通、教育网服务器的网站提供智能的解析,让电信用户访问电信的服务器,网通的用户访问网通的服务器,教育网的用户访问教育网的服务器,达到互联互通的效果。

    6 引用 • 26 回帖 • 513 关注
  • WebSocket

    WebSocket 是 HTML5 中定义的一种新协议,它实现了浏览器与服务器之间的全双工通信(full-duplex)。

    48 引用 • 206 回帖 • 325 关注