详解数据库中跨库数据表的运算_数据库其它_

1. 简单合并（FROM）

所谓跨库数据表，是指逻辑上同一张数据表被分别存储在不同数据库中。其原因有可能是因为数据量太大，放在一个数据库难以处理，也可能在业务上就需要将生产库和历史库分开。而不同的数据库，可能只是部署在不同的机器上的同种数据库，也可能是连类型都不同的数据库系统。

在面对跨库数据表，特别是数据库类型都不相同的情况时，数据库自带的工具往往就力所不及了，一般都需要寻找能够很好地支持多数据源类型的第三方工具，而集算器，可以说是其中的佼佼者了。下面，我们就针对几种常见的跨库混合运算情况详细讨论一下：

跨库运算，简单粗暴的思路就是把散布在各个库里的逻辑上相同的数据表合并成一个表，然后在这一个表上进行运算。

例如，在两个数据库 HSQL 和 MYSQL 中，分别存储了一张学生成绩表，两者各自保存了一部分学生信息，如下图所示：

利用集算器，我们可以很容易地将这两个结构相同的表合并为一个表，集算器的 SPL 脚本如下：

A1、A2 和 B1、B2 分别读取了两个库里的学生成绩表，而 A3 用一种简单直观的方式就把两个表合并了。

这种方式实际上是把两个表都读入了内存，分别生成了集算器的序表对象，然后利用序表的运算“|”完成了合并。可能有的同学会问：如果我的数据量比较大，无法全部读入内存怎么办？没关系，专为处理大数据而生的集算器，决不会被这么简单的小问题难住。我们可以使用游标，同样可以实现表的快速拼接：

A2、B2 分别用游标打开两个库里的学生成绩表，A3 则使用 conjx() 函数将这两个游标合并，形成了一个新的可以同时访问两个表的游标。

对应于 SQL，这种简单合并好比只是完成了 from 工作，让结构相同的跨库表的数据“纵向”拼接成了一个可以访问的序表或者游标，而实际运算中，还会涉及过滤 (where/having)、分组聚合 (group+sum/count/avg/max/min)、连接 (join+on)、去重 (distinct)、排序 (order)、取部分数据 (limit+offset)，等等操作，下面我们就将对这些运算一一展开讨论。

当然，我们在处理这些运算的需求时，不能只是简单的实现功能，我们还需要考虑实现的效率和性能，因此原则上，我们会尽量利用数据库的计算能力，而集算器主要负责混合运算。不过，有时也需要由集算器负责几乎所有的运算，数据库仅仅负责存储数据。

2. WHERE

where 过滤的本质是通过比较计算，去除比较的结果是 false 的记录，因此 where 只作用于一条记录，不涉及记录之间的运算，也不需要考虑数据位于哪个数据库。比如，在前面的例子中，我们要统计出“一班”所有同学的“数学”成绩，单库中的 SQL 是这样的：

 SELECT 学生 ID, 成绩 FROM 学生成绩表 WHERE 科目 ='数学' AND 班级 =‘一班'

多库时，也只要将 where 子句直接写在 SQL 中，让各个数据库去并行处理过滤就可以了：

我们也可以让集算器负责所有过滤运算，数据库仅存储数据。这时可以使用集算器的 select 函数（与 SQL 的 select 关键字不同）

数据量较大时，同样也可以将序表换成游标，使用 conjx 函数进行连接：

3. ORDER BY 和 LIMIT OFFSET

order by 是在结果集产生后才进行的处理。在上面的例子中，如果我们要按数学成绩排序，对于单数据库，只需要加上 order by 子句：

 SELECT 班级, 学生 ID, 成绩 FROM 学生成绩表 WHERE 科目 ='数学' AND 班级 =‘一班' ORDER BY 成绩

而对于多数据库，可以让数据库先分别排序，然后由集算器归并有序数据。这样可以最大的发挥数据库与并行服务器的性能。

也可以倒序排序，归并时在排序字段前加“-”（merge 函数可以不加“-”，不过按标准写法是加上的）

当然也可以完全由集算器来排序：

由集算器实现倒序排序：

而对于大数据量，需要使用游标及 mergex 来完成有序归并：

limit 和 offset 的执行又在 order 之后，例子中如果想取数学成绩除了第一名之后的前十名（可以少于但不能多于），单库情况下 SQL 是这样的：

 SELECT 班级, 学生 ID, 成绩 FROM 学生成绩表 WHERE 科目 ='数学' AND 班级 =‘一班' ORDER BY 成绩 DESC LIMIT 10 OFFSET 1

多数据库时，可以用集算器的 to 函数实现 limit offset 的功能，to(n+1,n+m) 等同于 limit m offset n

对于大数据量使用游标的情况，offset 功能可以使用集算器函数 skip 实现，而 limit 的功能则可以使用函数 fetch 实现

4. 聚合运算

我们来讨论五种常见的聚合运算：sum/count/avg/max/min。

• sum 的基础是加法，根据加法结合律，各数据库中内部数据先分别求和，然后拼接成一张表后再求总和，与先拼接成一张表然后一起求和的结果，其实是一样的。

• count 的本质，是对每项非 null 数据计 1，null 数据计 0，然后进行累加计算。所以其本质仍是加法运算，与 sum 一样符合加法结合律。唯一不同的是对原始数据不是累加其本身的数值而是计 1（非 null）或计 0（为 null）。

• avg 的本质，是当 count > 0 时 avg = sum/count，当 count = 0 时 avg = null。显然 avg 不能像 sum 或 count 那样先分别计算了。不过根据定义，我们可以先算出 sum 和 count，再通过 sum 和 count 计算出 avg。

• max 和 min 的基础都是比较运算，而因为比较运算具有传递性，因此所有数据库的最值，可以通过比较各个数据库的最值得到。

依旧是上面的例子，这次我们要求两个班全体学生的数学总分、人数、平均分、最高及最低分，对于单源数据：

 SELECT sum(成绩) 总分数, count(成绩) 考试人数, avg(成绩) 平均分, max(成绩) 最高分, min(成绩) 最低分 FROM 学生成绩表 WHERE 科目 ='数学'

聚合运算的结果集很小，只有一行，因此无论源数据量的大小，都可以使用游标，代码如下：

事实上，前面提到的 order by +limit offset 本质上也可以看成是一种聚合运算：top。从这个角度进行优化，可以获得更高的计算效率。毕竟数据量大时，全排序的成本很高，而且取前 N 个数据的操作也并不需要全排序。当然，这个方法对于数据量小的情况也同样适用。

具体来说，对于 order by F limit m offset n 的情况，只需先用 top(n+m, F, ~)，再用 to(n+1,) 就行了。

我们仍以之前的含 order by+limit offset 的 SQL 语句为例：

 SELECT 班级, 学生 ID, 成绩 FROM 学生成绩表 WHERE 科目 ='数学' AND 班级 =‘一班' ORDER BY 成绩 DESC LIMIT 10 OFFSET 1

对于多数据库, 脚本如下，其中倒序排序只需在排序字段前加“-”：

5. GROUP BY、DISTINCT 和 HAVING

A、分组聚合运算

对于 group by，因为最终所得结果与样本个体的输入顺序无关，所以只要样本的总体不变，最终结果也不会变。也就是说，只要在从分库中提取数据和最终汇总全部数据时，都预先进行了分类运算即可。

假设我们想分别求一、二班的数学总分、人数、平均分、最高及最低分，单数据库如下：

 SELECT 班级, sum(成绩) 总分数, count(成绩) 考试人数, avg(成绩) 平均分, max(成绩) 最高分, min(成绩) 最低分 FROM 学生成绩表 WHERE 科目 ='数学' GROUP BY 班级

我们分三种情况讨论：

第一，对于小数据，聚合运算的结果集只会更小，这时推荐使用 query+groups：

第二，对于大数据量，如果结果集也很大，那么就应该使用 cursor+groupx。

另外，由于大结果集的分组计算较慢，需要在外存产生缓存数据。而如果我们在数据库中对数据先排序，则可以避免这种缓存（此时计算压力会转到数据库，因此需要根据实际情况权衡，通常情况下，数据库服务器的计算能力会更强一些）。

具体的办法是对 SQL 的结果集使用 order by 排序，然后在集算器中使用 mergex 函数归并后，再使用 groupx 的 @o 选项分组：

当然如果不希望加重数据库负担，也可以让数据库只做分组而不排序，此时集算器直接用 groupx，注意不能加 @o 选项。另外汇总数据时，也要把 mergex 换成 conjx：

第三，如果已明确地知道结果集很小，那么推荐用 cursor+groups

此时 groups 比 groupx 有更好的性能，因为 groups 将运算数据都保存在内存中，比 groupx 节省了写入外存文件的时间。

另外用 groups 可以不要求在数据库中预先排序，因为数据库 group by 的结果集本身不一定有序，再使用 orde by 排序也会增加成本。而对于小结果集，集算器用 groups@o 也并不一定比直接用 groups 更有效率。

通常，汇总数据要用 conjx

B、去重后计数 (count distinct)

在各个数据库内去重，可以使用 distinct 关键字。而数据库之间的数据去重，则可以使用集算器的 merge@u 函数。要注意的是使用前应该确保表内数据对主键字段（或者具有唯一性的一个或多个字段）有序。

对于 distinct 来说， sum(distinct)、avg(distinct) 的计算方法与 count(distinct) 大同小异，而且业务中不常用到，而 max(distinct)、min(distinct) 与单纯使用 max、min 没有区别。因此，我们只以 count(distinct) 为例加以说明。

比如，想要计算全年级（假设只有一班和二班）语数外三科至少有一科不及格需要补考的总人数，单数据库的 SQL 是这样的：

 SELECT count(distinct 学生 ID) 人数 FROM 学生成绩表 WHERE 成绩 <60

对于多源数据，全分组聚合在使用游标或序表方面没有差别，为了语法简便起见以游标为例：

再如，想要分别计算每班语数外三科至少有一科不及格需要补考的总人数，单数据库的 SQL 是这样的：

 SELECT 班级, count(distinct 学生 ID) 人数 FROM 学生成绩表 WHERE 成绩 <60 GROUP BY 班级

对于多数据库，同样需要先汇总去重，再进行分组聚合。汇总前需要数据有序，且汇总后数据仍然有序，所以分组函数 groups 和 groupx 都可以使用 @o 选项。

对于小数据量，可以使用 merge@u、groups@o 和 query：

对于大数据量小结果集，可以使用 mergex@u、groups@o 和 cursor：

对于大数据量大结果集，可以使用 mergex@u、groupx@o 和 cursor：

C、对聚合字段过滤（having）

having 是对聚合 (分组) 后得出的结果集再做过滤。所以当语句中有 having 出现时，如果聚合 (分组) 操作没有彻底执行完毕，需要将 having 子句先提取出来。待数据彻底完成聚合 (分组) 操作之后，再执行条件过滤。

对于多源数据，如果聚合计算是在汇总之后才能最终完成，那么 having 必须使用集算器的函数 select 来实现过滤。

下面主要说明这种聚合计算在汇总之后才完成的情况：比如，想要获得一班和二班的三个科目的考试中，有哪些平均分是低于 60 分的。对于单数据库，SQL 可以这样写：

 SELECT 班级, 科目, avg(成绩) 平均分 FROM 学生成绩表 GROUP BY 班级, 科目 HAVING avg(成绩)<60

对于多数据库，相关集算器执行代码如下：

对于大数据量，需要使用游标 (select 函数同样适用于游标)

提示： 本文由神整理自网络，如有侵权请联系本站删除！
本站声明：
1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持；
2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！