oracle常用分组分析函数

1.rollup() ,cube(),grouping sets()

GROUP BY ROLLUP(A,B,C)：首先对(A,B,C)进行GROUP BY，然后对(A,B)进行GROUP BY，然后是(A)进行GROUP BY， 最后对全表进行GROUP BY操作。

GROUP BY CUBE(A,B,C)： 首先对(A,B,C)进行GROUP BY，然后依次对(A,B)、(A,C)、(A)、(B,C)、(B)、(C)进行GROUP BY，最后对全表进行GROUP BY操作。

GROUP BY GROUPING SETS(A,B,C)：依次对(C)、(B)、(A)进行GROUP BY。

---统计各部门各岗位工资总额
select deptno, job, sum(sal) from scott.emp group by rollup(deptno, job);  

---统计各部门内各个岗位工资总额，之后统计各岗位工资总额，最后统计工资总额

select deptno,job,sum(sal) from scott.emp
group by cube(deptno,job);

---统计部门工资总额和各岗位工资总额
select grouping(deptno),grouping(job),grouping_id(job),deptno,job,sum(sal)
from scott.emp
group by  grouping sets (deptno,job);

2.开窗函数rank()/dense_rank() over(partition by ...order by . range between n preceding and m following..)

select e.ename, e.job, e.sal, e.deptno
  from (select e.ename,
               e.job,
               e.sal,
               e.deptno,
               rank() over(partition by e.deptno order by e.sal desc) rank
          from scott.emp e) e
 where e.rank = 1;

select e.ename, e.job, e.sal, e.deptno
  from (select e.ename,
               e.job,
               e.sal,
               e.deptno,
               dense_rank() over(partition by e.deptno order by e.sal desc) rank
          from scott.emp e) e
 where e.rank = 1;

3.lead()/lag() over(partition by ... order by ...)函数

--<span style="color: rgb(51, 51, 51); font-family: Arial; font-size: 14px; line-height: 26px;">lead(列名,n,m):  </span><span style="color: rgb(51, 51, 51); font-family: Arial; font-size: 14px; line-height: 26px;">当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。</span><br style="color: rgb(51, 51, 51); font-family: Arial; font-size: 14px; line-height: 26px;" /><span style="color: rgb(51, 51, 51); font-family: Arial; font-size: 14px; line-height: 26px;">----lag(列名,n,m):  </span><span style="color: rgb(51, 51, 51); font-family: Arial; font-size: 14px; line-height: 26px;">当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没----有则默认值为null。</span>

select e.ename,
       e.job,
       e.sal,
       e.deptno,
       lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal,
       lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal,
       nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal,
           0) diff_lead_sal,
       nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal
  from scott.emp e; 

</pre><pre name="code" class="sql">  select e.ename,
         e.job,
         e.sal,
         e.deptno,
         first_value(e.sal) over(partition by e.deptno) first_sal,
         last_value(e.sal) over(partition by e.deptno) last_sal,
         sum(e.sal) over(partition by e.deptno) sum_sal,
         avg(e.sal) over(partition by e.deptno) avg_sal,

<pre name="code" class="sql" style="color: rgb(51, 51, 51); line-height: 26px;">              max(e.sal) over(partition by e.deptno) max_sal,
         min(e.sal) over(partition by e.deptno) min_sal,

         count(e.sal) over(partition by e.deptno) count_num,
         row_number() over(partition by e.deptno order by e.sal) row_num
    from scott.emp e;