对异构数据库进行关联查询
例题描述和简单分析
Oracle数据库中，有 BRANCH 表，存储 DVD 分店信息，部分数据如下所示：
| BID | STREET | CITY |
| B001 | street1 | New York |
| B002 | street2 | Houston |
| B003 | street3 | LA |
| B004 | street4 | Lincoln |
MySQL数据库中，有 DVD 表，存储 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 |
| DVDID | Category | Title |
HSQL数据库中，有 DVDCOPY 表，存储 DVD 的多张拷贝，部分数据如下所示：
| 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 |
其中 DVDCOPY 表以 BID 字段和 BRANCH 表关联，以 DVDID 字段和 DVD 表关联。
需求 1：找出城市为 New York 的分店中所有的 DVD 拷贝的标题及分类信息，结果如下：
| CITY | COPYID | CATEGORY | TITLE |
| New York | C000 | science fiction | Transformers IV |
| New York | C001 | act | The Expendables III |
| New York | C002 | science fiction | Transformers IV |
| New York | C003 | sport | Need for Speed |
| New York | C004 | feature | Grace of Monaco |
需求 2：查询出缺货的 DVD 分店，即现存的 DVD 拷贝不到 4 类的分店，结果如下：
| BID | STREET | CITY |
| B002 | street2 | Houston |
| B003 | street3 | LA |
| B004 | street4 | Lincoln |
解法及简要说明
在集算器中编写脚本 p1.dfx，如下所示：
需求1：简单SQL
| A | |
| 1 | =connect("demo").cursor@x("SELECT * FROM DVDCOPY") |
| 2 | =connect("mysql").cursor@x("SELECT * FROM DVD") |
| 3 | =connect("oracle").cursor@x("SELECT * FROM BRANCH") |
| 4 | $select b.CITY as CITY,dc.COPYID as COPYID,d.CATEGORY as CATEGORY,d.TITLE as TITLE from {A1} dc join {A2} d on dc.DVDID=d.DVDID join {A3} b on dc.BID=b.BID where b.CITY='New York' |
简要说明：
A1 连接数据源名为 demo 的数据库（HSQL），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接
A2 连接数据源名为 mysql 的数据库（MySQL），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接
A3 连接数据源名为 oracle 的数据库（Oracle），返回根据 sql 创建的数据库游标，关闭游标时自动关闭数据库连接
A3 用简单 SQL 关联三表
需求2：SPL
| A | |
| 1 | =BRANCH=connect("oracle").query@x("SELECT * FROM BRANCH") |
| 2 | =DVD=connect("mysql").query@x("SELECT * FROM DVD") |
| 3 | =DVDCOPY=connect("demo").query@x("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).icount(CATEGORY):CatCount) |
| 8 | =A7.select(CatCount<4) |
| 9 | =A8.(BonList)|(BRANCH\A7.(BonList)) |
简要说明：
A1-A3：从数据库中检索数据，分别命名为变量 BRANCH、DVD、DVDCOPY。
A4：用 switch 函数，将 DVDCOPY 表中的 DVDID 字段切换成 DVD 表中对应的记录，将 BID 字段切换成 BRANCH 表中对应的记录。
A5：过滤数据：丢失的和未归还的 DVD 拷贝不在计算范围内
A6：A5 按 BID 分组，每组代表一个门店的所有 DVD 拷贝
A7：计算每个门店对应的 DVD 拷贝各有几类。函数 new 可以根据 A6 中的数据生成新的对象 A7，A7 有两个列：BonList 和 CatCount，BonList 直接来自 A6 中组内数据的 BID 列，CatCount 来自于组内数据的 DVDID 列。CatCount 的算法分为三部分：~.(DVDID) 找到每个门店所有的 DVD 拷贝对应的 DVD 记录；icount(CATEGORY) 用来计算 CATEGORY 去重后的个数。
A8：在 A7 中找出 CatCount 小于 4 的门店
A9：运算符“|”表示将两个数据集进行并集计算（可用 union 函数代替），运算符“\”表示差集计算（可用 diff 函数代替）。A8.(BonList)、BRANCH、A7.(BonList) 分别代表：DVDCOPY 表中缺货的门店、所有的门店、DVDCOPY 表中出现过的门店
JAVA 集成这段代码的方法可参考：《Java 如何调用 SPL 脚本》。
https://stackoverflow.com/questions/16991792/java-library-for-cross-database-retrieval
