了解常见表达式 (CTE) 和窗口函数
SQL是每个数据专业人员的基本技能。无论您是数据分析师、数据科学家还是数据工程师，都需要对如何编写干净高效的SQL查询有扎实的理解。
这是因为在任何严格的数据分析或复杂的机器学习模型背后都是底层数据，而这些数据必须来自某个地方。
希望在阅读了我关于SQL的入门博客文章后，您已经了解到SQL代表结构化查询语言，它是一种用于从关系数据库中检索数据的语言。
在那篇博客文章中，我们学习了一些基本的SQL命令，比如SELECT，FROM和WHERE，这些命令应该涵盖了使用SQL时遇到的大多数基本查询。
但是如果这些简单的命令不足够呢？如果您想要的数据需要更强大的查询方法怎么办？
那么，请您放心，因为今天我们将介绍两种可以为您的工具包增加的新的SQL技术，这些技术称为常见表达式 (CTE) 和窗口函数。
为了帮助我们学习这些技术，我们将使用一个名为DB Fiddle的在线SQL编辑器（设置为SQLite v3.39）和从Google Cloud获取的出租车行程持续时间数据集（纽约市开放数据许可证）。
数据准备
如果您对我如何准备数据集不感兴趣，请随意跳过此部分，并将以下代码粘贴到DB Fiddle上以生成模式。
CREATE TABLE taxi ( id varchar, vendor_id integer, pickup_datetime datetime, dropoff_datetime datetime, trip_seconds integer, distance float);INSERT INTO taxi VALUES('id2875421', 2, '2016-03-14 17:24:55', '2016-03-14 17:32:30', 455, 0.93), ('id2377394', 1, '2016-06-12 00:43:35', '2016-06-12 00:54:38', 663, 1.12), ('id3858529', 2, '2016-01-19 11:35:24', '2016-01-19 12:10:48', 2124, 3.97), ('id3504673', 2, '2016-04-06 19:32:31', '2016-04-06 19:39:40', 429, 0.92), ('id2181028', 2, '2016-03-26 13:30:55', '2016-03-26 13:38:10', 435, 0.74), ('id0801584', 2, '2016-01-30 22:01:40', '2016-01-30 22:09:03', 443, 0.68), ('id1813257', 1, '2016-06-17 22:34:59', '2016-06-17 22:40:40', 341, 0.82), ('id1324603', 2, '2016-05-21 07:54:58', '2016-05-21 08:20:49', 1551, 3.55), ('id1301050', 1, '2016-05-27 23:12:23', '2016-05-27 23:16:38', 255, 0.82), ('id0012891', 2, '2016-03-10 21:45:01', '2016-03-10 22:05:26', 1225, 3.19), ('id1436371', 2, '2016-05-10 22:08:41', '2016-05-10 22:29:55', 1274, 2.37), ('id1299289', 2, '2016-05-15 11:16:11', '2016-05-15 11:34:59', 1128, 2.35), ('id1187965', 2, '2016-02-19 09:52:46', '2016-02-19 10:11:20', 1114, 1.16), ('id0799785', 2, '2016-06-01 20:58:29', '2016-06-01 21:02:49', 260, 0.62), ('id2900608', 2, '2016-05-27 00:43:36', '2016-05-27 01:07:10', 1414, 3.97), ('id3319787', 1, '2016-05-16 15:29:02', '2016-05-16 15:32:33', 211, 0.41), ('id3379579', 2, '2016-04-11 17:29:50', '2016-04-11 18:08:26', 2316, 2.13), ('id1154431', 1, '2016-04-14 08:48:26', '2016-04-14 09:00:37', 731, 1.58), ('id3552682', 1, '2016-06-27 09:55:13', '2016-06-27 10:17:10', 1317, 2.86), ('id3390316', 2, '2016-06-05 13:47:23', '2016-06-05 13:51:34', 251, 0.81), ('id2070428', 1, '2016-02-28 02:23:02', '2016-02-28 02:31:08', 486, 1.56), ('id0809232', 2, '2016-04-01 12:12:25', '2016-04-01 12:23:17', 652, 1.07), ('id2352683', 1, '2016-04-09 03:34:27', '2016-04-09 03:41:30', 423, 1.29), ('id1603037', 1, '2016-06-25 10:36:26', '2016-06-25 10:55:49', 1163, 3.03), ('id3321406', 2, '2016-06-03 08:15:05', '2016-06-03 08:56:30', 2485, 12.82), ('id0129640', 2, '2016-02-14 13:27:56', '2016-02-14 13:49:19', 1283, 2.84), ('id3587298', 1, '2016-02-27 21:56:01', '2016-02-27 22:14:51', 1130, 3.77), ('id2104175', 1, '2016-06-20 23:07:16', '2016-06-20 23:18:50', 694, 2.33), ('id3973319', 2, '2016-06-13 21:57:27', '2016-06-13 22:12:19', 892, 1.57), ('id1410897', 1, '2016-03-23 14:10:39', '2016-03-23 14:49:30', 2331, 6.18);在运行SELECT * from taxi之后，您应该得到一个如下所示的结果表。
对于那些好奇这个表是如何生成的人来说，我将训练数据筛选为前30行，并只保留了您在上面看到的列。至于距离字段，我计算了乘客上车点和下车点之间的正交距离（纬度和经度）。
正交距离是两点之间的最短距离，因此实际上低估了出租车实际行驶的距离。然而，对于我们今天的目的，我们可以暂时忽略这一点。
计算正交距离的公式可以在这里找到。现在，回到SQL。
公共表达式（CTE）
公共表达式（CTE）是您在查询中返回的临时表。您可以将其视为查询中的查询。它们不仅有助于将查询分割为更可读的块，还可以基于已定义的CTE编写新的查询。
为了证明这一点，假设我们想要分析按天的小时拆分的出租车行程，并过滤到发生在2016年1月至3月之间的行程。
SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, trip_seconds, distanceFROM taxiWHERE pickup_datetime > '2016-01-01' AND pickup_datetime < '2016-04-01'ORDER BY hour_of_day;
足够简单；让我们再进一步。
现在假设我们想要计算每个小时的行程数量和平均速度。这就是我们可以利用CTE的地方，首先获得一个类似上面观察到的临时表，然后通过后续查询来计算每天的行程数量和平均速度。
您可以使用WITH和AS语句来定义CTE。
WITH relevantrides AS(SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, trip_seconds, distanceFROM taxiWHERE pickup_datetime > '2016-01-01' AND pickup_datetime < '2016-04-01'ORDER BY hour_of_day)SELECT hour_of_day, COUNT(1) as num_trips, ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) as avg_speedFROM relevantridesGROUP BY hour_of_dayORDER BY hour_of_day;
<p使用CTE的替代方法是简单地将临时表包装在FROM语句中（参见下面的代码），这将给出相同的结果。然而，从代码可读性的角度来看，这是不可取的。此外，想象一下如果我们想创建不止一个临时表的情况。
SELECT hour_of_day, COUNT(1) as num_trips, ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) as avg_speedFROM ( SELECT CAST(STRFTIME('%H', pickup_datetime) AS INT) AS hour_of_day, trip_seconds, distance FROM taxi WHERE pickup_datetime > '2016-01-01' AND pickup_datetime < '2016-04-01' ORDER BY hour_of_day)GROUP BY hour_of_dayORDER BY hour_of_day;额外奖励：从这个练习中我们可以得出一个有趣的观点，那就是出租车在高峰小时会行驶得更慢（平均速度较低），这很可能是由于交通繁忙，人们上下班的原因。
窗口函数
窗口函数对一组行执行聚合操作，但对原始表中的每一行都会生成一个结果。
要完全理解窗口函数的工作原理，首先需要简要回顾通过GROUP BY进行聚合的方法。
假设我们希望使用出租车数据集按月份计算一系列汇总统计信息。
SELECT CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month, COUNT(1) AS trip_count, ROUND(SUM(distance), 3) AS total_distance, ROUND(AVG(distance), 3) AS avg_distance, MIN(distance) AS min_distance, MAX(distance) AS max_distanceFROM taxiGROUP BY month;
在上面的示例中，我们计算了数据集中每个月份的行程数、总距离、平均距离、最小距离和最大距离。请注意，原始的出租车表有30行，现在已经被合并为六行，每个月份一行。
那么实际上背后发生了什么呢？首先，SQL根据月份将原始表中的所有30行分组。然后，它根据这些单独的分组中的值应用相关的计算。
让我们以1月份为例。数据集中有两次在1月份发生的行程，行程距离分别为3.97和0.68。然后，SQL根据这两个值计算了计数、总和、平均值、最小值和最大值。该过程然后在其他月份中重复，直到最终得到上面的输出结果。
现在，保持这个思路，我们开始探索窗口函数的工作原理。窗口函数可以分为三大类：聚合函数、排序函数和导航函数。我们将看一些示例。
聚合函数
我们在之前的示例中已经看到了聚合函数的应用。聚合函数包括count、sum、average、minimum和maximum等函数。
但是，窗口函数与GROUP BY的区别在于最终输出中的行数。具体而言，我们看到在按月份聚合后，输出表只剩下了六行（每个不同月份一行）。
而窗口函数不会通过聚合字段对表进行汇总，而是简单地将结果作为一个新列输出给每一行。输出表的行数不会改变。换句话说，输出表的行数始终与原始表相同。
执行窗口函数的语法是OVER(PARTITION BY ...)。您可以将其视为我们之前示例中的GROUP BY语句。
让我们看看实际应用中的效果。
WITH aggregate AS(SELECT id, pickup_datetime, CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month, distanceFROM taxi)SELECT *, COUNT(1) OVER(PARTITION BY month) AS trip_count, ROUND(SUM(distance) OVER(PARTITION BY month), 3) AS total_month_distance, ROUND(AVG(distance) OVER(PARTITION BY month), 3) AS avg_month_distance, MIN(distance) OVER(PARTITION BY month) AS min_month_distance, MAX(distance) OVER(PARTITION BY month) AS max_month_distanceFROM aggregate;
在这里，我们希望得到与上次相同的输出，但是不是折叠表，而是将结果作为单独的行显示在新列中。
您会注意到聚合后的值并没有发生变化，而只是在表中以重复的行形式显示。例如，前两行（1月份）的行程数、总月份距离、平均月份距离、最小月份距离和最大月份距离与之前相同。其他月份也是如此。
如果你想知道窗口函数有什么用处，它可以帮助我们将每一行的值与聚合值进行比较。在这个例子中，我们可以轻松地将每一行的行驶距离与每月的平均值、最小值和最大值进行比较。
排名函数
另一种类型的窗口函数是排名函数。顾名思义，它根据聚合字段对一组行进行排名。
WITH ranking AS(SELECT id, pickup_datetime, CAST(STRFTIME('%m', pickup_datetime) AS INT) AS month, distanceFROM taxi)SELECT *, RANK() OVER(ORDER BY distance DESC) AS overall_rank, RANK() OVER(PARTITION BY month ORDER BY distance DESC) AS month_rankFROM rankingORDER BY pickup_datetime;
在上面的例子中，我们有两个排名列：一个是整体排名（从1到30），另一个是每月排名，两者均为降序排列。
要指定排名顺序，你需要在OVER语句中使用ORDER BY。
对于第一行的结果解释如下：它是整个数据集中行驶距离第三长的行程，也是1月份行驶距离最长的行程。
导航函数
最后，我们有导航函数。
导航函数基于当前行的不同行中的值来赋值。一些常见的导航函数包括FIRST_VALUE、LAST_VALUE、LEAD和LAG。
SELECT id, pickup_datetime, distance, LAG(distance) OVER(ORDER BY pickup_datetime) AS prev_distance, LEAD(distance) OVER(ORDER BY pickup_datetime) AS next_distanceFROM taxiORDER BY pickup_datetime;
在上面的例子中，我们使用LAG函数返回前一行的值，使用LEAD函数返回后一行的值。注意，lag列的第一行为null，而lead列的最后一行为null。
SELECT id, pickup_datetime, distance, LAG(distance, 2) OVER(ORDER BY pickup_datetime) AS prev_distance, LEAD(distance, 2) OVER(ORDER BY pickup_datetime) AS next_distanceFROM taxiORDER BY pickup_datetime;
类似地，我们还可以对LEAD和LAG函数进行偏移，即从特定的索引或位置开始。当偏移量设置为2时，你会发现lag列的前两行为null，lead列的最后两行为null。
我希望这篇博客能帮助你了解公共表达式（CTE）和窗口函数的概念。
简而言之，CTE是一个临时表格或者一个嵌套在查询中的查询。它们用于将查询拆分成更易读的部分，并且可以对定义的CTE编写新的查询。另一方面，窗口函数对一组行进行聚合，并为原始表中的每一行返回结果。
如果你希望在这些技术上有所提升，我强烈鼓励你在工作中、解决面试问题或者玩弄随机数据集时开始实践它们。熟能生巧，是吧？
通过以下链接注册小猪AI会员来支持我和其他优秀的作者。祝你学习愉快！
使用我的推荐链接加入小猪AI – Jason Chong
作为小猪AI会员，你的会费的一部分将用于支持你阅读的作者，并且你可以完全访问每个故事…
chongjason.medium.com
不知道接下来读什么？以下是一些建议。
每个数据分析师都需要了解的10个最重要的SQL命令
从数据库查询数据并不需要很复杂
towardsdatascience.com
用示例清晰解释正则表达式
当处理字符串时，任何数据分析师都应该具备的最低估技能之一
towardsdatascience.com
可能使你的数据科学项目成功或失败的常见问题
关于发现数据问题、为什么它们可能具有负面影响以及如何正确解决它们的有益指南
towardsdatascience.com