Oracle高级函数使用的浅显介绍

-- 分组统计时不受限制的统计所有记录
SELECT   deptno, COUNT (1),
         SUM (COUNT (1)) OVER(ORDER BY deptno DESC ROWS
 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS aaa,
 SUM(COUNT(1)) OVER() AS aaaa                                                                     
    FROM emp GROUP BY deptno;

-- 三个排序方式不同的排序函数
  SELECT empno,sal,RANK() OVER(ORDER BY sal DESC) AS aa,
                   DENSE_RANK() OVER(ORDER BY sal DESC) AS aaa,
                   ROW_NUMBER() OVER(ORDER BY sal DESC) AS aaaa
                   FROM EMP;

-- 排序，分别获取第一个和最后一个 
   SELECT MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY sal DESC) AS aa,
          MIN(empno) KEEP(DENSE_RANK LAST ORDER BY sal DESC) AS aaa
    FROM emp;

-- 百分比
SELECT   months,
                 ROUND (RATIO_TO_REPORT (SUM (tot_sales)) OVER (), 2) AS aaa
            FROM orders
        GROUP BY months

-- 平均分成五个级别
 SELECT empno,NTILE(5) OVER(ORDER BY sal DESC) AS aa FROM emp;

-- 取当前行的后指定行的汇总数据
SELECT months,SUM(tot_sales) monthly_sales,
        LAG(SUM(tot_sales),2) OVER(ORDER BY months) prev_month_sales
    FROM orders
    GROUP BY months;