精通SQL连接和窗口函数

I’m happy to translate the article for you, but I’ll need the full text you’d like translated. Could you please paste the content (excluding the source line you’ve already provided) here? Once I have the article, I’ll translate it into Simplified Chinese while preserving the formatting, markdown, and any code blocks.

Source: …

SQL 联接

JOIN 将两个或多个表中的行基于它们之间的相关列进行组合。联接的作用包括：

• 检索分布在多个表中的关联数据。
• 使用公共列匹配记录。
• 通过合并相关信息提升数据分析效果。
• 从独立表中创建有意义的结果集。

示例表

Customers

Orders

1. INNER JOIN

检索在 两个 表中都存在匹配值的行。它基于相关列合并记录。

语法

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM Customers AS c
INNER JOIN Orders AS o
    ON c.customer_id = o.customer_id;

结果

注意： 不带限定符的 JOIN 等同于 INNER JOIN。

2. LEFT JOIN（左外联接）

返回左表的 所有 行以及右表中匹配的行。如果没有匹配，右侧列的值为 NULL。

语法

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM Customers AS c
LEFT JOIN Orders AS o
    ON c.customer_id = o.customer_id;

结果

3. RIGHT JOIN（右外联接）

返回右表的 所有 行以及左表中匹配的行。由于可以通过交换表顺序使用 LEFT JOIN 实现相同效果，这种用法较少见。

4. FULL OUTER JOIN

返回 两个 表的所有行。当某一侧没有匹配时，缺失的列用 NULL 填充。

语法

SELECT 
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM Customers AS c
FULL JOIN Orders AS o
    ON c.customer_id = o.customer_id;

何时使用连接

使用连接的情况：

• 您需要合并相关数据集。
• 您的数据库已规范化。
• 您想要更丰富或关系型视图的数据。

窗口函数

SQL 窗口函数允许在与当前行相关的一组行上进行计算，而不会将结果折叠为单行聚合。它们常用于聚合、排名和累计总计。

基本语法

FUNCTION_NAME() OVER (
    PARTITION BY ...
    ORDER BY ...
)

OVER 子句定义了用于计算的“窗口”行范围：

• PARTITION BY – 将数据划分为组（分区）。
• ORDER BY – 指定每个分区内行的顺序。

示例数据集

窗口函数类型

1. 聚合窗口函数

这些函数在保留单行的同时，对窗口内进行聚合。常用函数：

• SUM() – 求和值。
• AVG() – 求平均值。
• COUNT() – 行数计数。
• MAX() – 最大值。
• MIN() – 最小值。

示例 – 按地区平均值

SELECT
    sale_id,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_region_sales
FROM Sales;

结果

2. 排名窗口函数

这些函数根据指定顺序为分区内的每行分配排名。

RANK()

分配排名；并列的行获得相同的排名，随后排名会被跳过。

SELECT
    sale_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS sales_rank
FROM Sales;

DENSE_RANK()

与 RANK() 类似，但在并列后不跳过排名。

SELECT
    sale_id,
    region,
    amount,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS sales_dense_rank
FROM Sales;

ROW_NUMBER()

为分区内的每行提供唯一的顺序号，忽略并列情况。

SELECT
    sale_id,
    region,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS row_num
FROM Sales;

3. 分析（非聚合）窗口函数

这些函数执行引用其他行的计算，但不对它们进行聚合。

• LEAD() / LAG() – 访问下一行/前一行的值。
• FIRST_VALUE() / LAST_VALUE() – 返回窗口中的首个/最后一个值。
• NTILE() – 将行分配到指定数量的桶中。

示例 – 累计总和

SELECT
    sale_id,
    region,
    amount,
    SUM(amount) OVER (
        PARTITION BY region
        ORDER BY sale_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM Sales;

摘要

• 连接 根据关系合并多个表的行。根据需要保留的行，选择合适的连接类型（INNER、LEFT、RIGHT、FULL）。
• 窗口函数 让您在仍然看到完整结果集的情况下，对每行计算聚合、排名和其他分析。它们是高级报表、时间序列分析和数据科学流水线的关键。

将两者结合使用，构建丰富且高性能的查询，将原始关系数据转化为可操作的洞察。

SQL 中的排名函数

RANK()

为每个分区内的行分配排名，在出现并列时会跳过数字。

SELECT
    sale_id,
    region,
    amount,
    RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS rank_in_region
FROM Sales;

结果

DENSE_RANK()

为每个分区内的行分配排名，即使出现并列也不会跳过数字。

SELECT
    sale_id,
    region,
    amount,
    DENSE_RANK() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS dense_rank_in_region
FROM Sales;

结果

ROW_NUMBER()

为结果集中的每一行提供唯一的顺序编号。

SELECT
    sale_id,
    region,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY region
        ORDER BY amount DESC
    ) AS row_number_in_region
FROM Sales;

结果

Tips for Using Window Functions

• 仔细分区 – 如果没有 PARTITION BY，整个表会被视为一个单一组。
• 检查 ORDER BY – 它决定了窗口内部的计算顺序。
• 优化性能 – 窗口函数在大数据集上可能较慢；考虑添加适当的索引。

结论

• Joins 将来自多个表的数据汇聚在一起，形成完整且有意义的数据集。它们是关系型数据库的基石，对分析、报表、后端系统以及数据工程工作流至关重要。
• Window Functions 让你在不压缩数据（如 GROUP BY 那样）的情况下，对相关行执行高级计算。它们在保留每一行的同时，添加诸如排名、累计总和、比较和百分位等洞察。