介紹PostgreSQL中的Lateral類型，postgresqllateral

介紹PostgreSQL中的Lateral類型，postgresqllateral

PostgreSQL 9.3 用了一種新的等位型別! Lateral聯合的推出比較低調，但它實現了之前需要使用編寫程式才能獲得的強大的新查詢. 在本文中, 我將會介紹一個在 PostgreSQL 9.2 不可能被實現的渠道轉換剖析.
什麼是 LATERAL 聯合?

對此的最佳描述在文檔中 可選 FROM 語句清單 的底部:

LATERAL 關鍵詞可以在首碼一個 SELECT FROM 子項. 這能讓 SELECT 子項在FROM項出現之前就引用到FROM項中的列. (沒有 LATERAL 的話, 每一個 SELECT 子項彼此都是獨立的，因此不能夠對其它的 FROM 項進行交叉引用.)
…
當一個 FROM 項包含 LATERAL 交叉引用的時候，查詢的計算過程如下: 對於FROM像提供給交叉引用列的每一行，或者多個FROM像提供給引用列的行的集合, LATERAL 項都會使用行或者行的集合的列值來進行計算. 計算出來的結果集像往常一樣被加入到聯集查詢之中. 這一過程會在列的來源表的行或者行的集合上重複進行.

這種計算有一點密集。你可以比較鬆散的將 LATERAL 聯合理解作一個 SQL 的foreach 選擇, 在這個迴圈中 PostgreSQL 將迴圈一個結果集中的每一行，並將那一行作為參數來執行一次子查詢的計算.

我們可以用這個來幹些什麼?

看看下面這個用來記錄點擊事件的表結構:
 

CREATE TABLE event (  user_id BIGINT,  event_id BIGINT,  time BIGINT NOT NULL,  data JSON NOT NULL,  PRIMARY KEY (user_id, event_id))

每一個事件都關聯了一個使用者，擁有一個ID，一個時間戳記，還有一個帶有事件屬性的JSON blob. 在堆中，這些屬性可能包含一次點擊的DOM層級, 視窗的標題，會話引用等等資訊.

加入我們要最佳化我們的登入頁面以增加註冊. 第一步就是要計算看看我們的哪個渠道轉換上正在丟失使用者.

樣本：一個註冊流程的個步驟之間的渠道轉換率.

假設我們已經在前端配備的裝置，來沿著這一流程來記錄事件記錄，所有的資料都會儲存到上述的事件數目據表中.[1] 最開始的問題是，我們要計算有多少人查看了我們的首頁，而他們之中有百分之多少在那次查看了首頁之後的兩個星期之內輸入了驗證資訊. 如果我們使用 PostgreSQL 較老的版本, 我們可能需要使用PL/pgSQL這一PostgreSQL內建的過程語言 來編寫一些定製的函數. 而在 9.3 中, 我們就可以使用一個 lateral 聯合，只用一個搞笑的查詢就能計算出結果，不需要任何擴充或者 PL/pgSQL.

 

SELECTuser_id,view_homepage,view_homepage_time,enter_credit_card,enter_credit_card_timeFROM (-- Get the first time each user viewed the homepage.SELECTuser_id,1 AS view_homepage,min(time) AS view_homepage_timeFROM eventWHEREdata->>'type' = 'view_homepage'GROUP BY user_id) e1 LEFT JOIN LATERAL (-- For each row, get the first time the user_id did the enter_credit_card-- event, if one exists within two weeks of view_homepage_time.SELECT1 AS enter_credit_card,time AS enter_credit_card_timeFROM eventWHEREuser_id = e1.user_id ANDdata->>'type' = 'enter_credit_card' ANDtime BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14)ORDER BY timeLIMIT 1) e2 ON true

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

SELECT  user_id,  1 AS view_homepage,  min(time) AS view_homepage_timeFROM eventWHERE  data->>'type' = 'view_homepage'GROUP BY user_id

也就是要擷取到每個使用者最開始觸發 view_homepage 事件的時間. 然後我們的 lateral 聯合就可以讓我們迭代結果集的每一行，並會在接下來執行一次參數化的子查詢. 這就等同於針對結果集的每一行都要執行一邊下面的這個查詢:
 

SELECT  1 AS enter_credit_card,  time AS enter_credit_card_timeFROM eventWHERE  user_id = e1.user_id AND  data->>'type' = 'enter_credit_card' AND  time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14)ORDER BY timeLIMIT 1

例如,對於每一個使用者，要擷取他們在觸發 view_homepage_time 事件後的兩星期內觸發 enter_credit_card  事件的時間. 因為這是一個lateral聯合，我們的子查詢就可以從之前的子查詢出引用到 view_homepage_time 結果集. 否則，子查詢就只能單獨執行，而沒辦法訪問到另外一個子查詢所計算出來的結果集.

之後哦我們整個封裝成一個select，它會返回像下面這樣的東西:

user_id | view_homepage | view_homepage_time | enter_credit_card | enter_credit_card_time---------+---------------+--------------------+-------------------+------------------------567 | 1 | 5234567890 | 1 | 5839367890234 | 1 | 2234567890 | |345 | 1 | 3234567890 | |456 | 1 | 4234567890 | |678 | 1 | 6234567890 | |123 | 1 | 1234567890 | |... 

因為這是一個左聯合，所以查詢結果集中會有不匹配 enter_credit_card 事件的行，只要有 view_homepage 事件就行. 如果我們匯總所有的數值列，就會得到渠道轉換的一個清晰匯總:
 

SELECT  sum(view_homepage) AS viewed_homepage,  sum(enter_credit_card) AS entered_credit_cardFROM (  -- Get the first time each user viewed the homepage.  SELECT  user_id,  1 AS view_homepage,  min(time) AS view_homepage_time  FROM event  WHERE  data->>'type' = 'view_homepage'  GROUP BY user_id) e1 LEFT JOIN LATERAL (  -- For each (user_id, view_homepage_time) tuple, get the first time that  -- user did the enter_credit_card event, if one exists within two weeks.  SELECT  1 AS enter_credit_card,  time AS enter_credit_card_time  FROM event  WHERE  user_id = e1.user_id AND  data->>'type' = 'enter_credit_card' AND  time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*14)  ORDER BY time  LIMIT 1) e2 ON true

… 它會輸出:

 viewed_homepage | entered_credit_card-----------------+---------------------827 | 10

我們可以向這個渠道中填入帶有更多lateral聯合的中間步驟，來得到流程中我們需要重點改進的部分. 讓我們在查看首頁和輸入驗證資訊之間加入對使用樣本步驟的查詢.
 

SELECT  sum(view_homepage) AS viewed_homepage,  sum(use_demo) AS use_demo,  sum(enter_credit_card) AS entered_credit_cardFROM (  -- Get the first time each user viewed the homepage.  SELECT  user_id,  1 AS view_homepage,  min(time) AS view_homepage_time  FROM event  WHERE  data->>'type' = 'view_homepage'  GROUP BY user_id) e1 LEFT JOIN LATERAL (  -- For each row, get the first time the user_id did the use_demo  -- event, if one exists within one week of view_homepage_time.  SELECT  user_id,  1 AS use_demo,  time AS use_demo_time  FROM event  WHERE  user_id = e1.user_id AND  data->>'type' = 'use_demo' AND  time BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*7)  ORDER BY time  LIMIT 1) e2 ON true LEFT JOIN LATERAL (  -- For each row, get the first time the user_id did the enter_credit_card  -- event, if one exists within one week of use_demo_time.  SELECT  1 AS enter_credit_card,  time AS enter_credit_card_time  FROM event  WHERE  user_id = e2.user_id AND  data->>'type' = 'enter_credit_card' AND  time BETWEEN use_demo_time AND (use_demo_time + 1000*60*60*24*7)  ORDER BY time  LIMIT 1) e3 ON true

這樣就會輸出:

 viewed_homepage | use_demo | entered_credit_card-----------------+----------+---------------------827 | 220 | 86 

從查看首頁到一周之內使用demo，再到一周以內向其輸入信用卡資訊，這就向我們提供了三個步驟的通道轉換. 從此，功能強大的 PostgreSQL 使得我們可以深入分析這些資料結果集，並對我們的網站效能進行整體的分析. 接著我們可能會有下面這些問題要解決:

•     使用demo是否能增加註冊的可能性?
•     通過廣告找到我們首頁的使用者是否同來自其他渠道的使用者擁有相同的轉換率?
•     轉換率會跟隨不同的 A/B 測試變數發生怎樣的變化?

這些問題的答案會直接影響到產品的改進，它們可以從 PostgreSQL 資料庫中找到答案，因為現在它支援 lateral 聯合.

沒有 lateral 聯合，我們就只能藉助 PL/pgSQL 來做這些分析。或者，如果我們的資料集很小，我們可能就不會碰這些複雜、低效的查詢. 在一項探索性資料研究使用情境下，你可能只是將資料從 PostgreSQL 裡面抽取出來，並使用你所選擇的指令碼語言來對其進行分析。但是其實還存在更強大的理由來用SQL表述這些問題, 特別是如果你正想要把整個全封裝到一套易於理解的UI中，並向非技術型使用者發布功能 的時候.

注意這些查詢可以被最佳化，以變得更加高效. 在本例中，如果我們在 (user_id, (data->>'type'), time)上建立一個btree索引, 我們只用一次索引尋找就能針對每一個使用者計算每一個渠道步驟. 如果你使用的是SSD，在上面做尋找花費是很小的，那這就足夠了。而如果不是，你就可能需要用稍微不同的手段來圖示化你的資料，詳細的內容我會留到另外一篇文章之中進行介紹.