聚合函式將結果集進行計算並且通常返回一行。視窗函式也是基於結果集的運算。與聚合函式不同的是，視窗函式並不會將結果集進行分組合並輸出一行；而是將計算的結果合併到基於結果集運算的列上。

語法

function_name ([expression [, expression ...]]) [FILTER (WHERE filter_clause)]
    OVER (window_definition)
    
window_definition:
  [existing_window_name]
  [PARTITION BY expression [, ...]]
  [ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST }] [, ...]]
  [frame_clause]

• OVER：表示視窗函式的關鍵字

• PARTITION BY：對查詢返回的結果集進行分組，之後視窗函式處理分組的資料。

• ORDER BY：設定結果集的分組資料排序

測試資料建立

-- 建立成績表
create table score(
id serial PRIMARY key,
subject   character(32),
stu_name  character(32),
grade     NUMERIC(3,0)
);
 
-- 插入資料
INSERT INTO SCORE(subject,stu_name,grade) values 
('語文','小王',80),
('語文','小張',70),
('語文','小李',80),
('英語','小王',90),
('英語','小張',70),
('英語','小李',50),
('數學','小王',100),
('數學','小張',70),
('數學','小李',65);

avg() OVER()

聚合函式後接 over屬性的視窗函式表示在一個查詢結果集上應用聚合函式。

查詢沒名學生學習成績並且顯示課程的平均分：

-- 對比
-- 先算課程的平均分，再用 score 表與平均分表關聯查詢
SELECT s.stu_name, s.subject, s.grade, v.avgscore
  FROM score s
  LEFT JOIN (
    SELECT subject, avg(grade) AS avgscore
      FROM score
     GROUP BY subject
  ) v ON s.subject = v.subject
 ORDER BY s.stu_name, s.subject
 
-- 使用視窗函式
SELECT s.stu_name, s.subject, grade,
       AVG(grade) OVER (PARTITION BY subject) AS avgscore
  FROM score s
 ORDER BY s.stu_name, s.subject

row_number()

對結果集分組後的資料標註行號，從 1 開始

SELECT row_number() OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;
  
SELECT row_number() OVER (ORDER BY score desc) as rownum, *
  FROM score;

rank()

當組內某行欄位值相同時，行號重複並且行號產生間隙

SELECT rank() OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

dense_rank()

當組內某行欄位值相同時，雖然行號重複，但行號不產生間隙

SELECT dense_rank() OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

lag()

獲取行偏移 offset 那行某個欄位的資料，語法如下：

lag(value anyelement [, offset integer [, default anyelement]])

• value：指定要返回記錄的欄位。
  

• offset：指行偏移量，可以是正整數或負整數，正整數表示取值結果集中向上偏移的記錄，負整數表示取結果集中向下偏移的記錄，預設值為 1。

• default：指如果不存在 offset 偏移的行時用預設值填充，default 值預設為 null。

獲取向上偏移 1 行記錄的 id 值

SELECT lag(id, 1) OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

first_value()

用來取結果集每一個分組的第一行資料的欄位值

score 表按課程分組後取分組的第一行的分數

SELECT first_value(score) OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

last_value()

用來取結果集每一個分組的最後一行資料的欄位值

score 表按課程分組後取分組的最後一行的分數

SELECT last_value(score) OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

nth_value()

用來取結果集每一個分組的指定行資料的欄位值

last_value(value any, nth integer)

• value：指定表的欄位

• nth：指定結果集分組資料中的第幾行，如果不存在則返回空

score 表按課程分組後取分組的第二行的分數

SELECT nth_value(score, 2) OVER (PARTITION BY subject ORDER BY score desc), *
  FROM score;

別名

如果 SQL 中需要多次使用視窗函式，可以使用視窗函式別名：

SELECT ... FROM ... WINDOW window_name AS ( window_definition )[, ...]

SELECT avg(score) OVER(r), sum(score) OVER(r), *
  FROM score
 WINDOW r as (PARTITION BY subject ORDER BY score desc)