SQL | 別找了，視窗函式看這篇文章足矣！ - 辦公

2024-02-26辦公

點選藍字 · 關註我們

大家好，我是Bryce~

這次和大家分享一下SQL中的視窗函式，它在SQL中的地位非常重要！

本文內容包括row_number、rank、dense_rank等排序視窗函式，和sum、avg、min、max等統計視窗函式， 共15個 。每個函式均給出了功能介紹、樣例程式碼和結果展示。

1. 基本介紹

1.1 視窗函式介紹

視窗函式是SQL中的一種強大工具，用於在查詢結果中進行分析和計算。與常規聚合函式不同，視窗函式可以在不影響查詢結果集的情況下，對結果集中的每一行套用函式，生成額外的資訊，例如排名、累計和等。

1.2 視窗函式分類

視窗函式可以分為排序視窗函式和統計視窗函式兩大類。排序視窗函式主要用於對數據進行排序和排名，包括row_number、rank、dense_rank、percent_rank、ntile；而統計視窗函式則用於進行統計計算，包括count、sum、avg、min、max、first_value、last_value、lag、lead、cume_dist。

2. 樣例數據

2.1 樣例數據SQL

CREATETABLE Sales (idINT, region VARCHAR(50), amount DECIMAL(10, 2));INSERTINTO Sales (id, region, amount) VALUES(1, 'North', 1000.50),(2, 'North', 1500.75),(3, 'South', 800.25),(4, 'West', 1200.00),(5, 'East', 2000.30);

3.各個視窗函式介紹

3.1 row_number視窗函式
row_number函式為結果集中的每一行分配一個唯一的整數，按照指定的排序順序進行排列。

SELECTid, region, amount, ROW_NUMBER() OVER (ORDERBY amount DESC) AS row_numFROM Sales;

3.2 rank視窗函式
rank函式為結果集中的每一行分配一個排名，如果有相同數值，則會跳過相同排名並繼續遞增。 （不連續排名）

SELECTid, region, amount, RANK() OVER (ORDERBY amount DESC) ASrankFROM Sales;

3.3 dense_ rank視窗函式
dense_rank函式為結果集中的每一行分配一個排名，與rank函式類似，但是在遇到相同數值時，dense_rank不會跳過相同排名，而是連續分配相同的排名。 (連續排名）

SELECTid, region, amount, DENSE_RANK() OVER (ORDERBY amount DESC) ASdense_rankFROM Sales;

3.4 percent_rank視窗函式
percent_rank函式計算每一行在排序結果中的相對排名，返回一個介於0和1之間的小數值，用來表示行在排序結果中的相對位置。

SELECTid, region, amount, PERCENT_RANK() OVER (ORDERBY amount DESC) ASpercent_rankFROM Sales;

3.5 ntile視窗函式
ntile函式將結果集分割成指定數量的桶，並為每個桶分配一個編號，確保每個桶中的行數量盡可能均勻。

SELECTid, region, amount, NTILE(2) OVER (ORDERBY amount DESC) AS ntileFROM Sales;

3.6 count視窗函式
count函式計算結果集中行的數量，可以結合分組函式使用，用於統計分組內的行數。

SELECTid, region, amount, COUNT(*) OVER (PARTITIONBY region) AS region_countFROM Sales;

3.7 sum視窗函式
sum函式計算指定列的總和，並將結果添加到每一行。

SELECTid, region, amount, SUM(amount) OVER () AS total_salesFROM Sales;

3.8 min視窗函式
min函式計算指定列的最小值，並將結果添加到每一行。

SELECTid, region, amount, MIN(amount) OVER () AS min_amountFROM Sales;

3.9 max視窗函式
max函式計算指定列的最大值，並將結果添加到每一行。

SELECTid, region, amount, MAX(amount) OVER () AS max_amountFROM Sales;

3.10 avg視窗函式
avg函式計算指定列的平均值，並將結果添加到每一行。

SELECTid, region, amount, AVG(amount) OVER () AS avg_amountFROM Sales;

3.11 first_value視窗函式
first_value函式返回分組內的第一個值，並將其添加到每一行。

SELECTid, region, amount, FIRST_VALUE(amount) OVER (PARTITIONBY region ORDERBYid) AS first_amountFROM Sales;

3.12 last_value視窗函式
last_value函式返回分組內的最後一個值，並將其添加到每一行。

SELECTid, region, amount, LAST_VALUE(amount) OVER (PARTITIONBY region ORDERBYidROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING) AS last_amountFROM Sales;