服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - 介绍PostgreSQL中的Lateral类型

介绍PostgreSQL中的Lateral类型

2021-10-22 15:59PostgreSQL教程网 PostgreSQL

这篇文章主要介绍了介绍PostgreSQL中的Lateral类型,Lateral是PostgreSQL9.3版本以来加入的内置类型,需要的朋友可以参考下

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层级, 窗口的标题,会话引用等等信息.

加入我们要优化我们的登录页面以增加注册. 第一步就是要计算看看我们的哪个渠道转换上正在丢失用户.

介绍PostgreSQL中的Lateral类型

示例:一个注册流程的个步骤之间的渠道转换率.


假设我们已经在前端配备的装置,来沿着这一流程来记录事件日志,所有的数据都会保存到上述的事件数据表中.[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,在上面做查找花费是很小的,那这就足够了。而如果不是,你就可能需要用稍微不同的手段来图示化你的数据,详细的内容我会留到另外一篇文章之中进行介绍.

延伸 · 阅读

精彩推荐
  • PostgreSQLPostgreSQL标准建表语句分享

    PostgreSQL标准建表语句分享

    这篇文章主要介绍了PostgreSQL标准建表语句分享,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    码上得天下7962021-02-27
  • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

    深入理解PostgreSQL的MVCC并发处理方式

    这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

    PostgreSQL教程网3622020-04-25
  • PostgreSQLPostgresql查询效率计算初探

    Postgresql查询效率计算初探

    这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Postgresql具有一定的参考学习价...

    轨迹4622020-05-03
  • PostgreSQL分布式 PostgreSQL之Citus 架构

    分布式 PostgreSQL之Citus 架构

    节点 Citus 是一种 PostgreSQL 扩展,它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群,允许 PostgreSQL 保存比单...

    未知802023-05-07
  • PostgreSQLpostgresql 数据库中的数据转换

    postgresql 数据库中的数据转换

    postgres8.3以后,字段数据之间的默认转换取消了。如果需要进行数据变换的话,在postgresql数据库中,我们可以用"::"来进行字段数据的类型转换。...

    postgresql教程网12482021-10-08
  • PostgreSQLPostgresql开启远程访问的步骤全纪录

    Postgresql开启远程访问的步骤全纪录

    postgre一般默认为本地连接,不支持远程访问,所以如果要开启远程访问,需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

    我勒个去6812020-04-30
  • PostgreSQLpostgresql 中的to_char()常用操作

    postgresql 中的to_char()常用操作

    这篇文章主要介绍了postgresql 中的to_char()常用操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...

    J符离13432021-04-12
  • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

    RDS PostgreSQL一键大版本升级技术解密

    一、PostgreSQL行业位置 (一)行业位置 在讨论PostgreSQL(下面简称为PG)在整个数据库行业的位置之前,我们先看一下阿里云数据库在全球的数据库行业里的...

    未知1192023-05-07