理解 SQL 中的连接和窗口函数

Source: Dev.to

介绍

在本文中，我们将探讨 SQL 中两项最强大且最常用的功能：JOIN 和窗口函数。
我们将先定义每个概念，然后通过实际示例展示它们在真实场景中 何时、何地、为何 使用。

Source: …

1️⃣ JOINs

JOIN 是一种子句，用于根据相关列将两个或多个表的行合并。
JOIN 的目的是检索分布在多个表中的数据，并将其呈现为单一、统一的结果集。

示例场景：
可以将 orders 表和 customers 表进行 JOIN，以回答诸如 “哪个客户下了每笔订单？” 之类的问题。

Joins 类型

1.1 INNER JOIN

-- INNER JOIN example
SELECT  c.first_name,
        c.last_name
FROM    article.customers   AS c
INNER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

结果： 仅返回在 customers 和 orders 两个表中都有匹配 customer_id 的记录。

1.2 LEFT JOIN

-- LEFT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
LEFT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

结果： 返回 所有 客户。如果某个客户从未下单，order_date（以及其他订单列）将为 NULL。
左连接也称为 left outer join。

1.3 RIGHT JOIN

-- RIGHT JOIN example
SELECT  c.first_name,
        c.last_name,
        o.order_date
FROM    article.customers   AS c
RIGHT JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

结果： 返回 所有 订单。对于没有匹配客户的订单，其客户列将为 NULL。
右连接也称为 right outer join。

1.4 FULL OUTER JOIN

-- FULL OUTER JOIN example
SELECT  c.first_name,
        c.last_name,
        c.email,
        c.phone_number,
        o.order_id,
        o.order_date,
        o.book_id
FROM    article.customers   AS c
FULL OUTER JOIN article.orders   AS o
        ON c.customer_id = o.customer_id;

结果： 返回 两个表的每一行。如果不存在匹配，则缺失侧的列填充为 NULL。

2️⃣ 窗口函数

窗口函数在与当前行相关的一组行上执行计算，而不会将这些行折叠为单个输出值。
与 SUM()、COUNT() 等聚合函数不同，后者会把多行合并为一行，窗口函数会为原始结果集中的每一行返回一个值。

语法围绕 OVER() 子句构建：

SELECT  <columns>,
        <window_function>() OVER (
            PARTITION BY <partition_expression>
            ORDER BY <order_expression>
            ROWS BETWEEN <frame_start> AND <frame_end>
        ) AS <alias>
FROM    <table>;

关键组件

窗口函数的类别

1. 聚合窗口函数 – 在窗口上计算聚合（例如 SUM()、AVG()），同时保留每一行。
2. 排名窗口函数 – 在分区内分配排名或行号（例如 RANK()、DENSE_RANK()、ROW_NUMBER()）。
3. 值窗口函数 – 从其他行返回值（例如 LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()）。

3️⃣ 示例：在部门内按工资对员工进行排名

假设我们有一个 employees 表：

3.1 使用 RANK()

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rank_by_salary
FROM    article.employees;

它的作用

• 按 department 对行进行分区。
• 在每个部门内部，按 salary 降序排列员工。
• 为每行分配一个排名（1 = 最高工资）。
• 如果两名员工的工资相同，则它们获得相同的排名，随后会跳过相应的名次（RANK() 的标准行为）。

结果（摘录）

每个部门的排名会重新开始。

4️⃣ 示例：每个部门的平均工资（聚合窗口函数）

SELECT  employee_id,
        first_name,
        last_name,
        department,
        salary,
        AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM    article.employees;

它的作用

• 计算 每个部门内部 的平均工资。
• 平均值会出现在 每一行 上，便于直接将个人工资与所在部门的平均工资进行比较。

结果（摘录）

5️⃣ 进一步阅读

• Window Functions – 综合指南：
• SQL JOIN Types – 详细比较：

从此以后，您可以尝试其他窗口函数（例如 LEAD()、LAG()、FIRST_VALUE()），以进行高级分析，而无需嵌套子查询。

您现在已经了解了连接和窗口函数是什么、可用的不同类型、它们的工作原理，以及如何编写简洁、高效的 SQL 查询来实现预期结果。掌握这些工具将显著提升您精准分析、转换和检索数据的能力。今天就开始在自己的项目中应用它们吧；练习得越多，使用起来就会越自然。

查询愉快！