《介绍PostgreSQL中的Lateral类型》由会员分享，可在线阅读，更多相关《介绍PostgreSQL中的Lateral类型（6页珍藏版）》请在金锄头文库上搜索。
1、PostgreSQL 9.3 用了一种新的联合类型! Lateral 联合的推出比较低调，但它实现了之前需 要使用编写程序才能获得的强大的新查询. 在本文中, 我将会介绍一个在 PostgreSQL 9.2 不可能被实现的渠道转换分析. 什么是 LATERAL 联合?对此的最佳描述在文档中 可选 FROM 语句清单 的底部:LATERAL 关键词可以在前缀一个 SELECT FROM 子项. 这能让 SELECT 子项在 FROM 项出现之前就引用到 FROM 项中的列. (没有 LATERAL 的话, 每一个 SELECT 子项彼此 都是独立的，因此不能够对其它的 FROM 项进行交叉引用.
2、) 当一个 FROM 项包含 LATERAL 交叉引用的时候，查询的计算过程如下: 对于 FROM 像 提供给交叉引用列的每一行，或者多个 FROM 像提供给引用列的行的集合, LATERAL 项 都会使用行或者行的集合的列值来进行计算. 计算出来的结果集像往常一样被加入到联合 查询之中. 这一过程会在列的来源表的行或者行的集合上重复进行.这种计算有一点密集。你可以比较松散的将 LATERAL 联合理解作一个 SQL 的 foreach 选择, 在这个循环中 PostgreSQL 将循环一个结果集中的每一行，并将那一行作为参数来 执行一次子查询的计算.我们可以用这个来干些什么?看看下面这个用来
3、记录点击事件的表结构: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 层级, 窗口的标题，会话引用等 等信息.加入我们要优化我们的登录页面以增加注册. 第一步就是要计算看看我们的哪个渠道转换 上正在丢失用户. 2015421170911413.png
4、(1246794)示例：一个注册流程的个步骤之间的渠道转换率.假设我们已经在前端配备的装置，来沿着这一流程来记录事件日志，所有的数据都会保存 到上述的事件数据表中.1 最开始的问题是，我们要计算有多少人查看了我们的主页，而他们之中有百分之多少在那次查看了主页之后的两个星期之内输入了验证信息. 如果我们 使用 PostgreSQL 较老的版本, 我们可能需要使用 PL/pgSQL 这一 PostgreSQL 内置的过程 语言 来编写一些定制的函数. 而在 9.3 中, 我们就可以使用一个 lateral 联合，只用一个 搞笑的查询就能计算出结果，不需要任何扩展或者 PL/pgSQL.SELECT
5、 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 ( -
6、 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_
7、time AND (view_homepage_time + 1000*60*60*24*14) ORDER BY time LIMIT 1 ) e2 ON true 没有人会喜欢 30 多行的 SQL 查询，所以让我们将这些 SQL 分成片段来分析。第一块是一 段普通的 SQL:SELECTuser_id,1 AS view_homepage,min(time) AS view_homepage_time FROM eventWHEREdata-type = view_homepage GROUP BY user_id 也就是要获取到每个用户最开始触发 view_homepage 事件的时间
8、. 然后我们的 lateral 联 合就可以让我们迭代结果集的每一行，并会在接下来执行一次参数化的子查询. 这就等同 于针对结果集的每一行都要执行一边下面的这个查询:SELECT1 AS enter_credit_card,time AS enter_credit_card_time FROM event WHEREuser_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
9、 time LIMIT 1 例如,对于每一个用户，要获取他们在触发 view_homepage_time 事件后的两星期内触发 enter_credit_card 事件的时间. 因为这是一个 lateral 联合，我们的子查询就可以从之前的 子查询出引用到 view_homepage_time 结果集. 否则，子查询就只能单独执行，而没办法 访问到另外一个子查询所计算出来的结果集.之后哦我们整个封装成一个 select，它会返回像下面这样的东西:user_id | view_homepage | view_homepage_time | enter_credit_card | enter_cr
10、edit_card_time -+-+-+-+- 567 | 1 | 5234567890 | 1 | 5839367890 234 | 1 | 2234567890 | | 345 | 1 | 3234567890 | | 456 | 1 | 4234567890 | | 678 | 1 | 6234567890 | | 123 | 1 | 1234567890 | | . 因为这是一个左联合，所以查询结果集中会有不匹配 enter_credit_card 事件的行，只要有 view_homepage 事件就行. 如果我们汇总所有的数值列，就会得到渠道转换的一个清晰汇 总:SELECTsum
11、(view_homepage) AS viewed_homepage,sum(enter_credit_card) AS entered_credit_card FROM (- Get the first time each user viewed the homepage.SELECTuser_id,1 AS view_homepage,min(time) AS view_homepage_timeFROM eventWHEREdata-type = view_homepageGROUP BY user_id ) e1 LEFT JOIN LATERAL (- For each (user_
12、id, view_homepage_time) tuple, get the first time that- user did the enter_credit_card event, if one exists within two weeks.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_ho
13、mepage_time + 1000*60*60*24*14)ORDER BY timeLIMIT 1 ) e2 ON true 它会输出:viewed_homepage | entered_credit_card -+- 827 | 10我们可以向这个渠道中填入带有更多 lateral 联合的中间步骤，来得到流程中我们需要重点 改进的部分. 让我们在查看主页和输入验证信息之间加入对使用示例步骤的查询.SELECTsum(view_homepage) AS viewed_homepage,sum(use_demo) AS use_demo,sum(enter_credit_card) AS e
14、ntered_credit_card FROM (- Get the first time each user viewed the homepage.SELECTuser_id,1 AS view_homepage,min(time) AS view_homepage_timeFROM eventWHEREdata-type = view_homepageGROUP BY user_id ) e1 LEFT JOIN LATERAL (- For each row, get the first time the user_id did the use_demo- event, if one
15、exists within one week of view_homepage_time.SELECTuser_id,1 AS use_demo,time AS use_demo_timeFROM eventWHEREuser_id = e1.user_id ANDdata-type = use_demo ANDtime BETWEEN view_homepage_time AND (view_homepage_time + 1000*60*60*24*7)ORDER BY timeLIMIT 1 ) e2 ON true LEFT JOIN LATERAL (- For each row,
16、get the first time the user_id did the enter_credit_card- event, if one exists within one week of use_demo_time.SELECT1 AS enter_credit_card,time AS enter_credit_card_timeFROM eventWHEREuser_id = e2.user_id ANDdata-type = enter_credit_card ANDtime BETWEEN use_demo_time AND (use_demo_time + 1000*60*60*24
