SQL PARTITION BY

摘要：在本教程中，您将学习如何使用 SQL PARTITION BY 子句将结果集划分为多个分区，窗口函数可以在这些分区上进行操作。

SQL PARTITION BY 子句简介 #

在 SQL 中，窗口函数允许您对与当前行有某种关系的表行进行计算。

窗口函数使用 OVER 子句来定义窗口或窗口函数操作的一组行。OVER 子句包含可选的 PARTITION BY 和 ORDER BY 子句。

以下是使用 PARTITION BY 和 ORDER BY 子句的窗口函数的基本语法：

window_function (expression) OVER (
  PARTITION BY
    column1,
    column2
  ORDER BY
    column3,
    column4
)Code language: SQL (Structured Query Language) (sql)

如果省略 PARTION BY 子句，窗口函数会将整个结果集视为单个分区。

当您使用 PARTITION BY 子句时，它会将结果集划分为多个分区。窗口函数将独立地对每个分区进行操作。

假设我们有以下 salary_reports 表：

DROP TABLE IF EXISTS salary_reports;

CREATE TABLE salary_reports (
  state varchar(255) NOT NULL,
  job varchar(255) NOT NULL,
  salary DEC(11, 2)
);

INSERT INTO
  salary_reports (state, job, salary)
VALUES
  ('California', 'IT', 150000),
  ('California', 'Marketing', 130000),
  ('Texas', 'IT', 100000),
  ('Texas', 'Marketing', 80000);

SELECT
  *
FROM
  salary_reports;Code language: SQL (Structured Query Language) (sql)

以下查询使用 AVG() 窗口函数计算所有州和工作的平均工资：

SELECT
  state,
  job,
  salary,
  ROUND(AVG(salary) OVER (), 2) average_salary
FROM
  salary_reports
ORDER BY
  state,
  job;Code language: SQL (Structured Query Language) (sql)

试一试

输出

   state    |    job    |  salary   | average_salary
------------+-----------+-----------+----------------
 California | IT        | 150000.00 |      115000.00
 California | Marketing | 130000.00 |      115000.00
 Texas      | IT        | 100000.00 |      115000.00
 Texas      | Marketing |  80000.00 |      115000.00Code language: SQL (Structured Query Language) (sql)

在此示例中，AVG 函数计算了四行中所有工资的平均值。

(150,000 + 130,000 + 100,000 + 80,000) / 4Code language: SQL (Structured Query Language) (sql)

按工作对数据进行分区 #

要计算所有州中按工作划分的平均工资，您可以使用 PARITION BY 子句将结果集划分为多个分区：

SELECT
  state,
  job,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY job), 2) average_salary
FROM
  salary_reports
ORDER BY
  state,
  job;Code language: SQL (Structured Query Language) (sql)

试一试

输出

   state    |    job    |  salary   | average_salary
------------+-----------+-----------+----------------
 California | IT        | 150000.00 |      125000.00
 California | Marketing | 130000.00 |      105000.00
 Texas      | IT        | 100000.00 |      125000.00
 Texas      | Marketing |  80000.00 |      105000.00Code language: SQL (Structured Query Language) (sql)

请注意，该查询使用 ROUND() 函数将平均值四舍五入到保留两位小数。

在此示例中，PARITION BY 子句根据 job 列中的值划分结果集。由于我们有两个工作 (IT & Marketing)，PARTITION BY 子句将结果集划分为两个分区：

第一个分区

   state    |    job    |  salary
------------+-----------+-----------
 California | IT        | 150000.00
 Texas      | IT        | 100000.00Code language: SQL (Structured Query Language) (sql)

第二个分区

   state    |    job    |  salary
------------+-----------+-----------
 California | Marketing | 130000.00
 Texas      | Marketing |  80000.00Code language: SQL (Structured Query Language) (sql)

AVG() 窗口函数计算每个分区的平均工资，第一个分区的返回值为 125,000。

(150,000 + 100,000) / 2 = 125,000Code language: SQL (Structured Query Language) (sql)

第二个分区的返回值为 105,000。

(100,000 + 80,000) /2 = 105,000Code language: SQL (Structured Query Language) (sql)

按州对数据进行分区 #

以下查询使用 PARTITION BY 子句计算所有工作中按州划分的平均工资：

SELECT
  state,
  job,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY state), 2) average_salary
FROM
  salary_reports
ORDER BY
  state,
  job;Code language: SQL (Structured Query Language) (sql)

试一试

输出

   state    |    job    |  salary   | average_salary
------------+-----------+-----------+----------------
 California | IT        | 150000.00 |      140000.00
 California | Marketing | 130000.00 |      140000.00
 Texas      | IT        | 100000.00 |       90000.00
 Texas      | Marketing |  80000.00 |       90000.00Code language: SQL (Structured Query Language) (sql)

在此示例中，PARITTION BY 子句根据 state 列中的值划分结果集。

由于有两个州 California 和 Texas，PARTITION BY 子句将结果集划分为两个分区，每个州一个分区：

第一个分区

   state    |    job    |  salary
------------+-----------+-----------
 California | IT        | 150000.00
 California | Marketing | 130000.00Code language: SQL (Structured Query Language) (sql)

第二个分区

   state    |    job    |  salary   | average_salary
------------+-----------+-----------+----------------
 Texas      | IT        | 100000.00 |       90000.00
 Texas      | Marketing |  80000.00 |       90000.00Code language: SQL (Structured Query Language) (sql)

AVG() 窗口函数计算每个分区的平均工资。第一个分区的返回值为 140,000，第二个分区的返回值为 90,000。

按部门对数据进行分区 #

我们将使用示例数据库中的 employees 表。

以下查询使用带 PARTITION BY 子句的 SUM 窗口函数来检索员工工资以及该员工所在部门的总工资：

SELECT
  first_name,
  last_name,
  department_name,
  salary,
  SUM(salary) OVER (
    PARTITION BY department_name
  ) department_salary
FROM
  employees e
  INNER JOIN departments d ON d.department_id = e.department_id
ORDER BY
  department_name;Code language: SQL (Structured Query Language) (sql)

试一试

输出

 first_name  |  last_name  | department_name  |  salary  | department_salary
-------------+-------------+------------------+----------+-------------------
 William     | Gietz       | Accounting       |  8300.00 |          20300.00
 Shelley     | Higgins     | Accounting       | 12000.00 |          20300.00
 Jennifer    | Whalen      | Administration   |  4400.00 |           4400.00
 Steven      | King        | Executive        | 24000.00 |          58000.00
 Neena       | Kochhar     | Executive        | 17000.00 |          58000.00
 Lex         | De Haan     | Executive        | 17000.00 |          58000.00
...Code language: SQL (Structured Query Language) (sql)

查询工作原理

• 首先，PARTITION BY 子句按部门划分 employees 表中的行。同一部门内的员工属于同一分区。
• 其次，SUM() 函数计算每个部门员工的总工资。

摘要 #

• 使用 PARTITION BY 子句将结果集划分为多个分区。