我要评论前言
sql server 中的窗口函数（window functions）是一种强大的查询工具，它允许我们在查询结果集中对数据进行分区、排序和计算，而不会改变结果集的行数。窗口函数通过 over 子句定义一个“窗口”，在该窗口内对数据进行操作。这使得我们能够轻松实现排名、聚合、移动平均等复杂计算，而无需使用子查询或自连接。
窗口函数的基本语法是：
窗口函数 over (
[partition by 分区列]
[order by 排序列 [asc|desc]]
[rows | range between 边界1 and 边界2]
)
- partition by：将结果集分成多个分区，每个分区独立计算。
- order by：定义窗口内的排序顺序。
- rows/range：可选，定义窗口帧（frame），指定计算的行范围。
窗口函数主要分为三类：排名函数、聚合函数和分析函数。下面我们逐一介绍每个函数的具体用法和使用场景。为了说明，我们假设有一个名为 sales 的表，结构如下：
| orderid | product | quantity | price | orderdate |
|---|---|---|---|---|
| 1 | a | 10 | 100 | 2023-01-01 |
| 2 | b | 20 | 200 | 2023-01-02 |
| 3 | a | 15 | 150 | 2023-01-03 |
| … | … | … | … | … |
窗口函数完整列表
排名函数（ranking functions）
- row_number() - 为每行分配唯一的连续整数
- rank() - 分配排名，相同值相同排名，有间隙
- dense_rank() - 分配排名，相同值相同排名，无间隙
- ntile(n) - 将数据分成n个组
聚合函数（aggregate functions）
- sum() - 计算窗口内总和
- avg() - 计算窗口内平均值
- min() - 计算窗口内最小值
- max() - 计算窗口内最大值
- count() - 计算窗口内行数
- stdev() - 计算窗口内标准差
- stdevp() - 计算窗口内总体标准差
- var() - 计算窗口内方差
- varp() - 计算窗口内总体方差
分析函数（analytic functions）
- lead() - 访问后续行的值
- lag() - 访问前面行的值
- first_value() - 获取窗口内第一个值
- last_value() - 获取窗口内最后一个值
- nth_value() - 获取窗口内第n个值
分布函数（distribution functions）
- percent_rank() - 计算相对排名（0-1）
- cume_dist() - 计算累计分布（0-1）
- percentile_cont() - 连续百分位数
- percentile_disc() - 离散百分位数
每个函数详细介绍
1. row_number()
函数说明：为结果集中的每一行分配一个唯一的连续整数，从1开始递增。相同值的行会获得不同的行号。
语法：
row_number() over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
参数说明：
partition by：可选，将结果集分成多个分区，每个分区独立编号order by：必需，定义排序顺序，决定行号的分配
示例：
-- 按产品分组，按数量降序编号
select
product,
quantity,
row_number() over (partition by product order by quantity desc) as rownum
from sales;
-- 全局按价格降序编号
select
product,
price,
row_number() over (order by price desc) as globalrank
from sales;
使用场景：
- 分页查询（limit offset）
- 删除重复记录
- 生成唯一标识符
- 数据抽样
2. rank()
函数说明：为行分配排名，相同值的行获得相同排名，下一个排名会跳过（产生间隙）。
语法：
rank() over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 按价格排名，相同价格相同排名
select
product,
price,
rank() over (order by price desc) as pricerank
from sales;
结果示例：
product | price | pricerank --------|-------|---------- a | 200 | 1 b | 200 | 1 c | 150 | 3 (跳过了2) d | 100 | 4
使用场景：
- 竞赛排名
- 成绩排名
- 销售排行榜
3. dense_rank()
函数说明：类似于rank，但排名连续，没有间隙。相同值的行获得相同排名，下一个排名连续。
语法：
dense_rank() over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
select
product,
price,
dense_rank() over (order by price desc) as denserank
from sales;
结果示例：
product | price | denserank --------|-------|---------- a | 200 | 1 b | 200 | 1 c | 150 | 2 (连续，没有跳跃) d | 100 | 3
使用场景：
- 奖牌排名（金银铜）
- 等级评定
- 需要连续排名的场景
4. ntile(n)
函数说明：将分区内的行分成n个组，每组分配一个从1到n的数字。如果行数不能被n整除，前面的组会多一行。
语法：
ntile(组数) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 将数据分成4个四分位数
select
product,
price,
ntile(4) over (order by price desc) as quartile
from sales;
使用场景：
- 分位数分析
- 数据分桶
- 等级划分（如a、b、c、d级）
5. sum()
函数说明：计算窗口内列的总和，支持累计计算。
语法：
sum(列) over (
[partition by 分区列1, 分区列2, ...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...]
[rows | range between 边界1 and 边界2]
)
窗口帧选项：
rows between unbounded preceding and current row- 累计到当前行rows between 3 preceding and current row- 当前行及前3行rows between current row and unbounded following- 当前行到末尾
示例：
-- 累计销售总额
select
orderdate,
price,
sum(price) over (order by orderdate) as runningtotal
from sales;
-- 按产品分组的累计销售
select
product,
orderdate,
price,
sum(price) over (partition by product order by orderdate) as productrunningtotal
from sales;
-- 移动3天平均
select
orderdate,
price,
avg(price) over (order by orderdate rows between 2 preceding and current row) as movingavg3
from sales;
使用场景：
- 财务累计报表
- 销售趋势分析
- 移动平均计算
6. avg()
函数说明：计算窗口内列的平均值。
语法：
avg(列) over (
[partition by 分区列1, 分区列2, ...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...]
[rows | range between 边界1 and 边界2]
)
示例：
-- 每个产品的平均价格
select
product,
price,
avg(price) over (partition by product) as avgprice
from sales;
-- 移动平均
select
orderdate,
price,
avg(price) over (order by orderdate rows between 4 preceding and current row) as movingavg5
from sales;
使用场景：
- 趋势分析
- 股票技术分析
- 性能基准
7. min() / max()
函数说明：计算窗口内列的最小值/最大值。
语法：
min(列) over (
[partition by 分区列1, 分区列2, ...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...]
[rows | range between 边界1 and 边界2]
)
max(列) over (
[partition by 分区列1, 分区列2, ...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...]
[rows | range between 边界1 and 边界2]
)
示例：
-- 每个产品的历史最高价和最低价
select
product,
price,
min(price) over (partition by product) as minprice,
max(price) over (partition by product) as maxprice
from sales;
使用场景：
- 价格监控
- 极值分析
- 范围计算
8. count()
函数说明：计算窗口内的行数。
语法：
count(*) over (
[partition by 分区列1, 分区列2, ...]
[order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...]
[rows | range between 边界1 and 边界2]
)
示例：
-- 每个产品的订单数量
select
product,
count(*) over (partition by product) as productordercount
from sales;
使用场景：
- 分组统计
- 数据验证
- 频率分析
9. lead()
函数说明：访问当前行后n行的值。如果超出范围，返回默认值。
语法：
lead(列, n, 默认值) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
参数说明：
列：要访问的列n：向后偏移的行数（默认为1）默认值：当超出范围时返回的值（默认为null）
示例：
-- 计算价格变化
select
orderdate,
price,
lead(price, 1, 0) over (order by orderdate) as nextprice,
price - lead(price, 1, 0) over (order by orderdate) as pricechange
from sales;
使用场景：
- 时间序列分析
- 增长率计算
- 趋势预测
10. lag()
函数说明：访问当前行前n行的值。如果超出范围，返回默认值。
语法：
lag(列, n, 默认值) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 计算环比增长率
select
orderdate,
price,
lag(price, 1, price) over (order by orderdate) as prevprice,
(price - lag(price, 1, price) over (order by orderdate)) /
lag(price, 1, price) over (order by orderdate) * 100 as growthrate
from sales;
使用场景：
- 环比分析
- 历史比较
- 变化率计算
11. first_value()
函数说明：返回窗口帧中第一个值。
语法：
first_value(列) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
[rows | range between 边界1 and 边界2]
)
示例：
-- 每个产品的首次销售价格
select
product,
orderdate,
price,
first_value(price) over (partition by product order by orderdate) as firstprice
from sales;
使用场景：
- 基准值比较
- 初始状态记录
- 历史对比
12. last_value()
函数说明：返回窗口帧中最后一个值。注意：默认窗口帧到当前行，通常需要调整。
语法：
last_value(列) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
[rows | range between 边界1 and 边界2]
)
示例：
-- 每个产品的最新价格（需要调整窗口帧）
select
product,
orderdate,
price,
last_value(price) over (
partition by product
order by orderdate
rows between unbounded preceding and unbounded following
) as lastprice
from sales;
使用场景：
- 最新状态获取
- 最终值比较
- 状态跟踪
13. percent_rank()
函数说明：计算相对排名，返回0到1之间的值。第一名的值为0，最后一名的值为1。
语法：
percent_rank() over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 价格百分位排名
select
product,
price,
percent_rank() over (order by price desc) as pricepercentrank
from sales;
使用场景：
- 统计分布分析
- 百分位计算
- 数据标准化
14. cume_dist()
函数说明：计算累计分布，返回0到1之间的值。表示小于等于当前值的行数占总行数的比例。
语法：
cume_dist() over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 价格累计分布
select
product,
price,
cume_dist() over (order by price) as pricecumedist
from sales;
使用场景：
- 分布分析
- 分位数计算
- 数据分布可视化
15. percentile_cont()
函数说明：计算连续百分位数，返回插值后的值。
语法：
percentile_cont(百分位数) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 计算中位数（50%分位数）
select
product,
percentile_cont(0.5) over (partition by product order by price) as medianprice
from sales;
使用场景：
- 统计分位数
- 数据分布分析
- 异常值检测
16. percentile_disc()
函数说明：计算离散百分位数，返回实际存在的值。
语法：
percentile_disc(百分位数) over (
[partition by 分区列1, 分区列2, ...]
order by 排序列1 [asc|desc], 排序列2 [asc|desc], ...
)
示例：
-- 计算离散中位数
select
product,
percentile_disc(0.5) over (partition by product order by price) as discretemedian
from sales;
使用场景：
- 离散分位数
- 实际值分析
- 数据验证
排名函数
排名函数用于为行分配排名或分组。
1. row_number()
用法：为每个分区内的行分配一个唯一的连续整数，从1开始，按照 order by 排序。
语法：
row_number() over (partition by 分区列 order by 排序列)
例子：
select
product,
quantity,
row_number() over (partition by product order by quantity desc) as rownum
from sales;
结果（假设数据）：
- a, 15, 1
- a, 10, 2
- b, 20, 1
使用场景：分页查询、生成唯一行号、删除重复记录（结合 cte）。
2. rank()
用法：为行分配排名，如果值相同则排名相同，下一个排名会跳过（有间隙）。
语法：
rank() over (partition by 分区列 order by 排序列)
例子：
select
product,
quantity,
rank() over (order by quantity desc) as rank
from sales;
结果：
- b, 20, 1
- a, 15, 2
- a, 10, 3 (没有跳跃，因为没有并列)
如果有两个15，则：15排2，下一个跳到4。
使用场景：排名竞赛、识别前n名，但允许并列。
3. dense_rank()
用法：类似于 rank，但排名连续，没有间隙。
语法：
dense_rank() over (partition by 分区列 order by 排序列)
例子：同上，如果有两个15，则：15排2，下一个排3。
使用场景：需要连续排名的场景，如奖牌排名（金银铜连续）。
4. ntile(n)
用法：将分区内的行分成 n 个组，每组分配一个从1到n的数字。
语法：
ntile(组数) over (partition by 分区列 order by 排序列)
例子：
select
product,
quantity,
ntile(2) over (order by quantity desc) as tile
from sales;
结果：分成两组，前半组1，后半组2。
使用场景：分桶分析、将数据分成等份（如分位数）。
聚合函数
聚合函数如 sum、avg、min、max、count 可以与 over 结合，在窗口内计算。
1. sum()
用法：计算窗口内列的总和。
语法：
sum(列) over (partition by 分区列 order by 排序列 rows between unbounded preceding and current row)
例子（累计销售）：
select
orderdate,
price,
sum(price) over (order by orderdate) as runningtotal
from sales;
结果：每行显示到当前日期的累计总价。
使用场景：运行总计、累计求和、财务报告。
2. avg()
用法：计算平均值。
语法：类似 sum。
例子：计算移动平均。
使用场景：趋势分析、股票移动平均。
3. min() / max()
用法：窗口内最小/最大值。
例子：查找每个产品的历史最低价。
使用场景：价格监控、极值分析。
4. count()
用法：计数。
例子：计算每个分区内的行数。
使用场景：分组计数而不使用 group by。
分析函数
分析函数用于访问窗口中其他行的数据。
1. lead(列, n, 默认值)
用法：返回当前行后 n 行的列值。
语法：
lead(列, n, 默认值) over (partition by 分区列 order by 排序列)
例子：
select
orderdate,
price,
lead(price, 1, 0) over (order by orderdate) as nextprice
from sales;
结果：显示下一订单的价格。
使用场景：比较前后行、计算增长率、时间序列分析。
2. lag(列, n, 默认值)
用法：返回当前行前 n 行的列值。
语法：类似 lead。
例子：计算价格变化。
使用场景：与 lead 类似，用于历史比较。
3. first_value(列)
用法：返回窗口帧中第一个值。
语法：
first_value(列) over (partition by 分区列 order by 排序列 rows between unbounded preceding and unbounded following)
例子：每个产品的首次销售价格。
使用场景：基准值比较。
4. last_value(列)
用法：返回窗口帧中最后一个值。注意：默认帧到当前行，需要调整。
例子：每个产品的最新价格。
使用场景：最新状态获取。
其他函数
1. percent_rank()
用法：计算相对排名（0到1）。
语法：over (order by 排序列)
例子：百分位排名。
使用场景：统计分布。
2. cume_dist()
用法：累计分布（0到1）。
使用场景：分布分析。
结论
sql server 窗口函数极大地简化了复杂查询，提高了效率。通过掌握这些函数，你可以处理各种数据分析任务。建议在实际项目中练习，以加深理解。注意：窗口函数不支持在 where 或 group by 中使用，通常在 select 或 order by 中。
到此这篇关于sql server窗口函数的文章就介绍到这了,更多相关sql server窗口函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论