前言

Oracle 窗口函數(shù)是SQL語言中一項(xiàng)極其強(qiáng)大的功能，它賦予了你在保留原始行集的同時(shí)，對(duì)相關(guān)數(shù)據(jù)子集（“窗口”）進(jìn)行復(fù)雜計(jì)算的能力。與將多行壓縮為一行的標(biāo)準(zhǔn)聚合函數(shù) (GROUP BY) 不同，窗口函數(shù)為結(jié)果集中的每一行都返回一個(gè)獨(dú)立的計(jì)算值。

思維導(dǎo)圖

一、窗口函數(shù)的通用語法結(jié)構(gòu)

所有窗口函數(shù)都遵循一個(gè)核心的 OVER() 子句結(jié)構(gòu)，它定義了計(jì)算的上下文——“窗口”。

function_name([arguments]) OVER (
  [PARTITION BY partition_expression, ...]
  [ORDER BY sort_expression [ASC|DESC] [NULLS FIRST|NULLS LAST], ...]
  [windowing_clause]
)

• PARTITION BY: 分區(qū)子句。將數(shù)據(jù)集邏輯上分割成多個(gè)獨(dú)立的組（分區(qū)），窗口函數(shù)在每個(gè)分區(qū)內(nèi)部獨(dú)立計(jì)算。若省略，整個(gè)結(jié)果集被視為單個(gè)分區(qū)。
• ORDER BY: 排序子句。它定義了分區(qū)內(nèi)各行的處理順序。對(duì)于排名和位置函數(shù)，此子句至關(guān)重要。
• windowing_clause: 窗口范圍子句。它更精確地定義了計(jì)算窗口的邊界（例如 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 表示當(dāng)前行、前一行和后一行）。如果省略（但有ORDER BY），默認(rèn)通常是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。

二、窗口函數(shù)分類與實(shí)戰(zhàn)

背景表：我們將使用一個(gè)簡化的 emp 表進(jìn)行所有演示，包含 empno, ename, job, deptno, sal, hiredate 等列。

2.1 排名窗口函數(shù)

ROW_NUMBER()

• 功能：為窗口內(nèi)的每一行分配一個(gè)從1開始的唯一且連續(xù)的排名。即使行具有相同的值，排名也不會(huì)重復(fù)。
• 代碼示例：按部門為員工按薪水降序進(jìn)行唯一排名。

SELECT ename, deptno, sal,
  ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num_rank
FROM emp;

RANK()

• 功能：計(jì)算排名。如果值相同，則排名相同，但后續(xù)排名會(huì)跳過相應(yīng)的位置（例如：1, 2, 2, 4）。
• 代碼示例：按部門為員工按薪水降序進(jìn)行跳躍排名。

SELECT ename, deptno, sal,
  RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rank_val
FROM emp;

DENSE_RANK()

• 功能：計(jì)算排名。如果值相同，則排名相同，且后續(xù)排名不會(huì)跳過位置（例如：1, 2, 2, 3）。
• 代碼示例：按部門為員工按薪水降序進(jìn)行連續(xù)排名。

SELECT ename, deptno, sal,
  DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dense_rank_val
FROM emp;

NTILE(n)

• 功能：將分區(qū)內(nèi)的行分成 n 個(gè)大致相等的組（桶），并返回每行所在的桶號(hào)。
• 代碼示例：將每個(gè)部門的員工按薪水降序分為4個(gè)等級(jí)。

SELECT ename, deptno, sal,
  NTILE(4) OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_quartile
FROM emp;

2.2 聚合窗口函數(shù)

SUM() / COUNT() / AVG() / MAX() / MIN()

• 功能：將標(biāo)準(zhǔn)聚合函數(shù)應(yīng)用于窗口。
• 代碼示例 (分區(qū)聚合)：計(jì)算每個(gè)員工的薪水，并顯示其所在部門的總薪水和平均薪水。

SELECT ename, deptno, sal,
  SUM(sal) OVER (PARTITION BY deptno) AS total_dept_salary,
  ROUND(AVG(sal) OVER (PARTITION BY deptno), 2) AS avg_dept_salary
FROM emp;

• 代碼示例 (累計(jì)聚合/移動(dòng)求和)：計(jì)算每個(gè)部門內(nèi)，按入職日期排序的累計(jì)薪水。

SELECT ename, deptno, sal, hiredate,
  SUM(sal) OVER (PARTITION BY deptno ORDER BY hiredate) AS running_total_salary
FROM emp;

• 代碼示例 (滑動(dòng)窗口/移動(dòng)平均)：計(jì)算每個(gè)部門內(nèi)，基于當(dāng)前行及前兩行（共三行）的移動(dòng)平均薪水。

SELECT ename, deptno, sal, hiredate,
  ROUND(AVG(sal) OVER (PARTITION BY deptno ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3_rows
FROM emp;

2.3 位置/偏移窗口函數(shù)

LAG(expression, [offset], [default_value])

• 功能：訪問當(dāng)前行之前特定偏移量 (offset，默認(rèn)為1) 的行的值。
• 代碼示例：顯示每個(gè)員工的薪水，以及其同部門內(nèi)按薪水降序排列的上一名員工的薪水（若無則為0）。

SELECT ename, deptno, sal,
  LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC) AS previous_salary
FROM emp;

LEAD(expression, [offset], [default_value])

• 功能：訪問當(dāng)前行之后特定偏移量 (offset，默認(rèn)為1) 的行的值。
• 代碼示例：顯示每個(gè)員工的薪水，以及其同部門內(nèi)按入職日期排序的下一名入職員工的姓名（若無則為’N/A’）。

SELECT ename, deptno, hiredate,
  LEAD(ename, 1, 'N/A') OVER (PARTITION BY deptno ORDER BY hiredate) AS next_hired_employee
FROM emp;

FIRST_VALUE(expression)

• 功能：返回窗口內(nèi)第一行的指定表達(dá)式的值。
• 代碼示例：顯示每個(gè)員工及其所在部門最早入職的員工姓名。

SELECT ename, deptno, hiredate,
  FIRST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY hiredate) AS first_hired_in_dept
FROM emp;

LAST_VALUE(expression)

• 功能：返回窗口內(nèi)最后一行的指定表達(dá)式的值。
• 重要提示：默認(rèn)窗口范圍是到 CURRENT ROW，要獲取整個(gè)分區(qū)的最后一個(gè)值，必須顯式定義窗口范圍。
• 代碼示例：顯示每個(gè)員工及其所在部門薪水最高的員工姓名。

SELECT ename, deptno, sal,
  LAST_VALUE(ename) OVER (PARTITION BY deptno ORDER BY sal ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS highest_paid_in_dept
FROM emp;

(這里通過薪水升序排列，然后取窗口的最后一行來找到薪水最高者)

NTH_VALUE(expression, n)

• 功能：返回窗口內(nèi)第 n 行的指定表達(dá)式的值。
• 代碼示例：顯示每個(gè)員工及其所在部門薪水第二高的員工薪水。

SELECT ename, deptno, sal,
  NTH_VALUE(sal, 2) OVER (PARTITION BY deptno ORDER BY sal DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest_salary
FROM emp;

2.4 統(tǒng)計(jì)/分布窗口函數(shù)

RATIO_TO_REPORT(expression)

• 功能：計(jì)算當(dāng)前行的值占分區(qū)內(nèi)總和的比例。
• 代碼示例：計(jì)算每個(gè)員工的薪水占其所在部門總薪水的百分比。

SELECT ename, deptno, sal,
  TO_CHAR(RATIO_TO_REPORT(sal) OVER (PARTITION BY deptno) * 100, '990.99') || '%' AS percentage_of_dept_sal
FROM emp;

PERCENT_RANK()

• 功能：計(jì)算行的百分比排名，計(jì)算公式為 (rank - 1) / (rows_in_partition - 1)。
• 代碼示例：計(jì)算每個(gè)員工薪水在其部門內(nèi)的百分位排名。

SELECT ename, deptno, sal,
  ROUND(PERCENT_RANK() OVER (PARTITION BY deptno ORDER BY sal ASC) * 100, 2) AS percentile_rank
FROM emp;

CUME_DIST()

• 功能：計(jì)算行的累積分布，即小于等于當(dāng)前值的行數(shù)占分區(qū)總行數(shù)的比例。
• 代碼示例：計(jì)算薪水小于等于當(dāng)前員工薪水的員工在其部門內(nèi)的累積占比。

SELECT ename, deptno, sal,
  ROUND(CUME_DIST() OVER (PARTITION BY deptno ORDER BY sal ASC) * 100, 2) AS cumulative_distribution
FROM emp;

三、綜合實(shí)戰(zhàn)案例：構(gòu)建員工績效分析報(bào)告

這個(gè)案例整合了多種窗口函數(shù)來生成一份詳細(xì)的員工分析報(bào)告。

目標(biāo)：對(duì)于每一位員工，我們希望得到他/她在其部門內(nèi)的薪水排名、與部門平均薪水的差距、薪水占部門總額的比例，以及其上司（按薪水排名的上一位）的薪水。

代碼示例：

WITH emp_analysis AS (
  SELECT
    empno,
    ename,
    deptno,
    sal,
    -- 使用聚合窗口函數(shù)計(jì)算部門的統(tǒng)計(jì)數(shù)據(jù)
    AVG(sal) OVER (PARTITION BY deptno) AS avg_dept_sal,
    SUM(sal) OVER (PARTITION BY deptno) AS total_dept_sal,
    -- 使用排名窗口函數(shù)計(jì)算薪水排名
    RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS dept_sal_rank,
    -- 使用位置窗口函數(shù)獲取上一位員工的薪水
    LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal DESC) AS prev_rank_sal
  FROM emp
)
SELECT
  a.ename AS employee_name,
  a.deptno,
  a.sal AS current_salary,
  a.dept_sal_rank,
  ROUND(a.avg_dept_sal, 2) AS department_avg_salary,
  a.sal - ROUND(a.avg_dept_sal, 2) AS diff_from_avg,
  TO_CHAR(a.sal / a.total_dept_sal * 100, '990.99') || '%' AS percentage_of_total,
  a.prev_rank_sal AS superior_salary
FROM emp_analysis a
ORDER BY a.deptno, a.dept_sal_rank;

解析：

• 我們使用公用表表達(dá)式 (CTE) WITH emp_analysis AS (...) 來分步處理，使查詢更清晰。
• 在 emp_analysis CTE 內(nèi)部：
  • AVG(sal) OVER (...) 和 SUM(sal) OVER (...) 為每行計(jì)算出其所在部門的平均和總薪水。
  • RANK() OVER (...) 計(jì)算出部門內(nèi)的薪水排名。
  • LAG(...) OVER (...) 找到了排名緊鄰的上一位員工的薪水。
• 在最終的 SELECT 語句中，我們引用 CTE emp_analysis 的結(jié)果，并進(jìn)行簡單的算術(shù)運(yùn)算和格式化，生成了最終的報(bào)告列，如 diff_from_avg (與平均薪水差額) 和 percentage_of_total (薪水占比)。

總結(jié)： Oracle 窗口函數(shù)是進(jìn)行復(fù)雜數(shù)據(jù)分析的核心技能。通過靈活運(yùn)用 PARTITION BY, ORDER BY, 和窗口范圍子句，你可以用簡潔的SQL實(shí)現(xiàn)過去需要通過自連接、子查詢或過程化代碼才能完成的復(fù)雜邏輯。

練習(xí)題

背景表結(jié)構(gòu)：

CREATE TABLE sales_data (
    sale_id          NUMBER(10),
    product_category VARCHAR2(50 CHAR),
    region           VARCHAR2(50 CHAR),
    sale_amount      NUMBER(10, 2),
    sale_date        DATE
);

請(qǐng)為以下每個(gè)場(chǎng)景編寫使用窗口函數(shù)的SQL查詢。

題目：

• 查詢所有銷售記錄，并為每條記錄添加一列 category_rank，表示該筆銷售額 (sale_amount) 在其所屬產(chǎn)品類別 (product_category) 內(nèi)的排名 (銷售額越高，排名越靠前)。使用 RANK() 函數(shù)。
• 查詢所有銷售記錄，并為每條記錄添加一列 total_region_sales，顯示該記錄所在地區(qū) (region) 的總銷售額。
• 查詢所有銷售記錄，并為每條記錄添加一列 monthly_running_total，計(jì)算每個(gè)地區(qū)內(nèi)，按銷售日期 (sale_date) 排序的累計(jì)銷售額。
• 查詢所有銷售記錄，并為每條記錄添加一列 prev_sale_amount，顯示同一地區(qū)內(nèi)，按銷售日期排序的上一筆銷售的銷售額。如果不存在上一筆，則顯示0。
• 查詢所有銷售記錄，并為每條記錄添加一-列 next_sale_amount，顯示同一產(chǎn)品類別內(nèi)，按銷售日期排序的下一筆銷售的銷售額。如果不存在下一筆，則顯示-1。
• 找出每個(gè)產(chǎn)品類別中銷售額最高的兩條銷售記錄。
• 查詢所有銷售記錄，并為每條記錄添加一列 highest_sale_in_category，顯示該記錄所在產(chǎn)品類別的單筆最高銷售額。
• 查詢所有銷售記錄，并為每條記錄添加一列 sale_percentage_of_region，計(jì)算該筆銷售額占其所在地區(qū)銷售總額的百分比。
• 將每個(gè)地區(qū)的銷售記錄按銷售額分為3個(gè)等級(jí) (1為最高，3為最低)。為每條記錄添加一列 sales_tier 來表示這個(gè)等級(jí)。
• 查詢所有銷售記錄，并為每條記錄添加一列 moving_avg_3_sales，計(jì)算每個(gè)地區(qū)內(nèi)，按銷售日期排序，當(dāng)前行及其前兩行 (共三行) 的移動(dòng)平均銷售額。

答案與解析

• 類別內(nèi)銷售額排名：

SELECT
  s.*,
  RANK() OVER (PARTITION BY product_category ORDER BY sale_amount DESC) AS category_rank
FROM sales_data s;

• 解析： PARTITION BY product_category 將數(shù)據(jù)按類別分片，ORDER BY sale_amount DESC 在每個(gè)片內(nèi)按銷售額降序排，RANK() 計(jì)算排名。

• 地區(qū)總銷售額：

SELECT
  s.*,
  SUM(sale_amount) OVER (PARTITION BY region) AS total_region_sales
FROM sales_data s;

• 解析： SUM(...) OVER (PARTITION BY region) 對(duì)每個(gè)地區(qū)分區(qū)內(nèi)的所有 sale_amount 求和，并將這個(gè)總和賦給分區(qū)內(nèi)的每一行。

• 地區(qū)內(nèi)月度累計(jì)銷售額：

SELECT
  s.*,
  SUM(sale_amount) OVER (PARTITION BY region ORDER BY sale_date) AS monthly_running_total
FROM sales_data s;

• 解析： ORDER BY sale_date 的加入，使得 SUM 的計(jì)算窗口默認(rèn)為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，從而實(shí)現(xiàn)了從分區(qū)開始到當(dāng)前行的累計(jì)求和。

• 獲取上一筆銷售額：

SELECT
  s.*,
  LAG(sale_amount, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) AS prev_sale_amount
FROM sales_data s;

• 解析： LAG(sale_amount, 1, 0) 在按地區(qū)分區(qū)、按日期排序的窗口中，獲取往前1行的 sale_amount 值，如果不存在（即第一行），則返回默認(rèn)值0。

• 獲取下一筆銷售額：

SELECT
  s.*,
  LEAD(sale_amount, 1, -1) OVER (PARTITION BY product_category ORDER BY sale_date) AS next_sale_amount
FROM sales_data s;

• 解析： LEAD(sale_amount, 1, -1) 在按類別分區(qū)、按日期排序的窗口中，獲取往后1行的 sale_amount 值，如果不存在（即最后一行），則返回默認(rèn)值-1。

• 每個(gè)類別銷售額最高的兩條記錄：

SELECT * FROM (
  SELECT
    s.*,
    ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY sale_amount DESC) AS rn
  FROM sales_data s
)
WHERE rn <= 2;

• 解析： 窗口函數(shù)不能直接用在 WHERE 子句中。因此，我們先用一個(gè)子查詢（或CTE）計(jì)算出每個(gè)類別內(nèi)的行號(hào)排名 rn，然后在外部查詢中篩選出 rn <= 2 的記錄。這里使用 ROW_NUMBER() 可以確保每個(gè)類別不多不少正好取兩條（如果銷售額相同）。

• 類別內(nèi)最高銷售額：

SELECT
  s.*,
  MAX(sale_amount) OVER (PARTITION BY product_category) AS highest_sale_in_category
FROM sales_data s;

• 解析： 類似于第2題，MAX(...) OVER (PARTITION BY ...) 會(huì)找到每個(gè)分區(qū)內(nèi)的最大值，并將其賦給該分區(qū)的所有行。

• 銷售額占地區(qū)總額百分比：

SELECT
  s.*,
  RATIO_TO_REPORT(sale_amount) OVER (PARTITION BY region) AS sale_percentage_of_region
FROM sales_data s;

• 解析： RATIO_TO_REPORT 在按 region 分區(qū)的窗口內(nèi)計(jì)算，得出當(dāng)前銷售額占該地區(qū)總銷售額的比例。

• 銷售額分等級(jí)：

SELECT
  s.*,
  NTILE(3) OVER (PARTITION BY region ORDER BY sale_amount DESC) AS sales_tier
FROM sales_data s;

• 解析： NTILE(3) 將每個(gè)地區(qū) (region) 的銷售記錄按銷售額降序分成3個(gè)桶，并返回每條記錄所在的桶號(hào) (1, 2, 或 3)。

• 3行移動(dòng)平均銷售額：

SELECT
  s.*,
  AVG(sale_amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_sales
FROM sales_data s;

• 解析： 這里必須顯式定義 windowing_clause。ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定義了一個(gè)包含當(dāng)前行和它前面兩行（共三行）的滑動(dòng)窗口，AVG 在這個(gè)窗口上計(jì)算平均值。

總結(jié) 

到此這篇關(guān)于Oracle窗口函數(shù)詳解及練習(xí)題的文章就介紹到這了,更多相關(guān)Oracle窗口函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論