介紹PostgreSQL中的Lateral類型

更新時間：2015年04月21日 17:15:13 投稿：goldensun

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

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

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

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

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

我們可以用這個來干些什么?

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

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

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

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

2015421170911413.png (1246×794)

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

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

SELECT
user_id,
view_homepage,
view_homepage_time,
enter_credit_card,
enter_credit_card_time
FROM (
-- 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 enter_credit_card
-- event, if one exists within two weeks of view_homepage_time.
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

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

SELECT
  user_id,
  1 AS view_homepage,
  min(time) AS view_homepage_time
FROM event
WHERE
  data->>'type' = 'view_homepage'
GROUP BY user_id

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

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

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

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

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

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

SELECT
  sum(view_homepage) AS viewed_homepage,
  sum(enter_credit_card) AS entered_credit_card
FROM (
  -- 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

我們可以向這個渠道中填入帶有更多l(xiāng)ateral聯(lián)合的中間步驟，來得到流程中我們需要重點改進的部分. 讓我們在查看主頁和輸入驗證信息之間加入對使用示例步驟的查詢.

SELECT
  sum(view_homepage) AS viewed_homepage,
  sum(use_demo) AS use_demo,
  sum(enter_credit_card) AS entered_credit_card
FROM (
  -- 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

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

使用demo是否能增加注冊的可能性?
通過廣告找到我們主頁的用戶是否同來自其他渠道的用戶擁有相同的轉(zhuǎn)換率?
轉(zhuǎn)換率會跟隨不同的 A/B 測試變量發(fā)生怎樣的變化?

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

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

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

您可能感興趣的文章:

PostgreSQL

neo4j創(chuàng)建數(shù)據(jù)庫以及導入csv文件內(nèi)容圖文詳解
這篇文章主要給大家介紹了關于neo4j創(chuàng)建數(shù)據(jù)庫以及導入csv文件內(nèi)容的相關資料,Neo4j是一個基于圖形結(jié)構的NoSQL數(shù)據(jù)庫,它提供了一種高效的方式來管理和查詢大型復雜數(shù)據(jù),需要的朋友可以參考下
2023-11-11
深入SQL中PIVOT 行列轉(zhuǎn)換詳解
T-SQL語句中，Pivot運算符用于在列和行之間對數(shù)據(jù)進行旋轉(zhuǎn)或透視轉(zhuǎn)換，PIVOT命令可以實現(xiàn)數(shù)據(jù)表的列轉(zhuǎn)行，同時執(zhí)行聚合運算，UNPIVOT則與其相反，實現(xiàn)數(shù)據(jù)的行轉(zhuǎn)列。
2015-10-10
postgres 數(shù)據(jù)庫中的數(shù)據(jù)轉(zhuǎn)換
postgres8.3以后，字段數(shù)據(jù)之間的默認轉(zhuǎn)換取消了。如果需要進行數(shù)據(jù)變換的話，在postgres數(shù)據(jù)庫中，我們可以用"::"來進行字段數(shù)據(jù)的類型轉(zhuǎn)換。
2009-07-07
navicat怎么卸載,navicat徹底卸載干凈教程
這篇文章主要介紹了navicat怎么卸載？navicat徹底卸載干凈教程。具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
2023-02-02
數(shù)據(jù)庫librarydb多表查詢的操作方法
這篇文章主要介紹了數(shù)據(jù)庫librarydb多表查詢的操作方法,本文通過示例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧
2023-12-12
5分鐘快速了解數(shù)據(jù)庫死鎖產(chǎn)生的場景和解決方法
這篇文章主要給大家介紹了關于如何通過5分鐘快速理解數(shù)據(jù)庫死鎖的相關資料，文中介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-05-05
sql left join 命令詳解
左向外聯(lián)接的結(jié)果集包括 LEFT OUTER 子句中指定的左表的所有行，而不僅僅是聯(lián)接列所匹配的行。如果左表的某行在右表中沒有匹配行，則在相關聯(lián)的結(jié)果集行中右表的所有選擇列表列均為空值。
2009-07-07
使用Navicat連接opengauss數(shù)據(jù)庫完整步驟(詳細圖文)
Navicat是一套快速、可靠并價格相當便宜的數(shù)據(jù)庫管理工具,專為簡化數(shù)據(jù)庫的管理及降低系統(tǒng)管理成本而設,下面這篇文章主要給大家介紹了關于使用Navicat連接opengauss數(shù)據(jù)庫的完整步驟,需要的朋友可以參考下
2024-02-02
SQL SERVER的字段類型說明
以下為SQL SERVER 7.0以上版本的字段類型說明。SQL SERVER6.5的字段類型說明請參考
2008-04-04
Hbase列式存儲入門教程
HBase是一種分布式、可擴展、支持海量數(shù)據(jù)存儲的NoSQL數(shù)據(jù)庫。本文通過應用場景，性質(zhì)特點給大家介紹Hbase列式存儲入門教程，感興趣的朋友一起看看吧
2021-07-07