窗函數 (SQL)

在SQL中，窗函數(window function)或分析函數(analytic function)^[1]是一個函數，它使用來自一行或多行的值來為每一行返回一個值。與之形成對比，聚合函數（英語：Aggregate function）為多行返回單個值。窗口函數有一個OVER子句；任何沒有OVER子句的函數都不是窗口函數，而是聚合函數或單行（純量）函數。^[2]

例子1

例如，這裡有一個查詢，它使用一個窗口函數來比較每個員工的工資與其部門的平均工資（來自PostgreSQL文檔的示例）:^[3]

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

輸出:

 depname  | empno | salary |          avg          
----------+-------+--------+----------------------
develop   |    11 |   5200 | 5020.0000000000000000
develop   |     7 |   4200 | 5020.0000000000000000
develop   |     9 |   4500 | 5020.0000000000000000
develop   |     8 |   6000 | 5020.0000000000000000
develop   |    10 |   5200 | 5020.0000000000000000
personnel |     5 |   3500 | 3700.0000000000000000
personnel |     2 |   3900 | 3700.0000000000000000
sales     |     3 |   4800 | 4866.6666666666666667
sales     |     1 |   5000 | 4866.6666666666666667
sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

PARTITION BY子句將行分組，並且該函數分別應用於每個分組。如果PARTITION BY子句被省略（例如如果我們有一個空的OVER()子句），那麼整個結果集（英語：Result set）被視為單個分組。^[4]對於此查詢，報告的平均工資將是所有行的平均值。

窗口函數在聚合之後進行評估（例如，在 GROUP BY（英語：Group by (SQL)）子句和非窗口聚合函數之後）。^[1]

語法

根據PostgreSQL文檔，窗函數具有下列之一的語法:^[4]

function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )

其中function_name包括：

開窗函數
- 序號函數
  - ROW_NUMBER：順序排序——1、2、3
  - RANK：並列排序，跳過重複序號——1、1、3
  - DENSE_RANK：並列排序，不跳過重複序號——1、1、2
- 分布函數
  - PERCENT_RANK
  - CUME_DIST
- 前驅後繼函數
  - LAG(expr,n)：返回當前行的前n行的expr的值
  - LEAD(expr,n)：返回當前行的後n行的expr的值
- 頭尾函數
  - FIRST_VALUE(expr)
  - LAST_VALUE(expr)
- 其他函數
  - NTH_VALUE(expr,n)：返回第n行的expr值
  - NTILE(n)：將有序數據分為n個桶，記錄等級數
所有聚合函數

其中 window_definition具有語法:

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

frame_clause具有下列之一的語法:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start與frame_end可以是UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, UNBOUNDED FOLLOWING. frame_exclusion可以是EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS.

expression指不包含窗函數調用的任何表達式。

注:

方括號[]指可選子句
圓括號{}指多種選項的集合，選項之間以豎槓|分割

例子2

窗函數允許訪問恰在當前記錄之前和之後的記錄的數據。^[5]^[6]^[7]^[8] 一個窗函數定義當前行周圍具有給定行數的幀或窗，並跨窗對數據集執行計算。^[9]^[10]

      NAME |
------------
      Aaron| <-- Preceding (unbounded)
     Andrew|
     Amelia|
      James|
       Jill|
     Johnny| <-- 1st preceding row
    Michael| <-- Current row
       Nick| <-- 1st following row
    Ophelia|
       Zach| <-- Following (unbounded)

在上表中，下一個查詢為每一行提取具有前一行和後一行的窗口的值w:

 SELECT
  LAG(name, 1) 
    OVER(ORDER BY name) "prev",
  name, 
  LEAD(name, 1) 
    OVER(ORDER BY name) "next"
 FROM people
 ORDER BY name

查詢結果為:

|     PREV |     NAME |     NEXT |
|----------|----------|----------|
|    (null)|     Aaron|    Andrew|
|     Aaron|    Andrew|    Amelia|
|    Andrew|    Amelia|     James|
|    Amelia|     James|      Jill|
|     James|      Jill|    Johnny|
|      Jill|    Johnny|   Michael|
|    Johnny|   Michael|      Nick|
|   Michael|      Nick|   Ophelia|
|      Nick|   Ophelia|      Zach|
|   Ophelia|      Zach|    (null)|

例子3

# 首先创建虚拟的业务员销售数据 
CREATE TABLE Sales
( 
idate date, 
iname char(2), 
sales int
); 
# 向表中插入数据 
INSERT INTO Sales VALUES 
('2021/1/1', '丁一', 200), 
('2021/2/1', '丁一', 180), 
('2021/2/1', '李四', 100), 
('2021/3/1', '李四', 150), 
('2021/2/1', '刘猛', 180), 
('2021/3/1', '刘猛', 150), 
('2021/1/1', '王二', 200), 
('2021/2/1', '王二', 180), 
('2021/3/1', '王二', 300), 
('2021/1/1', '张三', 300), 
('2021/2/1', '张三', 280), 
('2021/3/1', '张三', 280); 

# 查询各月中销售业绩最差的业务员
SELECT * FROM 
   (SELECT month(idate),iname,sales, 
        ROW_NUMBER() OVER(PARTITION BY month(idate) ORDER BY sales) as sales_order 
   FROM Sales) as t
WHERE sales_order=1;

例子4

求用戶連續登錄天數

 
# 首先创建虚拟的用户登录表，并插入数据 
create table user_login
( 
user_id varchar(100), 
login_time datetime
); 
insert into user_login values 
(1,'2020-11-25 13:21:12'), 
(1,'2020-11-24 13:15:22'), 
(1,'2020-11-24 10:30:15'), 
(1,'2020-11-24 09:18:27'), 
(1,'2020-11-23 07:43:54'), 
(1,'2020-11-10 09:48:36'), 
(1,'2020-11-09 03:30:22'), 
(1,'2020-11-01 15:28:29'), 
(1,'2020-10-31 09:37:45'), 
(2,'2020-11-25 13:54:40'), 
(2,'2020-11-24 13:22:32'), 
(2,'2020-11-23 10:55:52'), 
(2,'2020-11-22 06:30:09'), 
(2,'2020-11-21 08:33:15'), 
(2,'2020-11-20 05:38:18'), 
(2,'2020-11-19 09:21:42'), 
(2,'2020-11-02 00:19:38'), 
(2,'2020-11-01 09:03:11'), 
(2,'2020-10-31 07:44:55'), 
(2,'2020-10-30 08:56:33'), 
(2,'2020-10-29 09:30:28');  
  
# 第一种情况：查看每位用户连续登陆的情况。包括每位用户连续登录的情况、查看每位用户最大连续登录的天数、查看在某个时间段里连续登录天数超过N天的用户
select user_id, 
       min(login_date) start_date, 
       max(login_date) end_date, 
       count(login_date) days # 计算每位用户连续登录天数 
from (select *,date_sub(login_date, interval irank day) idate # 增加辅助列，以判断用户是否连续登录 
        from (select *,rank() over(partition by user_id order by login_date) irank # 对每个用户的登录日期排序 
                from (select distinct user_id, date(login_time) login_date from user_login 
                       # 数据预处理：由于统计的窗口期是天数，对登录时间字段格式转换为日期然后去重（去掉用户一天内多次登录的情况）
                     ) as a
            ) as b
    ) as c 
group by user_id,idate;

# 第二种情况：计算每个用户最大连续登录天数 
 
# 第三种情况：查看给定时间段内连续登录天数≥5天的用户  
# 找出相差天数为5的记录 
select distinct user_id 
    from (select *,datediff(idate5,login_date)+1 as days # 计算当前登录日期与之后第4次登陆的日期的差值 
              from (select *,lead(login_date,4) over(partition by user_id order by login_date) idate5 # 获取当前登录日期之后第4次登陆的日期
                       from user_logrin_date) 
                    as a)
         as b 
    where days = 5;

歷史

SQL:2003（英語：SQL:2003）引入了窗函數，其後的標準擴展了其功能。^[11]

MySQL從8.0開始引入了窗函數。目前支持的語法為：

# 开窗函数语法 
func_name(<parameter>) OVER([PARTITION BY <part_by_condition>] [ORDER BY <order_by_list> ASC|DESC])

參考文獻

^ ^1.0 ^1.1 Analytic function concepts in Standard SQL | BigQuery. Google Cloud. [2021-03-23]. （原始內容存檔於2022-03-24）（英語）.
^ Window Functions. sqlite.org. [2021-03-23]. （原始內容存檔於2022-11-17）.
^ 3.5. Window Functions. PostgreSQL Documentation. 2021-02-11 [2021-03-23]. （原始內容存檔於2022-10-31）（英語）.
^ ^4.0 ^4.1 4.2. Value Expressions. PostgreSQL Documentation. 2021-02-11 [2021-03-23]. （原始內容存檔於2022-10-10）（英語）.
^ Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas. Efficient Processing of Window Functions in Analytical SQL Queries. Proc. VLDB Endow. June 2015, 8 (10): 1058–1069. ISSN 2150-8097. doi:10.14778/2794367.2794375.
^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee. Optimization of Analytic Window Functions. Proc. VLDB Endow. July 2012, 5 (11): 1244–1255. ISSN 2150-8097. arXiv:1208.0086 . doi:10.14778/2350229.2350243.
^ Probably the Coolest SQL Feature: Window Functions. Java, SQL and jOOQ. 2013-11-03 [2017-09-26]. （原始內容存檔於2021-06-24）（美國英語）.
^ Window Functions in SQL - Simple Talk. Simple Talk. 2013-10-31 [2017-09-26]. （原始內容存檔於2021-09-22）（美國英語）.
^ SQL Window Functions Introduction. Apache Drill. （原始內容存檔於2022-10-10）.
^ PostgreSQL: Documentation: Window Functions. www.postgresql.org. [2020-04-04]. （原始內容存檔於2022-11-02）（英語）.
^ Window Functions Overview. MariaDB KnowledgeBase. [2021-03-23]. （原始內容存檔於2022-10-15）.

[:1-1] 1.0 ^1.1 Analytic function concepts in Standard SQL | BigQuery. Google Cloud. [2021-03-23]. （原始內容存檔於2022-03-24）（英語）.

[2] Window Functions. sqlite.org. [2021-03-23]. （原始內容存檔於2022-11-17）.

[3] 3.5. Window Functions. PostgreSQL Documentation. 2021-02-11 [2021-03-23]. （原始內容存檔於2022-10-31）（英語）.

[:0-4] 4.0 ^4.1 4.2. Value Expressions. PostgreSQL Documentation. 2021-02-11 [2021-03-23]. （原始內容存檔於2022-10-10）（英語）.

[5] Leis, Viktor; Kundhikanjana, Kan; Kemper, Alfons; Neumann, Thomas. Efficient Processing of Window Functions in Analytical SQL Queries. Proc. VLDB Endow. June 2015, 8 (10): 1058–1069. ISSN 2150-8097. doi:10.14778/2794367.2794375.

[6] Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee. Optimization of Analytic Window Functions. Proc. VLDB Endow. July 2012, 5 (11): 1244–1255. ISSN 2150-8097. arXiv:1208.0086 . doi:10.14778/2350229.2350243.

[7] Probably the Coolest SQL Feature: Window Functions. Java, SQL and jOOQ. 2013-11-03 [2017-09-26]. （原始內容存檔於2021-06-24）（美國英語）.

[8] Window Functions in SQL - Simple Talk. Simple Talk. 2013-10-31 [2017-09-26]. （原始內容存檔於2021-09-22）（美國英語）.

[9] SQL Window Functions Introduction. Apache Drill. （原始內容存檔於2022-10-10）.

[10] PostgreSQL: Documentation: Window Functions. www.postgresql.org. [2020-04-04]. （原始內容存檔於2022-11-02）（英語）.

[11] Window Functions Overview. MariaDB KnowledgeBase. [2021-03-23]. （原始內容存檔於2022-10-15）.

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]

[10]

[11]

閱論編 SQL
版本	SQL-86 SQL-89 SQL-92 SQL:1999（英語：SQL:1999） SQL:2003（英語：SQL:2003） SQL:2006（英語：SQL:2006） SQL:2008（英語：SQL:2008） SQL:2011（英語：SQL:2011） SQL:2016（英語：SQL:2016）
關鍵字	As（英語：Alias (SQL)） Case Create Delete From（英語：From (SQL)） Having（英語：Having (SQL)） Insert Join Merge（英語：Merge (SQL)） Null Order by（英語：Order by） Over Prepare（英語：Prepare (SQL)） Select Truncate Union（英語：Set operations (SQL)） Update Where（英語：Where (SQL)） With（英語：With (SQL)）
相關	埃德加·科德關係資料庫
ISO/IEC SQL部分	Framework（英語：SQL/Framework） Foundation（英語：SQL/Foundation） Call-Level Interface（英語：SQL/CLI） Persistent Stored Modules（英語：SQL/PSM） Management of External Data（英語：SQL/MED） Object Language Bindings（英語：SQL/OLB） Information and Definition Schemas（英語：SQL/Schemata） SQL Routines and Types for the Java Programming Language（英語：SQL/JRT） XML-Related Specifications（英語：SQL/XML）