简化SQL计算之分组中的关联计算

最新推荐文章于 2022-10-10 23:12:25 发布

iteye_15107

最新推荐文章于 2022-10-10 23:12:25 发布

阅读量269

点赞数

分类专栏： Java相关 DB 文章标签： sql分组组内关联计算窗口函数集算器

DB 同时被 2 个专栏收录

94 篇文章 0 订阅

订阅专栏

Java相关

74 篇文章 0 订阅

订阅专栏

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

分组关联在实际业务中遇到的较多，比如http://forums.bit-tech.net/showthread.php?t=207052。下面以链接中的实际业务为蓝本设计一个更通用的例子，用来说明集算器实现分组关联的具体过程。

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

数据结构：Branch表，存储DVD分店信息。DVD表，存储DVD的标题及分类信息，DVD是虚拟的数据，比如“变形金刚4”是一个DVD，但它不是一张可见的光盘。DVDCopy表，存储DVD的多张拷贝，DVD拷贝是真正的光盘，以实体形式存放于各个分店。注意：DVDCopy表以BranchID字段和Branch表关联，以DVDID字段和DVD表关联。下面是部分数据：

Branch表：

DVD表：

DVDCopy表：

说明：

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

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

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

解题思路：

A、从DVDCopy表过滤出有效的、店里现存的DVD拷贝。

B、按照BID对DVDCopy表分组，每组就是一个门店所有的DVD拷贝。

C、找到每个门店对应的DVD拷贝对应的DVD，再计算出这些DVD的分类数量。

D、查询出现存的DVD分类数量小于4的门店，这样的门店符合要求

E、找到DVDCopy表中没出现过的门店，这样的门店也符合要求。

F、将两类符合要求的门店合并。

集算器代码：

A1-A3：从数据库中检索数据，分别命名为变量Branch、DVD、DVDCopy。计算解结果如下：

A4：将DVDCopy表中的DVDID字段，切换成DVD表中对应的记录，将BID字段切换成Branch表中对应的记录。注意：这一步是对象式关联访问的基础，需要使用switch函数。计算后，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中的分组函数不同。有时候我们需要对分组后的数据进行较深入的加工，而不是简单的汇总，这时用集算器的group函数会更方便，比如A7中的代码。

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

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