1. 簡單合并（FROM）

所謂跨庫數(shù)據(jù)表，是指邏輯上同一張數(shù)據(jù)表被分別存儲在不同數(shù)據(jù)庫中。其原因有可能是因為數(shù)據(jù)量太大，放在一個數(shù)據(jù)庫難以處理，也可能在業(yè)務上就需要將生產(chǎn)庫和歷史庫分開。而不同的數(shù)據(jù)庫，可能只是部署在不同的機器上的同種數(shù)據(jù)庫，也可能是連類型都不同的數(shù)據(jù)庫系統(tǒng)。

在面對跨庫數(shù)據(jù)表，特別是數(shù)據(jù)庫類型都不相同的情況時，數(shù)據(jù)庫自帶的工具往往就力所不及了，一般都需要尋找能夠很好地支持多數(shù)據(jù)源類型的第三方工具，而集算器，可以說是其中的佼佼者了。下面，我們就針對幾種常見的跨庫混合運算情況詳細討論一下：

跨庫運算，簡單粗暴的思路就是把散布在各個庫里的邏輯上相同的數(shù)據(jù)表合并成一個表，然后在這一個表上進行運算。

例如，在兩個數(shù)據(jù)庫 HSQL 和 MYSQL 中，分別存儲了一張學生成績表，兩者各自保存了一部分學生信息，如下圖所示：

利用集算器，我們可以很容易地將這兩個結(jié)構相同的表合并為一個表，集算器的 SPL 腳本如下：

A1、A2 和 B1、B2 分別讀取了兩個庫里的學生成績表，而 A3 用一種簡單直觀的方式就把兩個表合并了。

這種方式實際上是把兩個表都讀入了內(nèi)存，分別生成了集算器的序表對象，然后利用序表的運算“|”完成了合并。可能有的同學會問：如果我的數(shù)據(jù)量比較大，無法全部讀入內(nèi)存怎么辦？沒關系，專為處理大數(shù)據(jù)而生的集算器，決不會被這么簡單的小問題難住。我們可以使用游標，同樣可以實現(xiàn)表的快速拼接：

A2、B2 分別用游標打開兩個庫里的學生成績表，A3 則使用 conjx() 函數(shù)將這兩個游標合并，形成了一個新的可以同時訪問兩個表的游標。

對應于 SQL，這種簡單合并好比只是完成了 from 工作，讓結(jié)構相同的跨庫表的數(shù)據(jù)“縱向”拼接成了一個可以訪問的序表或者游標，而實際運算中，還會涉及過濾 (where/having)、分組聚合 (group+sum/count/avg/max/min)、連接 (join+on)、去重 (distinct)、排序 (order)、取部分數(shù)據(jù) (limit+offset)，等等操作，下面我們就將對這些運算一一展開討論。

當然，我們在處理這些運算的需求時，不能只是簡單的實現(xiàn)功能，我們還需要考慮實現(xiàn)的效率和性能，因此原則上，我們會盡量利用數(shù)據(jù)庫的計算能力，而集算器主要負責混合運算。不過，有時也需要由集算器負責幾乎所有的運算，數(shù)據(jù)庫僅僅負責存儲數(shù)據(jù)。

2. WHERE

where 過濾的本質(zhì)是通過比較計算，去除比較的結(jié)果是 false 的記錄，因此 where 只作用于一條記錄，不涉及記錄之間的運算，也不需要考慮數(shù)據(jù)位于哪個數(shù)據(jù)庫。比如，在前面的例子中，我們要統(tǒng)計出“一班”所有同學的“數(shù)學”成績，單庫中的 SQL 是這樣的：

SELECT 學生 ID, 成績 FROM 學生成績表 WHERE 科目 ='數(shù)學' AND 班級 =‘一班'

多庫時，也只要將 where 子句直接寫在 SQL 中，讓各個數(shù)據(jù)庫去并行處理過濾就可以了：

我們也可以讓集算器負責所有過濾運算，數(shù)據(jù)庫僅存儲數(shù)據(jù)。這時可以使用集算器的 select 函數(shù)（與 SQL 的 select 關鍵字不同）

數(shù)據(jù)量較大時，同樣也可以將序表換成游標，使用 conjx 函數(shù)進行連接：

3. ORDER BY 和 LIMIT OFFSET

order by 是在結(jié)果集產(chǎn)生后才進行的處理。在上面的例子中，如果我們要按數(shù)學成績排序，對于單數(shù)據(jù)庫，只需要加上 order by 子句：

SELECT 班級, 學生 ID, 成績 FROM 學生成績表 WHERE 科目 ='數(shù)學' AND 班級 =‘一班' ORDER BY 成績

而對于多數(shù)據(jù)庫，可以讓數(shù)據(jù)庫先分別排序，然后由集算器歸并有序數(shù)據(jù)。這樣可以最大的發(fā)揮數(shù)據(jù)庫與并行服務器的性能。

也可以倒序排序，歸并時在排序字段前加“-”（merge 函數(shù)可以不加“-”，不過按標準寫法是加上的）

當然也可以完全由集算器來排序：

由集算器實現(xiàn)倒序排序：

而對于大數(shù)據(jù)量，需要使用游標及 mergex 來完成有序歸并：

limit 和 offset 的執(zhí)行又在 order 之后，例子中如果想取數(shù)學成績除了第一名之后的前十名（可以少于但不能多于），單庫情況下 SQL 是這樣的：

SELECT 班級, 學生 ID, 成績 FROM 學生成績表 WHERE 科目 ='數(shù)學' AND 班級 =‘一班' ORDER BY 成績 DESC LIMIT 10 OFFSET 1

多數(shù)據(jù)庫時，可以用集算器的 to 函數(shù)實現(xiàn) limit offset 的功能，to(n+1,n+m) 等同于 limit m offset n

對于大數(shù)據(jù)量使用游標的情況，offset 功能可以使用集算器函數(shù) skip 實現(xiàn)，而 limit 的功能則可以使用函數(shù) fetch 實現(xiàn)

4. 聚合運算

我們來討論五種常見的聚合運算：sum/count/avg/max/min。

• sum 的基礎是加法，根據(jù)加法結(jié)合律，各數(shù)據(jù)庫中內(nèi)部數(shù)據(jù)先分別求和，然后拼接成一張表后再求總和，與先拼接成一張表然后一起求和的結(jié)果，其實是一樣的。

• count 的本質(zhì)，是對每項非 null 數(shù)據(jù)計 1，null 數(shù)據(jù)計 0，然后進行累加計算。所以其本質(zhì)仍是加法運算，與 sum 一樣符合加法結(jié)合律。唯一不同的是對原始數(shù)據(jù)不是累加其本身的數(shù)值而是計 1（非 null）或計 0（為 null）。

• avg 的本質(zhì)，是當 count > 0 時 avg = sum/count，當 count = 0 時 avg = null。顯然 avg 不能像 sum 或 count 那樣先分別計算了。不過根據(jù)定義，我們可以先算出 sum 和 count，再通過 sum 和 count 計算出 avg。

• max 和 min 的基礎都是比較運算，而因為比較運算具有傳遞性，因此所有數(shù)據(jù)庫的最值，可以通過比較各個數(shù)據(jù)庫的最值得到。

依舊是上面的例子，這次我們要求兩個班全體學生的數(shù)學總分、人數(shù)、平均分、最高及最低分，對于單源數(shù)據(jù)：

SELECT sum(成績) 總分數(shù), count(成績) 考試人數(shù), avg(成績) 平均分, max(成績) 最高分, min(成績) 最低分 FROM 學生成績表 WHERE 科目 ='數(shù)學'

聚合運算的結(jié)果集很小，只有一行，因此無論源數(shù)據(jù)量的大小，都可以使用游標，代碼如下：

事實上，前面提到的 order by +limit offset 本質(zhì)上也可以看成是一種聚合運算：top。從這個角度進行優(yōu)化，可以獲得更高的計算效率。畢竟數(shù)據(jù)量大時，全排序的成本很高，而且取前 N 個數(shù)據(jù)的操作也并不需要全排序。當然，這個方法對于數(shù)據(jù)量小的情況也同樣適用。

具體來說，對于 order by F limit m offset n 的情況，只需先用 top(n+m, F, ~)，再用 to(n+1,) 就行了。

我們?nèi)砸灾暗暮?order by+limit offset 的 SQL 語句為例：

SELECT 班級, 學生 ID, 成績 FROM 學生成績表 WHERE 科目 ='數(shù)學' AND 班級 =‘一班' ORDER BY 成績 DESC LIMIT 10 OFFSET 1

對于多數(shù)據(jù)庫, 腳本如下，其中倒序排序只需在排序字段前加“-”：

5. GROUP BY、DISTINCT 和 HAVING

A、分組聚合運算

對于 group by，因為最終所得結(jié)果與樣本個體的輸入順序無關，所以只要樣本的總體不變，最終結(jié)果也不會變。也就是說，只要在從分庫中提取數(shù)據(jù)和最終匯總?cè)繑?shù)據(jù)時，都預先進行了分類運算即可。

假設我們想分別求一、二班的數(shù)學總分、人數(shù)、平均分、最高及最低分，單數(shù)據(jù)庫如下：

SELECT 班級, sum(成績) 總分數(shù), count(成績) 考試人數(shù), avg(成績) 平均分, max(成績) 最高分, min(成績) 最低分 FROM 學生成績表 WHERE 科目 ='數(shù)學' GROUP BY 班級

我們分三種情況討論：

第一，對于小數(shù)據(jù)，聚合運算的結(jié)果集只會更小，這時推薦使用 query+groups：

第二，對于大數(shù)據(jù)量，如果結(jié)果集也很大，那么就應該使用 cursor+groupx。

另外，由于大結(jié)果集的分組計算較慢，需要在外存產(chǎn)生緩存數(shù)據(jù)。而如果我們在數(shù)據(jù)庫中對數(shù)據(jù)先排序，則可以避免這種緩存（此時計算壓力會轉(zhuǎn)到數(shù)據(jù)庫，因此需要根據(jù)實際情況權衡，通常情況下，數(shù)據(jù)庫服務器的計算能力會更強一些）。

具體的辦法是對 SQL 的結(jié)果集使用 order by 排序，然后在集算器中使用 mergex 函數(shù)歸并后，再使用 groupx 的 @o 選項分組：

當然如果不希望加重數(shù)據(jù)庫負擔，也可以讓數(shù)據(jù)庫只做分組而不排序，此時集算器直接用 groupx，注意不能加 @o 選項。另外匯總數(shù)據(jù)時，也要把 mergex 換成 conjx：

第三，如果已明確地知道結(jié)果集很小，那么推薦用 cursor+groups

此時 groups 比 groupx 有更好的性能，因為 groups 將運算數(shù)據(jù)都保存在內(nèi)存中，比 groupx 節(jié)省了寫入外存文件的時間。

另外用 groups 可以不要求在數(shù)據(jù)庫中預先排序，因為數(shù)據(jù)庫 group by 的結(jié)果集本身不一定有序，再使用 orde by 排序也會增加成本。而對于小結(jié)果集，集算器用 groups@o 也并不一定比直接用 groups 更有效率。

通常，匯總數(shù)據(jù)要用 conjx

B、去重后計數(shù) (count distinct)

在各個數(shù)據(jù)庫內(nèi)去重，可以使用 distinct 關鍵字。而數(shù)據(jù)庫之間的數(shù)據(jù)去重，則可以使用集算器的 merge@u 函數(shù)。要注意的是使用前應該確保表內(nèi)數(shù)據(jù)對主鍵字段（或者具有唯一性的一個或多個字段）有序。

對于 distinct 來說， sum(distinct)、avg(distinct) 的計算方法與 count(distinct) 大同小異，而且業(yè)務中不常用到，而 max(distinct)、min(distinct) 與單純使用 max、min 沒有區(qū)別。因此，我們只以 count(distinct) 為例加以說明。

比如，想要計算全年級（假設只有一班和二班）語數(shù)外三科至少有一科不及格需要補考的總?cè)藬?shù)，單數(shù)據(jù)庫的 SQL 是這樣的：

SELECT count(distinct 學生 ID) 人數(shù) FROM 學生成績表 WHERE 成績 60

對于多源數(shù)據(jù)，全分組聚合在使用游標或序表方面沒有差別，為了語法簡便起見以游標為例：

再如，想要分別計算每班語數(shù)外三科至少有一科不及格需要補考的總?cè)藬?shù)，單數(shù)據(jù)庫的 SQL 是這樣的：

SELECT 班級, count(distinct 學生 ID) 人數(shù) FROM 學生成績表 WHERE 成績 60 GROUP BY 班級

對于多數(shù)據(jù)庫，同樣需要先匯總?cè)ブ?，再進行分組聚合。匯總前需要數(shù)據(jù)有序，且匯總后數(shù)據(jù)仍然有序，所以分組函數(shù) groups 和 groupx 都可以使用 @o 選項。

對于小數(shù)據(jù)量，可以使用 merge@u、groups@o 和 query：

對于大數(shù)據(jù)量小結(jié)果集，可以使用 mergex@u、groups@o 和 cursor：

對于大數(shù)據(jù)量大結(jié)果集，可以使用 mergex@u、groupx@o 和 cursor：

C、對聚合字段過濾（having）

having 是對聚合 (分組) 后得出的結(jié)果集再做過濾。所以當語句中有 having 出現(xiàn)時，如果聚合 (分組) 操作沒有徹底執(zhí)行完畢，需要將 having 子句先提取出來。待數(shù)據(jù)徹底完成聚合 (分組) 操作之后，再執(zhí)行條件過濾。

對于多源數(shù)據(jù)，如果聚合計算是在匯總之后才能最終完成，那么 having 必須使用集算器的函數(shù) select 來實現(xiàn)過濾。

下面主要說明這種聚合計算在匯總之后才完成的情況：比如，想要獲得一班和二班的三個科目的考試中，有哪些平均分是低于 60 分的。對于單數(shù)據(jù)庫，SQL 可以這樣寫：

SELECT 班級, 科目, avg(成績) 平均分 FROM 學生成績表 GROUP BY 班級, 科目 HAVING avg(成績)60

對于多數(shù)據(jù)庫，相關集算器執(zhí)行代碼如下：

對于大數(shù)據(jù)量，需要使用游標 (select 函數(shù)同樣適用于游標)

6. JOIN ON

跨庫的 JOIN 實現(xiàn)起來非常困難，不過比較幸運的是，我們可以通過存儲設計避免很多跨庫 JOIN。我們分三種情況討論：

1. 同維表分庫，需要重新拼接為一個表

2. 要連接的外鍵表在每個庫中都有相同的一份

3. 需要連接的外鍵表在另一個庫中

對于集算器來講，前兩種的處理情況是一樣的：都不需要涉及跨庫 join，join 操作都可以在數(shù)據(jù)庫內(nèi)完成。區(qū)別只在于第一種是分庫表，數(shù)據(jù)庫之間沒有重復數(shù)據(jù)；而第二種則要求把外鍵表的數(shù)據(jù)復制到每個庫中。

如果外鍵表沒有復制到每個庫中，那就會涉及真正的跨庫 join，因為很復雜，這里只舉一個內(nèi)存外鍵表的例子，其它更復雜情況會有專門的文章闡述。

A、同維表或主子表同步分庫

所謂同維表，簡單來講就是兩個表的主鍵字段完全一樣，且其中一個表的主鍵與另一個表的主鍵有邏輯意義上的外鍵約束（并不要求數(shù)據(jù)庫中一定有真正的外鍵，主鍵同理也是邏輯上的主鍵并不一定存在于數(shù)據(jù)庫中）。

假設有兩個庫，每個庫中有兩個表，分別記為 A 庫中的 A1 表和 A2 表，B 庫中的 B1 表和 B2 表。從邏輯上看 1 表是 A1 表加上 B1 表，2 表是 A2 表加上 B2 表，我們再假設 1 表與 2 表為同維表，現(xiàn)在要做 1 表與 2 表的 join 連接運算。

所謂同步分庫，就是在設計分庫存儲時，保證了 1 表和 2 表按主鍵進行了同步的分割。也就是必須保證分庫之后，A1 和 B2 的 join 等值連接的結(jié)果是空集，同樣 A2 和 B1 的 join 等值連接的結(jié)果也是空集，這樣也就不必有跨庫的 join 連接運算了。

舉例說明，比如有兩張表：股票信息與公司信息，表的結(jié)構如下：

公司信息

股票信息

兩個表的主鍵都是 (公司代碼, 股票代碼)，且股票信息的主鍵與公司信息的主鍵有邏輯意義上的外鍵約束關系，二者互為同維表。

現(xiàn)在假設我想將兩個表拼接在一起，單數(shù)據(jù)庫時 SQL 是這樣的：

SELECT * FROM 公司信息 T1 JOIN 股票信息 T2 ON T1. 公司代碼 =T2. 公司代碼 AND T1. 股票代碼 = T2. 股票代碼

現(xiàn)假設公司信息分為兩部分，分別存于 HSQL 和 MYSQL 數(shù)據(jù)庫中，股票信息同樣分為兩部分，分別存于 HSQL 和 MYSQL 數(shù)據(jù)庫中，且二者是同步分庫。

join 連接公司信息與股票信息的集算器代碼：

對于大數(shù)據(jù)：

主子表的情況與同維表類似，即一個表（主表）的主鍵字段被另一個表（子表）的主鍵字段所包含，且子表中對應的主鍵字段與主表的主鍵有邏輯意義上的外鍵約束關系。

舉例說明，比如有兩張表：訂單與訂單明細，表的結(jié)構如下：

訂單

訂單明細

其中訂單是主表，主鍵為 (訂單 ID)；而訂單明細為子表，主鍵為 (訂單 ID, 產(chǎn)品 ID)，且訂單明細的主鍵字段訂單 ID，與訂單的主鍵有邏輯意義上的外鍵約束關系，顯然二者為主子表的關系。

現(xiàn)在假設我想將兩個表拼接在一起，單數(shù)據(jù)庫的 SQL 是這樣的：

SELECT * FROM 訂單 T1 JOIN 訂單明細 T2 ON T1. 訂單 ID=T2. 訂單 ID

現(xiàn)假設訂單分為兩部分，分別存于 HSQL 和 MYSQL 數(shù)據(jù)庫中，訂單明細同樣分為兩部分，分別存于 HSQL 和 MYSQL 數(shù)據(jù)庫中，且二者同步分庫。

join 連接訂單與訂單明細的集算器代碼：

對于大數(shù)據(jù)：

B、外鍵表復制進每個庫

所謂外鍵表，即是指連接字段為外鍵字段的情況。這種外鍵表 join 也是業(yè)務上常見的一種情況。因為要連接的外鍵表在每個庫中都有同一份，那么兩個外鍵表匯總并去重后，其實還是任一數(shù)據(jù)庫中原來就有的那個外鍵表。

而 join 的連接操作，本質(zhì)上可以視為一種乘法，因為 join 連接等價于 cross join 后再用 on 中條件進行過濾。則根據(jù)乘法分配率可以推導出：若是需要做連接操作的外鍵表（不妨設為連接右側(cè)的表）在每個庫中都有同一份，則連接左側(cè)的表（每個數(shù)據(jù)庫中各有其一部分）在匯總后再連接，等同于各數(shù)據(jù)中的連接左側(cè)的表與外鍵表先做連接操作后，再匯總到一起的結(jié)果。如圖所示：

所以我們在存儲設計時，只要在每個數(shù)據(jù)庫中把外鍵表都重復一下，就可以避免復雜的跨庫 join 操作。一般情況下，外鍵表作為維表的數(shù)據(jù)量相對較小，這樣重復的成本就不會很高，而事實表則會得很大，然后用分庫存儲的方法，來解決運算速度緩慢或存儲空間不足等問題。

例如，有兩個表：客戶銷售表和客戶表，其中客戶銷售表的外鍵字段：客戶，與客戶表的主鍵字段：客戶 ID，有外鍵約束關系。現(xiàn)在我們想查詢面向河北省各公司的銷售額記錄，對于單數(shù)據(jù)源，它的 SQL 是這樣寫的：

SELECT T1. 公司名稱 公司名稱, T2. 訂購日期 訂購日期, T2. 銷售額 銷售額 FROM 客戶表 T1 JOIN 客戶銷售表 T2 ON T1. 客戶 ID=T2. 客戶 WHERE T1. 省份 ='河北'

對于多數(shù)據(jù)源的情況，我們假設客戶銷售表分別存儲在兩個不同的數(shù)據(jù)庫中，而每個數(shù)據(jù)庫中都有同一份的客戶表做為外鍵表。則相關的集算器代碼如下：

大數(shù)據(jù)量使用游標時：

C、需要連接的外鍵表在另一個庫中

對于維表（外鍵表）也被分庫的情況，我們只考慮維表全部可內(nèi)存化的情況，不可內(nèi)存化時，常常就不適合再將數(shù)據(jù)存在數(shù)據(jù)庫中了，需要專門針對性的的存儲和計算方案，這將在另外的文章中專門討論。在這里我們只通過例子來討論維表可內(nèi)存化的情況。

對于這種情況，當涉及的數(shù)據(jù)量比較大而需要使用游標時，計算邏輯會變得比較復雜。所以我們在這里只講一下針對小數(shù)據(jù)量的使用序表的 join 處理方法。關于對大數(shù)據(jù)量的使用游標的 join 處理，會另有一篇文章做專門的介紹。

當要做 join 連接運算的外鍵表全部或部分存儲在另一個庫中時，最直觀的辦法就是將兩個表都提取出來并各自匯總后，再計算 join 連接。

下面仍以客戶銷售表和客戶表來舉例，假設外鍵表客戶表也分別存儲在兩個數(shù)據(jù)庫中，此時就不能在 SQL 中使用 join 關鍵字來實現(xiàn)連接運算了，但我們可以將其提取出來后，用集算器的 join 函數(shù)來實現(xiàn)目的，它的集算器代碼如下所示：

當事實表數(shù)據(jù)量較大的時候，也可以使用游標處理事實表，只需將 join 換成 cs.join 即可：

7. 簡單 SQL

前面我們主要是從計算原理的角度出發(fā)，分析了如何使用集算器實現(xiàn)類似 SQL 效果的多數(shù)據(jù)源混合計算。除此之外，集算器還提供了一種更簡單、直觀的方法，那就是可以在各個數(shù)據(jù)庫上通過 SQL 查詢獲取游標，用所有這些游標構建成一個多路游標對象，再用簡單 SQL 對這個多路游標做二次處理。如果簡單 SQL 中沒有涉及 join 的運算，甚至還可以讓集算器直接將一句簡單 SQL 翻譯成各種數(shù)據(jù)庫的 SQL，從而實現(xiàn)更進一步的自動化。不過這種辦法屬于比較保守的做法，雖然簡單直接，但不能利用所了解的數(shù)據(jù)情況進行優(yōu)化（比如不會使用 groups），因此性能就會差一些。

下面仍舊用學生成績的例子，我們想要計算每個班的數(shù)學成績的總分、考試人數(shù)、平均分、最高分和最低分，使用簡單 SQL 處理這個問題的集算器代碼如下：

因為使用了游標，所以這種寫法也可以用于大數(shù)據(jù)量。另外再提一句，這個辦法甚至也可以用于非數(shù)據(jù)庫的數(shù)據(jù)源（比如文件數(shù)據(jù)源）！簡單 SQL 的特性可參考相關文檔，這里就不再進一步舉例了。

總結(jié)

以上所述是小編給大家介紹的詳解數(shù)據(jù)庫中跨庫數(shù)據(jù)表的運算 ，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！