既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上C C++开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
employee | 员工表 |
name | 员工姓名,假定无重名 |
gender | 员工性别 |
我们已经计算出“好”销售员的名单,比较自然的想法,是用名单到花名册时找出其性别,再计一下数。但在SQL中要跨表获得信息需要用表间连接,这样,接着最初的结果,SQL就会写成:
select employee.gender,count(\*)
from employee,
( ( select top 10 sales from sales_amount where product='AC' order by amount desc )
intersect
( select top 10 sales from sales_amount where product='TV' order by amount desc ) ) A
where A.sales=employee.name
group by employee.gender
仅仅多了一个关联表就会导致如此繁琐,而现实中信息跨表存储的情况相当多,且经常有多层。比如销售员有所在部门,部门有经理,现在我们想知道“好”销售员归哪些经理管,那就要有三个表连接了,想把这个计算中的where和group写清楚实在不是个轻松的活儿了。
这就是我们要说的SQL的第四个重要困难:缺乏对象引用机制,关系代数中对象之间的关系完全靠相同的外键值来维持,这不仅在寻找时效率很低,而且无法将外键指向的记录成员直接当作本记录的属性对待,试想,上面的句子可否被写成这样:
select sales.gender,count(\*)
from (…) // …是前面计算“好”销售员的SQL
group by sales.gender
显然,这个句子不仅更清晰,同时计算效率也会更高(没有连接计算)。
我们通过一个简单的例子分析了SQL的四个重要困难,这也是SQL难写或要写得很长的主要原因。基于一种计算体系解决业务问题的过程,也就是将业务问题的解法翻译成形式化计算语法的过程(类似小学生解应用题,将题目翻译成形式化的四则运算)。SQL的上述困难会造成问题解法翻译的极大障碍,极端情况就会发生这样一种怪现象:将问题解法形式化成计算语法的难度要远远大于解决问题本身。
再打个程序员易于理解的比方,用SQL做数据计算,类似于用汇编语言完成四则运算。我们很容易写出3+5*7这样的算式,但如果用汇编语言(以X86为例),就要写成
mov ax,3
mov bx,5
mul bx,7
add ax,bx
这样的代码无论书写还是阅读都远不如3+5*7了(要是碰到小数就更要命了)。虽然对于熟练的程序员也算不了太大的麻烦,但对于大多数人而言,这种写法还是过于晦涩难懂了,从这个意义上讲,FORTRAN确实是个伟大的发明。
为了理解方便,我们举的例子还是非常简单的任务。现实中的任务要远远比这些例子复杂,过程中会面临诸多大大小小的困难。这个问题多写几行,那个问题多写几行,一个稍复杂的任务写出几百行多层嵌套的SQL也就不奇怪了。而且这个几百行常常是一个语句,由于工程上的原因,SQL又很难调试,这又进一步加剧了复杂查询分析的难度。
更多例子
我们再举几个例子来分别说明这几个方面的问题。
为了让例子中的SQL尽量简捷,这里大量使用了窗口函数,故而采用了对窗口函数支持较好的ORACLE数据库语法,采用其它数据库的语法编写这些SQL一般将会更复杂。
这些问题本身应该也算不上很复杂,都是在日常数据分析中经常会出现的,但已经很难为SQL了。
计算不分步
把复杂的计算分步可以在很大程度地降低问题的难度,反过来,把多步计算汇成一步完成则会提高问题的复杂度。
任务1 销售部的人数,其中北京籍人数,再其中女员工人数?
销售部的人数
select count(\*) from employee where department='sales'
其中北京籍的人数
select count(\*) from employee where department='sales' and native_place='Beijing'
再其中的女员工人数
select count (\*) from employee
where department='sales' and native_place='Beijing' and gender='female'
常规想法:选出销售部人员计数,再在其中找出其中北京籍人员计数,然后再递进地找出女员工计数。每次查询都基于上次已有的结果,不仅书写简单而且效率更高。
但是,SQL的计算不分步,回答下一个问题时无法引用前面的成果,只能把相应的查询条件再抄一遍。
任务2 每个部门挑选一对男女员工组成游戏小组
with A as
(select name, department,
row_number() over (partition by department order by 1) seq
from employee where gender=‘male’)
B as
(select name, department,
row_number() over(partition by department order by 1) seq
from employee where gender=‘female’)
select name, department from A
where department in ( select distinct department from B ) and seq=1
union all
select name, department from B
where department in (select distinct department from A ) and seq=1
计算不分步有时不仅造成书写麻烦和计算低效,甚至可能导致思路严重变形。
这个任务的直观想法:针对每个部门循环,如果该部门有男女员工则各取一名添进结果集中。但SQL不支持这种逐步完成结果集的写法(要用存储过程才能实现此方案),这时必须转变思路为:从每个部门中选出男员工,从每个部门选出女员工,对两个结果集分别选出部门出现在另一个结果集的成员,最后再做并集。
好在还有with子句和窗口函数,否则这个SQL语句简直无法看了。
集合无序
有序计算在批量数据计算中非常普遍(取前3名/第3名、比上期等),但SQL延用了数学上的无序集合概念,有序计算无法直接进行,只能调整思路变换方法。
任务3 公司中年龄居中的员工
select name, birthday
from (select name, birthday, row_number() over (order by birthday) ranking
from employee )
where ranking=(select floor((count(\*)+1)/2) from employee)
中位数是个常见的计算,本来只要很简单地在排序后的集合中取出位置居中的成员。但SQL的无序集合机制不提供直接用位置访问成员的机制,必须人为造出一个序号字段,再用条件查询方法将其选出,导致必须采用子查询才能完成。
任务4 某支股票最长连续涨了多少交易日
select max (consecutive_day)
from (select count(\*) (consecutive_day
from (select sum(rise_mark) over(order by trade_date) days_no_gain
from (select trade_date,
case when
closing_price>lag(closing_price) over(order by trade_date)
then 0 else 1 END rise_mark
from stock_price) )
group by days_no_gain)
无序的集合也会导致思路变形。
常规的计算连涨日数思路:设定一初始为0的临时变量记录连涨日期,然后和上一日比较,如果未涨则将其清0,涨了再加1,循环结束看该值出现的最大值。
使用SQL时无法描述此过程,需要转换思路,计算从初始日期到当日的累计不涨日数,不涨日数相同者即是连续上涨的交易日,针对其分组即可拆出连续上涨的区间,再求其最大计数。这句SQL读懂已经不易,写出来则更困难了。
集合化不彻底
毫无疑问,集合是批量数据计算的基础。SQL虽然有集合概念,但只限于描述简单的结果集,没有将集合作为一种基本的数据类型以扩大其应用范围。
任务5 公司中与其他人生日相同的员工
select \* from employee
where to_char (birthday, ‘MMDD’) in
( select to_char(birthday, 'MMDD') from employee
group by to_char(birthday, 'MMDD')
having count(\*)>1 )
分组的本意是将源集合分拆成的多个子集合,其返回值也应当是这些子集。但SQL无法表示这种“由集合构成的集合”,因而强迫进行下一步针对这些子集的汇总计算而形成常规的结果集。
但有时我们想得到的并非针对子集的汇总值而是子集本身。这时就必须从源集合中使用分组得到的条件再次查询,子查询又不可避免地出现。
任务6 找出各科成绩都在前10名的学生
select name
from (select name
from (select name,
rank() over(partition by subject order by score DESC) ranking
from score_table)
where ranking<=10)
group by name
having count(*)=(select count(distinct subject) from score_table)
用集合化的思路,针对科目分组后的子集进行排序和过滤选出各个科目的前10名,然后再将这些子集做交集即可完成任务。但SQL无法表达“集合的集合”,也没有针对不定数量集合的交运算,这时需要改变思路,利用窗口函数找出各科目前10名后再按学生分组找出出现次数等于科目数量的学生,造成理解困难。
缺乏对象引用
在SQL中,数据表之间的引用关系依靠同值外键来维系,无法将外键指向的记录直接用作本记录的属性,在查询时需要借助多表连接或子查询才能完成,不仅书写繁琐而且运算效率低下。
任务7 女经理的男员工们
用多表连接
select A.*
from employee A, department B, employee C
where A.department=B.department and B.manager=C.name and
A.gender='male' and C.gender='female'
用子查询
select * from employee
where gender='male' and department in
(select department from department
where manager in
(select name from employee where gender='female'))
如果员工表中的部门字段是指向部门表中的记录,而部门表中的经理字段是指向员工表的记录,那么这个查询条件只要简单地写成这种直观高效的形式:
where gender='male' and department.manager.gender='female'
但在SQL中则只能使用多表连接或子查询,写出上面那两种明显晦涩的语句。
任务8 员工的首份工作公司
用多表连接
select name, company, first_company
from (select employee.name name, resume.company company,
row_number() over(partition by resume. name
order by resume.start_date) work_seq
from employee, resume where employee.name = resume.name)
where work_seq=1
用子查询
select name,
(select company from resume
where name=A.name and
start date=(select min(start_date) from resume
where name=A.name)) first_company
from employee A
没有对象引用机制和彻底集合化的SQL,也不能将子表作主表的属性(字段值)处理。针对子表的查询要么使用多表连接,增加语句的复杂度,还要将结果集用过滤或分组转成与主表记录一一对应的情况(连接后的记录与子表一一对应);要么采用子查询,每次临时计算出与主表记录相关的子表记录子集,增加整体计算量(子查询不能用with子句了)和书写繁琐度。
SPL的引入
问题说完,该说解决方案了。
其实在分析问题时也就一定程度地指明了解决方案,重新设计计算语言,克服掉SQL的这几个难点,问题也就解决了。
这就是发明SPL的初衷!
SPL是个开源的程序语言,其全名是Structured Process Language,和SQL只差一个词。目的在于更好的解决结构化数据的运算。SPL中强调了步骤化、支持有序集合和对象引用机制、从而得到彻底的集合化,这些都会大幅降低前面说的“解法翻译”难度。
这里的篇幅不合适详细介绍SPL了,我们只把上一节中的8个例子的SPL代码罗列出来感受一下:
任务1
A | B | |
---|---|---|
1 | =employee.select(department==“sales”) | =A1.len() |
2 | =A1.select(native_place==“Beijing”) | =A2.len() |
3 | =A2.select(gender==“female”) | =A3.len() |
SPL可以保持记录集合用作中间变量,可逐步执行递进查询。
任务2
A | B | C | |
---|---|---|---|
1 | for employee.group(department) | =A1.group@1(gender) | |
2 | >if B1.len()>1 | =@ |
有步骤和程序逻辑支持的SPL能很自然地逐步完成结果。
任务3
A | |
---|---|
1 | =employee.sort(birthday) |
2 | =A1((A1.len()+1)/2) |
对于以有序集合为基础的SPL来说,按位置取值是个很简单的任务。
任务4
A | |
---|---|
1 | =stock_price.sort(trade_date) |
2 | =0 |
3 | =A1.max(A2=if(close_price>close_price[-1],A2+1,0)) |
SPL按自然的思路过程编写计算代码即可。
任务5
A | |
---|---|
1 | =employee.group(month(birthday),day(birthday)) |
2 | =A1.select(~.len()>1).conj() |
SPL可以保存分组结果集,继续处理就和常规集合一样。
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
A | |
---|---|
1 | =employee.group(month(birthday),day(birthday)) |
2 | =A1.select(~.len()>1).conj() |
SPL可以保存分组结果集,继续处理就和常规集合一样。
[外链图片转存中…(img-41TRP09S-1715818667573)]
[外链图片转存中…(img-CGupFDjZ-1715818667573)]
网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!