您绝对需要了解的 3 个基本 SQL 技巧

当今世界，每个组织都在处理大量数据，SQL（结构化查询语言）成为开发人员、数据分析师和数据库管理员的基本工具。这种强大的语言是有效管理关系数据库中存储的数据并从中提取价值的入口。掌握 SQL 对于任何希望在技术领域有所作为的人来说都至关重要，它提供了深入了解数据和提高数据库性能所需的技能。掌握一些高级技巧可以大大提高你的效率和分析能力。让我们来探讨每个数据爱好者都应该掌握的三种基本 SQL 技巧。

1.通用表表达式（CTE）

在处理复杂的 SQL 查询，尤其是涉及多重连接、子查询或聚合的查询时，可读性和可管理性很快就会变得具有挑战性。这就是通用表表达式（CTE）发挥作用的地方。CTE 允许您创建临时结果集，以便在 SQL 查询中轻松引用。通过将查询分解成更简单、更易读的部分，CTE 不仅能使 SQL 代码更简洁，还能提高可维护性和调试效率。让我们探讨一下使用 CTE 如何将复杂的查询转化为更易于管理的查询。

不使用 CTE

考虑这样一种情况：您需要分析客户订单，找出在过去一年中下订单超过 5 次的客户。如果没有 CTE，您的 SQL 查询可能会如下所示：

SELECT customer_id, COUNT(order_id) AS order_count
FROM (
    SELECT customer_id, order_id
    FROM orders
    WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
) AS yearly_orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

在这个示例中，我们使用子查询首先过滤去年以内的订单，然后计算去年以外每个客户的订单。虽然这种方法行之有效，但当它变得越来越复杂时就会变得棘手。

使用 CTE

现在，让我们使用 CTE 重写查询，以获得更清晰、更有条理的方法：

WITH yearly_orders AS (
    SELECT customer_id, order_id
    FROM orders
    WHERE order_date >= '2023-01-01' AND order_date <= '2023-12-31'
)
SELECT customer_id, COUNT(order_id) AS order_count
FROM yearly_orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;

通过使用 CTE，我们将 yearly_orders 定义为仅包含过去一年订单的临时结果集。然后，我们使用这个结果集来执行聚合和过滤。这不仅使查询更容易阅读和理解，还简化了修改。如果需要调整日期范围或在订单的初始选择中添加更多条件，可以在 CTE 中的一个地方完成，而无需触及查询的其他部分。

CTE 是使 SQL 查询简单明了的好工具。我建议您仔细研究一下，看看它们有多有用。无论是探索数据、创建报表还是清理数据，它们都能为你的工作带来很大的不同。了解更多有关 CTE 的知识可以让你的工作变得更轻松。

2.部分索引

想象一下，你的数据库有一个庞大的表，其中包含数百万个条目，但当你搜索它时，通常只查找其中的一小部分数据。为整个表创建索引会占用大量空间，并降低添加或更改数据的速度。部分索引可以解决这个问题，它只为你经常搜索的部分建立索引，帮助你在不浪费资源的情况下更快地运行查询。

让我们以名为订单的表为例进行说明。如果您经常需要查找仍未处理的订单，那么为每个订单建立索引的效率就会很低。相反，您可以只为这些待处理订单创建部分索引。

CREATE INDEX idx_orders_pending
ON orders (order_date)
WHERE status = 'pending';

如图所示，部分索引是针对具有挂单状态的订单在 order_date 上创建的。该索引尤其适用于快速查找最近的挂单，如上周的挂单。

部分索引是加快数据库中大表搜索速度的一种智能方法。部分索引可以让你只选择数据的一部分来建立索引，而不是对每一行都建立索引，因为这样做可能会矫枉过正，降低速度。

3.条件聚合

条件聚合就像 SQL 查询中的魔术，可以让你在单个查询中根据特定条件对数据执行不同的计算。当你同时研究数据的不同方面时，它尤其有价值，可以让你同时挖掘复杂的问题，保持整洁和直接。

不使用条件聚合

假设您想知道图书和电子产品在销售额中的占比。要想在不使用条件聚合的情况下通过单次查询找出答案，可以尝试类似下面的方法，但这种方法并不标准，而且由于其复杂性和潜在的性能问题，一般不推荐使用：

SELECT 
  (SELECT SUM(amount) FROM sales WHERE category = 'Books') * 100.0 / SUM(amount) AS books,
  (SELECT SUM(amount) FROM sales WHERE category = 'Electronics') * 100.0 / SUM(amount) AS electronics
FROM sales;

使用条件聚合

现在，让我们来看看条件聚合是如何改变游戏规则的。你需要同样的信息：书籍和电子产品占总销售额的比例。但这次，你可以一次性获得所有信息：

SELECT 
    (SUM(CASE WHEN category = 'Books' THEN amount ELSE 0 END) / SUM(amount)) * 100 AS books_percentage,
    (SUM(CASE WHEN category = 'Electronics' THEN amount ELSE 0 END) / SUM(amount)) * 100 AS electronics_percentage
FROM sales;

使用这种方法，您可以在聚合函数（SUM 和 COUNT）中使用 CASE 语句来指定条件。这样，您就可以加快分析速度，使代码更加简洁。条件聚合允许数据库对数据一次性计算所有需要的聚合。这比执行多个子查询更有效，因为每个子查询都可能扫描整个表或其中的一个大型子集。

结论

在我多年的软件开发人员生涯中，我已经掌握了这些 SQL 技巧，它们真正改变了我处理数据的方式。但请记住，并非所有这些技巧都能在你的 RDBMS 或其特定版本中使用，所以最好先检查一下。如果您发现任何有用的 SQL 技巧，也欢迎告诉我。请在下面的评论中与我分享，让我们互相帮助，写出更好、更高效的 SQL 查询！

本文文字及图片出自 3 Essential SQL Tricks You Absolutely Need to Know