hive的分析函数

hive的分析函数

1、rollup：group by 的增强函数

实现根据部门号、工作职位求工资总和，根据部门求工资总和，查所有人的工资总和

            通过group by 语句的增加来实现：
            select deptno,job,sum(sal)
            from emp 
            group by rollup(deptno,job);
            
            语法：
            group by rollup(a,b) = group by a,b
                                               +
                                               group by a
                                               +
                                              不分组

测试oracle

测试hive

2、over（开窗参数）开窗函数

本质：就是一个“分组函数”，可以返回多个值

        参数：
        unbounded：指定哪些数据进入窗口
        preceding：在...之前，用法：unbounded preceding：表示：分组后的第一行
        following：在...之后，用法：unbounded following：表示：分组后的最后一行
        current row：分组后的当前行

举例：求部门进行分组，求：员工号、姓名、部门号，薪水，每个部门的第一行到当前行的工资总和

                select empno,ename,deptno,sal,
                       sum(sal) 
                       over(partition by deptno order by sal
                            rows between unbounded preceding and current row )
                from emp;

我认为该语句其实是这样

select empno,ename,deptno,sal from emp;这句sql语句可以查出所有的行，但是我还想使用开窗函数求出一个分组，

分组后的sum(sal)是分组后的第一行到当前行的合计sal之

所以使用到了over，参数中partition  by设置了开窗条件（其实就是分组），order by 在窗口中排序，每行的计算格式

当然我们也可以把看到的窗口打乱使用窗外排序，但它计算的时候还是使用窗口来计算的

行号: Oracle中有一个伪列：rownum（一般不建议使用）
       row_number()：对部门进行分组，按照薪水排序，并显示该员工在部门中的序号
                select empno,ename,deptno,sal,
                       row_number() over(partition by deptno order by sal) 序号
                from emp;

       rank(): 考虑了重复的记录，序号可能不连续
                select empno,ename,deptno,sal,
                       rank() over(partition by deptno order by sal) 序号
                from emp;       
       
       dense_rank()：考虑了重复的记录，序号连续
                select empno,ename,deptno,sal,
                       dense_rank() over(partition by deptno order by sal) 序号
                from emp;

lag：滞后，把某个列往后错行
       lead，提前，把某个列往前提行
                按照薪水：
                   select empno,ename,deptno,sal,
                       lag(sal,2) over(partition by deptno order by sal) 滞后,
                       lead(sal,2) over(partition by deptno order by sal) 提前
                from emp;
                

               
       first_value:取分组后的第一行
       last_value：取分组后的最后一行
       
        select empno,ename,deptno,sal,
               first_value(sal) over(partition by deptno order by sal 
                                  rows between unbounded preceding and unbounded following) 第一行,
               last_value(sal) over(partition by deptno order by sal 
                                  rows between unbounded preceding and unbounded following) 最后一行                          
        from emp;