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 子句將結果集劃分為多個分割槽。