我们通常需要处理长长且不可读的SQL语句。有多种方法可以长期提高其可读性和可维护性。让我们从一些示例开始：
- 关键字的大写 - 我们应该编写所有关键字（例如
SELECT，FROM，JOIN），以将它们与查询的其他部分区分开来 - snake_case用于标识符和名称 - 命名您的表
customer_data代替CustomerData（因此使用小写和下划线） - 别名 - 只需在查询中重命名您的列和表即可提高可读性，尤其是当您多次加入同一表格
- 缩进 - 缩进列名称，缩进部分，缩进子征服，缩进
WHERE滤波器 - 使用
JOIN ON代替JOIN+WHERE（甚至这两个是等效的）
我们可能还可以遵循其他最佳实践，尤其是取决于我们在哪里工作和使用哪种软件。但是，所有这些技巧都不能让我们避免重复并使我们的查询大大缩短。
为了解决这个问题，我们可以使用两种不同的方法。从长远来看，它们将使我们的代码更具可读性和更易于维护。
通用表表达式
公共表表达式（CTE），也称为WITH表达式，使我们能够编写辅助语句，以稍后在较大的查询中引用它。这就像仅用于一个查询的临时桌子。 CTE可以包含SELECT，INSERT，UPDATE或DELETE，但是，我们通常将其与SELECT语句一起使用。
让我们看看一个示例。想象一下，我们在给定的地区存储有关订单的数据。
CREATE TABLE orders(region VARCHAR(100), amount INT, product VARCHAR(100));
INSERT INTO orders(region, amount, product)
VALUES ('EU', 10, 'Product1'), ('EU', 20, 'Product2'), ('US', 1, 'XYZ'), ('JP', 10, 'ABC')
我们想展示至少占总销售额至少10％的地区的订单。让我们进行此查询：
SELECT region, product, (SELECT SUM(amount) AS total_sales FROM orders AS o2 WHERE o2.region = o.region GROUP BY region)
FROM orders AS o
WHERE region IN (
SELECT region
FROM orders
GROUP BY region
HAVING SUM(amount) > (
SELECT SUM(amount)
FROM orders
) / 10
)
+---------+------------+-------------+
| region | product | total_sales |
+---------+------------+-------------+
| EU | Product 1 | 30 |
| EU | Product 2 | 30 |
| JP | ABC | 10 |
+---------+------------+-------------+
我们显示该地区销售的地区，产品和产品总数。我们根据给定区域中的订单之和必须至少是所有订单总和的10％的条件。
。但是，我们可以看到此查询不是很简单。它混合了计算和过滤的地方，计算总和多次，并且对我们如何计算阈值提供了清晰的理解。现在，让我们用语句重写：
WITH best_sales_threshold AS (
SELECT SUM(amount) / 10 AS threshold
FROM orders
), sales_by_region AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT DISTINCT(region)
FROM sales_by_region
WHERE total_sales > (SELECT threshold FROM best_sales_threshold)
)
SELECT o.region, o.product, o2.total_sales
FROM orders AS o
JOIN sales_by_region AS o2 ON o2.region = o.region
WHERE o.region in (SELECT region FROM top_regions)
我们首先将阈值计算为带有一列的临时表。接下来，我们计算另一个带有每个区域销售总和的临时表。然后，我们找到表现最好的区域。最后，我们只是呈现数据。
我们可以看到查询更容易读取且易于理解。我们可以看到它不是多次嵌套的，事情是“线性”，我们可以通过遵循查询和有意义的名称来理解发生了什么。
但是，CTE可以让我们在标准SQL中无法实现其他可能性。 CTE可以是递归的，因此临时表可以将其引用以填充内容。让我看到这一点：
WITH RECURSIVE numbers(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;
我们定义一个具有一列的递归CTE。我们将初始值设置为1。接下来，我们使用UNION ALL引入可以参考查询自己的输出的递归术语。因此，我们将所有低于一百的行采用，并创建新的行，值增加了一个。这将生成1到100的数字。
窗口功能
窗口函数可以计算与当前行相关的一组表行之间的值。这与聚合非常相似，但是窗口函数不会将行分组为一行。这些行仍然是独立的，但是它们可以包含组合在一起的值。
让我们以上一节中的示例为例，但忽略了顶部部分：
SELECT region, product, (SELECT SUM(amount) AS total_sales FROM orders AS o2 WHERE o2.region = o.region GROUP BY region)
FROM orders AS o
+---------+------------+-------------+
| region | product | total_sales |
+---------+------------+-------------+
| EU | Product 1 | 30 |
| EU | Product 2 | 30 |
| US | XYZ | 1 |
| JP | ABC | 10 |
+---------+------------+-------------+
因此，我们将特定区域的区域，产品和总销售额进行。使用窗口功能，我们可以执行以下操作：
SELECT region, product, SUM(amount) OVER (PARTITION BY region) AS total_sales
FROM orders AS o
我们简单地指定total_sales列的值应为region列具有与当前行的region值相同的所有行，然后将所有这些值总结。
我们还可以包括订购。可以说，我们想在一个区域内对产品进行排名。我们可以使用以下内容：
SELECT region, product, SUM(amount) OVER (PARTITION BY region) AS total_sales, rank() OVER (PARTITION BY region ORDER BY amount DESC)
FROM orders AS o
我们再次包含来自同一区域的行，但是这次我们也通过减少量订购它们，并使用rank()函数计算值
+---------+------------+--------------+------------+
| region | product | total_sales | sales_rank |
+---------+------------+--------------+------------+
| EU | Product 1 | 30 | 1 |
| EU | Product 2 | 30 | 2 |
| US | XYZ | 1 | 1 |
| JP | ABC | 10 | 1 |
+---------+------------+--------------+------------+
我们可以使用更多功能：
-
FIRST_VALUE-返回分区中第一行的值 -
LAST_VALUE-返回分区中最后一行的值 -
LAG-返回分区中的行之前的行 -
LEAD-返回分区中的行之后的行 -
NTH_VALUE-返回分区中n -th行的值
我们还可以使用常规聚合功能，例如SUM，MIN，MAX等
实际例子
让我们现在查看一些实际示例。
从组中选择n-then元素
让我们说我们想分组行并在每个组中选择前三行：
WITH numbered_orders AS (
SELECT region, product, rank() OVER (PARTITION BY region ORDER BY amount DESC) AS rank
FROM orders AS o
)
SELECT *
FROM numbered_orders
WHERE rank < 4
我们根据其等级使用CTE在给定区域中的编号订单。然后，我们选择等级小于4的行。
运行总和
让我们说我们要计算运行总和，因此将当前行之前所有行的给定列的值总和：
SELECT region, product, SUM(amount) OVER (PARTITION BY region ORDER BY amount) AS running_sum
FROM orders AS o
相邻行之间的区别
我们要说的是，我们想通过产品的性能要比前面的产品更好。我们可以这样做：
SELECT region, product, amount, amount - LAG(amount, 1) OVER (PARTITION BY region ORDER BY amount)
FROM orders AS o
因此，我们采用当前数量并减去使用滞后函数提前一行的产品的数量。
表现
CTE和窗口功能似乎只带来好处。不一定是这样。让我们进行这两个查询：
WITH cte_performance AS (
SELECT *, MD5(MD5(ticket_no)) AS double_hash
FROM boarding_passes
)
SELECT COUNT(*)
FROM cte_performance AS C1
JOIN cte_performance AS C2 ON C2.ticket_no = C1.ticket_no
JOIN cte_performance AS C# ON C3.ticket_no = C1.ticket_no
WHERE
C1.double_hash = 'HASH'
AND C2.double_hash = 'HASH'
AND C3.double_hash = 'HASH'
和
SELECT COUNT(*)
FROM boarding_passes AS C1
JOIN boarding_passes AS C2 ON C2.ticket_no = C1.ticket_no
JOIN boarding_passes AS C# ON C3.ticket_no = C1.ticket_no
WHERE
MD5(MD5(C1.ticket_no)) = 'HASH'
AND MD5(MD5(C2.ticket_no)) = 'HASH'
AND MD5(MD5(C3.ticket_no)) = 'HASH'
它们是等效的。 Boarding_passes表总共有800万行。以前的查询需要13秒才能完成，而后者则为8。他们返回相同的结果，但是它们的性能差异很大。
概括
常见的表格表达式和窗口功能使您可以使查询更加可读性和可维护。他们还可以直接在SQL中进行一些查询。探索它们在特定情况下是否提高可读性总是一个好主意。