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 将循环一个结果集中的每一行，并将那一行作为参数来执行一次子查询的计算.
我们可以用这个来干些什么?
看看下面这个用来记录点击事件的表结构:
|
1
2
3
4
5
6
7
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
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:
|
1
2
3
4
5
6
7
8
|
select
user_id,
1 as view_homepage,
min(time) as view_homepage_time
from event
where
data->>'type' = 'view_homepage'
group by user_id
|
也就是要获取到每个用户最开始触发 view_homepage 事件的时间. 然后我们的 lateral 联合就可以让我们迭代结果集的每一行，并会在接下来执行一次参数化的子查询. 这就等同于针对结果集的每一行都要执行一边下面的这个查询:
|
1
2
3
4
5
6
7
8
9
10
|
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
|
例如,对于每一个用户，要获取他们在触发 view_homepage_time 事件后的两星期内触发 enter_credit_card 事件的时间. 因为这是一个lateral联合，我们的子查询就可以从之前的子查询出引用到 view_homepage_time 结果集. 否则，子查询就只能单独执行，而没办法访问到另外一个子查询所计算出来的结果集.
之后哦我们整个封装成一个select，它会返回像下面这样的东西:
|
1
2
3
4
5
6
7
8
9
|
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 | |
...
|
因为这是一个左联合，所以查询结果集中会有不匹配 enter_credit_card 事件的行，只要有 view_homepage 事件就行. 如果我们汇总所有的数值列，就会得到渠道转换的一个清晰汇总:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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
|
… 它会输出:
|
1
2
3
|
viewed_homepage | entered_credit_card
-----------------+---------------------
827 | 10
|
我们可以向这个渠道中填入带有更多lateral联合的中间步骤，来得到流程中我们需要重点改进的部分. 让我们在查看主页和输入验证信息之间加入对使用示例步骤的查询.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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
|
这样就会输出:
|
1
2
3
|
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，在上面做查找花费是很小的，那这就足够了。而如果不是，你就可能需要用稍微不同的手段来图示化你的数据，详细的内容我会留到另外一篇文章之中进行介绍.