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;