這篇文章主要介紹了介紹PostgreSQL中的Lateral類型,Lateral是PostgreSQL9.3版本以來(lái)加入的內(nèi)置類型,需要的朋友可以參考下

PostgreSQL 9.3 用了一種新的聯(lián)合類型! Lateral聯(lián)合的推出比較低調(diào)，但它實(shí)現(xiàn)了之前需要使用編寫程序才能獲得的強(qiáng)大的新查詢. 在本文中, 我將會(huì)介紹一個(gè)在 PostgreSQL 9.2 不可能被實(shí)現(xiàn)的渠道轉(zhuǎn)換分析.

什么是 LATERAL 聯(lián)合?

對(duì)此的最佳描述在文檔中可選 FROM 語(yǔ)句清單的底部:

LATERAL 關(guān)鍵詞可以在前綴一個(gè) SELECT FROM 子項(xiàng). 這能讓 SELECT 子項(xiàng)在FROM項(xiàng)出現(xiàn)之前就引用到FROM項(xiàng)中的列. (沒有 LATERAL 的話, 每一個(gè) SELECT 子項(xiàng)彼此都是獨(dú)立的，因此不能夠?qū)ζ渌?FROM 項(xiàng)進(jìn)行交叉引用.)

…

當(dāng)一個(gè) FROM 項(xiàng)包含 LATERAL 交叉引用的時(shí)候，查詢的計(jì)算過(guò)程如下: 對(duì)于FROM像提供給交叉引用列的每一行，或者多個(gè)FROM像提供給引用列的行的集合, LATERAL 項(xiàng)都會(huì)使用行或者行的集合的列值來(lái)進(jìn)行計(jì)算. 計(jì)算出來(lái)的結(jié)果集像往常一樣被加入到聯(lián)合查詢之中. 這一過(guò)程會(huì)在列的來(lái)源表的行或者行的集合上重復(fù)進(jìn)行.

這種計(jì)算有一點(diǎn)密集。你可以比較松散的將 LATERAL 聯(lián)合理解作一個(gè) SQL 的foreach 選擇, 在這個(gè)循環(huán)中 PostgreSQL 將循環(huán)一個(gè)結(jié)果集中的每一行，并將那一行作為參數(shù)來(lái)執(zhí)行一次子查詢的計(jì)算.

我們可以用這個(gè)來(lái)干些什么?

看看下面這個(gè)用來(lái)記錄點(diǎn)擊事件的表結(jié)構(gòu):

1. CREATE TABLE event ( 
2. user_id BIGINT, 
3. event_id BIGINT, 
4. time BIGINT NOT NULL, 
5. data JSON NOT NULL, 
6. PRIMARY KEY (user_id, event_id) 
7. ) 

每一個(gè)事件都關(guān)聯(lián)了一個(gè)用戶，擁有一個(gè)ID，一個(gè)時(shí)間戳，還有一個(gè)帶有事件屬性的JSON blob. 在堆中，這些屬性可能包含一次點(diǎn)擊的DOM層級(jí), 窗口的標(biāo)題，會(huì)話引用等等信息.

加入我們要優(yōu)化我們的登錄頁(yè)面以增加注冊(cè). 第一步就是要計(jì)算看看我們的哪個(gè)渠道轉(zhuǎn)換上正在丟失用戶.

示例：一個(gè)注冊(cè)流程的個(gè)步驟之間的渠道轉(zhuǎn)換率.

假設(shè)我們已經(jīng)在前端配備的裝置，來(lái)沿著這一流程來(lái)記錄事件日志，所有的數(shù)據(jù)都會(huì)保存到上述的事件數(shù)據(jù)表中.[1] 最開始的問題是，我們要計(jì)算有多少人查看了我們的主頁(yè)，而他們之中有百分之多少在那次查看了主頁(yè)之后的兩個(gè)星期之內(nèi)輸入了驗(yàn)證信息. 如果我們使用 PostgreSQL 較老的版本, 我們可能需要使用PL/pgSQL這一PostgreSQL內(nèi)置的過(guò)程語(yǔ)言 來(lái)編寫一些定制的函數(shù). 而在 9.3 中, 我們就可以使用一個(gè) lateral 聯(lián)合，只用一個(gè)搞笑的查詢就能計(jì)算出結(jié)果，不需要任何擴(kuò)展或者 PL/pgSQL.

1. SELECT 
2. user_id, 
3. view_homepage, 
4. view_homepage_time, 
5. enter_credit_card, 
6. enter_credit_card_time 
7. FROM ( 
8. -- Get the first time each user viewed the homepage. 
9. SELECT 
10. user_id, 
11. 1 AS view_homepage, 
12. min(time) AS view_homepage_time 
13. FROM event 
14. WHERE 
15. data->>'type' = 'view_homepage' 
16. GROUP BY user_id 
17. ) e1 LEFT JOIN LATERAL ( 
18. -- For each row, get the first time the user_id did the enter_credit_card 
19. -- event, if one exists within two weeks of view_homepage_time. 
20. SELECT 
21. 1 AS enter_credit_card, 
22. time AS enter_credit_card_time 
23. FROM event 
24. WHERE 
25. user_id = e1.user_id AND 
26. data->>'type' = 'enter_credit_card' AND 
27. time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14) 
28. ORDER BY time 
29. LIMIT 1 
30. ) e2 ON true 

沒有人會(huì)喜歡30多行的SQL查詢，所以讓我們將這些SQL分成片段來(lái)分析。第一塊是一段普通的 SQL:

1. SELECT 
2. user_id, 
3. 1 AS view_homepage, 
4. min(time) AS view_homepage_time 
5. FROM event 
6. WHERE 
7. data->>'type' = 'view_homepage' 
8. GROUP BY user_id 

也就是要獲取到每個(gè)用戶最開始觸發(fā) view_homepage 事件的時(shí)間. 然后我們的 lateral 聯(lián)合就可以讓我們迭代結(jié)果集的每一行，并會(huì)在接下來(lái)執(zhí)行一次參數(shù)化的子查詢. 這就等同于針對(duì)結(jié)果集的每一行都要執(zhí)行一邊下面的這個(gè)查詢:

1. SELECT 
2. 1 AS enter_credit_card, 
3. time AS enter_credit_card_time 
4. FROM event 
5. WHERE 
6. user_id = e1.user_id AND 
7. data->>'type' = 'enter_credit_card' AND 
8. time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14) 
9. ORDER BY time 
10. LIMIT 1 

例如,對(duì)于每一個(gè)用戶，要獲取他們?cè)谟|發(fā) view_homepage_time 事件后的兩星期內(nèi)觸發(fā) enter_credit_card 事件的時(shí)間. 因?yàn)檫@是一個(gè)lateral聯(lián)合，我們的子查詢就可以從之前的子查詢出引用到 view_homepage_time 結(jié)果集. 否則，子查詢就只能單獨(dú)執(zhí)行，而沒辦法訪問到另外一個(gè)子查詢所計(jì)算出來(lái)的結(jié)果集.

之后哦我們整個(gè)封裝成一個(gè)select，它會(huì)返回像下面這樣的東西:

1. user_id | view_homepage | view_homepage_time | enter_credit_card | enter_credit_card_time 
2. ---------+---------------+--------------------+-------------------+------------------------ 
3. 567 | 1 | 5234567890 | 1 | 5839367890 
4. 234 | 1 | 2234567890 | | 
5. 345 | 1 | 3234567890 | | 
6. 456 | 1 | 4234567890 | | 
7. 678 | 1 | 6234567890 | | 
8. 123 | 1 | 1234567890 | | 
9. ...  

因?yàn)檫@是一個(gè)左聯(lián)合，所以查詢結(jié)果集中會(huì)有不匹配 enter_credit_card 事件的行，只要有 view_homepage 事件就行. 如果我們匯總所有的數(shù)值列，就會(huì)得到渠道轉(zhuǎn)換的一個(gè)清晰匯總:

1. SELECT 
2. sum(view_homepage) AS viewed_homepage, 
3. sum(enter_credit_card) AS entered_credit_card 
4. FROM ( 
5. -- Get the first time each user viewed the homepage. 
6. SELECT 
7. user_id, 
8. 1 AS view_homepage, 
9. min(time) AS view_homepage_time 
10. FROM event 
11. WHERE 
12. data->>'type' = 'view_homepage' 
13. GROUP BY user_id 
14. ) e1 LEFT JOIN LATERAL ( 
15. -- For each (user_id, view_homepage_time) tuple, get the first time that 
16. -- user did the enter_credit_card event, if one exists within two weeks. 
17. SELECT 
18. 1 AS enter_credit_card, 
19. time AS enter_credit_card_time 
20. FROM event 
21. WHERE 
22. user_id = e1.user_id AND 
23. data->>'type' = 'enter_credit_card' AND 
24. time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14) 
25. ORDER BY time 
26. LIMIT 1 
27. ) e2 ON true 

… 它會(huì)輸出:

1. viewed_homepage | entered_credit_card 
2. -----------------+--------------------- 
3. 827 | 10 

我們可以向這個(gè)渠道中填入帶有更多l(xiāng)ateral聯(lián)合的中間步驟，來(lái)得到流程中我們需要重點(diǎn)改進(jìn)的部分. 讓我們?cè)诓榭粗黜?yè)和輸入驗(yàn)證信息之間加入對(duì)使用示例步驟的查詢.

1. SELECT 
2. sum(view_homepage) AS viewed_homepage, 
3. sum(use_demo) AS use_demo, 
4. sum(enter_credit_card) AS entered_credit_card 
5. FROM ( 
6. -- Get the first time each user viewed the homepage. 
7. SELECT 
8. user_id, 
9. 1 AS view_homepage, 
10. min(time) AS view_homepage_time 
11. FROM event 
12. WHERE 
13. data->>'type' = 'view_homepage' 
14. GROUP BY user_id 
15. ) e1 LEFT JOIN LATERAL ( 
16. -- For each row, get the first time the user_id did the use_demo 
17. -- event, if one exists within one week of view_homepage_time. 
18. SELECT 
19. user_id, 
20. 1 AS use_demo, 
21. time AS use_demo_time 
22. FROM event 
23. WHERE 
24. user_id = e1.user_id AND 
25. data->>'type' = 'use_demo' AND 
26. time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*7) 
27. ORDER BY time 
28. LIMIT 1 
29. ) e2 ON true LEFT JOIN LATERAL ( 
30. -- For each row, get the first time the user_id did the enter_credit_card 
31. -- event, if one exists within one week of use_demo_time. 
32. SELECT 
33. 1 AS enter_credit_card, 
34. time AS enter_credit_card_time 
35. FROM event 
36. WHERE 
37. user_id = e2.user_id AND 
38. data->>'type' = 'enter_credit_card' AND 
39. time BETWEEN use_demo_time AND (use_demo_time + 1000*60*60*24*7) 
40. ORDER BY time 
41. LIMIT 1 
42. ) e3 ON true 

這樣就會(huì)輸出:

1. viewed_homepage | use_demo | entered_credit_card 
2. -----------------+----------+--------------------- 
3. 827 | 220 | 86  

從查看主頁(yè)到一周之內(nèi)使用demo，再到一周以內(nèi)向其輸入信用卡信息，這就向我們提供了三個(gè)步驟的通道轉(zhuǎn)換. 從此，功能強(qiáng)大的 PostgreSQL 使得我們可以深入分析這些數(shù)據(jù)結(jié)果集，并對(duì)我們的網(wǎng)站性能進(jìn)行整體的分析. 接著我們可能會(huì)有下面這些問題要解決:

使用demo是否能增加注冊(cè)的可能性?

通過(guò)廣告找到我們主頁(yè)的用戶是否同來(lái)自其他渠道的用戶擁有相同的轉(zhuǎn)換率?

轉(zhuǎn)換率會(huì)跟隨不同的 A/B 測(cè)試變量發(fā)生怎樣的變化?

這些問題的答案會(huì)直接影響到產(chǎn)品的改進(jìn)，它們可以從 PostgreSQL 數(shù)據(jù)庫(kù)中找到答案，因?yàn)楝F(xiàn)在它支持 lateral 聯(lián)合.

沒有 lateral 聯(lián)合，我們就只能借助 PL/pgSQL 來(lái)做這些分析。或者，如果我們的數(shù)據(jù)集很小，我們可能就不會(huì)碰這些復(fù)雜、低效的查詢. 在一項(xiàng)探索性數(shù)據(jù)研究使用場(chǎng)景下，你可能只是將數(shù)據(jù)從 PostgreSQL 里面抽取出來(lái)，并使用你所選擇的腳本語(yǔ)言來(lái)對(duì)其進(jìn)行分析。但是其實(shí)還存在更強(qiáng)大的理由來(lái)用SQL表述這些問題, 特別是如果你正想要把整個(gè)全封裝到一套易于理解的UI中，并向非技術(shù)型用戶發(fā)布功能 的時(shí)候.

注意這些查詢可以被優(yōu)化，以變得更加高效. 在本例中，如果我們?cè)?(user_id, (data->>'type'), time)上創(chuàng)建一個(gè)btree索引, 我們只用一次索引查找就能針對(duì)每一個(gè)用戶計(jì)算每一個(gè)渠道步驟. 如果你使用的是SSD，在上面做查找花費(fèi)是很小的，那這就足夠了。而如果不是，你就可能需要用稍微不同的手段來(lái)圖示化你的數(shù)據(jù)，詳細(xì)的內(nèi)容我會(huì)留到另外一篇文章之中進(jìn)行介紹.