PostgreSQL 连接和窗口函数

Source: Dev.to

理解 PostgreSQL 中的 JOIN

JOIN 通过关联列将多个表（或视图）中的数据合并。
选择哪种 JOIN 类型主要取决于：

• 你想保留哪些行（包括未匹配的行）
• 表之间的关联方式

主要的 JOIN 类型

CROSS JOIN

创建笛卡尔积——第一个表的每一行都与第二个表的每一行配对。无需 ON 子句。

SELECT p.project_name, e.name, e.salary
FROM sales_data.projects p
CROSS JOIN sales_data.employees e;

INNER JOIN

仅返回两张表中匹配的行。非匹配的行会被排除。

SELECT emp.name, dep.department_name
FROM sales_data.employees emp
INNER JOIN sales_data.departments dep
  ON emp.department_id = dep.department_id;

LEFT JOIN（LEFT OUTER JOIN）

保留左表的所有行，并加入右表中匹配的行。

SELECT *
FROM sales_data.projects p
LEFT JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

RIGHT JOIN（RIGHT OUTER JOIN）

保留右表的所有行，并加入左表中匹配的行。

SELECT *
FROM sales_data.projects p
RIGHT JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

FULL JOIN（FULL OUTER JOIN）

返回两张表的所有行。没有匹配的地方用 NULL 填充。

SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

增强 JOIN

可以添加过滤、排序等操作：

SELECT *
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id
WHERE e.employee_id < 4
ORDER BY e.employee_id ASC NULLS LAST,
         p.project_name ASC;

PostgreSQL 中的窗口函数

窗口函数在与当前行相关的“窗口”内计算值——不会像 GROUP BY 那样把行合并。

排名函数

• ROW_NUMBER() – 为每行分配唯一的连续编号（1、2、3、…）
• RANK() – 相同值获得相同排名，但在出现并列后会跳过编号（1、1、3、…）
• DENSE_RANK() – 相同值获得相同排名，编号不跳过（1、1、2、…）
• NTILE(n) – 将行划分为 n 个大致相等的桶（用于四分位数、百分位数等）

SELECT *,
       RANK()       OVER (ORDER BY salary DESC NULLS FIRST) AS rank_col,
       DENSE_RANK() OVER (ORDER BY salary DESC NULLS FIRST) AS dense_rank_col,
       ROW_NUMBER() OVER (ORDER BY salary DESC NULLS FIRST) AS row_num
FROM sales_data.working_hub;

窗口中的聚合函数

在窗口内运行聚合（SUM、AVG、COUNT、MIN、MAX 等），而不进行分组。

SELECT *,
       SUM(e.salary) OVER () AS total_salary_company_wide
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;

导航 / 值函数

在有序窗口中比较行：

• LAG(col) – 前一行的值
• LEAD(col) – 下一行的值
• FIRST_VALUE(col) – 窗口中的第一个值
• LAST_VALUE(col) – 窗口中的最后一个值

SELECT *,
       LAG(salary)  OVER (ORDER BY salary DESC) AS prev_salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary
FROM sales_data.working_hub;

PARTITION BY

将数据划分为组（类似 GROUP BY），但保持所有行完整。

SELECT *,
       SUM(e.salary) OVER (PARTITION BY p.project_name) AS salary_per_project
FROM sales_data.projects p
FULL JOIN sales_data.employees e
  ON p.employee_id = e.employee_id;