分析函数（partition by）与 开窗函数(over())_partition by over

分析函数（partition by）：

分析函数是Oracle专门用于解决复杂报表统计需求的函数，它可以在数据中进行分组，然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

普通的聚合函数用group by分组，每个分组返回一个统计值，只有一行，而分析函数采用partition by分组，每组中包含多个值。

开窗函数(over())：

开窗函数指定了分析函数中的分组的大小。

分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，这些就是窗口的规则。他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。

注意：窗口子句不能单独出现，必须有order by子句时才能出现。

示例

1.1、数据准备

一、创建表
CREATE TABLE Z_EMPLOYEES(
"RRN" NUMBER(11) NOT NULL ,
"NAME" VARCHAR2(64 BYTE) NULL ,
"BIRTHDAY" DATE NULL ,
"DEPT_ID" VARCHAR2(64 BYTE) NULL ,
"SALARY" NUMBER(11,2) NULL 
)
ALTER TABLE Z_EMPLOYEES ADD PRIMARY KEY ("RRN");

二、插入数据：
INSERT INTO Z_EMPLOYEES VALUES ('100001', '小王1', TO_DATE('1990-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT', '2000');
INSERT INTO Z_EMPLOYEES VALUES ('100002', '小王2', TO_DATE('1990-01-02 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT', '2000');
INSERT INTO Z_EMPLOYEES VALUES ('100003', '小王3', TO_DATE('1990-01-03 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT', '3000');
INSERT INTO Z_EMPLOYEES VALUES ('100004', '小王4', TO_DATE('1990-01-04 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'IT', '3000');
INSERT INTO Z_EMPLOYEES VALUES ('100005', '小芳1', TO_DATE('1994-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PIE', null);
INSERT INTO Z_EMPLOYEES VALUES ('100006', '小芳2', TO_DATE('1994-01-02 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PIE', null);
INSERT INTO Z_EMPLOYEES VALUES ('100007', '小芳3', TO_DATE('1994-01-03 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PIE', '8000');
INSERT INTO Z_EMPLOYEES VALUES ('100008', '小刚1', TO_DATE('1996-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PERSON', null);
INSERT INTO Z_EMPLOYEES VALUES ('100009', '小刚2', TO_DATE('1996-01-02 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PERSON', null);
INSERT INTO Z_EMPLOYEES VALUES ('100010', '小刚3', TO_DATE('1996-01-03 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'PERSON', '1000');
INSERT INTO Z_EMPLOYEES VALUES ('100011', '小黄1', TO_DATE('1992-01-01 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'EQ', '1400');
INSERT INTO Z_EMPLOYEES VALUES ('100012', '小黄2', TO_DATE('1992-01-02 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'EQ', '1600');
INSERT INTO Z_EMPLOYEES VALUES ('100013', '小黄3', TO_DATE('1992-01-03 10:30:00', 'YYYY-MM-DD HH24:MI:SS'), 'EQ', '1700');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

1.2、查询各个部门最高/低工资员工信息

--查询各个部门工资最高的员工（空值表示工资最低，并列的也要查出）--row_number() dense_rank() rank()
select * from (
    select e.*, dense_rank() over(partition by dept_id order by salary desc) rn
    from Z_EMPLOYEES e where e.salary > 0) t
where t.rn = 1;

--查询各个部门工资最低的员工（空值表示工资最低，并列的也要查出）--row_number() dense_rank() rank()
select * from (
    select e.*, dense_rank() over(partition by dept_id order by nvl(salary,0) asc) rn
    from Z_EMPLOYEES e) t
where t.rn = 1;
1
2
3
4
5
6
7
8
9
10
11

1.3 NULLS FIRST/LAST

--查询各个部门工资最低的员工（空值表示工资最低，并列的也要查出）--row_number() dense_rank() rank()
select * from (
    select e.*, dense_rank() over(partition by dept_id order by salary asc NULLS FIRST) rn
    from Z_EMPLOYEES e) t
where t.rn = 1;
1
2
3
4
5

2.1 LISTAGG( T.XX1, ‘,’ ) WITHIN GROUP(ORDER BY T.XX2)

SELECT LISTAGG(B.RRN, ' ') WITHIN GROUP(ORDER BY B.BIRTHDAY ASC) RRN,
   LISTAGG(B.NAME, ' ') WITHIN GROUP(ORDER BY B.BIRTHDAY ASC) NAME,
   LISTAGG(TO_CHAR(B.BIRTHDAY,'YYYY-MM-DD HH24:MI:SS'), '  ') WITHIN GROUP(ORDER BY B.BIRTHDAY ASC) BIRTHDAY,
   LISTAGG(B.DEPT_ID, ' ') WITHIN GROUP(ORDER BY B.BIRTHDAY ASC) DEPT_ID,
   SALARY,
   LISTAGG(B.RN, ' ') WITHIN GROUP(ORDER BY B.BIRTHDAY DESC) RN
	 FROM (SELECT * FROM (SELECT E.*, DENSE_RANK() OVER(PARTITION BY DEPT_ID ORDER BY SALARY ASC NULLS FIRST) RN FROM Z_EMPLOYEES E) A WHERE A.RN = 1) B
	 GROUP BY B.SALARY ORDER BY B.SALARY DESC;

1
2
3
4
5
6
7
8
9

其他

 select 
   FIRST_VALUE(t.salary) OVER(PARTITION BY t.dept_id) FIRST_SAL,
   LAST_VALUE(t.salary) OVER(PARTITION BY t.dept_id) LAST_SAL,
   sum(t.salary)  OVER(PARTITION BY t.dept_id) dept_sum_sal,--全统计，各部门总薪资
   sum(t.salary) OVER() all_sal,--所有员工薪资总和
   sum(t.salary)  OVER(order by t.rrn) Cum_sum,--滚动统计，累加
   AVG(t.salary) OVER(PARTITION BY t.dept_id) dept_avg_sal,--不包含null
   ROUND(AVG(t.salary) OVER(PARTITION BY t.dept_id),2) avg_salary_r2,----ROUND(数据,位数)   函数代表四舍五入，TRUNC(数据,位数)   直接向下取到指定位数的小数
   sum(t.salary) over(order by t.rrn rows between 1 preceding and 1 following) sum_p1f1,--范围统计，按id排序，相邻三人工资求和
   lag(t.salary,1) over(order by t.rrn) lag_1,--(相邻)行比较，前一行
   t.salary curren_sal,--当前行
   lead(t.salary,1) over(order by t.rrn) lead_1,--后一行
   count(*) OVER(PARTITION BY t.dept_id) Num_people,--各部门人数
   '--------------'  SEPARATOR,
   t.*
 from Z_EMPLOYEES t order by t.rrn;
-------------------------
SELECT MAX(t.birthday) KEEP(DENSE_RANK FIRST ORDER BY t.salary ASC NULLS FIRST) a,--工资最低人中年龄最小的
  MIN(t.birthday) KEEP(DENSE_RANK FIRST ORDER BY t.salary ASC NULLS FIRST) b--工资最低人中年龄最大的
 FROM z_employees t
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20