PostgreSQL的新联接类型：横向(2014)

PostgreSQL9.3有了一个新的连接类型！横向联接并没有大张旗鼓地出现，但它们启用了一些功能强大的新查询，这些查询以前只能通过过程代码来处理。在这篇文章中，我将介绍一个转换漏斗分析，这在PostgreSQL9.2中是不可能的。

文档中的最佳描述位于FROM子句选项列表的底部：

横向关键字可以在子SELECT FROM项之前。这允许子选择引用在“发件人”列表中显示在其前面的“发件人”项目的列。(如果没有横向，则每个子选择都是独立评估的，因此不能交叉引用任何其他来源项目。)…。当起始项目包含横向交叉引用时，评估按如下方式进行：对于提供交叉引用列的起始项目的每一行，或提供列的多个起始项目的行集，使用该行或列的行集的值来评估横向项目。结果行将照常与从中计算它们的行联接。对列源表中的每行或每组行重复此操作。

这有点太浓了。大致来说，这意味着横向联接类似于SQL foreach循环，在该循环中，PostgreSQL将迭代结果集中的每一行，并使用该行作为参数计算子查询。

每个事件都与一个用户相关联，并且有一个ID、一个时间戳和一个带有事件属性的JSON blob。在Heap中，这些属性可能包括单击的DOM层次结构、窗口标题、会话引用，等等。

比方说，我们想要优化我们的登录页面以增加注册人数。第一步是找出我们在转换漏斗中用户流失的位置。

我们假设我们已经检测了前端以记录沿此流发生的事件，并且所有数据都位于上面指定的事件表中。[1]作为第一个问题，让我们计算一下有多少人查看了我们的主页，其中有多少人在初次主页查看后的两周内进入了信用卡。如果我们使用的是较旧版本的PostgreSQL，我们可能会用PostgreSQL的内置过程语言PL/pgSQL编写一些自定义函数。但是，在9.3中，我们可以在一个没有扩展或PL/pgSQL的高效查询中使用横向联接来计算这一点。

没有人喜欢30行的SQL查询，所以让我们把它分成几个部分。这其中的第一个部分是普通SQL：

也就是说，获取每个用户执行VIEW_HOMEPAGE事件的初始时间。然后，我们的横向联接允许我们迭代每个结果行，并执行下一个子查询的参数化版本。这等效于接受下面的查询并为每个结果行运行该查询：

即，对于每个用户，获取他或她在VIEW_HOMEWAGE_TIME的两周内第一次执行ENTER_CREDITY_CARD事件的时间。因为这是横向联接，所以我们的子查询可以引用上一个子查询的VIEW_HOMEPAGE_TIME结果。否则，子查询将独立求值，并且在求值另一个子查询时将无法访问另一个子查询的结果。

因为这是一个左连接，所以只要存在VIEW_HOMEPAGE事件，查询仍然会为没有匹配的ENTER_CREDITY_CARD事件的用户生成结果行。如果我们在数字列上进行聚合，我们会得到此转换漏斗的整齐摘要：

我们可以通过更多的横向连接将中间步骤添加到此漏斗中，以评估我们应该重点改进流的哪些部分。[2]让我们在查看主页和输入信用卡之间添加一个USE_DEMO步骤。

这为我们提供了三个步骤的转换漏斗，从查看主页到在一周内使用演示程序，再到在一周内输入信用卡。从这里开始，PostgreSQL的表现力让我们可以深入研究这些结果，并彻底分析我们网站的性能。我们可能会跟进：

通过广告发现我们主页的用户转换的可能性与其他来源的用户相同吗？

这些问题的答案直接适用于产品更改，并且可以在PostgreSQL中确定，因为它支持横向联接。

如果没有横向联接，我们将需要求助于PL/pgSQL来执行此分析。或者，如果我们的数据集很小，我们可以逃脱复杂、低效的查询。在探索性数据科学用例中，您可能只需将数据从PostgreSQL中提取出来，然后使用您选择的脚本语言对其进行分析。但是，能够用SQL表达这些问题有相当大的威力，特别是当您将所有问题都包装在一个易于理解的UI中并向非技术用户公开其功能时更是如此。

请注意，可以调整这些查询以使其非常高效。在本例中，如果我们在(user_id，(data->；>；‘type’)，time)上创建一个btree索引，我们可以使用单个索引查找来评估每个用户的每个漏斗步骤。如果您使用的是SSD，其寻道价格较低，这可能就足够好了。如果没有，您可能需要以不同的方式将您的数据图案化，但我将把这方面的细节留到另一篇文章中介绍。

有最喜欢的PostgreSQL新功能或整齐的横向联接用例吗？PING Me@danlovesfors。

对构建功能强大且易于使用的系统感兴趣吗？给我们发邮件到[email protected]。

[1]或者我们可以使用Heap，它会在我们安装后立即为我们捕获所有内容！不需要编写任何日志代码，也不会有忘记记录稍后要分析的内容的风险。[2]请注意，如果我们使用像Heap这样的产品分析，那么向转换漏斗添加额外的步骤将特别容易，因为我们已经有了相关数据。[3]此查询中具有ENTER_CREDUDE_CARD事件的用户数比上一个查询中的要少，因为此查询仅返回执行USE_DEMO事件后执行此操作的用户的ENTER_CREDUDE_CARD事件，其中17个用户在未使用demo的情况下注册。