源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 3

这一章的SQL语句比较长，第一次接触，感觉用不习惯，可能需要在用到的时候查资料了。不管怎样，先把源码备在这里。

--排序子句(Order by)
--未指定排序时，AVG值相同
SELECT ename, sal, AVG(sal) OVER()
FROM   emp
WHERE  ROWNUM <= 3;

--指定排序后，计算累计平均值
SELECT ename, sal, AVG(sal) OVER(ORDER BY ename)
FROM   emp
WHERE  ROWNUM <= 3;

select ((select sal from emp where ename='JONES')+(select sal from emp where ename='PANDENG'))/2.0 from dual;
select sum(sal) from emp;

--开窗子句（windowing）
SELECT deptno,
       SUM(sal) 部门薪资小计,
       SUM(SUM(sal)) OVER(ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 部门总计
FROM   emp
GROUP  BY deptno;

--9.2.4分析函数列表
--COUNT函数
SELECT empno, ename, COUNT(*) OVER(PARTITION BY deptno  ORDER BY empno) 条数小计
FROM   emp;

--Range between
SELECT empno,
       ename,
       sal,
       COUNT(*) OVER(ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) 薪水差异个数
FROM   emp

--AVG函数
         SELECT deptno,
                empno,
                ename,
                sal,
                AVG(sal) OVER(PARTITION BY deptno ORDER BY deptno) avg_sal
         FROM   emp;

--MIN和MAX函数
SELECT deptno,
       empno,
       ename,
       hiredate,
       sal,
       MIN(sal) OVER(PARTITION BY deptno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最低薪水,
       MAX(sal) OVER(PARTITION BY deptno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最高薪水
FROM   emp;

--RANK,DENSE_RANK和ROW_NUMBER函数
SELECT deptno,
       ename,
       sal,
       mgr,
       RANK() OVER(ORDER BY deptno) RANK结果,
       DENSE_RANK() OVER(ORDER BY deptno) DENSE_RANK结果,
       ROW_NUMBER() OVER(ORDER BY deptno) ROW_NUMBER结果
FROM   emp
WHERE  deptno IN (10, 20, 30) AND mgr IS NOT NULL
ORDER  BY DEPTNO;

--Min, Max函数
SELECT deptno,
       MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY comm) 最低提成薪水,
       MAX(sal) KEEP(DENSE_RANK LAST ORDER BY comm) 最高提成薪水
FROM   emp
WHERE  deptno IN (10, 20, 30)
GROUP  BY deptno;

select min(sal),max(sal) from emp WHERE  deptno IN (10, 20, 30)

--FIRST和LAST函数(不太明白)
SELECT deptno,
       empno,
       sal,
       FIRST_VALUE(sal) OVER(PARTITION BY deptno  ORDER BY empno) "第一个值",
       LAST_VALUE(sal) OVER(PARTITION BY deptno  ORDER BY empno) "最后一个值"
FROM   emp
WHERE  deptno IN (10, 20);

--LAG和LEAD函数（暂且起名错位函数）
SELECT ename,
       hiredate,
       sal,
       deptno,
       LAG(sal, 1, 0) OVER(ORDER BY hiredate)  AS 前一个雇员薪水,
       LEAD(sal, 1, 0) OVER(ORDER BY hiredate) AS 后一个雇员薪水
FROM   emp
WHERE  deptno = 20;


--9.3分析函数使用示例
--记录排名
SELECT deptno,
       empno,
       ename,
       SUM(sal) dept_sales,
       RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_跳号,
       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_同级同号,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_不跳号
FROM   emp
GROUP  BY deptno, empno, ename


DELETE
FROM   EMP
WHERE  deptno IS NULL;
SELECT *
FROM   EMP
ORDER  BY SAL
           SELECT deptno, MIN(SAL), MAX(SAL)
           FROM   EMP
           GROUP  BY deptno;

--首尾记录查询（First, last）
SELECT MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名首位,
       MIN(empno) KEEP(DENSE_RANK LAST ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名尾位
FROM   emp
WHERE  sal IS NOT NULL AND deptno IS NOT NULL
GROUP  BY empno;

--前后排名查询
SELECT deptno, empno, ename, dept_sales, 薪资排名
FROM   (SELECT deptno,
               empno,
               ename,
               SUM(sal) dept_sales,
               RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名
        FROM   emp
        WHERE  deptno IS NOT NULL
        GROUP  BY deptno, empno, ename)
WHERE  薪资排名 <= 2;


SELECT deptno, empno, ename, dept_sales, 薪资排名
FROM   (SELECT deptno,
               empno,
               ename,
               SUM(sal) dept_sales,
               RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) NULLS LAST) 薪资排名
        FROM   emp
        WHERE  deptno IS NOT NULL
        GROUP  BY deptno, empno, ename)
WHERE  薪资排名 <= 2;


--层次查询(ntile)
SELECT *
FROM   (SELECT deptno,
               empno,
               ename,
               SUM(sal) dept_sales,
               NTILE(3) OVER(PARTITION BY deptno ORDER BY SUM(sal) NULLS LAST) RANK_RATIO
        FROM   emp
        WHERE  deptno IS NOT NULL
        GROUP  BY deptno, empno, ename)
WHERE  RANK_RATIO = 1 

--范围统计查询（看不大懂）
  SELECT empno,
         ename,
         hiredate,
         sal,
         MAX(sal) OVER(ORDER BY hiredate ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) "前10天入职最高薪资",
         MAX(sal) OVER(ORDER BY hiredate ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) "后10天入职最高薪资"
        FROM   emp
        WHERE  deptno IN (10, 20, 30) AND sal IS NOT NULL;

--相邻记录比较
SELECT ename,
       hiredate,
       deptno,
       sal,
       sal - prev_sal "与前面的差异",
       sal - next_sal "与后面的差异"
FROM   (SELECT ename,
               hiredate,
               sal,
               deptno,
               LAG(sal, 1, 0) OVER(ORDER BY hiredate) AS prev_sal,
               LEAD(sal, 1, 0) OVER(ORDER BY hiredate) AS next_sal
        FROM   emp
        WHERE  deptno IS NOT NULL AND SAL IS NOT NULL);

--抑制重复
SELECT *
FROM   (SELECT empno,
               ename,
               sal,
               hiredate,
               ROW_NUMBER() OVER(PARTITION BY EXTRACT(YEAR FROM hiredate) ORDER BY empno) rn
        FROM   emp
        WHERE  hiredate IS NOT NULL AND
               EXTRACT(YEAR FROM hiredate) between 1980 and 2016)
WHERE  rn = 1;

--行列转换（不太明白）
SELECT deptno,
       ename,
       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC NULLS LAST) seq
FROM   emp


         SELECT MAX(DECODE(job, '职员', ename, NULL)) "职员名称",
                MAX(DECODE(job, '职员', empno, NULL)) "职员编号",
                MAX(DECODE(job, '分析人员', ename, NULL)) "分析人员名称",
                MAX(DECODE(job, '分析人员', empno, NULL)) "分析人员编号",
                MAX(DECODE(job, '经理', ename, NULL)) "经理名称",
                MAX(DECODE(job, '经理', empno, NULL)) "经理编号",
                MAX(DECODE(job, '老板', ename, NULL)) "老板名称",
                MAX(DECODE(job, '老板', empno, NULL)) "老板编号",
                MAX(DECODE(job, '销售人员', ename, NULL)) "销售人员名称",
                MAX(DECODE(job, '销售人员', empno, NULL)) "销售人员编号"
         FROM   (SELECT job,
                        empno,
                        ename,
                        ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn
                 FROM   emp
                 WHERE  job IS NOT NULL) x
         GROUP  BY rn;

SELECT MAX(DECODE(job, '职员', ename, NULL)) "职员名称",
       MAX(DECODE(job, '分析人员', ename, NULL)) "分析人员名称",
       MAX(DECODE(job, '经理', ename, NULL)) "经理名称",
       MAX(DECODE(job, '老板', ename, NULL)) "老板名称",
       MAX(DECODE(job, '销售人员', ename, NULL)) "销售人员名称"
FROM   (SELECT job,
               empno,
               ename,
               ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn
        FROM   emp
        WHERE  job IS NOT NULL) x
GROUP  BY rn;

SELECT job, empno, ename, ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn
FROM   emp
WHERE  job IS NOT NULL 

--在PL/SQL中使用分析函数
DECLARE TYPE refempcur IS REF CURSOR;
empcur refempcur;
jobname VARCHAR(20); --职位名
ename VARCHAR2(20); --员工名
empno NUMBER; --员工编号
rn INT; --排名
BEGIN
  --打开游标
  OPEN empcur FOR
    SELECT job,
           empno,
           ename,
           ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn
    FROM   emp
    WHERE  job IS NOT NULL;
  --循环提取游标内容
  LOOP
    EXIT WHEN empcur%NOTFOUND;
    FETCH empcur
      INTO jobname, empno, ename, rn;
    --输出游标内容
    DBMS_OUTPUT.put_line(jobname || '   ' || empno || '   ' || ename || '   ' || rn);
  END LOOP;
END;