常見聚合窗口函數`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; ```