使用CTE(公共表表达式),您可以走得更远。
什么是CTE?
公共表表达式(CTE)是在单个查询中可用的临时表。
它们使用以下语法:
WITH cte_name AS
(SELECT ... FROM ... )
SELECT * FROM cte_name;
为什么CTE很重要?
为了说明这种查询构造为何如此有用,让我们看一个示例。假设有两张表:
- 比赛(matches):网球比赛统计数据
- 球员(players):网球球员元数据
网球比赛表:
网球运动员表:
我想知道每个球员在赢得第一个大满贯决赛时的年龄。
为此,我需要从比赛表中找出每个球员第一次赢得大满贯的时间,然后使用球员表将该日期与他们的出生日期进行比较。
有很多方法可以解决这个问题,但主要归结为两种方法:
虽然两种方法的代码长度相似,使用的逻辑相同,但CTE方法有一些明显的优势:
1. 更容易理解
如果回答问题的步骤1涉及获得每个球员第一次赢得大满贯,那这不应该是你读到的第一件事吗?
使用子查询,执行顺序几乎不可能辨别,因为您被迫扫描最低级别的缩进并向上和周围工作,直到最终到达第一行。
CTE允许您以一种简单的方式构建查询,不仅使您在编写查询时受益,而且更容易解释。
2. 更快速的迭代
如果我想知道最年轻的球员进入大满贯决赛但输了怎么办?或者是在任何锦标赛中赢得决赛的最年长球员,而不仅仅是大满贯?
我可以轻松地调整CTEgrand_slam_matches
来回答这些问题,而不必担心我是否在正确的子查询中进行了更改。
如此多的数据分析要求对查询进行快速迭代。当我们创建复杂的查询时,这些“调整”会成为艰辛的任务;CTE为您提供了一个常识性的结构,可以快速完成这些快速调整。
3. 快速验证
像每个优秀的分析师一样,我会始终验证我的结果,而CTE令验证变得更简单。由于我可以检查每个单独的CTE,因此我可以快速确定错误的来源并排除故障,而不是处理混乱的子查询。
增强CTE
CTE在单个查询中的优势是显而易见的,但如果可以将这种优势扩展到整个分析,而不仅仅是单个查询呢?
随者SQL notebooks的发展,我们看到大规模应用CTE的可能性。
在SQL笔记本中,每个单元格代表一个CTE,并且这些单元格中的每一个都可以被任何其他单元格引用,这就创建了一个完整的CTE连接图。
或者作为连接图:
SQL笔记本大幅增强了CTE,您仍然可以享受单个CTE的好处,例如在逻辑流中构建分析、快速迭代和验证结果,此外它还允许对查询和文本进行参数化。
如果我们想扩展分析以包含更多查询,可以将更多单元格链接在一起。如果我们想包括全局过滤器,可以将它们添加到笔记本中,并像任何其他单元格一样链接它们。
CTE最佳实践
1. 使用唯一且有意义的列和CTE名称(请不要使用 WITH 'cte' AS...)
2. 尽可能使CTE是“通用”的,以便您可以轻松调整逻辑
数据黑客:开源金融数据仓库,专注数据工程和机器学习。
- 作者:Taylor Brownlow
- 来源:Medium
- 原文:Take your SQL from Good to Great: Part 1
- 翻译:数据黑客