--分析函数中使用常规聚合函数
SELECT name, dept_num, salary ,COUNT(*) OVER (PARTITION BY dept_num) AS row_cnt from emp_ct ;
SELECT name, dept_num, salary ,sum(salary) OVER (PARTITION BY dept_num) AS row_cnt from emp_ct ;
--对部分分区并排序,只对分区内的数据进行累加求和。
SELECT name, dept_num, salary ,SUM(salary) OVER(PARTITION BY dept_num ORDER BY dept_num) AS t1 from emp_ct ;
--对部门排序,累加每个部分的工资总和,后续的统计会包含之前的计算总额。
SELECT name, dept_num, salary , SUM(salary) OVER(ORDER BY dept_num) as t2 from emp_ct ;
--不进行分区,对所有数据进行排序处理。
SELECT name, dept_num, salary , SUM(salary) OVER(ORDER BY dept_num, name rows unbounded preceding) AS t3 from emp_ct ;
--rank,常规排名,对部门分区,在分区内对salary排序,计算统计排名
SELECT name, dept_num, salary , RANK() OVER (PARTITION BY dept_num ORDER BY salary) from emp_ct ;
--dense_rank() , 密