SQL Server中如何使用OVER子句代替传统的GROUP BY？

OVER不能替代GROUP BY，它在保留原始行基础上叠加聚合结果；GROUP BY压缩行数做归并，二者逻辑不同、执行时机不同、字段约束不同。

OVER 不能真的“代替”GROUP BY，但能解决它做不到的事

GROUP BY 的作用是压缩行数、做聚合归并；OVER 的作用是在保留原始每一行的前提下，叠加计算结果。如果你要从 1000 行员工数据里统计出 20 个部门的平均薪资——必须用 GROUP BY；但如果你要显示每位员工的姓名、部门、薪资，同时在旁边加一列“本部门平均薪资”，那就得用 AVG(salary) OVER (PARTITION BY dept)。

常见错误是把 SELECT name, dept, AVG(salary) OVER (PARTITION BY dept) 当成 GROUP BY dept 的简写，结果发现返回行数没变、name 也没报错——这恰恰说明它不是替代，而是另起一套逻辑。

• GROUP BY 后所有非聚合字段必须出现在 GROUP BY 列表中，否则 SQL Server 直接报错
• OVER 允许你混用明细字段（如 name、email）和窗口聚合（如 COUNT(*) OVER (PARTITION BY dept)），不破坏原始粒度
• 执行顺序上，GROUP BY 发生在 SELECT 之前，而 OVER 是在 SELECT 阶段计算，能看到 WHERE 过滤后的行，但看不到 GROUP BY 后的聚合结果（除非你把窗口函数写在聚合之后的子查询里）

怎么写一个安全可用的 PARTITION BY 分组统计

核心就三件事：明确分区字段、避免 NULL 干扰、别乱加 ORDER BY。

PARTITION BY dept 会把所有 dept = NULL 的行归为一组，如果业务上 NULL 表示“未分配部门”，那这部分人会被算进一个隐式分组，容易漏掉或误统计。真要排除，得提前在 WHERE 里过滤，或者用 PARTITION BY ISNULL(dept, 'unknown') 显式处理。

• 分区字段只能是原始列名或确定性表达式（比如 PARTITION BY YEAR(order_date) 可以，PARTITION BY UPPER(name) 在某些版本可能不支持）
• 对 AVG()、SUM()、COUNT() 这类静态聚合，不要加 ORDER BY——加了就变成滚动计算，比如 SUM(amount) OVER (PARTITION BY category ORDER BY create_time) 返回的是“到当前行为止的累计和”，不是该分类总和
• 多个窗口函数共存时（如同时要部门均值、部门最大值、部门人数），SQL Server 通常会复用一次分组扫描，但若 PARTITION BY 不同（比如一个按 dept，一个按 region），就可能触发多次扫描，大数据量下需留意性能

百分比计算必须防整除和除零

直接写 COUNT(*) * 100 / COUNT(*) OVER() 得到的全是 0，因为 SQL Server 默认整数除法截断小数。更糟的是，如果 COUNT(*) OVER() 结果为 0（比如全表被 WHERE 过滤光了），就会触发除零错误。

正确写法必须组合三要素：CONVERT 或 CAST 强转小数类型、乘 100.0（带小数点的字面量）、用 NULLIF 包裹分母。

• CONVERT(DECIMAL(5,2), COUNT(*) * 100.0 / NULLIF(COUNT(*) OVER(), 0))
• 别用 ROUND(..., 2) 替代 CONVERT，因为 ROUND 不改变数据类型，后续参与运算仍可能回退成整除
• NULLIF(x, 0) 在 x 为 0 时返回 NULL，除法得 NULL 而非报错，符合多数报表场景预期

旧版本兼容和子查询陷阱

SQL Server 2005 就支持 OVER，但低版本（如 2000）完全不识别语法，会报错 Incorrect syntax near 'OVER'。确认版本用 SELECT @@VERSION。

有人试图用相关子查询模拟窗口效果：(SELECT AVG(salary) FROM emp e2 WHERE e2.dept = e1.dept)，这看似可行，但隐患明显：

• 每次外层扫描一行，内层都全表扫描一次，O(n²) 复杂度，万级数据就明显卡顿
• 子查询无法利用 dept 上的索引加速（除非引擎足够智能，但不保证）
• WHERE 条件若只写在外层，子查询会统计全表数据，导致分母/分子口径不一致（比如外层加了 WHERE status = 'active'，子查询没加，算出来就是错的占比）

真正难处理的不是语法，而是窗口定义范围和主查询过滤逻辑是否严格同步——这个细节一旦松动，结果偏差往往悄无声息。