Oracle之分析函数 - 各种分析函数例子

最新推荐文章于 2023-12-19 21:49:57 发布

一个阳光努力的程序盐

最新推荐文章于 2023-12-19 21:49:57 发布

阅读量1.6k

点赞数 1

分类专栏： Oracle之分析函数

本文链接：https://blog.csdn.net/qq_33879355/article/details/78450394

版权

Oracle之分析函数专栏收录该内容

11 篇文章 2 订阅

订阅专栏

/*函数应用主要分为如下4类：

1 统计函数（MAX MIN AVG SUM COUNT等）
2.排序函数（ROW_NUMBER RANK DENSE_RNAK FIRST FIRST_VALUE LAST LAST_VALUE LAG LEAD 等）
3.数据分布函数（NTILE CUME_DIST PERCENT_RANK PERCENTILE_CON PERCENTILE_DISC RATIO_TO_REPORT 等）
4.数学分析函数（CORR COVAR_POP COVAR_SAMP STDDEV STDDEV_SAMP VAR_POP VAR_SAMP VARIANCE 等）

对于大部分人员来说，用的最多的是第1和第2类。后面两类可以暂时不用关注（特别说明，第3类中的RATIO_TO_REPORT倒是用的频率还不小）

此外由于统计函数似乎过于简单，大家都明白具体的用途，也没有什么必要特别再来说明，因此主要说说排序函数。

*/

drop table emp purge;

CREATE TABLE emp
(
emp_id NUMBER(6),
ename VARCHAR2(45),
dept_id NUMBER(4),
hire_date DATE,
sal NUMBER(8,2)
);

--创建emp数据
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom', 20, TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike', 20, TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John', 50, TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy', 50, TO_DATE('10-04-1997', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich', 50, TO_DATE('01-05-1995', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate', 50, TO_DATE('10-10-1997', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess', 50, TO_DATE('16-11-1999', 'DD-MM-YYYY'), 6000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev', 10, TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);
COMMIT;

set linesize 2000
set pagesize 2000
col emp_id format 999
col dept_id format 99
col sal format 9999
col ename format a5
col hire_date FORMAT DATE
col fir_val FORMAT a10
col fir_val_desc FORMAT a10
col last_val FORMAT a10
col last_val_desc FORMAT a10

SELECT
emp_id,ename,dept_id,hire_date,sal,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY sal) AS row_number,
RANK() OVER (PARTITION BY dept_id ORDER BY sal) AS rank,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY sal) AS dense_rank
FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE SAL ROW_NUMBER RANK DENSE_RANK
------ ----- ------- -------------- ----- ---------- ---------- ----------
100 Stev 10 01-1月 -90 7000 1 1 1
101 Tom 20 21-9月 -89 2000 1 1 1
102 Mike 20 13-1月 -93 8000 2 2 2
120 John 50 18-7月 -96 1000 1 1 1
123 Kate 50 10-10月-97 4000 2 2 2
122 Rich 50 01-5月 -95 4000 3 2 2
121 Joy 50 10-4月 -97 4000 4 2 2
124 Jess 50 16-11月-99 6000 5 5 3

SELECT
emp_id,ename,dept_id,hire_date,sal,
MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY hire_date) OVER (PARTITION BY dept_id) Worst,
MAX(sal) KEEP (DENSE_RANK LAST ORDER BY hire_date) OVER (PARTITION BY dept_id) Best
FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE SAL WORST BEST
------ ----- ------- -------------- ----- ---------- ----------
100 Stev 10 01-1月 -90 7000 7000 7000
101 Tom 20 21-9月 -89 2000 2000 8000
102 Mike 20 13-1月 -93 8000 2000 8000
124 Jess 50 16-11月-99 6000 4000 6000
123 Kate 50 10-10月-97 4000 4000 6000
122 Rich 50 01-5月 -95 4000 4000 6000
120 John 50 18-7月 -96 1000 4000 6000
121 Joy 50 10-4月 -97 4000 4000 6000

SELECT
emp_id,ename,dept_id,hire_date,sal,
LAG(sal) OVER (ORDER BY hire_date) AS prev_sal1,
LEAD(sal) OVER (ORDER BY hire_date) AS next_sal1,
LAG(sal, 1, 0) OVER (ORDER BY hire_date) AS prev_sal2,
LEAD(sal, 1,0) OVER (ORDER BY hire_date) AS next_sal2,
LAG(sal, 1, 0) OVER (partition BY dept_id ORDER BY hire_date) AS prev_sal3,
LEAD(sal, 1,0) OVER (partition BY dept_id ORDER BY hire_date) AS next_sal3,
LAG(sal, 2, 999) OVER (partition BY dept_id ORDER BY hire_date) AS prev_sal4,
LEAD(sal, 2,999) OVER (partition BY dept_id ORDER BY hire_date) AS next_sal4
FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE SAL PREV_SAL1 NEXT_SAL1 PREV_SAL2 NEXT_SAL2 PREV_SAL3 NEXT_SAL3 PREV_SAL4 NEXT_SAL4
------ ----- ------- -------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
101 Tom 20 21-9月 -89 2000 7000 0 7000 0 8000 999 999
100 Stev 10 01-1月 -90 7000 2000 8000 2000 8000 0 0 999 999
102 Mike 20 13-1月 -93 8000 7000 3000 7000 3000 2000 0 999 999
122 Rich 50 01-5月 -95 3000 8000 1000 8000 1000 0 1000 999 4000
120 John 50 18-7月 -96 1000 3000 4000 3000 4000 3000 4000 999 5000
121 Joy 50 10-4月 -97 4000 1000 5000 1000 5000 1000 5000 3000 6000
123 Kate 50 10-10月-97 5000 4000 6000 4000 6000 4000 6000 1000 999
124 Jess 50 16-11月-99 6000 5000 5000 0 5000 0 4000 999

SELECT
emp_id,ename,dept_id,hire_date,sal,
FIRST_VALUE(ename) OVER(PARTITION BY dept_id ORDER BY sal ) AS fir_val,
FIRST_VALUE(ename) OVER(PARTITION BY dept_id ORDER BY sal DESC) AS fir_val_desc,
LAST_VALUE(ename) OVER(PARTITION BY dept_id ORDER BY sal ) AS last_val,
LAST_VALUE(ename) OVER(PARTITION BY dept_id ORDER BY sal DESC) AS last_val_desc
FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE SAL FIR_VAL FIR_VAL_DE LAST_VAL LAST_VAL_D
------ ----- ------- -------------- ----- ---------- ---------- ---------- ----------
100 Stev 10 01-1月 -90 7000 Stev Stev Stev Stev
101 Tom 20 21-9月 -89 2000 Tom Mike Tom Tom
102 Mike 20 13-1月 -93 8000 Tom Mike Mike Mike
120 John 50 18-7月 -96 1000 John Jess John John
121 Joy 50 10-4月 -97 4000 John Jess Rich Rich
123 Kate 50 10-10月-97 4000 John Jess Rich Rich
122 Rich 50 01-5月 -95 4000 John Jess Rich Rich
124 Jess 50 16-11月-99 6000 John Jess Jess Jess