窗函數 (SQL)

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

## 例子1

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

```psql
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()`子句），那麼整個結果集被視為單個分組。對於此查詢，報告的平均工資將是所有行的平均值。

窗口函數在聚合之後進行評估（例如，在 Group by子句和非窗口聚合函數之後）。

## 語法

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

```psql
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`具有語法:

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

`frame_clause`具有下列之一的語法:

```psql
{ 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`指不包含窗函數調用的任何表達式。

注:

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

以下為各個語法項的舉例說明：

```psql
WITH t AS (
  SELECT * FROM (VALUES
    ('A', DATE '2024-01-01', 10),
    ('A', DATE '2024-01-02', 10),
    ('A', DATE '2024-01-02', 30),
    ('A', DATE '2024-01-03', 20),
    ('B', DATE '2024-01-01',  5),
    ('B', DATE '2024-01-02',  7),
    ('B', DATE '2024-01-02',  7),
    ('B', DATE '2024-01-04',  1)
  ) AS v(dept, dt, amt)
)
SELECT
  dept, dt, amt,
  SUM(amt) OVER w AS running_sum, -- 复用窗口名
  AVG(amt) OVER w AS running_avg -- 复用窗口名
FROM t
WINDOW w AS (PARTITION BY dept); -- 定义窗口名
```

```psql
WITH t AS (...) -- 具体定义见前例
SELECT
  dept, dt, amt,
  SUM(amt) OVER w1 AS sum_by_dept_date,
  SUM(amt) OVER w2 AS sum_by_dept_date_amt
FROM t
WINDOW
  w1 AS (PARTITION BY dept ),
  w2 AS (w1 ORDER BY dt, amt);  -- 继承已有窗口w1，再扩展 order by  
```

```psql
WITH t AS (
  SELECT * FROM (VALUES
    ('A', DATE '2024-01-01', 10),
    ('A', NULL::date, 99),
    ('A', DATE '2024-01-02', 20)
  ) v(dept, dt, amt)
)
SELECT
  dept, dt, amt,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY dt NULLS LAST)  AS null_last, -- NULLS FIRST / NULLS LAST（处理排序中 NULL 的位置）
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY dt NULLS FIRST) AS null_first
FROM t;
```

```psql
WITH t AS (SELECT * FROM (VALUES ('b'),('a'),('c')) v(x))
SELECT x,
       ROW_NUMBER() OVER (ORDER BY x USING <) AS rn -- USING operator（PostgreSQL：用特定比较运算符排序，较少用）
                                      -- 一般直接 ORDER BY x 就够，USING 多用于自定义类型或特殊排序语义。
FROM t;
```

frame子句，定義了窗口「幀」，決定聚合函數在每行看到哪些行。多數資料庫對 ORDER BY + 聚合有默認frame（PostgreSQL默認是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。frame可由ROWS、RANGES、GROUPS3種方式定義。

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt, amt
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- 从分区开头到当前行，按“物理行”定义帧
  ) AS running_sum_rows
FROM t;
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  AVG(amt) OVER (
    PARTITION BY dept
    ORDER BY dt, amt
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING -- 滑动窗口：前1行到后1行
  ) AS moving_avg_3rows
FROM t; 
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt, amt
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- 从当前行到分区末尾
  ) AS suffix_sum
FROM t;
```

RANGE：按「排序鍵的值的範圍」定義幀（會把ties一起算進去）。排序鍵值與當前行相同的行叫做tie。RANGE 的 offset 類型必須與排序鍵可相加/可比較（日期用 interval；數值用數字）。

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum_range
FROM t;
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt
    RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW -- 按值范围：昨天和今天
  ) AS sum_last_2days
FROM t;
```

group：在ORDER BY下排序鍵相同的行構成一組（ties 組）。

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt
    GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW -- 按“组”滑动，当前组以及前一组
  ) AS sum_prev_group_and_curr_group
FROM t; 
```

frame_exclusion：EXCLUDE ...（把某些行從幀中排除）

```psql
SUM(amt) OVER (
  PARTITION BY dept ORDER BY dt
  RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  EXCLUDE NO OTHERS -- 默认：不排除
)
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt, amt
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    EXCLUDE CURRENT ROW -- 排除当前行。也即：包含3行的滑窗里把当前行剔除，只加左右邻居。
  ) AS sum_neighbors_only
FROM t;
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE GROUP  -- 排除当前 peer group：即与当前行 ORDER BY 值相同的所有行。解释：累计到当前 dt，但把当前 dt 这组（同 dt 的所有行）排除。
  ) AS sum_before_current_date_group
FROM t;
```

```psql
WITH t AS (...)
SELECT
  dept, dt, amt,
  SUM(amt) OVER (
    PARTITION BY dept
    ORDER BY dt
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    EXCLUDE TIES  -- 排除同组里除当前行外的 ties。解释：如果当前 dt 有多行，只把“同 dt 的其它行”剔除，但当前行保留。
  ) AS sum_excluding_peers_except_self
FROM t; 
```

## 例子2

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

```
      NAME |

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

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

```psql
 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

```sql
# 首先创建虚拟的业务员销售数据 
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

求使用者連續登錄天數

```sql
 
# 首先创建虚拟的用户登录表，并插入数据 
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引入了窗函數，其後的標準擴展了其功能。

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

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