精通SQL：深入探讨JOIN和窗口函数

Source: Dev.to

介绍

SQL 是数据操作和分析的支柱。它最强大的两个特性是 JOINs 和 Window Functions。JOINs 允许你合并多个表的数据，而 Window Functions 在一组行上实现高级计算。本文将通过清晰的解释、可视化和实用示例来阐明这些概念。

理解 JOIN

什么是 JOIN？

JOIN 将两个或多个表中的行基于相关列进行组合。它们是处理关系型数据库的关键，因为数据通常分布在多个表中。

JOIN 的类型

• INNER JOIN（内连接）： 仅返回两个表中具有匹配值的行。
• LEFT JOIN（或 LEFT OUTER JOIN，左外连接）： 返回左表的所有行以及右表中匹配的行。如果没有匹配，右表的列返回 NULL。
• RIGHT JOIN（或 RIGHT OUTER JOIN，右外连接）： 返回右表的所有行以及左表中匹配的行。如果没有匹配，左表的列返回 NULL。
• FULL JOIN（或 FULL OUTER JOIN，全外连接）： 当左表或右表任一侧有匹配时返回所有行。如果没有匹配，缺失侧的列返回 NULL。

内连接示例

SELECT employees.name,
       departments.name AS department
FROM employees
INNER JOIN departments
  ON employees.dept_id = departments.dept_id;

左连接示例

SELECT employees.name,
       departments.name AS department
FROM employees
LEFT JOIN departments
  ON employees.dept_id = departments.dept_id;

Source: …

探索窗口函数

什么是窗口函数？

窗口函数在与当前行相关的一组表行上执行计算。与聚合函数（例如 SUM、AVG）不同，窗口函数 不会 将行分组为单个输出行。

关键窗口函数

• ROW_NUMBER(): 为分区内的行分配唯一的顺序整数。
• RANK(): 为分区内的每行分配排名，出现并列时会留下空位。
• DENSE_RANK(): 为分区内的每行分配排名，出现并列时不会留下空位。
• LEAD() 和 LAG(): 在同一结果集中访问后续或前一行的数据。
• SUM() OVER(): 在窗口上计算累计总和或运行总计。

示例 1：ROW_NUMBER()

SELECT name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

结果： 按工资对员工进行排名，工资最高的排名为 1。

示例 2：使用 SUM() OVER() 计算运行总计

SELECT date,
       revenue,
       SUM(revenue) OVER (ORDER BY date) AS running_total
FROM sales;

结果： 显示随时间累积的收入。

示例 3：使用 LAG() 比较前一行

SELECT date,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY date) AS previous_day_revenue
FROM sales;

结果： 显示每一天的收入以及前一天的收入。

结合 JOIN 与窗口函数

实际场景

按部门分析员工绩效，按薪资对每个部门的员工进行排名。

SELECT e.name,
       d.name AS department,
       e.salary,
       RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_rank
FROM employees e
JOIN departments d
  ON e.dept_id = d.dept_id;

结论

JOIN 和窗口函数是任何使用 SQL 的人不可或缺的工具。JOIN 让您可以合并多个表的数据，而窗口函数在不折叠行的情况下实现高级分析。掌握这些概念可以释放 SQL 在数据分析和报告中的全部潜力。