ITPub博客

浅解比 SQL 更好用的 SPL(二)

原创 数据治理 作者:raqsoft 时间:2018-08-21 11:39:55 0 删除 编辑

从SQL到 SPL 基本查询语法迁移 之多表操作

上一篇 我们针对单表的情形了解了如何把数据计算从SQL查询迁移到集算器,或者更准确地说,迁移到集算器所使用的 SPL 集算语言。这个迁移过程,既有相同的概念,也有不同的思路。

接下来,我们一起针对多表的情况看一下集算器和 SPL 语言是如何发挥更大的优势的。

JOIN连接两个记录

在前面的例子中,我们得到了每个雇员的销售额,如果进一步还想知道每个雇员给出的最小折扣,那就又复杂了些。因为折扣信息在另一张订单明细表里。急性子的朋友可能上来就要JOIN两个表,然后再聚合两个测度字段——这样就算错了!正确的做法应该是每个测度字段先按分组聚合出结果,之后再JOIN。这是因为先JOIN的话可能导致结果中出现重复记录。

按照这个思路写出SQL,并和集算器中的 SPL 代码进行比较:

SQL
select t1.employeeId, salesAmount, lowestDiscount from (


select employeeId, sum(money) salesAmount from order

where orderDate>=’2012-01-01′ and orderDate<‘2012-02-01’

group by employeeId

having sum(money)>5000) t1

left join (

select employeeId, min(discount) lowestDiscount from order

join orderDetail on order.orderId=orderDetail.orderId

where orderDate>=’2012-01-01′ and orderDate<‘2012-02-01’

group by employeeId) t2

on t1.employeeId=t2.employeeId

 

集算器 A
1 =connect(“hsqlDB”)
2 =A1.query(“select * from order”)
3 =A2.select(orderDate>=date(“2012-01-01”) && orderDate<date(“2012-02-01”))
4 =A3.group(employeeId;~.sum(money):salesAmount)
5 =A4.select(salesAmount>5000)
6 =A1.query(“select * from orderDetail”)
7 =join(A3:order,orderId;A6:orderDetail,orderId)
8 =A7.group(A7.order.employeeId:employeeId;~.min(orderDetail.discount):lowestDiscount)
9 =join(A5:r1,employeeId;A8:r2,employeeId)
10 =A9.new(r1.employeeId:employeeId,r1.salesAmount:salesAmount,r2.lowestDiscount:lowestDiscount)
11 >A1.close()

A5之前是我们做过的聚合符合条件的按雇员分组的销售额;

A6查出订单明细表的所有数据;

A7中的 SPL 用到了对应SQL的多表连接的新函数join。join函数把两个表连接起来,这里它的参数的含义是:将A3序表起一个别名order,A6序表起一个别名orderDetail,然后用两个表各自的orderId作为关联字段,观察A7的结果如下:

可以看到,join后形成的序表有order、orderDetail两个字段,两个字段的值分别直接指向了两个原始序表的记录,这就是说字段的值可以是复杂数据类型(包括序表、序列类型),也可以嵌套多层结构的数据,这是SQL语法里不允许的,也是 SPL 的特色和优势之一。

下图比较形象地说明了这种结构(长方形表示序表、椭圆形表示记录、三角形表示关联字段):

A8直接用 SPL 中的group函数对这个复杂结构的序表进行分组聚合运算,获得各个雇员曾经给出的最小折扣。

再需要注意的一点是A3序表被后面两个不同的动作(A4、A7)都使用了一次,达到了中间结果复用的效果。这种方式对于越是复杂的计算,往往作用越大,可以起到类似“数据模块化”的作用。

在A9中通过 SPL 的join函数把A5(销售额超过5000)、A8(最小折扣)两个序表连接起来;

最后,A10使用new函数生成新的序表结构,而数据来自A9复杂结构记录的不同层次位置。

A10最终的结果如下:

简单回顾一下,对于最后这个比较复杂的SQL,如果你是个SQL高手,可能会看出第二个子查询里的where并不是必须的,之所以保留它,是因为有可能会缩小处理数据的范围,从而提升一些性能。这也是SQL的另一个特点,一方面需要不喘气的一句话把整个查询表达出来,另一方面还需要同时考虑性能优化因素,甚至即便考虑到了优化方案,也不一定能轻松、自然地描述出来。

在上面的例子中,不难体会出集算器以及 SPL 分步过程的 重视, 每一个步骤的结果都是可以随时观察的 ,而且 前面步骤的结果也可以重复利用 ,同时 执行步骤也可以被程序员自由定制 。这些特点最直接的好处是降低了学习和编码的难度,而更本质的是符合人的自然思维,为描述复杂计算奠定了基础。

 

UNION等合并两个集合

SQL中还有一类针对多个集合(表)的运算,就是常说的 并、合、交、差(UNION、UNION ALL、INTERSECTION、MINUS),与之对应的,在 SPL 表示为 &、|、^、\。

集合运算时常需要判断一条记录是否重复,在这个细节上,SQL和 SPL 有一点区别。 SPL 里因为序表、记录,以及字段的值都被看成是一个对象,所以在进行并集运算时,可以比对元素是否为同一个对象,而SQL的记录是抽象的,不是一个实体对象,所以只能通过逐个比对两条记录的字段值来判断是否重复。

我们来看一下实际的例子:

集算器 A B
1 =connect(“hsqlDB”)
2 =A1.query(“select * from order”)
3 =A2.select(orderId>10251 && orderId<10256) =A2.select(orderId>10254 && orderId<10258)
4 =A3&B3 =A3|B3
5 =A3^B3 =A3\B3
6 >A1.close()

 

A3序表的结果:

B3序表的结果:

A3&B3,去掉重复合并到一起,并集运算后得到A4序表的结果:

A3|B3,保留重复合并到一起,合集运算后得到B4序表的结果:

A3^B3,取A3和B3里相同的记录,交集运算后得到A5序表的结果:

A3\B3,A3里去掉B3中存在的记录,差集运算后得到A5序表的结果:

其它常用语法

最后,再看两个常用的SQL函数语法,我们来对比看一下 SPL 的实现。

CASE WHEN …

SQL中的case when … then … else … end,在 SPL 中用if函数,语法是 if(条件,真值,假值) ,下面的例子是把employeeId<5的分为一组,剩余的分到另一组:

SQL
select (case when employeeId<5 then 1 else 2 end)  groupId, orderId from order

 

集算器 A
1 =connect(“hsqlDB”)
2 =A1.query(“select * from order”)
3 =A2.new(if(employeeId<5,1,2):groupId,orderId)
4 >A1.close()

COALESCE(exp1,exp2…expn)

SPL 中仍然用if函数, if(条件,真值,假值) ,下面这个例子把1000这个特殊的值赋值给为null的employeeId。

SQL
select coalesce(employeeId,1000) employeeId,orderId from order

 

集算器 A
1 =connect(“hsqlDB”)
2 =A1.query(“select * from order”)
3 =A2.new(if(employeeId==null,1000,employeeId):employeeId,orderId)
4 >A1.close()

 

集算器(SPL)≠SQL

看完上面这些例子,会给人一个感觉:集算器,或者其中的 SPL 代码只是SQL的替代品,而常用的关系数据库系统(RDBMS)本身已经是 存储能力(数据表) + 计算能力(SQL) 的综合体了,还要这么一个外来的计算体系干嘛?

针对这个疑问,有两个层面的答案。首先是数据层面,并非所有要计算的数据都在数据库里。当你手边有个文本、excel或从一个网络服务临时得到一些数据想要计算,或者从大量终端采集上来的数据马上要处理出结果……如果这些都不得不倒腾到数据库里然后再计算,还真是有点太绕了,更何况还可能涉及到数据库的安装部署,或者权限控制等琐碎而现实的问题。

其次是计算层面,对于很多复杂的计算过程,SQL因为天生缺陷无法做到开发高效、维护高效、执行效高。这也是我们这篇文章最想说明的地方,SQL的计算主要还是面向查询,而这些对“高效”的追求,面向的是更广泛的数据计算,这正是集算器和 SPL 语言所要重点改善的。

正因为有了这两个层面的原因,集算器和 SPL 语言已经可以独立于各种数据存储形式,在“存储”和“应用”之间,作为一个功能完整、形式灵活、部署方便的“数据计算中间件”而存在。这方面的内容,我们会在后面的章节做更多的介绍和探讨。

这里,我们只是先简单看一下集算器和 SPL 语言如何基于文本、Excel等数据源进行计算。事实上,前面这些例子中的计算步骤完全不用改写,只需要改变加载数据那一句就可以了。也就是把:

=A1.query(“select * from order”)

改为下列任意方式之一:

=file(“d:/data/order.txt”) .import@t()  //从文本文件加载数据表

=file(“d:/data/orderDetail.xls”) .importxls@t()  //从excel加载数据表

=httpfile(“http://127.0.0.1/service”,”param1=value1&param2=value2″) //从http server加载数据表

……

 

很明显,通过这种简单明了的数据加载方式,可以将各种数据源的数据形成统一的“序表”,从而在一个计算过程里可以轻松混合使用。换句话说,不必为了计算能力而要求必须统一存储方式。同样的,在集算器和 SPL 的环境中,计算结果的存储,也和加载数据一样方便,也是多样化的,可以由程序员自由选择。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31551377/viewspace-2212443/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2018-08-21

  • 博文量
    3
  • 访问量
    7395