ORACLEC学习笔记（7）（排名函数、行列转换）

分析函数：排名函数
RANK(), DENSE_RANK()与 ROW_NUMBER()：

RANK，DENSE_RANK，ROW_NUMBER函数为每条记录产生一个从1开始至N的自然数，
N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①ROW_NUMBER：
ROW_NUMBER函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②DENSE_RANK：
DENSE_RANK函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
③RANK：
RANK函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，
同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

-----查询EMP表所有员工姓名,工资以部门分组降序排序

SELECT
      A1.ENAME
     ,A1.SAL 
    --- ,A1.DEPTNO
FROM (SELECT
           A.ENAME
          ,A.SAL
          ,A.DEPTNO
          ,ROW_NUMBER()OVER(PARTITION BY A.DEPTNO ORDER BY A.SAL  DESC) ROW_N
      FROM EMP A
      )A1
WHERE A1.ROW_N<15

-------------查询EMP表中工资排名在第5到第10名的员工姓名、工作岗

位、工资
SELECT
      A1.ENAME
     ,A1.JOB
     ,A1.SAL 
FROM (SELECT
             A.ENAME
            ,A.JOB 
            ,A.SAL
            ,ROW_NUMBER()OVER(ORDER BY A.SAL DESC) ROW_M
        FROM EMP A
        )A1
WHERE A1.ROW_M BETWEEN 5 AND 10;        

---

3. LAG()与 LEAD()：求之前或之后的第N行

LAG 和 LEAD 函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。
这种操作可以使用对相同表的表连接来实现，不过使用 LAG 和 LEAD 有更高的效率。

LAG(ARG1,ARG2,ARG3)
第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。----一般不用写

—比较EMP表中员工与上一个入职员工晚入职多久

SELECT
    /* A1.姓名
    ,A1.较晚入职的时间
    ,A1.较早入职的时间
    ,*/A1.较晚入职的时间- A1.较早入职的时间 相差入职的时间
FROM ( SELECT
           A.ENAME     姓名
          ,A.HIREDATE   较晚入职的时间
          ,LAG(A.HIREDATE,1)OVER(ORDER BY A.HIREDATE ) 较早入职的时间
      FROM EMP A
      )A1
WHERE A1.较早入职的时间 IS NOT  NULL;

---

SELECT
    /* A1.姓名
    ,A1.较晚入职的时间
    ,A1.较早入职的时间
    ,*/A1.较晚入职的时间- A1.较早入职的时间 相差入职的时间
FROM (
      SELECT
           A.ENAME     姓名
          ,A.HIREDATE   较早入职的时间
          ,LEAD(A.HIREDATE,1)OVER(ORDER BY A.HIREDATE )  较晚入职的时间
      FROM EMP A
      )A1
WHERE A1.较晚入职的时间 IS NOT  NULL;

---

------查询EMP表中每个部门的员工工资相差多少钱

SELECT 
      A.EMPNO,
     A.ENAME, 
    ABS(A.SAL-B.SAL)
FROM EMP A
INNER JOIN EMP B
        ON  A.DEPTNO=B.DEPTNO 
       AND A.EMPNO<B.EMPNO 

-------------查询EMP表中每个部门的员工工资从高到底依次相差多少钱

SELECT
      A1.DEPTNO
      ,A1.SAL-A1.LEAD_SAL
FROM(SELECT
           A.EMPNO,
           A.ENAME,
           A.JOB,
           A.MGR,
           A.HIREDATE,
           A.SAL,
           LEAD(A.SAL,1)OVER(PARTITION BY A.DEPTNO ORDER BY A.SAL DESC) LEAD_SAL,
           A.COMM,
           A.DEPTNO
      FROM EMP A 
      )A1
WHERE A1.LEAD_SAL IS NOT NULL;      
------------------------------------------------      

----4.3 行列转换 （LAG LEAD ）
在用户制作数据报表时，经常会使用到表数据的行列转换操作。

1.列转行：

【例】有一张表S，记录了某公司每个月的销售额，如下
Y Q AMT
2015 1 100
2015 2 110
2015 3 130
2015 4 100
2016 1 200
2016 2 150
2016 3 100
2016 4 300

Y Q1 Q2 Q3 Q4
2015 100 110 130 100
2016 200 150 100 300

CREATE TABLE S
              (
                Y  NUMBER    
               ,Q  NUMBER   
               ,AMT NUMBER
        );

INSERT INTO S VALUES (2015,1,100);
INSERT INTO S VALUES (2015,2,110);
INSERT INTO S VALUES (2015,3,130);
INSERT INTO S VALUES (2015,4,100);
INSERT INTO S VALUES (2016,1,200);
INSERT INTO S VALUES (2016,2,150);
INSERT INTO S VALUES (2016,3,100);
INSERT INTO S VALUES (2016,4,300);
COMMIT;  
SELECT * from s;   

—（1）用分析函数lead/lag
-------LAG的方法

SELECT
      A1.Y
     ,A1.Q1
     ,A1.Q2
     ,A1.Q3
     ,A1.Q4 
FROM (SELECT
            A.Y
           ,A.Q
           ,A.AMT Q4 
           ,LAG(A.AMT,1)OVER(PARTITION BY A.Y ORDER BY 1 ) Q3
           ,LAG(A.AMT,2)OVER(PARTITION BY A.Y ORDER BY 1 ) Q2
           ,LAG(A.AMT,3)OVER(PARTITION BY A.Y ORDER BY 1 ) Q1
      FROM S A
      )A1
WHERE A1.Q=4;

-----------LEAD 的方法

SELECT
     A1.Y
    ,A1.Q1
    ,A1.Q2
    ,A1.Q3
    ,A1.Q4 
FROM (SELECT
           A.Y
          ,A.Q
          ,A.AMT  Q1
          ,LEAD(A.AMT,1)OVER(PARTITION BY A.Y ORDER BY 1 ) Q2
          ,LEAD(A.AMT,2)OVER(PARTITION BY A.Y ORDER BY 1 ) Q3
          ,LEAD(A.AMT,3)OVER(PARTITION BY A.Y ORDER BY 1 ) Q4
      FROM S A 
      )A1
WHERE A1.Q=1;

---

----方法3：CASE WHEN 的方法

SELECT
       A.Y
      ,SUM(CASE WHEN A.Q=1 THEN A.AMT ELSE 0 END ) Q1
      ,SUM(CASE WHEN A.Q=2 THEN A.AMT ELSE 0 END ) Q2
      ,SUM(CASE WHEN A.Q=3 THEN A.AMT ELSE 0 END ) Q3
      ,SUM(CASE WHEN A.Q=4 THEN A.AMT ELSE 0 END ) Q4
FROM S A 
GROUP BY A.Y;