精通SQL：Joins 与 Window Functions 指南

Source: Dev.to

在数据分析的世界中，SQL 是从关系型数据库中提取洞察的关键语言。虽然基本查询（SELECT、WHERE）相对简单，但 SQL 的真正威力在于对复杂数据关系的操作和分析。这正是 Joins（连接）和 Window Functions（窗口函数）成为不可或缺工具的原因。

理解这两个概念可以将初学者与高级 SQL 使用者区分开来。Joins 帮助你将不同的数据集关联起来，而 Window Functions 让你在不丢失细节的前提下，对这些数据集内部进行分析。

Source: …

SQL 连接：跨表合并数据

关系型数据库被划分为多个较小的表，以防止数据重复（规范化）。例如，customers 表和 orders 表会被分别保存。连接（JOIN）使你能够基于相关列（通常是 Primary‑Key – Foreign‑Key 关系，如 customers.customer_id = orders.customer_id）将这些表合并。

核心连接类型

INNER JOIN

仅返回两张表中都有匹配的行。
使用场景： 查找有效且已关联的数据（例如，属于某个客户的所有订单）。

SELECT
    customers.name,
    orders.order_id,
    orders.amount
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.customer_id;

LEFT JOIN（LEFT OUTER JOIN）

返回左表的所有行以及右表中匹配的行。
如果没有匹配，右侧的列会显示 NULL 值。
使用场景： 保持主列表完整（例如，所有客户，即使他们没有下订单）。

SELECT
    customers.name,
    orders.order_id
FROM customers
LEFT JOIN orders
    ON customers.customer_id = orders.customer_id;

RIGHT JOIN

与 LEFT JOIN 相反；保留右表的所有行。

SELECT
    customers.name,
    orders.order_id
FROM customers
RIGHT JOIN orders
    ON customers.customer_id = orders.customer_id;

FULL OUTER JOIN

只要任一表中有匹配，就返回所有行。它相当于 LEFT JOIN 与 RIGHT JOIN 的结果合并。

SELECT
    customers.name,
    orders.order_id
FROM customers
FULL OUTER JOIN orders
    ON customers.customer_id = orders.customer_id;

窗口函数：上下文感知分析

窗口函数在与当前行相关的一组表行上执行计算。与会把行合并为单个汇总行的 GROUP BY 不同，窗口函数在保持各行完整的同时添加计算列。

语法：OVER()

OVER() 子句定义函数操作的 窗口（数据子集）。

• PARTITION BY – 将行划分为组（例如 PARTITION BY customer_id）。
• ORDER BY – 在每个分区内对行进行排序（例如 ORDER BY amount DESC）。

常用窗口函数

排名函数

示例：按金额从高到低对订单进行排名。

SELECT
    order_id,
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders;

聚合函数

SUM()、AVG()、COUNT()、MAX()、MIN()

示例：随时间累计订单金额的运行总和。

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

值函数

示例：将每个订单与前一个订单进行比较。

SELECT
    order_id,
    amount,
    LAG(amount) OVER (ORDER BY order_id) AS previous_amount
FROM orders;

合并连接和窗口函数

在分析中，通常先连接表以获取完整视图，然后再应用窗口函数来分析趋势。

场景： 列出所有订单及其客户名称，并在每个客户内部按金额对订单进行排名。

SELECT
    customers.name,
    orders.order_id,
    orders.amount,
    RANK() OVER (
        PARTITION BY orders.customer_id
        ORDER BY orders.amount DESC
    ) AS order_rank
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;

连接与窗口函数的区别

• 连接 将两个或多个表关联起来，汇集相关数据。根据连接类型，它们可能会改变结果集中的行数（扩展或缩减）。
• 窗口函数 在单个结果集的行集合上执行计算，而不会合并这些行。它们在保持原始行数不变的同时，添加诸如累计总和、排名或移动平均等计算值。窗口函数通过 OVER() 子句定义，通常配合 PARTITION BY 将行分组进行分析。

结论

通过精通 Joins 与 Window Functions，你可以将零散的原始数据转化为有意义、可操作的洞察——通常只需 minimal code 并具备 maximum flexibility。

……但需要复杂的多步骤子查询。这些工具是从事数据分析、后端开发或数据库管理的任何人的基础。

如果目标是从中级迈向高级 SQL，这是一组你可以学习的最有价值的技能组合之一。