SQL Server窗口函数详细指南(函数用法与场景)
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">前言</a></li><li><a href="#_label1">窗口函数完整列表</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">排名函数（Ranking Functions）</a></li><li><a href="#_lab2_1_1">聚合函数（Aggregate Functions）</a></li><li><a href="#_lab2_1_2">分析函数（Analytic Functions）</a></li><li><a href="#_lab2_1_3">分布函数（Distribution Functions）</a></li></ul><li><a href="#_label2">每个函数详细介绍</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_4">1. ROW_NUMBER()</a></li><li><a href="#_lab2_2_5">2. RANK()</a></li><li><a href="#_lab2_2_6">3. DENSE_RANK()</a></li><li><a href="#_lab2_2_7">4. NTILE(n)</a></li><li><a href="#_lab2_2_8">5. SUM()</a></li><li><a href="#_lab2_2_9">6. AVG()</a></li><li><a href="#_lab2_2_10">7. MIN() / MAX()</a></li><li><a href="#_lab2_2_11">8. COUNT()</a></li><li><a href="#_lab2_2_12">9. LEAD()</a></li><li><a href="#_lab2_2_13">10. LAG()</a></li><li><a href="#_lab2_2_14">11. FIRST_VALUE()</a></li><li><a href="#_lab2_2_15">12. LAST_VALUE()</a></li><li><a href="#_lab2_2_16">13. PERCENT_RANK()</a></li><li><a href="#_lab2_2_17">14. CUME_DIST()</a></li><li><a href="#_lab2_2_18">15. PERCENTILE_CONT()</a></li><li><a href="#_lab2_2_19">16. PERCENTILE_DISC()</a></li></ul><li><a href="#_label3">排名函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_20">1. ROW_NUMBER()</a></li><li><a href="#_lab2_3_21">2. RANK()</a></li><li><a href="#_lab2_3_22">3. DENSE_RANK()</a></li><li><a href="#_lab2_3_23">4. NTILE(n)</a></li></ul><li><a href="#_label4">聚合函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_24">1. SUM()</a></li><li><a href="#_lab2_4_25">2. AVG()</a></li><li><a href="#_lab2_4_26">3. MIN() / MAX()</a></li><li><a href="#_lab2_4_27">4. COUNT()</a></li></ul><li><a href="#_label5">分析函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_28">1. LEAD(列, n, 默认值)</a></li><li><a href="#_lab2_5_29">2. LAG(列, n, 默认值)</a></li><li><a href="#_lab2_5_30">3. FIRST_VALUE(列)</a></li><li><a href="#_lab2_5_31">4. LAST_VALUE(列)</a></li></ul><li><a href="#_label6">其他函数</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_32">1. PERCENT_RANK()</a></li><li><a href="#_lab2_6_33">2. CUME_DIST()</a></li></ul><li><a href="#_label7">结论</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>前言</h2><p>SQL Server 中的窗口函数（Window Functions）是一种强大的查询工具，它允许我们在查询结果集中对数据进行分区、排序和计算，而不会改变结果集的行数。窗口函数通过 OVER 子句定义一个“窗口”，在该窗口内对数据进行操作。这使得我们能够轻松实现排名、聚合、移动平均等复杂计算，而无需使用子查询或自连接。</p>
<p>窗口函数的基本语法是：</p>
<div class="jb51code"><pre class="brush:sql;">窗口函数 OVER (
]
)
</pre></div>
<ul><li><strong>PARTITION BY</strong>：将结果集分成多个分区，每个分区独立计算。</li><li><strong>ORDER BY</strong>：定义窗口内的排序顺序。</li><li><strong>ROWS/RANGE</strong>：可选，定义窗口帧（frame），指定计算的行范围。</li></ul>
<p>窗口函数主要分为三类：排名函数、聚合函数和分析函数。下面我们逐一介绍每个函数的具体用法和使用场景。为了说明，我们假设有一个名为 <code>Sales</code> 的表，结构如下：</p>
<table><thead><tr><th>OrderID</th><th>Product</th><th>Quantity</th><th>Price</th><th>OrderDate</th></tr></thead><tbody><tr><td>1</td><td>A</td><td>10</td><td>100</td><td>2023-01-01</td></tr><tr><td>2</td><td>B</td><td>20</td><td>200</td><td>2023-01-02</td></tr><tr><td>3</td><td>A</td><td>15</td><td>150</td><td>2023-01-03</td></tr><tr><td>…</td><td>…</td><td>…</td><td>…</td><td>…</td></tr></tbody></table>
<p class="maodian"><a name="_label1"></a></p><h2>窗口函数完整列表</h2>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>排名函数（Ranking Functions）</h3>
<ol><li><strong>ROW_NUMBER()</strong> - 为每行分配唯一的连续整数</li><li><strong>RANK()</strong> - 分配排名，相同值相同排名，有间隙</li><li><strong>DENSE_RANK()</strong> - 分配排名，相同值相同排名，无间隙</li><li><strong>NTILE(n)</strong> - 将数据分成n个组</li></ol>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>聚合函数（Aggregate Functions）</h3>
<ol start="5"><li><strong>SUM()</strong> - 计算窗口内总和</li><li><strong>AVG()</strong> - 计算窗口内平均值</li><li><strong>MIN()</strong> - 计算窗口内最小值</li><li><strong>MAX()</strong> - 计算窗口内最大值</li><li><strong>COUNT()</strong> - 计算窗口内行数</li><li><strong>STDEV()</strong> - 计算窗口内标准差</li><li><strong>STDEVP()</strong> - 计算窗口内总体标准差</li><li><strong>VAR()</strong> - 计算窗口内方差</li><li><strong>VARP()</strong> - 计算窗口内总体方差</li></ol>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>分析函数（Analytic Functions）</h3>
<ol start="14"><li><strong>LEAD()</strong> - 访问后续行的值</li><li><strong>LAG()</strong> - 访问前面行的值</li><li><strong>FIRST_VALUE()</strong> - 获取窗口内第一个值</li><li><strong>LAST_VALUE()</strong> - 获取窗口内最后一个值</li><li><strong>NTH_VALUE()</strong> - 获取窗口内第n个值</li></ol>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>分布函数（Distribution Functions）</h3>
<ol start="19"><li><strong>PERCENT_RANK()</strong> - 计算相对排名（0-1）</li><li><strong>CUME_DIST()</strong> - 计算累计分布（0-1）</li><li><strong>PERCENTILE_CONT()</strong> - 连续百分位数</li><li><strong>PERCENTILE_DISC()</strong> - 离散百分位数</li></ol>
<p class="maodian"><a name="_label2"></a></p><h2>每个函数详细介绍</h2>
<p class="maodian"><a name="_lab2_2_4"></a></p><p class="maodian"><a name="_lab2_3_20"></a></p><h3>1. ROW_NUMBER()</h3>
<p><strong>函数说明</strong>：为结果集中的每一行分配一个唯一的连续整数，从1开始递增。相同值的行会获得不同的行号。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">ROW_NUMBER() OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>参数说明</strong>：</p>
<ul><li><code>PARTITION BY</code>：可选，将结果集分成多个分区，每个分区独立编号</li><li><code>ORDER BY</code>：必需，定义排序顺序，决定行号的分配</li></ul>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 按产品分组，按数量降序编号
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>分页查询（LIMIT OFFSET）</li><li>删除重复记录</li><li>生成唯一标识符</li><li>数据抽样</li></ul>
<p class="maodian"><a name="_lab2_2_5"></a></p><p class="maodian"><a name="_lab2_3_21"></a></p><h3>2. RANK()</h3>
<p><strong>函数说明</strong>：为行分配排名，相同值的行获得相同排名，下一个排名会跳过（产生间隙）。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">RANK() OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 按价格排名，相同价格相同排名
SELECT
Product,
Price,
RANK() OVER (ORDER BY Price DESC) AS PriceRank
FROM Sales;
</pre></div>
<p><strong>结果示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">Product | Price | PriceRank
--------|-------|----------
A | 200 | 1
B | 200 | 1
C | 150 | 3(跳过了2)
D | 100 | 4
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>竞赛排名</li><li>成绩排名</li><li>销售排行榜</li></ul>
<p class="maodian"><a name="_lab2_2_6"></a></p><p class="maodian"><a name="_lab2_3_22"></a></p><h3>3. DENSE_RANK()</h3>
<p><strong>函数说明</strong>：类似于RANK，但排名连续，没有间隙。相同值的行获得相同排名，下一个排名连续。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">DENSE_RANK() OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
Product,
Price,
DENSE_RANK() OVER (ORDER BY Price DESC) AS DenseRank
FROM Sales;
</pre></div>
<p><strong>结果示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">Product | Price | DenseRank
--------|-------|----------
A | 200 | 1
B | 200 | 1
C | 150 | 2(连续，没有跳跃)
D | 100 | 3
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>奖牌排名（金银铜）</li><li>等级评定</li><li>需要连续排名的场景</li></ul>
<p class="maodian"><a name="_lab2_2_7"></a></p><p class="maodian"><a name="_lab2_3_23"></a></p><h3>4. NTILE(n)</h3>
<p><strong>函数说明</strong>：将分区内的行分成n个组，每组分配一个从1到n的数字。如果行数不能被n整除，前面的组会多一行。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">NTILE(组数) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 将数据分成4个四分位数
SELECT
Product,
Price,
NTILE(4) OVER (ORDER BY Price DESC) AS Quartile
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>分位数分析</li><li>数据分桶</li><li>等级划分（如A、B、C、D级）</li></ul>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>5. SUM()</h3>
<p><strong>函数说明</strong>：计算窗口内列的总和，支持累计计算。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SUM(列) OVER (
, 排序列2 , ...]
)
</pre></div>
<p><strong>窗口帧选项</strong>：</p>
<ul><li><code>ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> - 累计到当前行</li><li><code>ROWS BETWEEN 3 PRECEDING AND CURRENT ROW</code> - 当前行及前3行</li><li><code>ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING</code> - 当前行到末尾</li></ul>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 累计销售总额
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>财务累计报表</li><li>销售趋势分析</li><li>移动平均计算</li></ul>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>6. AVG()</h3>
<p><strong>函数说明</strong>：计算窗口内列的平均值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">AVG(列) OVER (
, 排序列2 , ...]
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 每个产品的平均价格
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>趋势分析</li><li>股票技术分析</li><li>性能基准</li></ul>
<p class="maodian"><a name="_lab2_2_10"></a></p><h3>7. MIN() / MAX()</h3>
<p><strong>函数说明</strong>：计算窗口内列的最小值/最大值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">MIN(列) OVER (
, 排序列2 , ...]
)
MAX(列) OVER (
, 排序列2 , ...]
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 每个产品的历史最高价和最低价
SELECT
Product,
Price,
MIN(Price) OVER (PARTITION BY Product) AS MinPrice,
MAX(Price) OVER (PARTITION BY Product) AS MaxPrice
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>价格监控</li><li>极值分析</li><li>范围计算</li></ul>
<p class="maodian"><a name="_lab2_2_11"></a></p><h3>8. COUNT()</h3>
<p><strong>函数说明</strong>：计算窗口内的行数。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">COUNT(*) OVER (
, 排序列2 , ...]
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 每个产品的订单数量
SELECT
Product,
COUNT(*) OVER (PARTITION BY Product) AS ProductOrderCount
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>分组统计</li><li>数据验证</li><li>频率分析</li></ul>
<p class="maodian"><a name="_lab2_2_12"></a></p><h3>9. LEAD()</h3>
<p><strong>函数说明</strong>：访问当前行后n行的值。如果超出范围，返回默认值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">LEAD(列, n, 默认值) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>参数说明</strong>：</p>
<ul><li><code>列</code>：要访问的列</li><li><code>n</code>：向后偏移的行数（默认为1）</li><li><code>默认值</code>：当超出范围时返回的值（默认为NULL）</li></ul>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 计算价格变化
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>时间序列分析</li><li>增长率计算</li><li>趋势预测</li></ul>
<p class="maodian"><a name="_lab2_2_13"></a></p><h3>10. LAG()</h3>
<p><strong>函数说明</strong>：访问当前行前n行的值。如果超出范围，返回默认值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">LAG(列, n, 默认值) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 计算环比增长率
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>环比分析</li><li>历史比较</li><li>变化率计算</li></ul>
<p class="maodian"><a name="_lab2_2_14"></a></p><h3>11. FIRST_VALUE()</h3>
<p><strong>函数说明</strong>：返回窗口帧中第一个值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">FIRST_VALUE(列) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 每个产品的首次销售价格
SELECT
Product,
OrderDate,
Price,
FIRST_VALUE(Price) OVER (PARTITION BY Product ORDER BY OrderDate) AS FirstPrice
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>基准值比较</li><li>初始状态记录</li><li>历史对比</li></ul>
<p class="maodian"><a name="_lab2_2_15"></a></p><h3>12. LAST_VALUE()</h3>
<p><strong>函数说明</strong>：返回窗口帧中最后一个值。注意：默认窗口帧到当前行，通常需要调整。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">LAST_VALUE(列) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 每个产品的最新价格（需要调整窗口帧）
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;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>最新状态获取</li><li>最终值比较</li><li>状态跟踪</li></ul>
<p class="maodian"><a name="_lab2_2_16"></a></p><h3>13. PERCENT_RANK()</h3>
<p><strong>函数说明</strong>：计算相对排名，返回0到1之间的值。第一名的值为0，最后一名的值为1。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">PERCENT_RANK() OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 价格百分位排名
SELECT
Product,
Price,
PERCENT_RANK() OVER (ORDER BY Price DESC) AS PricePercentRank
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>统计分布分析</li><li>百分位计算</li><li>数据标准化</li></ul>
<p class="maodian"><a name="_lab2_2_17"></a></p><h3>14. CUME_DIST()</h3>
<p><strong>函数说明</strong>：计算累计分布，返回0到1之间的值。表示小于等于当前值的行数占总行数的比例。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">CUME_DIST() OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 价格累计分布
SELECT
Product,
Price,
CUME_DIST() OVER (ORDER BY Price) AS PriceCumeDist
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>分布分析</li><li>分位数计算</li><li>数据分布可视化</li></ul>
<p class="maodian"><a name="_lab2_2_18"></a></p><h3>15. PERCENTILE_CONT()</h3>
<p><strong>函数说明</strong>：计算连续百分位数，返回插值后的值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">PERCENTILE_CONT(百分位数) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 计算中位数（50%分位数）
SELECT
Product,
PERCENTILE_CONT(0.5) OVER (PARTITION BY Product ORDER BY Price) AS MedianPrice
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>统计分位数</li><li>数据分布分析</li><li>异常值检测</li></ul>
<p class="maodian"><a name="_lab2_2_19"></a></p><h3>16. PERCENTILE_DISC()</h3>
<p><strong>函数说明</strong>：计算离散百分位数，返回实际存在的值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">PERCENTILE_DISC(百分位数) OVER (
ORDER BY 排序列1 , 排序列2 , ...
)
</pre></div>
<p><strong>示例</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 计算离散中位数
SELECT
Product,
PERCENTILE_DISC(0.5) OVER (PARTITION BY Product ORDER BY Price) AS DiscreteMedian
FROM Sales;
</pre></div>
<p><strong>使用场景</strong>：</p>
<ul><li>离散分位数</li><li>实际值分析</li><li>数据验证</li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>排名函数</h2>
<p>排名函数用于为行分配排名或分组。</p>
<h3>1. ROW_NUMBER()</h3>
<p><strong>用法</strong>：为每个分区内的行分配一个唯一的连续整数，从1开始，按照 ORDER BY 排序。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">ROW_NUMBER() OVER (PARTITION BY 分区列 ORDER BY 排序列)
</pre></div>
<p><strong>例子</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
Product,
Quantity,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY Quantity DESC) AS RowNum
FROM Sales;
</pre></div>
<p><strong>结果</strong>（假设数据）：</p>
<ul><li>A, 15, 1</li><li>A, 10, 2</li><li>B, 20, 1</li></ul>
<p><strong>使用场景</strong>：分页查询、生成唯一行号、删除重复记录（结合 CTE）。</p>
<h3>2. RANK()</h3>
<p><strong>用法</strong>：为行分配排名，如果值相同则排名相同，下一个排名会跳过（有间隙）。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">RANK() OVER (PARTITION BY 分区列 ORDER BY 排序列)
</pre></div>
<p><strong>例子</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
Product,
Quantity,
RANK() OVER (ORDER BY Quantity DESC) AS Rank
FROM Sales;
</pre></div>
<p><strong>结果</strong>：</p>
<ul><li>B, 20, 1</li><li>A, 15, 2</li><li>A, 10, 3 (没有跳跃，因为没有并列)</li></ul>
<p>如果有两个15，则：15排2，下一个跳到4。</p>
<p><strong>使用场景</strong>：排名竞赛、识别前N名，但允许并列。</p>
<h3>3. DENSE_RANK()</h3>
<p><strong>用法</strong>：类似于 RANK，但排名连续，没有间隙。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">DENSE_RANK() OVER (PARTITION BY 分区列 ORDER BY 排序列)
</pre></div>
<p><strong>例子</strong>：同上，如果有两个15，则：15排2，下一个排3。</p>
<p><strong>使用场景</strong>：需要连续排名的场景，如奖牌排名（金银铜连续）。</p>
<h3>4. NTILE(n)</h3>
<p><strong>用法</strong>：将分区内的行分成 n 个组，每组分配一个从1到n的数字。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">NTILE(组数) OVER (PARTITION BY 分区列 ORDER BY 排序列)
</pre></div>
<p><strong>例子</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
Product,
Quantity,
NTILE(2) OVER (ORDER BY Quantity DESC) AS Tile
FROM Sales;
</pre></div>
<p><strong>结果</strong>：分成两组，前半组1，后半组2。</p>
<p><strong>使用场景</strong>：分桶分析、将数据分成等份（如分位数）。</p>
<p class="maodian"><a name="_label4"></a></p><h2>聚合函数</h2>
<p>聚合函数如 SUM、AVG、MIN、MAX、COUNT 可以与 OVER 结合，在窗口内计算。</p>
<p class="maodian"><a name="_lab2_4_24"></a></p><h3>1. SUM()</h3>
<p><strong>用法</strong>：计算窗口内列的总和。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SUM(列) OVER (PARTITION BY 分区列 ORDER BY 排序列 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
</pre></div>
<p><strong>例子</strong>（累计销售）：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
OrderDate,
Price,
SUM(Price) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Sales;
</pre></div>
<p><strong>结果</strong>：每行显示到当前日期的累计总价。</p>
<p><strong>使用场景</strong>：运行总计、累计求和、财务报告。</p>
<p class="maodian"><a name="_lab2_4_25"></a></p><h3>2. AVG()</h3>
<p><strong>用法</strong>：计算平均值。</p>
<p><strong>语法</strong>：类似 SUM。</p>
<p><strong>例子</strong>：计算移动平均。</p>
<p><strong>使用场景</strong>：趋势分析、股票移动平均。</p>
<p class="maodian"><a name="_lab2_4_26"></a></p><h3>3. MIN() / MAX()</h3>
<p><strong>用法</strong>：窗口内最小/最大值。</p>
<p><strong>例子</strong>：查找每个产品的历史最低价。</p>
<p><strong>使用场景</strong>：价格监控、极值分析。</p>
<p class="maodian"><a name="_lab2_4_27"></a></p><h3>4. COUNT()</h3>
<p><strong>用法</strong>：计数。</p>
<p><strong>例子</strong>：计算每个分区内的行数。</p>
<p><strong>使用场景</strong>：分组计数而不使用 GROUP BY。</p>
<p class="maodian"><a name="_label5"></a></p><h2>分析函数</h2>
<p>分析函数用于访问窗口中其他行的数据。</p>
<p class="maodian"><a name="_lab2_5_28"></a></p><h3>1. LEAD(列, n, 默认值)</h3>
<p><strong>用法</strong>：返回当前行后 n 行的列值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">LEAD(列, n, 默认值) OVER (PARTITION BY 分区列 ORDER BY 排序列)
</pre></div>
<p><strong>例子</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">SELECT
OrderDate,
Price,
LEAD(Price, 1, 0) OVER (ORDER BY OrderDate) AS NextPrice
FROM Sales;
</pre></div>
<p><strong>结果</strong>：显示下一订单的价格。</p>
<p><strong>使用场景</strong>：比较前后行、计算增长率、时间序列分析。</p>
<p class="maodian"><a name="_lab2_5_29"></a></p><h3>2. LAG(列, n, 默认值)</h3>
<p><strong>用法</strong>：返回当前行前 n 行的列值。</p>
<p><strong>语法</strong>：类似 LEAD。</p>
<p><strong>例子</strong>：计算价格变化。</p>
<p><strong>使用场景</strong>：与 LEAD 类似，用于历史比较。</p>
<p class="maodian"><a name="_lab2_5_30"></a></p><h3>3. FIRST_VALUE(列)</h3>
<p><strong>用法</strong>：返回窗口帧中第一个值。</p>
<p><strong>语法</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">FIRST_VALUE(列) OVER (PARTITION BY 分区列 ORDER BY 排序列 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
</pre></div>
<p><strong>例子</strong>：每个产品的首次销售价格。</p>
<p><strong>使用场景</strong>：基准值比较。</p>
<p class="maodian"><a name="_lab2_5_31"></a></p><h3>4. LAST_VALUE(列)</h3>
<p><strong>用法</strong>：返回窗口帧中最后一个值。注意：默认帧到当前行，需要调整。</p>
<p><strong>例子</strong>：每个产品的最新价格。</p>
<p><strong>使用场景</strong>：最新状态获取。</p>
<p class="maodian"><a name="_label6"></a></p><h2>其他函数</h2>
<p class="maodian"><a name="_lab2_6_32"></a></p><h3>1. PERCENT_RANK()</h3>
<p><strong>用法</strong>：计算相对排名（0到1）。</p>
<p><strong>语法</strong>：OVER (ORDER BY 排序列)</p>
<p><strong>例子</strong>：百分位排名。</p>
<p><strong>使用场景</strong>：统计分布。</p>
<p class="maodian"><a name="_lab2_6_33"></a></p><h3>2. CUME_DIST()</h3>
<p><strong>用法</strong>：累计分布（0到1）。</p>
<p><strong>使用场景</strong>：分布分析。</p>
<p class="maodian"><a name="_label7"></a></p><h2>结论</h2>
<p>SQL Server 窗口函数极大地简化了复杂查询，提高了效率。通过掌握这些函数，你可以处理各种数据分析任务。建议在实际项目中练习，以加深理解。注意：窗口函数不支持在 WHERE 或 GROUP BY 中使用，通常在 SELECT 或 ORDER BY 中。</p>
頁:
[1]