SQL 窗口函数有多少
列出最常用模板加示例
先 PARTITION → 再 ORDER → 最后 ROWS/RANGE 详细讲解
详细拆解每一步的含义、作用、执行顺序，以及它们如何共同决定窗口函数的计算结果。

🍋窗口函数 = 聚合函数 + 保留明细行

在 SQL 中，窗口函数允许我们对查询结果集中的每一行，基于其“窗口”（即一组相关行）进行计算，而不会将多行聚合成一行（区别于 GROUP BY）。

这三个部分（partition、order、rows/range）共同定义了一个“窗口”——即：对当前行而言，哪些其他行会被纳入计算范围。

一句话核心

一句话：先分组（partition by），再排序（order by），最后定窗口（rows/range），函数只在窗口内算。

over 是窗口函数的标志，它定义了函数作用的“窗口范围”。

partition：先分组，各算各的。类似 GROUP BY，但不下沉行
order：组内排队，定先后。
rows/range：再框定“当前行±几行”参与计算。

rows/range的语法：

rows 按“物理行”框范围，range 按“字段值”框范围，写法都是

rows/range between 边界1 and 边界2

起点/终点四选一：

unbounded preceding 分区头
n preceding 前 n 行/值
current row 当前行/值
n following 后 n 行/值
unbounded following 分区尾

示例：

前两行

rows between 1 preceding and current row

分区的所有行

rows between unbounded preceding and unbounded following

日期往前推一个月

1
2
3

select date,sales,sum(sales) over(order by date
		range between interval '1 month' preceding and current row) as month_cum
from sales_data

range between interval '1 month' preceding and current row
把日期值落在当前日期往前推 1 个月到当前日期之间的所有行都圈进来。物理行数不固定，只要日期差在 1 个月内就算，哪怕同一日期有 100 行也全部收进窗口。如现在是9.25，前100行刚好是9.1，前200行刚好是8.25，前201行刚好是8.24，那就取前200行

窗口函数的函数

列出sql窗口函数的所有函数，每个函数用一句话讲清楚它的用途
- 列出 SQL 标准中定义的全部窗口函数（Window Function），每个函数用一句话概括其核心用途
- 给出基于我提供的 5 行样例数据，把 24 个窗口函数全部跑一遍的完整 SQL 与查询结果。为了节省篇幅，所有函数放在一条 SELECT 里，按上面罗列顺序依次命名别名。
写得不够清晰，用起来不够
- 给出一份“直接能抄、一眼能懂”的速查表。左边是“我此刻想干什么”，右边是“该用哪个窗口函数、怎么写、注意什么”。所有例子都基于同一张表
没懂/什么意思
- rows 按“物理行”框范围，range 按“字段值”框范围没懂
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING什么意思
- 没懂
- 还是没懂，拆解语法
- 我不会用，教我
给出具体示例

排序类

row_number()
连续唯一序号（1,2,3,4）
rank()
每一行按排序值赋予名次，同值同秩，后续名次会跳号。
同值同秩：并列时排名相同，后续序号跳过（1,2,2,4）
dense_rank()
同 rank()，但同值同秩后不跳号，名次连续。
并列时排名相同，序号连续（1,2,2,3）
ntile(n)
将数据有序地平均分配到指定数量（n）的桶中，并返回所属桶号。

示例：shop店铺，date日期，sales销售额

shop	date	sales	rn	rnk	d_rnk	ntile3
A店	2025/9/1	100	1	7	6	3
A店	2025/9/2	200	2	5	4	2
A店	2025/9/3	300	3	3	2	1
A店	2025/9/4	400	4	1	1	1
B店	2025/9/1	150	1	6	5	3
B店	2025/9/2	250	2	4	3	2
B店	2025/9/3	400	3	1	1	1

取值类（偏移窗口函数）

速查：

lag 往前看，lead 往后看，first_value 队头，last_value 队尾（可到当前行），nth_value 第 n 个。

lag(expr, n) 获取当前行之前第n行的数据
lead(expr, n) 获取当前行之后第n行的数据
first_value(expr) 返回窗口内第一行的数据
first_value(expr) 返回窗口内第一行的数据
nth_value(expr, n) 返回窗口内第n行的数据

expr = expression，就是“任何能算出一个值的式子”——列名、常量、函数、加减乘除、case when … 都算。

示例：shop店铺，date日期，sales销售额

shop	date	sales	lag1	lead1	first_val	last_val	nth2
A店	2025/9/1	100	null	200	100	400	200
A店	2025/9/2	200	100	300	100	400	200
A店	2025/9/3	300	200	400	100	400	200
A店	2025/9/4	400	300	null	100	400	200
B店	2025/9/1	150	null	250	150	400	250
B店	2025/9/2	250	150	400	150	400	250
B店	2025/9/3	400	250	null	150	400	250

以下均按shop分区，按date升序
- lag1 ＝上一行 sales；首行为 null。
- lead1 ＝下一行 sales；末行为 null。
- first_val ＝该店第一行 sales（恒 100/150）。
- last_val ＝该店最后一行 sales（400），因为手动把 frame 拉到整个分区。
- nth2 ＝该店第二行 sales（200/250）。
last_value(sales) over(partition by shop order by date rows between unbounded preceding and unbounded following) as last_val是不是冗余了
- 默认不写 frame 时，last_value 的窗口是 range between unbounded preceding and current row → 只能看到“第一行 → 当前行”，最后一行根本还没出现，因此这句 frame 非冗余，是必需。
- Frame?
  frame = 窗口帧，就是“在分区内再圈一个小范围”的那一段定义

聚合类（作为窗口函数使用）

把 GROUP BY 的聚合结果“摊平”到每一行，还能让它只累加当前行之前、或前后 N 行，而不是一次性全算完。

sum(expr) 计算分区内（或框架内）expr 的累计和。
avg(expr) 计算分区内（或框架内）expr 的累计平均值。
count(expr) 统计分区内（或框架内）非 NULL 行数。
min(expr) 取分区内（或框架内）到当前行的最小值。
max(expr) 取分区内（或框架内）到当前行的最大值。

示例：shop店铺，date日期，sales销售额

shop	date	sales	run_sum	run_avg	run_cnt	run_min	run_max
A店	2025/9/1	100	100	100	1	100	100
A店	2025/9/2	200	300	150	2	100	200
A店	2025/9/3	300	600	200	3	100	300
A店	2025/9/4	400	1000	250	4	100	400
B店	2025/9/1	150	150	150	1	150	150
B店	2025/9/2	250	400	200	2	150	250
B店	2025/9/3	400	800	266.7	3	150	400
B店	2025/9/4	null	800	266.7	3	150	400

把这一批结果一眼看懂：

带 ORDER BY 的那 5 个（run_*）。
默认窗口 = 从分区第一行累加到当前行，所以值逐行变大（或保持），就是“店铺内到当天的累计和/均值/行数/最小/最大”。

统计类

percent_rank() 计算当前行的相对排名（百分比），取值 0～1。公式为：(rank - 1) / (总行数 - 1)
cume_dist() 计算当前行的累积分布（小于等于当前值的行数占总行数的比例）
stddev_pop(expr) 计算分区内总体标准差。衡量数据相对于平均数的离散程度。适用于分析完整总体（而非样本）的波动性
stddev_samp(expr) 计算分区内样本标准差。当数据是总体中的一个样本时，使用此函数可得到对总体标准差的无偏估计
var_pop(expr) 计算分区内总体方差。标准差的平方，同样反映数据离散程度，但单位是原数据的平方
var_samp(expr) 计算分区内样本方差。分母为n-1，是对总体方差的无偏估计。常用于抽样数据
covar_pop(y, x) 计算分区内两变量的总体协方差。衡量两个变量的总体协同变化趋势。值为正表示正相关，为负表示负相关
covar_samp(y, x) 计算分区内两变量的样本协方差。基于样本数据对总体协方差进行无偏估计
corr(y, x) 计算分区内两变量的皮尔逊相关系数。衡量两个变量间的线性相关程度，返回值介于-1（完全负相关）和1（完全正相关）之间，结果已标准化，不受数据原始尺度影响
regr_*（系列）用于执行线性回归分析。如 regr_slope(斜率)、regr_intercept(截距)、regr_r2(决定系数)等

PERCENT_RANK()和CUME_DIST()这两个函数，销售额大的数值大还是小？日常使用，用降序还是升序？
- 降序（最常用，看头部好数据）：
  - 销售额越高 → 两个函数的值越小（最高销售额对应 PERCENT_RANK=0、CUME_DIST=1 / 总行数）
  - 解读直觉：值越小 = 表现越好（比如 PERCENT_RANK=0.1 → 比 90% 的对象好；CUME_DIST=0.2 → 高价值数据占 20%）
- 升序（仅用在看尾部差数据）：
  - 销售额越低 → 两个函数的值越小（最低销售额对应 PERCENT_RANK=0、CUME_DIST=1 / 总行数）
  - 解读直觉：值越小 = 表现越差（比如 PERCENT_RANK=0.1 → 比 90% 的对象差；CUME_DIST=0.3 → 低价值数据占 30%）
把这一批结果一眼看懂：
- 不带 ORDER BY 的那 7 个（stddev_pop 及以后）。
  窗口 = 整个店铺，所以每行拿到的结果一模一样，相当于把 group by 的聚合值“摊平”到每一行。
特殊写法
- COVAR_POP(sales, sales) 退化成总体方差，结果与 VAR_POP(sales) 相同。
- CORR(sales, sales) 恒为 1（自己跟自己完全相关）。
- REGR_SLOPE(sales, date_epoch) 给出销售额随时间增长的日均斜率（销售额/天），正数表示上涨，负数表示下降。

上面示例的代码

PostgreSQL语法

原始数据：

('A店','2025-09-01',100),
('A店','2025-09-02',200),
('A店','2025-09-03',300),
('B店','2025-09-01',150),
('B店','2025-09-02',250);

加了同样销售额400的数据：

排序类+取值类

create table t(shop text, dt date, sales int);
insert into t values
('A店','2025-09-01',100),
('A店','2025-09-02',200),
('A店','2025-09-03',300),
('A店','2025-09-04',400),
('B店','2025-09-01',150),
('B店','2025-09-02',250),
('B店','2025-09-03',400);

select
    shop,dt,sales

    /* 排序类 */
    ,row_number()         over(partition by shop order by dt) as rn
    ,rank()               over(order by sales DESC) as rnk
    ,dense_rank()         over(order by sales DESC) as d_rnk
    ,ntile(3)             over(order by sales DESC) as ntile3

    /* 取值类 */
    ,lag(sales,1)         over(partition by shop order by dt) as lag1
    ,lead(sales,1)        over(partition by shop order by dt) as lead1
    ,first_value(sales)   over(partition by shop order by dt) as first_val
    ,last_value(sales)    over(partition by shop order by dt rows between unbounded preceding and unbounded following) as last_val
    ,nth_value(sales,2)   over(partition by shop order by dt rows between unbounded preceding and unbounded following) as nth2
  
from(
    select *,extract(epoch from dt) as date_epoch   -- 把日期转数值，方便回归
    from t
) as x
order by shop, dt;

聚合类

create table t(shop text, dt date, sales int);
insert into t values
('A店','2025-09-01',100),
('A店','2025-09-02',200),
('A店','2025-09-03',300),
('A店','2025-09-04',400),
('B店','2025-09-01',150),
('B店','2025-09-02',250),
('B店','2025-09-03',400),
('B店','2025-09-04',null);

select
    shop,dt,sales

    /* 聚合类 */
    ,sum(sales)           over(partition by shop order by dt) as run_sum
    ,round(avg(sales)     over(partition by shop order by dt), 1) as run_avg
    ,count(sales)         over(partition by shop order by dt) as run_cnt
    ,min(sales)           over(partition by shop order by dt) as run_min
    ,max(sales)           over(partition by shop order by dt) as run_max
  
from(
    select *,extract(epoch from dt) as date_epoch   -- 把日期转数值，方便回归
    from t
) as x
order by shop, dt;

统计类

create table t(shop text, dt date, sales int);
insert into t values
('A店','2025-09-01',100),
('A店','2025-09-02',200),
('A店','2025-09-03',300),
('A店','2025-09-04',400),
('B店','2025-09-01',150),
('B店','2025-09-02',250),
('B店','2025-09-03',400);

select
    shop,dt,sales

    /* 统计类 */
    ,percent_rank()       over(partition by shop order by sales DESC) as pct_rnk
    ,cume_dist()         over(partition by shop order by sales) as cume_dist
    ,stddev_pop(sales)   over(partition by shop) as stddev_pop
    ,stddev_samp(sales)  over(partition by shop) as stddev_samp
    ,var_pop(sales)      over(partition by shop) as var_pop
    ,var_samp(sales)     over(partition by shop) as var_samp
    ,corr(sales, date_epoch) over(partition by shop) as corr_coef
    ,covar_pop(sales, date_epoch) over(partition by shop) as covar_pop
    ,covar_samp(sales, date_epoch) over(partition by shop) as covar_samp
    ,regr_slope(sales, date_epoch) over(partition by shop) as regr_slope
    ,regr_intercept(sales, date_epoch) over(partition by shop) as regr_intercept
    ,regr_r2(sales, date_epoch) over(partition by shop) as regr_r2
  
from(
    select *,extract(epoch from dt) as date_epoch   -- 把日期转数值，方便回归
    from t
) as x
order by shop, dt;

trino语法

drop table if exists t;
create table t(shop varchar, dt varchar, sales integer);
insert into t values
('A店','2025-09-01',100),
('A店','2025-09-02',200),
('A店','2025-09-03',300),
('A店','2025-09-04',400),
('B店','2025-09-01',150),
('B店','2025-09-02',250),
('B店','2025-09-03',400),
('B店','2025-09-04',null);

select
    shop,dt,sales

    /* 聚合类 */
    ,sum(sales)           over(partition by shop order by dt) as run_sum
    ,round(avg(sales)     over(partition by shop order by dt), 1) as run_avg
    ,count(sales)         over(partition by shop order by dt) as run_cnt
    ,min(sales)           over(partition by shop order by dt) as run_min
    ,max(sales)           over(partition by shop order by dt) as run_max
  
from(
    select *,to_unixtime(cast(dt as timestamp with time zone)) as date_epoch  -- 把日期转数值，方便回归
    from t
) as x
order by shop, dt;

最常用模板

累计值
1. sum()
分组累计（按店铺）
1. sum()、partition by
移动平均（近2行）
1. 对每个店，算最近两天平均销售额
2. avg()、partition by、row 1 preceding
3. 历史平均销售额rows between unbounded preceding and 1 preceding
环比（上一日）
1. lag()先取出上一日数据
2. 两个字段名的英文来源如下：
  1. prev_sales prev 是 previous（之前的）的缩写，sales 即“销售额”，合起来表示“上一条销售额”。
  2. delta 源自希腊字母 Δ（delta），在数学/编程里习惯用它表示“差值、变化量”，这里即“销售额的变化量”。
排名/去重（每组只留第一行/取最新的记录/取TOP-N）
1. row_number()、partition by、DESC
一次性定义多个窗口（省代码）
1. over w
2. window w as (partition by shop order by dt)

范围窗口（近 1 个月）

range between interval ‘1 month’ preceding and current row

INTERVAL ‘1 month’怎么算的

加减一个月先找“同号日”，没有就拉回到目标月最后一天；范围窗口以此算出闭区间边界。

SELECT date '2025-01-30' + INTERVAL '1 month';  -- 2025-02-28（2025 非闰年）
SELECT date '2024-01-30' + INTERVAL '1 month';  -- 2024-02-29（闰年）
SELECT date '2025-03-31' - INTERVAL '1 month';  -- 2025-02-28

date/timestamp
时间戳同理，时分秒保留
SELECT timestamp '2025-01-30 23:59:59' + INTERVAL '1 month';
-- 2025-02-28 23:59:59