常见聚合窗口函数`sum(column)`求和、`count(column)`计数、`avg(column)`求平均值、`max(column)`最大值、`min(column)`最小值。 ```sql select ename, deptno, sal, count(*) over(partition by deptno) as row_cnt, -- count(distinct *) over(partition by deptno) as row_cnt_dis, sum(sal) over(partition by deptno order by deptno) as deptTotal, sum(sal) over(partition by deptno) as runingTotal1, sum(sal) over(order by deptno, ename rows unbounded preceding) as runingTotal2, avg(sal) over(partition by deptno) as avgDept, min(sal) over(partition by deptno) as minDept, max(sal) over(partition by deptno) as maxDept from emp order by deptno, ename; +---------+---------+---------+----------+------------+---------------+---------------+---------------------+----------+----------+--+ | ename | deptno | sal | row_cnt | depttotal | runingtotal1 | runingtotal2 | avgdept | mindept | maxdept | +---------+---------+---------+----------+------------+---------------+---------------+---------------------+----------+----------+--+ | CLARK | 10 | 2450.0 | 3 | 8750.0 | 8750.0 | 2450.0 | 2916.6666666666665 | 1300.0 | 5000.0 | | KING | 10 | 5000.0 | 3 | 8750.0 | 8750.0 | 7450.0 | 2916.6666666666665 | 1300.0 | 5000.0 | | MILLER | 10 | 1300.0 | 3 | 8750.0 | 8750.0 | 8750.0 | 2916.6666666666665 | 1300.0 | 5000.0 | | ADAMS | 20 | 1100.0 | 5 | 10875.0 | 10875.0 | 9850.0 | 2175.0 | 800.0 | 3000.0 | | FORD | 20 | 3000.0 | 5 | 10875.0 | 10875.0 | 12850.0 | 2175.0 | 800.0 | 3000.0 | | JONES | 20 | 2975.0 | 5 | 10875.0 | 10875.0 | 15825.0 | 2175.0 | 800.0 | 3000.0 | | SCOTT | 20 | 3000.0 | 5 | 10875.0 | 10875.0 | 18825.0 | 2175.0 | 800.0 | 3000.0 | | SMITH | 20 | 800.0 | 5 | 10875.0 | 10875.0 | 19625.0 | 2175.0 | 800.0 | 3000.0 | | ALLEN | 30 | 1600.0 | 6 | 9400.0 | 9400.0 | 21225.0 | 1566.6666666666667 | 950.0 | 2850.0 | | BLAKE | 30 | 2850.0 | 6 | 9400.0 | 9400.0 | 24075.0 | 1566.6666666666667 | 950.0 | 2850.0 | | JAMES | 30 | 950.0 | 6 | 9400.0 | 9400.0 | 25025.0 | 1566.6666666666667 | 950.0 | 2850.0 | | MARTIN | 30 | 1250.0 | 6 | 9400.0 | 9400.0 | 26275.0 | 1566.6666666666667 | 950.0 | 2850.0 | | TURNER | 30 | 1500.0 | 6 | 9400.0 | 9400.0 | 27775.0 | 1566.6666666666667 | 950.0 | 2850.0 | | WARD | 30 | 1250.0 | 6 | 9400.0 | 9400.0 | 29025.0 | 1566.6666666666667 | 950.0 | 2850.0 | +---------+---------+---------+----------+------------+---------------+---------------+---------------------+----------+----------+--+ ``` 从Hive 2.1.0开始在`over()`子句中支持聚合函数。 ```sql select rank() over (order by sum(b)) from T group by a; ```