北京网站建设价格便宜,网站做跳转链接的好处,注册帐号,女频做的最好的网站发明SQL的初衷之一显然是为了降低人们实施数据查询计算的难度。SQL中用了不少类英语的词汇和语法#xff0c;这是希望非技术人员也能掌握。确实#xff0c;简单的SQL可以当作英语阅读#xff0c;即使没有程序设计经验的人也能运用。
然而#xff0c;面对稍稍复杂的查询计算…发明SQL的初衷之一显然是为了降低人们实施数据查询计算的难度。SQL中用了不少类英语的词汇和语法这是希望非技术人员也能掌握。确实简单的SQL可以当作英语阅读即使没有程序设计经验的人也能运用。
然而面对稍稍复杂的查询计算需求SQL就会显得力不从心经常写出几百行有多层嵌套的语句。这种SQL不要说非技术人员难以完成即使对于专业程序员也不是件容易的事常常成为很多软件企业应聘考试的重头戏。三行五行的SQL仅存在教科书和培训班现实中用于报表查询的SQL通常是以“K”计的。
SQL困难的分析探讨
这是为什么呢我们通过一个很简单的例子来考察SQL在计算方面的缺点。
设有一个由三个字段构成的销售业绩表为了简化问题省去日期信息
sales_amount销售业绩表sales销售员姓名假定无重名product销售的产品amount该销售员在该产品上的销售额
现在我们想知道出空调和电视销售额都在前10名的销售员名单。
这个问题并不难人们会很自然地设计出如下计算过程
1 按空调销售额排序找出前10名
2 按电视销售额排序找出前10名
3 对1、2的结果取交集得到答案
我们现在来用SQL做。
1 找出空调销售额前10名还算简单
select top 10 sales from sales_amount where productAC order by amount desc2 找出电视销售额前10名。动作一样
select top 10 sales from sales_amount where productTV order by amount desc3 求1、2的交集。这有点麻烦SQL不支持步骤化上两步的计算结果无法保存只能再重抄一遍了:
select * from( select top 10 sales from sales_amount where productAC order by amount desc )
intersect( select top 10 sales from sales_amount where productTV order by amount desc )一个只三步的简单计算用SQL要写成这样而日常计算中多达十几步的比比皆是这显然超出来许多人的可接受能力。
我们知道了SQL的第一个重要缺点不支持步骤化。把复杂的计算分步可以在很大程度地降低问题的难度反过来把多步计算汇成一步则很大程度地提高了问题的难度。
可以想象如果老师要求小学生做应用题时只能列一个算式完成小朋友们会多么苦恼当然不乏一些聪明孩子搞得定。
SQL查询不能分步但用SQL写出的存储过程可以分步那么用存储过程是否可以方便地解决这个问题呢
暂先不管使用存储过程的技术环境有多麻烦和数据库的差异性造成的不兼容我们只从理论上来看用分步SQL是否能让这个计算更简单捷些。
1 计算空调销售额前10名。语句还是那样但我们需要把结果存起来供第3步用而SQL中只能用表存储集合数据这样我们要建一个临时表
create temporary table x1 asselect top 10 sales from sales_amount where productAC order by amount desc2 计算电视销售额前10名。类似地
create temporary table x2 asselect top 10 sales from sales_amount where productTV order by amount desc3 求交集前面麻烦了这步就简单些
select * from x1 intersect x2分步后思路变清晰了但临时表的使用仍然繁琐。在批量结构化数据计算中作为中间结果的临时集合是相当普遍的如果都建立临时表来存储运算效率低代码也不直观。
而且SQL不允许某个字段取值是集合即临时表这样有些计算即使容忍了繁琐也做不到。
如果我们把问题改为计算所有产品销售额都在前10名的销售员试想一下应当如何计算延用上述的思路很容易想到
1 将数据按产品分组将每组排序取出前10名
2 将所有的前10名取交集
由于我们事先不知道会有多个产品这样需要把分组结果也存储在一个临时表中而这个表有个字段要存储对应的分组成员这是SQL不支持的办法就行不通了。
如果有窗口函数的支持可以转换思路按产品分组后计算每个销售员在所有分组的前10名中出现的次数若与产品总数相同则表示该销售员在所有产品销售额中均在前10名内。
select sales
from ( select sales,from ( select sales,rank() over (partition by product order by amount desc ) rankingfrom sales_amount)where ranking 10 )
group by sales
having count(*)(select count(distinct product) from sales_amount)这样的SQL有多少人会写呢
况且窗口函数在有些数据库中还不支持。那么就只能用存储过程写循环依次计算每个产品的前10名与上一次结果做交集。这个过程比用高级语言编写程序并不简单多少而且仍然要面对临时表的繁琐。
现在我们知道了SQL的第二个重要缺点集合化不彻底。虽然SQL有集合概念但并未把集合作为一种基础数据类型提供这使得大量集合运算在思维和书写时都需要绕路。
我们在上面的计算中使用了关键字top事实上关系代数理论中没有这个东西它可以被别的计算组合出来这不是SQL的标准写法。
我们来看一下没有top时找前10名会有多困难
大体思路是这样找出比自己大的成员个数作为是名次然后取出名次不超过10的成员写出的SQL如下
select sales
from ( select A.sales sales, A.product product,(select count(*)1 from sales_amountwhere A.productproduct AND A.amountamount) rankingfrom sales_amount A )
where productAC AND ranking10或
select sales
from ( select A.sales sales, A.product product, count(*)1 rankingfrom sales_amount A, sales_amount Bwhere A.salesB.sales and A.productB.product AND A.amountB.amountgroup by A.sales,A.product )
where productAC AND ranking10这样的SQL语句专业程序员写出来也未必容易吧而仅仅是计算了一个前10名。
退一步讲即使有top那也只是使取出前一部分轻松了。如果我们把问题改成取第6至10名或者找比下一名销售额超过10%的销售员困难仍然存在。
造成这个现象的原因就是SQL的第三个重要缺点缺乏有序支持。SQL继承了数学上的无序集合这直接导致与次序有关的计算相当困难而可想而知与次序有关的计算会有多么普遍诸如比上月、比去年同期、前20%、排名等。
SQL2003标准中增加的窗口函数提供了一些与次序有关的计算能力这使得上述某些问题可以有较简单的解法在一定程度上缓解SQL的这个问题。但窗口函数的使用经常伴随着子查询而不能让用户直接使用次序访问集合成员还是会有许多有序运算难以解决。
我们现在想关注一下上面计算出来的“好”销售员的性别比例即男女各有多少。一般情况下销售员的性别信息会记在花名册上而不是业绩表上简化如下
employee员工表name员工姓名假定无重名gender员工性别
我们已经计算出“好”销售员的名单比较自然的想法是用名单到花名册时找出其性别再计一下数。但在SQL中要跨表获得信息需要用表间连接这样接着最初的结果SQL就会写成
select employee.gender,count(*)
from employee,( ( select top 10 sales from sales_amount where productAC order by amount desc )intersect( select top 10 sales from sales_amount where productTV order by amount desc ) ) A
where A.salesemployee.name
group by employee.gender仅仅多了一个关联表就会导致如此繁琐而现实中信息跨表存储的情况相当多且经常有多层。比如销售员有所在部门部门有经理现在我们想知道“好”销售员归哪些经理管那就要有三个表连接了想把这个计算中的where和group写清楚实在不是个轻松的活儿了。
这就是我们要说的SQL的第四个重要困难缺乏对象引用机制关系代数中对象之间的关系完全靠相同的外键值来维持这不仅在寻找时效率很低而且无法将外键指向的记录成员直接当作本记录的属性对待试想上面的句子可否被写成这样
select sales.gender,count(*)
from (…) // …是前面计算“好”销售员的SQL
group by sales.gender显然这个句子不仅更清晰同时计算效率也会更高没有连接计算。
我们通过一个简单的例子分析了SQL的四个重要困难这也是SQL难写或要写得很长的主要原因。基于一种计算体系解决业务问题的过程也就是将业务问题的解法翻译成形式化计算语法的过程类似小学生解应用题将题目翻译成形式化的四则运算。SQL的上述困难会造成问题解法翻译的极大障碍极端情况就会发生这样一种怪现象将问题解法形式化成计算语法的难度要远远大于解决问题本身。
再打个程序员易于理解的比方用SQL做数据计算类似于用汇编语言完成四则运算。我们很容易写出35*7这样的算式但如果用汇编语言以X86为例就要写成 mov ax,3mov bx,5mul bx,7add ax,bx这样的代码无论书写还是阅读都远不如35*7了要是碰到小数就更要命了。虽然对于熟练的程序员也算不了太大的麻烦但对于大多数人而言这种写法还是过于晦涩难懂了从这个意义上讲FORTRAN确实是个伟大的发明。
为了理解方便我们举的例子还是非常简单的任务。现实中的任务要远远比这些例子复杂过程中会面临诸多大大小小的困难。这个问题多写几行那个问题多写几行一个稍复杂的任务写出几百行多层嵌套的SQL也就不奇怪了。而且这个几百行常常是一个语句由于工程上的原因SQL又很难调试这又进一步加剧了复杂查询分析的难度。
更多例子
我们再举几个例子来分别说明这几个方面的问题。
为了让例子中的SQL尽量简捷这里大量使用了窗口函数故而采用了对窗口函数支持较好的ORACLE数据库语法采用其它数据库的语法编写这些SQL一般将会更复杂。 这些问题本身应该也算不上很复杂都是在日常数据分析中经常会出现的但已经很难为SQL了。
计算不分步
把复杂的计算分步可以在很大程度地降低问题的难度反过来把多步计算汇成一步完成则会提高问题的复杂度。 任务1 销售部的人数其中北京籍人数再其中女员工人数 销售部的人数
select count(*) from employee where departmentsales其中北京籍的人数
select count(*) from employee where departmentsales and native_placeBeijing再其中的女员工人数
select count (*) from employee
where departmentsales and native_placeBeijing and genderfemale常规想法选出销售部人员计数再在其中找出其中北京籍人员计数然后再递进地找出女员工计数。每次查询都基于上次已有的结果不仅书写简单而且效率更高。
但是SQL的计算不分步回答下一个问题时无法引用前面的成果只能把相应的查询条件再抄一遍。 任务2 每个部门挑选一对男女员工组成游戏小组 with A as(select name, department,row_number() over (partition by department order by 1) seqfrom employee where gender‘male’)B as(select name, department,row_number() over(partition by department order by 1) seqfrom employee where gender‘female’)
select name, department from A
where department in ( select distinct department from B ) and seq1
union all
select name, department from B
where department in (select distinct department from A ) and seq1计算不分步有时不仅造成书写麻烦和计算低效甚至可能导致思路严重变形。
这个任务的直观想法针对每个部门循环如果该部门有男女员工则各取一名添进结果集中。但SQL不支持这种逐步完成结果集的写法要用存储过程才能实现此方案这时必须转变思路为从每个部门中选出男员工从每个部门选出女员工对两个结果集分别选出部门出现在另一个结果集的成员最后再做并集。
好在还有with子句和窗口函数否则这个SQL语句简直无法看了。
集合无序
有序计算在批量数据计算中非常普遍取前3名/第3名、比上期等但SQL延用了数学上的无序集合概念有序计算无法直接进行只能调整思路变换方法。 任务3 公司中年龄居中的员工 select name, birthday
from (select name, birthday, row_number() over (order by birthday) rankingfrom employee )
where ranking(select floor((count(*)1)/2) from employee)中位数是个常见的计算本来只要很简单地在排序后的集合中取出位置居中的成员。但SQL的无序集合机制不提供直接用位置访问成员的机制必须人为造出一个序号字段再用条件查询方法将其选出导致必须采用子查询才能完成。 任务4 某支股票最长连续涨了多少交易日 select max (consecutive_day)
from (select count(*) (consecutive_dayfrom (select sum(rise_mark) over(order by trade_date) days_no_gainfrom (select trade_date,case whenclosing_pricelag(closing_price) over(order by trade_date)then 0 else 1 END rise_markfrom 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 employeegroup by to_char(birthday, MMDD)having count(*)1 )分组的本意是将源集合分拆成的多个子集合其返回值也应当是这些子集。但SQL无法表示这种“由集合构成的集合”因而强迫进行下一步针对这些子集的汇总计算而形成常规的结果集。
但有时我们想得到的并非针对子集的汇总值而是子集本身。这时就必须从源集合中使用分组得到的条件再次查询子查询又不可避免地出现。 任务6 找出各科成绩都在前10名的学生 select name
from (select namefrom (select name,rank() over(partition by subject order by score DESC) rankingfrom score_table)where ranking10)
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.departmentB.department and B.managerC.name andA.gendermale and C.genderfemale用子查询
select * from employee
where gendermale and department in(select department from departmentwhere manager in(select name from employee where genderfemale))如果员工表中的部门字段是指向部门表中的记录而部门表中的经理字段是指向员工表的记录那么这个查询条件只要简单地写成这种直观高效的形式
where gendermale and department.manager.genderfemale但在SQL中则只能使用多表连接或子查询写出上面那两种明显晦涩的语句。 任务8 员工的首份工作公司 用多表连接
select name, company, first_company
from (select employee.name name, resume.company company,row_number() over(partition by resume. nameorder by resume.start_date) work_seqfrom employee, resume where employee.name resume.name)
where work_seq1用子查询
select name,(select company from resumewhere nameA.name andstart date(select min(start_date) from resumewhere nameA.name)) first_company
from employee A没有对象引用机制和彻底集合化的SQL也不能将子表作主表的属性字段值处理。针对子表的查询要么使用多表连接增加语句的复杂度还要将结果集用过滤或分组转成与主表记录一一对应的情况连接后的记录与子表一一对应要么采用子查询每次临时计算出与主表记录相关的子表记录子集增加整体计算量子查询不能用with子句了和书写繁琐度。
SPL的引入
问题说完该说解决方案了。
其实在分析问题时也就一定程度地指明了解决方案重新设计计算语言克服掉SQL的这几个难点问题也就解决了。
这就是发明SPL的初衷
SPL是个开源的程序语言其全名是Structured Process Language和SQL只差一个词。目的在于更好的解决结构化数据的运算。SPL中强调了步骤化、支持有序集合和对象引用机制、从而得到彻底的集合化这些都会大幅降低前面说的“解法翻译”难度。
这里的篇幅不合适详细介绍SPL了我们只把上一节中的8个例子的SPL代码罗列出来感受一下 任务1 AB1employee.select(department“sales”)A1.len()2A1.select(native_place“Beijing”)A2.len()3A2.select(gender“female”)A3.len()
SPL可以保持记录集合用作中间变量可逐步执行递进查询。 任务2 ABC1for employee.group(department)A1.group1(gender)2if B1.len()1|B1
有步骤和程序逻辑支持的SPL能很自然地逐步完成结果。 任务3 A1employee.sort(birthday)2A1((A1.len()1)/2)
对于以有序集合为基础的SPL来说按位置取值是个很简单的任务。 任务4 A1stock_price.sort(trade_date)203A1.max(A2if(close_priceclose_price[-1],A21,0))
SPL按自然的思路过程编写计算代码即可。 任务5 A1employee.group(month(birthday),day(birthday))2A1.select(~.len()1).conj()
SPL可以保存分组结果集继续处理就和常规集合一样。 任务6 A1score_table.group(subject)2A1.(~.rank(score).pselecta(~10))3A1.(~(A2(#)).(name)).isect()
使用SPL只要按思路过程写出计算代码即可。 任务7 A1employee.select(gender“male” department.manager.gender“female”)
支持对象引用的SPL可以简单地将外键指向记录的字段当作自己的属性访问。 任务8 A1employee.new(name,resume.minp(start_date).company:first_company)
SPL支持将子表集合作为主表字段就如同访问其它字段一样子表无需重复计算。
SPL有直观的IDE提供了方便的调试功能可以单步跟踪代码进一步降低代码的编写复杂度。 对于应用程序中的计算SPL提供了标准的JDBC驱动可以像SQL一样集成到Java应用程序中
…
Class.forName(com.esproc.jdbc.InternalDriver);
Connection conn DriverManager.getConnection(jdbc:esproc:local://);
Statement st connection.();
CallableStatement st conn.prepareCall({call xxxx(?,?)});
st.setObject(1, 3000);
st.setObject(2, 5000);
ResultSet resultst.execute();
...
SPL资料
SPL官网SPL下载SPL源代码
