將兩個表或者多個表關聯在一起是常見的運算，這時通常使用 SQL join 的方式進行關聯並進行後續計算。但有時資料並不儲存在資料庫，而是以檔案的形式儲存在檔案系統，單純為了計算而把資料儲存到資料庫有點得不償失。

Python 的 Pandas 提供了豐富的關聯運算函式，能更方便的完成文字檔案間的關聯計算，現在我們就一起來討論下 Python 的關聯處理。

基礎關聯

所謂關聯是指兩張資料表通過某個欄位或者某些欄位之間存在的某種關係，將兩張表以某種條件關聯起來。SQL 中的關聯可以是等值 JOIN 也可以是非等值 JOIN，所謂非等值 JOIN 就是 JOIN 時的過濾條件不一定是相等的，比如 select A.x,B.y from A join B on A.a<B.b。但在實際工作中這種運算是很少的，絕大多數情況都是等值 JOIN，而且即使遇到非等值 JOIN 的情況，多數情況下也可以轉換成等值 JOIN。Python 的 Pandas 中的關聯函式都是等值關聯的，在這裡我們也只討論等值 JOIN 的情況。

關聯的方式有內連線、左連線、右連線、全連線。Pandas 使用 merge() 函式的 how 引數完成，其中 inner：內連線，left：左連線，right：右連線，outer：全連線（預設 how=‘inner’）。下面以一些例子加以說明。

合同資料檔案和老客戶資料檔案分別儲存在兩份檔案中，合同資料檔案是 2019 年的合同資訊，其中的 Client 可能有新客戶，部分內容如下：

老客戶資料檔案是歷年來經常合作的老客戶的資訊，部分內容如下：

內連線（inner）

內連線，是兩表關聯後，保留兩表共有的鍵。

問題一：計算老客戶 2019 年的合同總金額

問題分析：由於合同資料中可能有新客戶，也可能有老客戶沒有在 2019 年簽訂合同，所以需要使用內連線的方式將兩表關聯。

Python 程式碼

討論：本例中關聯列在兩個 dataframe 中列名不同，不能使用 on 引數，而是使用 left_on，左 dataframe 的關聯列名，和 right_on，右 dataframe 的關聯列名，此時預設的引數 how=’inner’，對關聯結果的 Amount 列求和得到結果。

左連線（left）

左連線，指兩表關聯後，保留左側表的全部鍵，右側表不具備左側鍵的列為 nan。

問題二：計算出各省客戶的合同金額，新客戶的省份用 unknown 表示。

問題分析：因為不光要求各省老客戶的合同金額，還要把新客戶作為 unknown 來計算，因此關聯的時候需要使用 left 的方式。

Python 程式碼

討論：修改預設引數 how=‘inner’為‘left’，實現左連線，保留新客戶的合同資訊。後續再用‘unknown’填補缺失值，分組求和得到結果。

右連線和全連線的方式就是修改 how=‘right’或者 how=‘outer’。這裡就不再一一舉例了。

左排除連線（left_exluding）

左排除連線，指兩表關聯後，只保留左側鍵值未在右側表的鍵出現的行，右側表的列為 nan。

問題三：計算新客戶的合同總金額

問題分析，只求新客戶，只需要左連線後，排除掉兩表共有鍵的行就可以了。

Python 程式碼

討論：左連線時，增加引數 indicator，那些兩表共有的鍵的行為 both，左表獨有的鍵的行為 left_only，右表獨有的為 right_only。然後篩選出 left_only 標記的行就得到了新客戶的行，直接求和得到結果。

右排除連線和全排除連線和左排除連線類似，這裡也不舉例說明了。

Pandas 中的關聯函式除了 merge，還有 join 和 concat，我們繼續以問題一——計算老客戶 2019 年的合同總金額作為案例簡單介紹一下這兩個函式的使用方法。

join 函式

Python 程式碼

討論：join() 函式關聯時，只能使用兩表的索引，因此需要先將關聯列設定成索引。join 時預設的方式 how=‘left’，這裡需要修改為‘inner’。最後求和即可。

concat 函式

問題分析：concat()函式關聯時，要求關聯表的索引是唯一的，這裡合同資料的 Client 列有重複，不適合使用 concat() 函式關聯，不過為了更好的理解 concat 函式，我們對資料中的客戶去重，然後再用 concat 連線。

問題四：求老客戶 2019 年第一份合同的總金額。

Python 程式碼

討論：把兩個 dataframe 放入列表，axis=1 指按行索引進行關聯，預設的 join 方式是 outer，這裡改為 inner，最後進行求和。

多欄位關聯

多欄位關聯是指兩表關聯時的鍵不是單一欄位，需要同時關聯兩個或者更多欄位。

現有學生表，部分內容如下：

班級表，部分內容如下：

問題五：查詢學生的學號、姓名、專業、班級和班主任

問題分析，要查詢學生的學號、姓名、專業、班級和班主任，需將兩表按照專業號 majorid 和班級號 classid 進行關聯。

Python 程式碼：

討論：Pandas 支援多欄位關聯，只要把要關聯的欄位放入列表裡就可以了。

如果兩表的關聯欄位名不同，如學生表的專業號和班級號是 MAJORID 和 CLASSID。

Python 程式碼

討論：merge 函式中使用 left_on 和 right_on 引數即可。

同維關聯

表 A 的主鍵與表 B 的主鍵關聯，他們是一對一的關係，A 和 B 互稱為同維表。

（一）多同維表

期末考試成績表，平時成績表，選修課成績表部分內容如下：

期末考試成績表

平時成績表

選修課成績表

問題六：統計學生的學期成績（期末考試成績、平時成績、選修課成績所佔比例分別是 0.6,0.3,0.1）。

問題分析：

三張表都是以學生學號為主鍵，但有的學生可能沒有參加某一項的考試，因此可能會有學生缺少某一項成績，在關聯的時候需要使用外關聯的方式。

關聯關係如下圖：

Python 程式碼：

討論：因為三類成績的主鍵都是學生學號，因此將學號設為索引，然後使用 concat 函式同時關聯三張表，使得關聯更加快捷。

（二）多層主子表

表 A 的主鍵與表 B 的部分主鍵關聯，A 稱為主表，B 稱為子表。主子表是多對一的關聯關係。

門店資訊，訂單表，訂單明細表部分內容如下：

門店資訊表（主表）：

訂單表（門店資訊表的子表，訂單明細表的主表）：

訂單明細表（訂單資訊表的子表）

問題七：檢視各門店各省客戶的消費情況和各省門店的銷售情況。

問題分析：1. 訂單表和訂單明細表關聯，其中訂單表是主表，訂單明細表是子表，關聯欄位是 [storeid,ordered]，關聯後分組彙總即得結果。 2. 門店資訊和第 1 步的彙總結果關聯，門店資訊表是主表，第 1 步的彙總結果是子表，關聯欄位是 storeid，關聯後分組彙總即得結果。

關聯關係如下圖：

Python 程式碼：

討論：主子表關聯時要一步一步理清關聯關係，找準關聯欄位和需彙總的欄位，必要的時候畫出關聯關係圖。本例實際上是兩次主子表關聯，每一步關聯也可以按照分組彙總——同維關聯——分組彙總的方式完成。具體程式碼如下：

（三）多子表查詢

訂單表，訂單明細表，回款表部分內容如下：

訂單表（主表）

訂單明細表（子表）

回款表（子表）

問題八：找出未完全回款的訂單

問題分析：訂單表是主表，訂單明細表和回款表都是子表，關聯的欄位是 ordered。注意這裡不可以先用主表分別與兩個子表關聯，再用兩個關聯結果關聯，因為這樣做會發生多對多的關聯，使得計算錯誤。正確的做法應該是先對兩個子表彙總，然後再分別與主表關聯，得到最終的結果。

關聯關係如下：

Python 程式碼：

討論：兩子表彙總結果與訂單表的索引相同，此時可以使用 join 函式或者 concat 函式同時關聯多表，最後篩選即可得到結果。

外來鍵關聯

表 A 的某些欄位與表 B 的主鍵關聯，表 A 的關聯欄位可以不唯一，表 B 的關聯欄位唯一，這就是多對一關聯，也稱作外來鍵關聯，即表 A 是事實表，表 B 是維表，A 表中與 B 表主鍵關聯的欄位稱為 A 指向 B 的外來鍵，B 也稱為 A 的外來鍵表。下面介紹幾種常見的外來鍵關聯例項：

（一）一個事實表，多個不同維表。

銷售記錄表、城市資訊表、產品資訊表部分內容如下：

銷售記錄表 (事實表)：

城市資訊表（維表 1）：

產品資訊表（維表 2）：

問題九：彙總各個省份各類產品的銷售額。

問題分析：計算各省的各類產品的銷售額，需要用銷售記錄表的 sale_city 和城市資訊表的 cityid 關聯獲取省份的資訊，用 product 和產品資訊表的 productid 關聯獲取產品類別資訊。

關聯關係如下圖：

Python 程式碼

討論：銷售記錄表作為事實表，分別與兩個維表關聯，得到一張寬表，最後對寬表分組彙總即可。

（二）一個事實表，多個維表有維表被多次使用。

銷售記錄表，城市資訊表部分內容如下：

銷售記錄表（事實表）：

城市資訊表（維表）：

問題十：找出產品產地與銷售地在同一省份的銷售記錄。

問題分析：產品產地與銷售地都要關聯城市資訊，從而找到兩者在同一省的銷售記錄，城市資訊表被關聯了兩次。

關聯關係如下：

Python 程式碼：

討論：第二次關聯城市資訊時，recitys 的表中已經有了城市的資訊，因此需要設定相同欄位名的字尾，這裡把銷售地的城市資訊加字尾“_s”，產地資訊字尾“_p”，最後過濾出兩者省份相同的記錄即可。

（三）多層維表

銷售記錄表、城市資訊表、產品資訊表部分內容如下：

銷售記錄表 (事實表)：

城市資訊表（維表 1）：

產品資訊表（維表 2）：

問題十一：找出產品產地與銷售地在同一省份的銷售記錄。

問題分析：為獲取產品資訊，需要用銷售記錄關聯產品資訊。關聯產品資訊後就和例 2 類似了，分別用產地和銷售地關聯城市資訊，過濾後就可得到產品產地與銷售地在同一省份的銷售記錄。

關聯關係如下：

Python 程式碼

討論：三次關聯，1. 銷售記錄與產品；2. 銷售地與城市；3. 產地與城市。最後對三次關聯後的結果過濾即可。

（四）自關聯

員工資訊表部分內容如下：

問題十二：列出所有員工姓名及其直接上級的姓名。

問題分析：要獲取上級的名字須用上級的工號 superior 與員工表的工號 empid 關聯。

關聯關係如下：

Python 程式碼

討論：自己既是事實表又是維表，關聯的時候設定字尾即可。

（五）環狀關聯。

員工資訊表，部門資訊表部分內容如下：

員工資訊表：

部門資訊表：

問題十三：找出北京經理的北京員工

問題分析：首先要關聯部門資訊表，找到經理欄位；然後用經理欄位關聯員工編號；最後篩選出北京經理的北京員工。

關聯關係如圖：

Python 程式碼：

討論：第一步關聯時，員工資訊表是事實表，部門資訊表是維表；第二步關聯時，關聯的結果是事實表，員工資訊表是維表，這就形成了環狀關聯。由於員工資訊表和部門表都有 name 欄位，所以第一步關聯時，預設為員工資訊表的 name 欄位增加了字尾“_x”。

9. 多表混合關聯

現有以下幾張表：訂單表，訂單明細表，產品資訊表，員工資訊表，出差資訊表，客戶資訊表，城市資訊表，他們的表結構與關聯關係如下：

問題十四：計算出差時間大於 10 天的 90 後銷售員在各省份銷售黑龍江商品的金額。

問題分析：

多張表關聯，要理清下述三個問題：

(1) 關聯關係，多對一、一對一、一對多（當發生多對多關聯時，多半是錯了）。

(2) 關聯方式，內連線、左連線、右連線、全連線。

(3) 關聯與處理的順序，先處理（包括過濾和分組）還是先關聯。

Python 程式碼：

討論：在日常工作中，經常會遇到這種混合的關聯，多種關聯關係混在一起，理不清其中的關係，會事倍功半甚至得到錯誤的結果。

小結

Python 中的關聯是將兩表根據某個或者某些欄位連線在一起，組成一張寬表，這和 SQL 類似。從上述例子中也可以看出，Python 在解決基礎關聯和同維關聯（問題一至問題八）時，還是比較方便的，一步一步計算下來，思路清晰，程式碼也好理解。

但是，對於外來鍵關聯，Python 的處理方法有這樣一些問題：

1.Python 的 merge 函式一次只能解析一個關聯關係，在關聯關係較多時比較麻煩。

2. 每次解析關聯後得到的是一個新表，資料會被複制，耗時且耗記憶體；這個問題同維表關聯時也存在。

3. 當發生自關聯（迴圈關聯）時，其本質還是兩表關聯得到新表，不可以重複利用已經建立好的關聯關係。

相比之下，esProc SPL 在處理外來鍵關聯時要聰明一些。SPL 會建立外來鍵與主鍵物件的關聯，並沒有複製資料本身，這樣就可以同時建立多個關聯關係，計算快捷且節省記憶體，而且發生自關聯（迴圈關聯）時，也可以重複利用建好的關聯關係，簡潔而高效。

比如問題九至十四，SPL 程式碼寫出來是這樣的：

問題SPL程式碼簡單說明

當然，SPL 處理同維關聯也一樣簡單，比如問題六至八：

Python 還有一個比較嚴重的缺點，那就是當資料量大到無法一次性載入記憶體時，使用 Python 進行關聯運算將是災難性的，幾乎所有的關聯方式都需要自己手動來實現，涉及到外存排序，hash 分段等等複雜程式碼，非高階程式設計師幾乎不可能完成。這些程式碼實在過於繁瑣，這裡也就沒有再給出來。對於大檔案的場景，Python 不再合適，還是得用 SPL，它提供有遊標物件，可以輕鬆解決記憶體裝不下的檔案之間的關聯及其它運算。

比如問題一計算 2019 年老客戶的合同總金額。當合同資料很大，無法一次性載入記憶體時，而客戶資料可以放入記憶體時，可以為合同表建立遊標來計算，SPL 程式碼只有區區幾行：

兩個表都特別大時，SPL 還提供了 joinx() 函式，對有序遊標進行關聯，可以非常高效的完成大資料的關聯任務。

資料包（http://img.raqsoft.com.cn/file/2020/11/f6075d73b8b949ae8c75c7c005af08e6_Python.rar）