--分析函数中使用常规聚合函数
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() , 密度排名，无缝。
SELECt name, dept_num, salary , DENSE_RANK() OVER (PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--percent_rank() , 比例排名，衡量排名在整体排名中的分布情况。
SELECt name, dept_num, salary , percent_rank() OVER (PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--分桶统计,将分区内容数据均匀分配到若干个桶中。
SELECt name, dept_num, salary , NTILE(4) OVER(PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--lead,从当前行计数，访问下两行的salary,如果超过窗口范围返回null。
SELECt name, dept_num, salary, LEAD(salary, 2) OVER(PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--lag,从当前行计数，访问之前的行salary,如果超过窗口范围返回null。
SELECt name, dept_num, salary, lag(salary, 2) OVER(PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--first_value,访问分区内的第一行值
SELECt name, dept_num, salary, FIRST_VALUE(salary) OVER (PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--laster_value,
SELECt name, dept_num, salary, last_VALUE(salary) OVER (PARTITION BY dept_num ORDER BY salary) AS t1 from emp_ct ;
--使用range开窗函数 RANGE BETWEEN ..、AND ...
SELECt name, dept_num, salary, LAST_VALUE(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS t1 from emp_ct ;
--RANGE : 对值的+/-.
SELECt name, dept_num, salary, LAST_VALUE(salary) OVER (PARTITION BY dept_num ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND current row) AS t1 from emp_ct ;
--ROWS : 查看的行
SELECt name, dept_num, salary, LAST_VALUE(salary) OVER (PARTITION BY dept_num ORDER BY salary ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS t1 from emp_ct ;
SELECt name, dept_num, salary, MAX(salary) OVER (PARTITION BY dept_num ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) t1 from emp_ct ;
SELECt name, dept_num, salary, MAX(salary) OVER (PARTITION BY dept_num ORDER BY salary ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) t1 from emp_ct ;
SELECt name, dept_num, salary, MAX(salary) OVER (PARTITION BY dept_num ORDER BY salary ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) t1 from emp_ct ;
--窗口重用
SELECt name, dept_num, salary , MAX(salary) OVER w1 AS mx,MIN(salary) OVER w1 AS mn,AVG(salary) OVER w1 AS ag from emp_ct WINDOW w1 AS (PARTITION BY dept_num ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ;
[开窗函数]
range|rows between ..、and ;
range是值偏移，rows是行偏移。