让你的 SQL 从优秀走向卓越：第 1 部分

神译局是36氪旗下编译团队，关注科技、商业、职场、生活等领域，重点介绍国外的新技术、新观点、新风向。

编者按：做开发的多少都要懂点SQL。但是只会点皮毛跟精通的差别还是很大的。有很多事情如果用SQL处理的话，会让你的后端开发工作事半功倍。那有哪些堪称神奇的SQL技巧呢？Taylor Brownlow用4篇系列文章总结了四个方面的SQL使用技巧，相信你会用得上的。此为第一部分，原文发表在Medium上，标题是：Take Your SQL from Good to Great: Part 1。

上周我看到了一条推文。这条推文让我陷入了思考：如果时光可以倒流的话，有什么样的SQL技巧是我想自学的呢？就这么一个很简单的问题很快就演变成了一系列的文章。在这四部分的系列文章里面，我会阐述自己最喜欢的一些做法，解释这么做为什么有用，以及怎么用。我选择的主题是：

第 1 部分：通用数据表表达式

第 2 部分：跟日期有关的一切

第 3 部分：不一样的JOIN

第 4 部分：Window函数

你觉得还有哪些SQL方法是被低估的呢？这些方法对你又产生了什么样的影响？请在评论栏给出你的意见！

好吧，我们就进入到第 1 部分：CTE吧！

什么是CTE？

通用表表达式 （Common Table Expressions ，CTE） 是在单一查询中可用的临时表。

CTE的语法是这样的：

WITH cte_name AS

(SELECT ... FROM ... )

SELECT * FROM cte_name;

CTE为什么重要

这种查询看起来也没什么了不起啊，它有什么好处呢？我们来举例说明吧。假设我有两张表：

1、matches：关于网球比赛统计数据的一份大文件

2、players：网球运动员的元数据文件

matches表预览

players表预览

我想知道每一位球员在赢得第一个大满贯时的年龄多大。

为此，我需要从matches表中找出每一位球员第一次赢得大满贯的时间，然后用players表的出生日期跟这个时间进行比较。

解决这个问题可以有很多方法，但主要可以归结成这两种方法：

子查询法与CTE法

虽然这两种方法的代码长度类似，应用的逻辑也相同，但CTE 方法还是有一些明显的优势：

1. 更容易理解。

如果回答这个问题的第一个步骤是要获取每位球员第一次赢得大满贯的时间的话，你要读取的第一个东西不就应该是这个吗？

可如果用子查询的话，想要辨别执行的顺序辨别几乎不可能，因为你得从缩进的最里层开始向上扫描，直到最终扫描到第一行。

CTE 让你用一种完全行得通的方式构建查询，这样受益的不仅是写查询的你，而且还包括其他被迫要对它作出解释的人。

2. 迭代更快。

如果我想知道进入大满贯决赛但最后输掉的最年轻的球员是谁又该怎么办？或者是在任何一场锦标赛而不仅仅是大满贯中赢得决赛的最年长球员是谁呢？

我只需要把grand_slam_matches这个CTE稍微调整一下就能回答这些问题，但用子查询的方法的话我得看是不是对合适的子查询进行了适当的修改。

在我们跟数据进行“对话”的时候，需要对查询进行做这种快速迭代调整的数据分析太多了。一旦查询很复杂的时候，这样的“调整”会膨胀成令人痛苦的任务；但CTE 可以提供一个常识性的结构，让可以快速完成这些调整。

3.验证值得信赖。

就像每一位优秀的分析师一样，我始终都要验证自己的结果，有了 CTE 这件事情就很简单了。因为检查每一个 CTE就可以快速确定验证错误的来源在哪里，并对每一个逻辑步骤进行故障排除，而不是被迫出处理一团糟的子查询。

如果要打分的话，CTE对子查询就是40：0。

CTE的更多用法

在单一查询的层面上CTE确实好处很多，但是如果我们把 CTE 的好处应用到整个分析而不仅仅是一个查询呢？

随着SQL notebooks最近取得的进步，我们开始看到把CTE应用到更大规模场景的可能性。

在这些notebook里面，每以个单元格就代表一个 CTE，而且每个单元格都可以被任何其他单元格引用，实际上，这相当于创建了一个完整的 CTE 连接图。

我们用 Count.co 完成的分析

或者作为一张连接图：

用连接图表示的分析。

可以用来做到的事情也许会令人惊讶。一方面你仍然可以享受到单个 CTE 的好处，比方说在逻辑流当中构建你的分析、进行快速迭代和验证检查，但除此以外，你还可以对查询和文本进行参数化。

其累积效应更像是一个应用而不是查询。

如果我们希望扩展分析，把更多查询包含进去的话，我们可以把更多的单元格链接到一起。如果我们想纳入全局过滤器的话，可以把它们添加到notebook里面，然后像任何其他单元格一样链接进来。

此处可以查看上述连接图的notebook：

此处可以了解有关 SQL notebook的更多信息。

CTE 最佳实践

1、列和 CTE要用唯一的有意义的名称（不要这样用： WITH ' cte ' AS...）；

2、尽量让CTE比较“通用”，这样很容易就能回去对逻辑进行调整。

译者：boxi。