窗函数 (SQL) 📖 Wikipedia

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

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

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

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

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); -- 定义窗口名

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

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;

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;

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;

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;

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;

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;

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;

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;

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

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;

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;

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;

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