介绍PostgreSQL中的Lateral类型

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.

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

也就是要获取到每个用户最开始触发 view_homepage 事件的时间. 然后我们的 lateral 联合就可以让我们迭代结果集的每一行,并会在接下来执行一次参数化的子查询. 这就等同于针对结果集的每一行都要执行一边下面的这个查询:

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,它会返回像下面这样的东西:

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 事件就行. 如果我们汇总所有的数值列,就会得到渠道转换的一个清晰汇总:

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

我们可以向这个渠道中填入带有更多lateral联合的中间步骤,来得到流程中我们需要重点改进的部分. 让我们在查看主页和输入验证信息之间加入对使用示例步骤的查询.

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

从查看主页到一周之内使用demo,再到一周以内向其输入信用卡信息,这就向我们提供了三个步骤的通道转换. 从此,功能强大的 PostgreSQL 使得我们可以深入分析这些数据结果集,并对我们的网站性能进行整体的分析. 接着我们可能会有下面这些问题要解决:

  • 使用demo是否能增加注册的可能性?
  • 通过广告找到我们主页的用户是否同来自其他渠道的用户拥有相同的转换率?
  • 转换率会跟随不同的 A/B 测试变量发生怎样的变化?

这些问题的答案会直接影响到产品的改进,它们可以从 PostgreSQL 数据库中找到答案,因为现在它支持 lateral 联合.

没有 lateral 联合,我们就只能借助 PL/pgSQL 来做这些分析。或者,如果我们的数据集很小,我们可能就不会碰这些复杂、低效的查询. 在一项探索性数据研究使用场景下,你可能只是将数据从 PostgreSQL 里面抽取出来,并使用你所选择的脚本语言来对其进行分析。但是其实还存在更强大的理由来用SQL表述这些问题, 特别是如果你正想要把整个全封装到一套易于理解的UI中,并向非技术型用户发布功能 的时候.

注意这些查询可以被优化,以变得更加高效. 在本例中,如果我们在 (user_id, (data->>'type'), time)上创建一个btree索引, 我们只用一次索引查找就能针对每一个用户计算每一个渠道步骤. 如果你使用的是SSD,在上面做查找花费是很小的,那这就足够了。而如果不是,你就可能需要用稍微不同的手段来图示化你的数据,详细的内容我会留到另外一篇文章之中进行介绍.

时间: 2015-04-18

Windows下Postgresql数据库的下载与配置方法

注意下载的是二进制版,不是带Windows Installer的. http://www.enterprisedb.com/products-services-training/pgbindownload x86下载http://get.enterprisedb.com/postgresql/postgresql-9.2.4-1-windows-binaries.zip x64下载http://get.enterprisedb.com/postgresql/postgresql-9.2.4-1-

用PostgreSQL数据库做地理位置app应用

项目中用到了postgreSQL中的earthdistance()函数功能计算地球上两点之间的距离,中文的资料太少了,我找到了一篇 英文的.讲的很好的文章  ,特此翻译,希望能够帮助到以后用到earthdistance的同学. 做一个GEO应用从来都不是一件容易的事.但是用一些身边的开源项目就可以在几分钟内轻松解决这个问题. PostgreSQL有许多特性.是我的首选,它能够把数据库平台提升到另一个层次. 一.两种可用的选择 当我们想用Postgres作为GEO函数使用时,我们通常有2中选择(据

15个postgresql数据库实用命令分享

最初是想找postgresql数据库占用空间命令发现的这篇blog,发现其中提供的几 条命令很有用(但也有几条感觉是充数的=.=),于是就把它翻译过来了.另外这篇文章是09年的,所以里面的内容可能有点过时,我收集了原文中有用的评论放在了最后面. 现在有不少开源软件都在使用postgreSQL作为它们的数据库系统.但公司可能不会招一些全职的postgreSQL DBA来维护它(piglei: 在国内基本也找不到).而会让一些比如说Oracle DBA.Linux系统管理员或者程序员去 维护.在这篇

mysql数据库备份命令分享(mysql压缩数据库备份)

备份MySQL数据库的命令 复制代码 代码如下: mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 备份MySQL数据库为带删除表的格式 备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库. 复制代码 代码如下: mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile

Oracle数据库常用命令整理(实用方法)

这篇文章主要介绍了oracle查询语句,有助于新手迅速熟悉ORACLE基本语法有助于新手迅速熟悉ORACLE基本语法,需要的朋友可以收藏下 oracle查看用户状态 select username,account_status from dba_users; 查看所有角色 select * from dba_roles; 锁定用户 alter user XXX account lock; Linux下新建用户 useradd -d /home/XXX -s /usr/bin/ksh -m XXX

PostgreSQL数据库事务实现方法分析

本文实例讲述了PostgreSQL数据库事务实现方法.分享给大家供大家参考,具体如下: 事务简介 事务管理器:有限状态机 日志管理器 CLOG:事务的执行结果 XLOG:undo/redo日志 锁管理器:实现并发控制,读阶段采用MVCC,写阶段采用锁控制实现不同的隔离级别 事务是所有数据库系统的一个基本概念. 一次事务的要点就是它把多个步骤捆绑成了一个单一的,不成功则成仁的操作. 其它并发的事务是看不到在这些步骤之间的中间状态的,并且如果发生了一些问题, 导致该事务无法完成,那么所有这些步骤都完

PHP连接及操作PostgreSQL数据库的方法详解

本文实例讲述了PHP连接及操作PostgreSQL数据库的方法.分享给大家供大家参考,具体如下: PostgreSQL扩展在默认情况下在最新版本的PHP 5.3.x中是启用的. 可以在编译时使用--without-pgsql来禁用它.仍然可以使用yum命令来安装PHP-PostgreSQL接口: yum install php-pgsql 在开始使用PHP连接PostgreSQL接口之前,请先在PostgreSQL安装目录中找到pg_hba.conf文件,并添加以下行: # IPv4 local

Debian中PostgreSQL数据库安装配置实例

用惯了MySQL后,想尝试使用下PostgreSQL,据说这玩意儿也很强大,我在网上搜索了下相关中文书籍,可惜相关书籍远远没有MySQL那么多,不过我在官方找到了由志愿者翻译的文档(参考9.1文档翻译项目),说实话我英文文档阅读能力还是比较纠结的,所以有中文文档当然优先中文了. 在Debian下可以通过apt-get命令直接安装: 复制代码 代码如下: sudo apt-get install postgresql postgresql-client postgresql-server-dev-

如何使用 Rails 和七牛云存储,在 15 分钟内打造一个图片分享社交应用原型

十年前,Web 应用框架 Rails 创始人 David Heinemeier Hansson 曾录制视频,向我们演示如何使用 Ruby on Rails 在 15 分钟内创作一个 blog 引擎.这个视频通过 Rails 优秀的 MVC .习惯优于配置(Convention over Configuration)等设计,以及强大的代码生成.scaffold 等功能,成功展示了 Ruby on Rails 编写 Web 应用核心功能的高效简洁.Ruby on Rails 这门技术也在 Web 2

php连接与操作PostgreSQL数据库的方法

本文实例讲述了php连接与操作PostgreSQL数据库的方法.分享给大家供大家参考. 具体实现方法如下: 复制代码 代码如下: $pg=@pg_connect("host=localhost user=postgres password=sa dbname=employes") or die("can't connect to database."); $query="select * from employes order by serial_no&q

Python使用PyGreSQL操作PostgreSQL数据库教程

PostgreSQL是一款功能强大的开源关系型数据库,本文使用python实现了对开源数据库PostgreSQL的常用操作,其开发过程简介如下: 一.环境信息: 1.操作系统: RedHat Enterprise Linux 4         Windows XP SP2 2.数据库: PostgreSQL8.3 3. 开发工具: Eclipse+Pydev+python2.6+PyGreSQL(提供pg模块) 4.说明: a.PostgreSQL数据库运行于RedHat Linux上,Win