SQL 连接和窗口函数

Source: Dev.to

请提供需要翻译的正文内容，我将为您翻译成简体中文。

介绍

SQL 连接允许您使用公共标识符（例如外键）将两个或多个表组合在一起。由于规范化的原因，单个表可能不包含您需要的所有列，因此连接会在表之间“传输”数据，使您能够在结果集中包含来自两个表的列。

示例表格用于说明

学生表

课程表

SQL 连接类型

1️⃣ 内连接

仅返回在两个表中都存在的行。

SELECT name, course_name
FROM Student
INNER JOIN Course
  ON Student.course_id = Course.course_id;

输出

2️⃣ 左连接

返回左表的所有行以及右表中匹配的行。

SELECT name, course_name
FROM Student
LEFT JOIN Course
  ON Student.course_id = Course.course_id;

输出

3️⃣ 右连接

返回右表的所有行以及左表中匹配的行。

SELECT name, course_name
FROM Student
RIGHT JOIN Course
  ON Student.course_id = Course.course_id;

输出

4️⃣ 全连接

返回两个表的所有行，在可能的情况下进行匹配。

SELECT name, course_name
FROM Student
FULL JOIN Course
  ON Student.course_id = Course.course_id;

输出

窗口函数

A window function performs a calculation across a specific set of rows (the “window”) defined by an OVER () clause. Unlike traditional aggregate functions, which collapse rows into a single result, window functions return a value for each row while still having access to other rows in the defined window.

语法

SELECT column_1,
       column_2,
       function() OVER (
           PARTITION BY partition_expression
           ORDER BY order_expression
       ) AS output_column_name
FROM table_name;

说明

示例

SELECT dem.first_name,
       dem.last_name,
       gender,
       AVG(salary) OVER (PARTITION BY gender) AS avg_salary_by_gender
FROM employee_demographics dem
JOIN employee_salary sal
  ON dem.employee_id = sal.employee_id;

此查询在仍然显示每位员工记录的同时，计算每个性别的平均工资。

常用窗口函数

• ROW_NUMBER()
• RANK()
• DENSE_RANK()
• LAG(expression, offset)
• FIRST_VALUE()
• LAST_VALUE()

作为窗口函数使用的聚合函数

• SUM()
• AVG()
• MAX()
• MIN()
• COUNT()

结论

• 当需要从多个表合并相关数据时使用 JOIN。
• 根据要保留的行选择合适的连接类型（INNER、LEFT、RIGHT、FULL）。
• 当需要基于一组相关行进行行级计算（例如累计总和、排名、移动平均）时使用 窗口函数。
• 将 PARTITION BY 与 ORDER BY 结合使用，以微调窗口范围和排序。

这些工具共同使 SQL 成为既能检索数据又能进行分析计算的强大语言。