SPL 简化 SQL 案例详解:分组关联

在数据库应用开发中,我们经常需要面对复杂的SQL式计算,比如多层分组中的关联计算。在SQL中,分组必须同时进行汇总计算,并且不能进行对象式关联访问,因此处理这类问题会比较复杂,只能用窗口函数嵌套多层子查询这类高级技巧来实现。而本文要介绍的SPL能够支持真正的分组,进行直观的对象式关联访问,从而解决这类问题更加容易。

分组关联在实际业务中遇到的很多,下面以实际业务为蓝本设计一个比较通用的例子,以此说明SPL实现分组关联的具体过程:

计算目标:查询出缺货的DVD分店,即现存的DVD拷贝不到4类的分店。

数据结构

l  Branch表,存储DVD分店信息;

l  DVD表,存储DVD的标题及分类信息,DVD是虚拟的数据,比如“变形金刚4”是一个DVD,但它不是一张可见的光盘

l  DVDCopy表,存储DVD的多张拷贝,DVD拷贝是真正的光盘,以实体形式存放于各个分店。注意:DVDCopy表以BranchID字段和Branch表关联,以DVDID字段和DVD表关联。

下面是部分数据示例:

Branch表:

BID

Street

City

B001

street1

New York

B002

street2

Houston

B003

street3

LA

B004

street4

Lincoln

DVD表:

DVDID

Category

Title

D001

science fiction

Transformers IV

D002

science fiction

Transformers II

D003

science fiction

Guardians of the Galaxy

D004

act

The Expendables III

D005

sport

Need for Speed

D006

feature

Grace of Monaco

DVDCopy表:

CopyID

DVDID

BID

Status

LastDateRented

LastDateReturned

MemberID

C000

D001

B001


7/10/2014

7/13/2014

M001

C001

D004

B001


7/10/2014

7/13/2014

M001

C002

D001

B001


7/10/2014


M001

C003

D005

B001


7/10/2014

7/13/2014

M003

C004

D006

B001


7/10/2014

7/13/2014

M003

C005

D005

B002


7/10/2014

7/13/2014

M003

C006

D002

B002


7/10/2014

7/13/2014

M006

C007

D002

B002


7/10/2014

7/13/2014

M007

C008

D001

B002


7/10/2014

7/13/2014

M008

C009

D004

B002


7/10/2014

7/13/2014

M009

C010

D005

B002


7/10/2014

7/13/2014

M010

C011

D006

B002

Miss

7/10/2014

7/13/2014

M010

C000

D001

B003


7/10/2014

7/13/2014

M001

C001

D004

B003


7/10/2014

7/13/2014

M001

C002

D001

B003

Miss

7/10/2014


M001

C003

D005

B003


7/10/2014

7/13/2014

M003

说明

1.     计算结果应当是Branch表中的某些记录。

2.     DVDCopy表中的Status字段如果是“Miss”,则说明光盘丢失。LastDateReturned字段如果为空,则说明光盘借出尚未归还。显然,丢失或未归还的光盘不在计算范围内,应当过滤掉。

3.     应当考虑某些分店可能在DVDCopy表中不存在记录,虽然这种情况比较罕见。

解题思路

1.     DVDCopy表过滤出店里现存的DVD拷贝(没有丢失或借出)。

2.     按照BIDDVDCopy表分组,每组就是一个门店所有的DVD拷贝。

3.     找到每个门店的DVD拷贝对应的DVD,再计算出这些DVD的分类数量。

4.     查询出现存的DVD分类数量小于4的门店,这样的门店符合要求。

5.     找到DVDCopy表中没出现过的门店,这样的门店也符合要求。

6.     将两类符合要求的门店合并。

SPL代码


A

1

=Branch=db.query("select * from   Branch")

2

=DVD=db.query("select * from   DVD")

3

=DVDCopy=db.query("select * from   DVDCopy")

4

=DVDCopy.switch(DVDID,DVD:DVDID;   BID,Branch:BID)

5

=DVDCopy.select(STATUS!="Miss"   && LASTDATERETURNED!=null)

6

=A5.group(BID)

7

=A6.new(~.BID:BonList,   ~.(DVDID).id(CATEGORY).count():CatCount)

8

=A7.select(CatCount<4)

9

=A8.(BonList) | (Branch \ A7.(BonList))

10

>file("shortage.xlsx").xlsexport@t(A9)

A1-A3:从数据库中检索数据,分别命名为变量BranchDVDDVDCopy。计算结果如下:

A4=DVDCopy.switch(DVDID,DVD:DVDID; BID,Branch:BID)

使用函数switch,将DVDCopy表中的DVDID字段切换成DVD表中对应的记录,将BID字段切换成Branch表中对应的记录。这一步是对象式关联访问的基础,计算后DVDCopy的结果如下:

浅蓝色字体表示该字段对应为某条记录,点击后可查看,如下图:

此时,只需用操作符“.”就可以进行对象式关联访问,比如DVDCopy.(DVDID). (CATEGORY)表示每个DVD拷贝对应的DVD分类。DVDCopy.(BID)则可以取得每个DVD拷贝对应的分店详情(完整记录)。

A5=DVDCopy.select(STATUS!="Miss" && LASTDATERETURNED!=null)

这句代码用来过滤数据,即:丢失的,未归还的DVD拷贝不在计算范围内,过滤后A5的值如下:

A6=A5.group(BID)

上述代码用来对A5中的数据按照BID分组,每行代表一个门店的所有DVD拷贝,如下:

点击浅蓝色字体,可以看到组内成员:

可以看到,函数group只对数据进行分组,并不会同时进行汇总计算,这一点和SQL中的分组函数不同。当我们需要对分组后的数据进行较深入加工,而不是简单汇总时,用SPLgroup函数会更方便,比如A7中的代码。

A7=A6.new(~.BID:BonList, ~.(DVDID).id(CATEGORY).count():CatCount)

上述代码用来计算每个门店对应的DVD拷贝各有几类。函数new可以根据A6中的数据生成新的对象A7A7有两个列:BonListCatCountBonList直接来自A6中组内数据的BID列,CatCount来自于组内数据的DVDID列。CatCount的算法分为三部分:~.(DVDID)找到每个门店所有的DVD拷贝对应的DVD记录;id(CATEGORY)去除这些DVD记录中重复的Categorycount()用来计算Category的数量。计算结果如下:

即:B002门店有3DVD拷贝,B003门店有3类,B001门店有4类。

A8A7.select(CatCount<4)

上述代码执行查询,求出CatCount小于4的门店,结果如下:

上述缺货的门店是根据DVDCopy表计算出的。但有些严重缺货的门店也许不会出现在DVDCopy表,比如该门店所有的DVD拷贝都借出去了,或者该门店完全没有DVD拷贝,因此要把这部分门店合并进来,代码如下:

A9=A8.(BonList) | (Branch \ A7.(BonList))

上述代码中,运算符“|”表示将两个数据集进行并集计算(可用union函数代替),运算符“\”表示差集计算(可用函数diff代替)。A8.(BonList)BranchA7.(BonList)分别代表:DVDCopy表中缺货的门店、所有的门店、DVDCopy表中出现过的门店,其值分别为:

 

A9就是本案例最终的计算结果,其值为:

A10>file("shortage.xlsx").xlsexport@t(A9)

最后将结果导出到excel文件shortage.xlsx,打开文件查看结果如下:

通过这个例子我们可以看到,SQL缺乏显式集合,不能用A8Branch这样的变量来代表数据集,因此上述简短的SPL代码必须用几个冗长的SQL才能实现。

另外,SPL可被报表工具或java程序调用,调用的方法也和普通数据库相似,使用它提供的JDBC接口即可向java主程序返回ResultSet形式的计算结果,具体方法可参考相关文档。Java如何调用SPL脚本】


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾