Oracle 分析函数

DROP TABLE DEPT;
CREATE TABLE DEPT (
       DID VARCHAR2(10) PRIMARY KEY,
       DEPTNO VARCHAR2(30) NOT NULL,
       DNAME VARCHAR2(30) NOT NULL,
       D_PART VARCHAR2(30),
       MIN_SAR VARCHAR2(30),
       MAX_SAR VARCHAR2(30)
       );
INSERT INTO DEPT VALUES(1,'000001','DEPT1','A','2000','5000');
INSERT INTO DEPT VALUES(2,'000002','DEPT2','A','2000','5000');
INSERT INTO DEPT VALUES(3,'000003','DEPT3','A','4000','6000');
INSERT INTO DEPT VALUES(4,'000004','DEPT4','B','4000','7000');
INSERT INTO DEPT VALUES(5,'000005','DEPT5','B','5000','8000');
INSERT INTO DEPT VALUES(6,'000006','DEPT6','B','2000','9000');
COMMIT;

DROP TABLE DEPT_USER;  
CREATE TABLE DEPT_USER(
       U_ID VARCHAR2(10) PRIMARY KEY,
       D_DID VARCHAR2(10),
       D_UNAME VARCHAR2(30) NOT NULL,
       D_SAR VARCHAR2(30)
       );
INSERT INTO DEPT_USER VALUES(1,1,'JONES','5000');
INSERT INTO DEPT_USER VALUES(2,1,'IRIS','5000');
INSERT INTO DEPT_USER VALUES(3,3,'SIMIDA','6000');
INSERT INTO DEPT_USER VALUES(4,5,'TOM','7000');
INSERT INTO DEPT_USER VALUES(5,5,'SUSAN','8000');
INSERT INTO DEPT_USER VALUES(6,5,'JANE','9000');
INSERT INTO DEPT_USER VALUES(7,NULL,'TIM','9000');
COMMIT;       
 
DROP TABLE EMP1;       
CREATE TABLE EMP1(
       U_ID VARCHAR2(10) PRIMARY KEY,
       DID VARCHAR2(10),
       DEPTNO VARCHAR2(30),
       D_UNAME VARCHAR2(30) NOT NULL,
       SAR VARCHAR2(30),
       SAl VARCHAR2(30),
       SEX VARCHAR2(30),
       JOB VARCHAR2(30)
       );   
INSERT INTO EMP1 VALUES(1,1,'000001','JONES','500','4500','B','厨师');
INSERT INTO EMP1 VALUES(2,2,'000002','IRIS','650','6500','B','助理');
INSERT INTO EMP1 VALUES(3,2,'000003','SIMIDA','660','6600','B','工程师');
INSERT INTO EMP1 VALUES(4,3,'000003','TOM','780','7800','G','工程师');
INSERT INTO EMP1 VALUES(5,3,'000003','SUSAN','850','850','G','助理');
INSERT INTO EMP1 VALUES(6,3,'000003','JANE','800','800','G','经理');
INSERT INTO EMP1 VALUES(7,NULL,'','TIM','9000','9000','G','助理');
INSERT INTO EMP1 VALUES(8,9,'000009','TIM','6000','6000','G','经理');
COMMIT; 

--分析函数
/**
1、over(
(1)、partition by：分组
(2)、order by：排序
(3)、rows/range ..between number/unbounded preceding and number/unbounded following  and（窗口，有group by才能用）
)
2、排序函数（从一开始向N递增）
（
(1)、row_number()：
(2)、rank()
(1)、dense_rank()
）

3、first_value()、last_value()--first_value第一個值、last_value最後一個值--可用於求最值
4、lag/lead(列名，偏移位数，超出表默认值)
5、group by rollup/cube(a,b,c)
**/

/**
--排序函数
row_number():123
rank():112
dense_rank():113
**/

select d.rn,d.* from 
(select a.*,row_number() over(partition by d_part order by did) rn from dept a )d 
order by did;

select a.*,
row_number() over(order by max_sar desc) rn,
rank() over(order by max_sar desc) rk,
dense_rank() over(order by max_sar desc)dr from dept a

--偏移lag、lead
select a.*,
lag(min_sar,1,0) over(partition by d_part order by max_sar) 向下偏移,
lead(max_sar,1,10000) over(partition by d_part order by max_sar) 向上偏移
from dept a

--第一个值，最后一个值
select a.*,
first_value(max_sar) over(partition by d_part order by max_sar ) fv,
last_value(max_sar) over(partition by d_part order by max_sar  ) lv
from dept a

--获取工资比例  ratio_to_report
select d.*,round(ratio_to_report(d_sar)over(partition by d_did),2)占当前部门比例 
from dept_user d; 

--ntile函数分组
select d.*, ntile(4) over(order by did) as 组 from emp1 d--必須要有order by 條件

--rank/range
select d.*, sum(d_sar) 
over(partition by d_did order by U_ID rank between 3 preceding and 3 following) as 组 --上下3行的值的總和
from dept_user d

select d.*, avg(d_sar) 
over(partition by d_did order by d_sar rank between current row  and 3 following) as 组--當前組當前行到下3行的平均值
from dept_user d 

select e.*,avg(sar)
over(partition by did order by sar range between current row  and 4300 following) as 值--當前組當前值到下浮動為4300的平均值
from emp1 e; 

select e.*,avg(sar)
over(partition by did order by sar range between 4400 preceding and 4500 following) as 值--上浮動4400和下浮動4500的值的平均值
from emp1 e; 

--返回最值所在行的数据 
--first_value第一個值、last_value最後一個值
select d.*,topsar from (select first_value(d_uname) over(partition by d_did) as topsar 
from dept_user 
where d_sar in (select max(d_sar)from dept_user) order by u_id,d_sar desc)a,dept_user d;

select dept_user.*,d_uname  as topsar 
from dept_user 
where d_sar in (select max(d_sar)from dept_user)order by u_id,d_sar desc;

with a as
(select d.*, max(d_sar) over(partition by d_did)最高工资 from dept_user d), 
b as
(select d.*, rank() over(partition by d_did order by d_sar) rk from dept_user d)
select b.* from b where rk=1;

select d.*,first_value(d_uname)over(partition by d_did order by d_sar desc,d_uname desc) from dept_user d;

select d.*,max(d_uname) over(partition by d_did order by d_sar desc)部门工资最高的人 from 
 dept_user d;

--獲取各個部門最高工資的人的排序
select * from dept_user;
select d_did,
LISTAGG(d_uname,',') 
within group(order by d_sar desc)
over(partition by d_did) 工作最高的人 from dept_user  order by d_sar;
--eg:
SELECT did,sar,listagg(D_UNAME,',')
WITHIN GROUP(ORDER BY sar DESC)
OVER(PARTITION BY did) FROM emp1;
 

--獲取各個部門最高工資的人（單個）
select u_id,d_did,
max(d_uname) keep(dense_rank first order by d_sar)
over(partition by d_did) 工作最高的人 from dept_user;

select did,d_uname,
max(d_uname) keep(dense_rank first order by sar)
over(partition by did) 工作最高的人 from emp1;

--獲取某個部門最高工資的人（多個）
create or replace view three_dept_sqr as select d.*, rank() over(partition by d_did order by d_sar desc) rk from dept_user d where d_did=5;
select * from three_dept_sqr;
with x1 as
(select listagg(d_uname,',')within group(order by rk)最高工資的人 from three_dept_sqr ds where rk=1)
select ds.*,最高工資的人 from three_dept_sqr ds,x1 

--獲取各個部門最高工資的人（多個）
with 
x1 as 
(select d.*, rank() over(partition by d_did order by d_sar desc) rk from dept_user d),
x2 as
(select d_did,listagg(d_uname,',')within group(order by rk)最高工資的人 from x1 where rk=1 group by d_did)
select x1.*,最高工資的人 from x1,x2 where x1.d_did=x2.d_did  
--eg:
WITH x1 AS
(SELECT e.*,dense_rank()OVER(PARTITION BY did ORDER BY sar desc)rk FROM emp1 e),
x2 AS
(SELECT did,listagg(d_uname,',')WITHIN GROUP(ORDER BY sar)FROM x1 WHERE rk=1 GROUP BY did)
SELECT * FROM x1,x2 WHERE x1.did=x2.did ORDER BY sar DESC; 

-----------------------行列裝換begin------------------

--行转列(case..when..then\pivot)
select d_uname,
sum(case when d_did=1 then d_sar end) as 部门2的工资,
  sum(case when d_did=3 then d_sar end) as 部门3的工资,
   sum(case when d_did=5 then d_sar end) as 部门4的工资
 from dept_user 
 group by d_uname;
 
 --pivot只能对一个条件有效，需要同时转换2个或以上条件时需要用case..when..then
 select * from(select d_uname,d_sar,d_did from dept_user)
 pivot(sum(d_sar) as s for d_did in (1 as b1,3 as b3,5 as b5));
  

--列转行(union all\unpivot)
with x1 as
 (select * from(select d_uname,d_sar,d_did from dept_user)
 pivot(sum(d_sar) as s for d_did in (1 as b1,3 as b3,5 as b5)))
 select * from x1 unpivot(d_sar for d_did in(b1_s,b3_s,b5_s));

--sept1:
select job, 
sum(case when deptno='000001' then sal end) as 部門000001總工資,
sum(case when deptno='000003' then sal end) as 部門000003總工資,
sum(case when deptno='000005' then sal end) as 部門000005總工資
from emp1 group by job;

--sept2: 
 create or replace view p_view
 as
 select * from emp1
 pivot(sum(sal) as 總工資 for deptno in('000001' 部門000001,'000003' 部門000003,'000005' 部門000005));

--sept3: 
 select * from p_view
 unpivot(sal for deptno IN(部門000001_總工資,部門000003_總工資,部門000005_總工資 ));
 
-----------------------行列裝換end------------------

CREATE OR REPLACE  VIEW v_emp1 AS( SELECT  * FROM (SELECT decode(sex,'B','男','G','女') AS 性別,did,U_ID FROM emp1) 
PIVOT(COUNT(U_ID) FOR did IN(1 AS 部門一,2 AS 部門二,3 AS 部門三)));
SELECT * FROM v_emp1;

SELECT * FROM v_emp1
unpivot(人數 FOR 部門 IN(部門一,部門二,部門三));

SELECT did,SUM(CASE WHEN sex='B' THEN 1 ELSE 0 END) 男,
        SUM(CASE WHEN sex='G' THEN 1 ELSE 0 END) 女 FROM emp1 GROUP BY did;
        
------------纍計

 create table User_Salary (UserName varchar(200), Month varchar(20), Salary int)

  insert into User_Salary (UserName,Month,Salary ) values('AAA','2010/12',1000);
  insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/01',2000);
  insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/02',3000);
  insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/03',1000);
  insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/04',2000);
  insert into User_Salary (UserName,Month,Salary ) values('AAA','2011/05',3000);

  SELECT USERNAME,MONTH,SALARY, (
         SELECT SUM(SALARY) 
         FROM 
              USER_SALARY I
          WHERE  I.MONTH<=O.MONTH
         )CUMMULATION
 FROM USER_SALARY O
 ORDER BY 1,2
 

/**
分組函數：avg、sum、min、count、max、group by...
**/
select e.sar+e.sex esar from emp1 e where e.sar>avg(e.sar) and e.sex>avg(e.sex) order by esar;--錯誤示範，分組函數不能用在當前位置

--group by
/**
1、group by (a,b,c)根據所有字段abc分組
2、group by rollup(a,b,c)以a為主，根據a,ab,abc分組，外加一個匯總行
3、group by cube(a,b,c)根據a,ab,ac,abc,b,bc,c分組，外加一個匯總行
**/
select job,deptno,sal,avg(sal),sum(sal) from emp1 group by job,deptno,sal;
select job,deptno,sal,avg(sal),sum(sal) from emp1 group by rollup(job,deptno,sal);
select job,deptno,sal,avg(sal),sum(sal) from emp1 group by cube(job,deptno,sal);

/**将查询结果一行显示**/
 SELECT   listagg (COLUMN, ',') WITHIN GROUP (ORDER BY COLUMN) names
       FROM TABLE