在SQL Server 2005中，数据统计是数据库管理员和开发人员经常面临的任务，它涉及到对大量数据进行分析和汇总。本篇文章将探讨几个利用`OVER`子句提高统计效率的技巧。`OVER`子句是SQL Server提供的一种强大功能，它允许我们在一个查询中对分组数据进行计算，而无需使用子查询或者多次扫描表，从而提高了查询性能。
让我们看看提供的示例表格，该表格包含两列：`name`和`val`。`name`列代表不同的类别，而`val`列则存储每个类别的数值。我们的目标是根据`name`对数据进行分组，并对每个组内的`val`进行各种统计计算，如排名、占比、与最大值的差距、与最小值的差距以及与平均值的差距。
以下是如何使用`OVER`子句实现这些统计的示例代码：
```sql
USE tempdb;
GO
IF (OBJECT_ID('tb') IS NOT NULL) DROP TABLE tb;
GO
CREATE TABLE tb (name VARCHAR(10), val INT);
GO
INSERT INTO tb SELECT 'aa', 10 UNION ALL SELECT 'aa', 20 UNION ALL
SELECT 'aa', 20 UNION ALL SELECT 'aa', 30 UNION ALL SELECT 'bb', 55 UNION ALL
SELECT 'bb', 45 UNION ALL SELECT 'bb', 0;
-- 使用OVER子句进行统计
SELECT
name,
val,
排名 = RANK() OVER (PARTITION BY name ORDER BY val),
占比 = CAST(val * 1.0 / SUM(val) OVER (PARTITION BY name) AS DECIMAL(2, 2)),
距最大 = val - MAX(val) OVER (PARTITION BY name),
距最小 = val - MIN(val) OVER (PARTITION BY name),
距平均 = val - AVG(val) OVER (PARTITION BY name)
FROM tb;
```
1. **排名（RANK）**：`RANK()`函数用于对每个组内的`val`进行排序，返回一个唯一的排名值。`OVER (PARTITION BY name ORDER BY val)`语句将`name`相同的行分组并按照`val`进行升序排序。
2. **占比**：通过`SUM(val) OVER (PARTITION BY name)`，我们可以在每个组内计算`val`的总和，然后使用`CAST`和`/`操作计算当前`val`占组内总和的比例，转换为两位小数的百分比。
3. **距最大值**：`MAX(val) OVER (PARTITION BY name)`获取每个组内的最大`val`，然后用当前`val`减去这个最大值，得到当前`val`与组内最大值的差距。
4. **距最小值**：类似地，`MIN(val) OVER (PARTITION BY name)`获取每个组内的最小`val`，然后用当前`val`减去最小值，得到当前`val`与组内最小值的差距。
5. **距平均值**：`AVG(val) OVER (PARTITION BY name)`计算每个组内的平均`val`，然后用当前`val`减去平均值，得到当前`val`与组内平均值的差距。
`OVER`子句结合聚合函数（如`SUM`, `MAX`, `MIN`, `AVG`等）可以高效地完成复杂的数据统计，避免了嵌套子查询可能导致的性能下降。这种方法在处理大数据集时尤其有用，因为它只扫描表一次，减少了计算的复杂性和资源消耗。
在实际应用中，SQL Server 2005的`OVER`子句还能与其他窗口函数一起使用，例如`LAG`（获取前一行的值）和`LEAD`（获取后一行的值），以实现更高级的数据分析和报告。通过灵活运用这些统计技巧，你可以更有效地管理和理解存储在SQL Server中的大量数据。
