公共表表达式（Common Table Expression，CTE）和窗口函数（Window Functions）

摘要：

SQL 就像是数据专业人士的面包和黄油。无论你是数据分析师，数据科学家，亦或是数据工程师，你都需要对如何写干净高效的 SQL 查询有一个扎实的理解。这是因为任何严格数据分析或任何复杂机器学习模型的背后都是底层数据本身，而这些数据必然来自某个地方。

在阅读这篇文章之前，希望你已经对 SQL 已经有一个基础了解。SQL 表示结构查询语言（Structured Query Language，SQL），用于从关系数据库中检索数据。希望你已经用过了一些基础 SQL 命令，比如，SELECT，FROM，WHERE，这些最基础的查询子句。

但是如果这些简单的命令还不够，或者如果你想要的数据需要更强大的更健壮的查询方法的话，该怎么办呢？

Well，如果你也在面临这样的困境和需求，那么恭喜你来对地方了。因为今天我们将介绍两种新的 SQL 技术，你可以添加到你的工具包中，将你的查询提升到下一个层次。这两种技术叫做公共表表达式（Common Table Expression，CTE）和窗口函数（Window Functions）。

一、数据准备（Data Preparation）

CREATE TABLE taxi (
  id VARCHAR(50),
  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);

你可以将以上代码拷贝到你本地或任何可执行 SQL 查询的数据库环境（我的环境是 MySQL 数据库）。

在执行 SELECT * FROM taxi 命令之后，你应该可以得到下面这样的结果：

对于那些想知道这个表格是怎么来的热心人，我筛选了前30行的数据，并且只保留了你上面看到的列。至于距离字段，我计算了上车和下车坐标（纬度和经度）之间的正畸距离（orthodromic distance）。

正畸距离是球面上两点之间的最短距离，所以这实际上是行驶中出租车实际距离的估计。然而，这并非本文我们讨论的内容，现在，回到我们的主题 SQL 本身。

二、公共表表达式（CTE）

公共表表达式（Common Table Expression，CTE）就是一张返回查询结果集的临时表，你可以将其看作查询中的查询（嵌套查询）。它不仅可以帮助你将你的查询分为多个可读块，同时你还能基于已定义的 CTE 编写新的查询。

为了证明这一点，假设我们想要分析按照一天中的小时拆分的出租车行程，并筛选在2016年1月至3月之间发生的行程。

SELECT HOUR(pickup_datetime) AS hour_of_day,
       trip_seconds,
       distance
FROM taxi
WHERE pickup_datetime BETWEEN '2016-01-01' AND '2016-03-31'
ORDER BY hour_of_day;

很直截了当对吧；让我们继续。

假设现在我们想计算每个小时的行程数量和平均速度。这就是我们可以利用公共表达式（CTE）来首先获得类似上面观察到的临时表，然后通过后续查询按每天的小时来统计行程数量和计算平均速度。

定义公共表达式的方式是使用 WITH 和 AS 语句：

WITH relevanttrides AS
         (SELECT HOUR(pickup_datetime) AS hour_of_day,
                 trip_seconds,
                 distance
          FROM taxi
          WHERE pickup_datetime BETWEEN '2016-01-01' AND '2016-03-31'
          ORDER BY hour_of_day)
SELECT hour_of_day,
       COUNT(1)                                              num_trips,
       ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) AS avg_speed
FROM relevanttrides
GROUP BY hour_of_day
ORDER BY hour_of_day;

注：如果你也使用 MySQL 的话请保证版本>=8.0，因为8.0以前的版本不支持公共表表达式。

一个替代使用公共表表达式的方法是简单地在 FROM 语句中包装临时表（参见下面的代码）——即嵌套子查询，这将给您相同的结果。但是，从代码可读性的角度来看，这并不可取。此外，想象一下，如果我们想创建不止一个临时表，那样代码将会显得更加臃肿。

SELECT hour_of_day,
       COUNT(1)                                              num_trips,
       ROUND(3600 * SUM(distance) / SUM(trip_seconds), 2) AS avg_speed
FROM (SELECT HOUR(pickup_datetime) AS hour_of_day,
             trip_seconds,
             distance
      FROM taxi
      WHERE pickup_datetime BETWEEN '2016-01-01' AND '2016-03-31'
      ORDER BY hour_of_day) t
GROUP BY hour_of_day
ORDER BY hour_of_day;

Bonus：我们从这个练习中得到的一个有趣观点是，出租车在高峰时段的平均速度较低，这很可能是上下班高峰期交通拥堵导致的。

三、窗口函数（Window Functions）

窗口函数可以在行分组上执行聚合操作，同时他们会在原始表的每一行都生成一个结果。

为了完整理解窗口函数的工作方式，首先我们有必要通过 group by 来快速回顾一下聚合操作。

假设我们想要使用出租车数据集按照月份计算一个汇总统计列表。

SELECT MONTH(pickup_datetime)  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_distance
FROM taxi
GROUP BY month;

在上面的示例中，我们计算了数据集中每个月的乘车次数、总行驶里程、平均里程、最小和最大行驶距离。请注意，我们原始的30行出租车数据现在已经合并成了六行，分别对应每个月的数据。

实际上，该场景背后实际发生了什么？首先，SQL 根据原始表中的月份对所有30行进行了分组。然后，它基于这些单独分组中的值应用相关的计算。

让我们以一月为例。数据集中有两次在一月份进行的乘车，行驶里程分别为3.97和0.68。SQL 根据这两个值计算了次数、总和、平均值、最小和最大值。然后将这个过程重复适用于其他月份，直到最终我们得到了上述的输出。

现在，请记住这个思路，因为我们将开始探索窗口函数的工作原理。窗口函数可以分为三类：聚合函数（aggregate functions）、排名函数（ranking functions）和导航函数（navigation functions）。我们将对每一类函数进行举例解释。

3.1 聚合函数（aggregate functions）

在前面的示例中我们其实已经看到了聚合函数的使用。聚合函数包括计数（count）、求和（sum）、求平均（average）、最小值（minimum）以及最大值（maximum）这样的函数。

窗口函数与普通的 GROUP BY 的区别在于最终输出中的行数量。具体来说，在前面的示例中，根据月份聚合后，我们可以看到输出表中只剩下6行（每月一行）。然而，窗口函数不会根据聚合字段对表做汇总，而是简单的用一个新列输出每行的结果，输出表中的行数量并不会改变。换句话说，窗口函数的输出表行数始终与原始表保持一致。

执行窗口函数的语法为：OVER (PARTITION BY ...) 。你可以将其看作是前面示例中的 GROUP BY 语句。

让我们来看看实际中它是如如何发挥作用的。

WITH aggregate AS
         (SELECT id,
                 pickup_datetime,
                 MONTH(pickup_datetime) AS month,
                 distance
          FROM 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_distance
FROM aggregate;

这里，我们得到了和上次相同的输出，但不会将表折叠，而是在新列中将输出每一行的聚合结果。

你会注意到，在进行聚合后，这些值并没有改变，而是在表中显示为重复的行。例如，前两行（一月份）的行程次数、总月行程、平均月行程、最小月行程和最大月行程的值与以前相同。其他月份也是一样。

如果您想知道窗口函数有什么用，它可以帮助我们将每行的值与聚合值进行比较。在这种情况下，我们可以轻松地将每行的行驶里程与月平均值、最小值和最大值等进行比较。

3.2 排名函数（ranking functions）

窗口函数的另一种类型就是排名函数。顾名思义，它会基于聚合字段对行分组进行排名。

WITH ranking AS
         (SELECT id,
                 pickup_datetime,
                 MONTH(pickup_datetime) AS month,
                 distance
          FROM taxi)
SELECT *,
       RANK() OVER (ORDER BY distance DESC) AS                 overall_rank,
       RANK() OVER (PARTITION BY month ORDER BY distance DESC) month_rank
FROM ranking
ORDER BY pickup_datetime;

在上面的示例中，我们派生出了两个排名列，一个是总体排名（1-30），另一个是月度排名，两者都以降序排序。如果你想在排名时指定排序方式，则需要在 OVER 语句内部使用 ORDER BY 子句。

如何解读结果呢？以第一行为例，它表示该行程在整个数据集中排第三，而在一月份则拥有最长的行程距离。

3.3 导航函数（navigation functions）

窗口函数的最后一个也是不可或缺的类型是导航函数。

导航函数会基于与当前行不同行中的值来分配一个值。一些常见的导航函数包括 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_distance
FROM taxi
ORDER 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_distance
FROM taxi
ORDER BY pickup_datetime;