窗函式 (SQL)

在SQL中，窗函式(window function)或分析函式(analytic function)是一個函式，它使用來自一行或多行的值來為每一行返回一個值。 與之形成對比，為多行返回單個值。視窗函式有一個codice_1子句；任何沒有codice_1子句的函式都不是視窗函式，而是聚合函式或單行函式。例子1.例如，這裡有一個查詢，它使用一個視窗函式來比較每個員工的工資與其部門的平均工資:SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;輸出:depname | empno | salary | avgdevelop | 11 | 5200 | 5020.0000000000000000develop | 7 | 4200 | 5020.0000000000000000develop | 9 | 4500 | 5020.0000000000000000develop | 8 | 6000 | 5020.0000000000000000develop | 10 | 5200 | 5020.0000000000000000personnel | 5 | 3500 | 3700.0000000000000000personnel | 2 | 3900 | 3700.0000000000000000sales | 3 | 4800 | 4866.6666666666666667sales | 1 | 5000 | 4866.6666666666666667sales | 4 | 4800 | 4866.6666666666666667(10 rows)codice_3子句將行分組，並且該函式分別應用於每個分組。 如果codice_3子句被省略，那麼整個被視為單個分組。對於此查詢，報告的平均工資將是所有行的平均值。視窗函式在聚合之後進行評估。語法.根據PostgreSQL文件，窗函式具有下列之一的語法:function_name ([expression [, expression ... ]]) OVER window_namefunction_name ([expression [, expression ... ]]) OVER ( window_definition )function_name ( * ) OVER window_namefunction_name ( * ) OVER ( window_definition )其中codice_6包括：其中 codice_7具有語法:[ existing_window_name ][ PARTITION BY expression [, ...] ][ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ][ frame_clause ]codice_8具有下列之一的語法:codice_9與codice_10可以是codice_11, codice_12, codice_13, codice_14, codice_15. codice_16可以是codice_17, codice_18, codice_19, codice_20.codice_21指不包含窗函式呼叫的任何表示式。注:例子2.窗函式允許訪問恰在當前記錄之前和之後的記錄的資料。 一個窗函式定義當前行周圍具有給定行數的幀或窗，並跨窗對資料集執行計算。NAME |Aaron| &lt;-- Preceding (unbounded)Andrew|Amelia|James|Jill|Johnny| &lt;-- 1st preceding rowMichael| &lt;-- Current rowNick| &lt;-- 1st following rowOphelia|Zach| &lt;-- Following (unbounded)在上表中，下一個查詢為每一行提取具有前一行和後一行的視窗的值w:SELECTLAG(name, 1)OVER(ORDER BY name) "prev",name,LEAD(name, 1)OVER(ORDER BY name) "next"FROM peopleORDER 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 Salesidate date,iname char(2),sales intINSERT 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_orderFROM Sales) as tWHERE sales_order=1;例子4.求使用者連續登入天數create table user_loginuser_id varchar(100),login_time datetimeinsert 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');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 cgroup by user_id,idate;select distinct user_idfrom (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 bwhere days = 5;歷史.引入了窗函式，其後的標準擴充套件了其功能。MySQL從8.0開始引入了窗函式。目前支援的語法為：func_name( ASC|DESC])