LEFT JOIN 需要注意的點（Presto）

Lorry_Lu發表於2018-02-03
REST

最近工在Presto中用了很多關聯查詢操作，遇到了一些問題再這裡記錄一下。

LEFT JOIN的基本概念

LEFT JOIN是我們最常用的關聯查詢，對於之前很少直接接觸複雜關聯查詢的兄弟，我們還是需要弄清楚一些基本概念。

  • LEFT JOIN是邏輯操作符，對於放在左邊的表來說，是以其為base，如果SELECT查詢的欄位全都來自左邊的表，那麼最終的結果條數會 >= 左表條數
  • 資料庫優化器是最終決定執行順序的地方，一般的會按照你的LEFT JOIN的順序執行，但也不保證完全是那樣。

怎麼執行呢？

例如：

SELECT table_1.a, table_1.b, table_1.c
FROM table_1 
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid
    LEFT JOIN talbe_3
    ON table_1.uid = table_3.uid

執行順序是：table_1和table_2先組合成一個虛擬表，然後這個虛擬表再和table_3關聯。

多個LEFT JOIN連線，記錄的條數是不是主表的條數？

如上面的語句，在沒有WHERE語句情況下，是大於等於table_1的條數。
這是因為：

- SELECT 後面的欄位均來自table_1;
- 所有的關聯條件都是為了匹配table_1;

LEFT JOIN後資料量增加的問題

但是有一種情況，最終結果可能是大於table_1的情況的，那就是在table_2或者table_3中有重複的uid，並且正好符合關聯條件的時候，結果表就會被撐大。

如下面的情況：

這裡先考慮只有table_2的情況。
table_1中有如下資料：

uid a b c
1 100 101 102
2 200 201 202
3 300 301 302

如果table_2是以下形式：

uid a b
1 10 10
1 10 10
2 20 10
2 20 10

如果沒有去重，最終的結果就是：

a b c
100 101 102
100 101 102
200 201 202
200 201 202
300 301 302

為了看得更清楚一些，我們改寫一下SQL語句：

SELECT 
    table_1.a t1_a, table_1.b t1_b, 
    table_1.c t1_c, table_1.uid t1_uid,
    table_2.uid t2_uid, table_2.a t2_a,
    table_2.b t2_b
FROM table_1
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid;
t1_a t1_b t1_c t1_uid t2_uid t2_a t2_b
100 101 102 1 1 10 10
100 101 102 1 1 10 10
200 201 202 2 2 20 10
200 201 202 2 2 20 10
300 301 302 3 NULL NULL NULL

我們可以清楚的看到，uid=1, uid=2被分別匹配了兩次。

怎樣解決？

我們可以考慮想將table_2去重。

我們可以先利用一個子查詢去掉重複的記錄，然後再與table_1進行聯合。這裡使用了Presto的語法

WITH table_2 AS (
    SELECT distinct uid, a, b 
    FROM table_2
)
SELECT 
    table_1.a t1_a, table_1.b t1_b, 
    table_1.c t1_c, table_1.uid t1_uid,
    table_2.uid t2_uid, table_2.a t2_a,
    table_2.b t2_b
FROM table_1
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid;

結果為：

t1_a t1_b t1_c t1_uid t2_uid t2_a t2_b
100 101 102 1 1 10 10
200 201 202 2 2 20 10
300 301 302 3 NULL NULL NULL

這樣就避免了被關聯表中的重複記錄影響最終結果。

上面的情況在處理大資料量的表時，最容易造成“Insufficient Resources”的error，導致Presto罷工。所以一定要小心。

LEFT JOIN後得到的資料許多NULL資料

如果被關聯的表中沒有能匹配關聯條件，這會讓資料庫用NULL去填充結果。
如果你的查詢結果是兩個表欄位共同決定的，要資訊處理這個問題。
例如(table_1和table_2還是用上面的資料)：

WITH table_2 AS (
    SELECT distinct uid, a, b 
    FROM table_2
)
SELECT 
    table_2.a a, table_2.b b, table_1.c c
FROM table_1
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid;

得到的結果就是：

a b c
10 10 102
20 10 202
NULL NULL 302

所以，要加上if語句進行判斷。

WITH table_2 AS (
    SELECT distinct uid, a, b 
    FROM table_2
)
SELECT 
    if(table_2.a IS NOT NULL, table_2.a, table_1.a) a, 
    if(table_2.b IS NOT NULL, table_2.b, table_1.b) b, 
    table_1.c c
FROM table_1
    LEFT JOIN table_2
    ON table_1.uid = table_2.uid;

得到的結果：

a b c
10 10 102
20 10 202
300 301 302

JOIN的左邊儘量放小資料量的表

這樣可以提高查詢效率，優化查詢速度。

不要JOIN（LEFT RIGHT FULL等等）起來沒完

要知道，每次JOIN關聯，資料庫都會先將兩邊的資料進行全量組合（也就是笛卡爾積的形式），然後再進行條件篩選，會造成伺服器資源緊張。
所以，我們需要將許多的JOIN用with語句分割成多個子查詢，然後再一步一步關聯。

但是也要注意，在做ETL清洗時，一般需要將一個表的全量欄位經過清洗步驟，然後插入到另一個目標表中。這時，子查詢很多的情況，也會使得伺服器的記憶體緊張，必要的時候我們可以採用臨時表的方案，用臨時表儲存一些中間結果，最後再綜合中間結果完成整個操作。

相關文章