group by 无法实现“分组但保留明细”，因其本质是聚合降维，会丢失原始行；需用窗口函数 over (partition by ...) 广播聚合结果而不减少行数。
为什么不能直接用 GROUP BY 实现“分组但保留明细”
因为 GROUP BY 的本质是聚合降维：它把多行压缩成一行，原始明细必然丢失。想按某列分组统计（比如算每个部门的平均薪资），同时又让每条员工记录都还在结果里显示这个平均值——GROUP BY 做不到，必须换思路。
典型错误是写成这样：
SELECT dept, name, salary, AVG(salary) FROM emp GROUP BY dept;
这会报错：column "emp.name" must appear in the GROUP BY clause or be used in an aggregate function。PostgreSQL 和大多数严格 SQL 标准引擎都会拦住你。
OVER 子句怎么写出“分组不丢行”的效果
OVER 是窗口函数的核心语法，它不改变行数，只在逻辑分组内计算并广播结果。关键不是“替代 GROUP BY”，而是用 PARTITION BY 模拟分组边界，再套一个聚合函数（如 AVG()、COUNT()）作为窗口函数。
-
PARTITION BY dept相当于按部门切出一个个独立计算区，但每行仍独立存在 - 聚合函数加了
OVER就变成窗口函数，不再触发分组折叠 - 所有窗口函数必须显式写
OVER，漏掉括号或PARTITION BY会变全表计算（比如AVG(salary) OVER()算的是全公司平均）
正确写法示例：
SELECT dept, name, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg_salary
FROM emp;
常见误用：ORDER BY 在 OVER 里加不加？
加 ORDER BY 进 OVER 子句会改变窗口函数的行为，尤其对 ROW_NUMBER()、RANK()、累计类函数（如 SUM() OVER (ORDER BY ...)）是必需的；但对 AVG()、COUNT() 这类聚合窗口函数，加了反而可能引入非预期的“帧定义”（frame），导致结果和直觉不符。
- 纯分组统计（如部门平均值）：只用
PARTITION BY dept，不要加ORDER BY - 需要排序后累计（如部门内薪资累计）：必须写
ORDER BY salary，否则默认帧是UNBOUNDED PRECEDING TO CURRENT ROW，但语义易混淆 - PostgreSQL 中
AVG() OVER (PARTITION BY dept ORDER BY hire_date)会按入职时间动态算“到当前行为止的部门平均”，不是静态部门均值
性能和兼容性要注意什么
窗口函数普遍比普通聚合慢，因为需维护内部状态和排序逻辑。但现代数据库（PostgreSQL 11+、SQL Server 2012+、MySQL 8.0+）优化得不错；老版本 MySQL（5.7 及以前）根本不支持 OVER，强行写会语法报错：ERROR 1064 (42000): You have an error in your SQL syntax。
- 大数据量时，
PARTITION BY字段最好有索引，尤其是还带ORDER BY的场景 - 避免在
WHERE之前用窗口函数过滤（如WHERE dept_avg_salary > 10000），必须用子查询或 CTE 包一层，否则报错：column "dept_avg_salary" does not exist - 不同数据库对空值处理略有差异：PostgreSQL 中
PARTITION BY col会把所有NULL归为同一组；Oracle 默认忽略NULL分区键，需显式写DECODE(col, NULL, 'NULL_GROUP', col)来统一
真正容易被忽略的是：窗口函数的结果不可用于同一层的 WHERE 或 HAVING，这是执行顺序决定的——先算 SELECT 列，再过滤。想筛部门平均工资超 1 万的员工？得套一层：
SELECT * FROM (
SELECT dept, name, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM emp
) t WHERE dept_avg > 10000;
