oracle函数

计算工资的平均，中位数，最小值，最大值

SELECT AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal,
MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp;

查看每个部门的内容，分类汇总

SELECT deptno,
AVG(sal) AS mean_sal,
MEDIAN(sal) AS media_sal,
MIN(sal) AS min_sal,
MAX(sal) AS max_sal
FROM emp
GROUP by deptno;

显示原始内容，对比的看

SELECT deptno,ename,sal,
AVG(sal) OVER () AS mean_sal,
MEDIAN(sal) OVER () AS media_sal,
MIN(sal) OVER () AS min_sal,
MAX(sal) OVER () AS max_sal
FROM emp;

显示每个部门的平均薪水以及每个部门的员工数据

SELECT deptno,ename,sal,
AVG(sal) OVER (PARTITION BY deptno) AS mean_sal,
MEDIAN(sal) OVER (PARTITION BY deptno) AS media_sal,
MIN(sal) OVER (PARTITION BY deptno) AS min_sal,
MAX(sal) OVER (PARTITION BY deptno) AS max_sal
FROM emp;

使用计算

SELECT deptno,ename,sal,
MIN(sal) OVER (PARTITION BY deptno) AS min_sal,
sal-MIN(sal) OVER (PARTITION BY deptno) AS sal_diff
FROM emp;

FIRST_VALUE:返回的第一个结果

返回第一条工资

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER () AS FIRST_sal
FROM emp;

进行排列

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER (ORDER BY sal) AS FIRST_sal
FROM emp;

进行计算

SELECT empno,ename,job,sal,
FIRST_VALUE(sal) OVER (ORDER BY sal) AS lowest_sal,
sal-FIRST_VALUE(sal) OVER (ORDER BY sal) AS sal_diff
FROM emp;

每个部门的最低工资

## 显示部门最高和最低工资
SELECT empno,deptno,sal,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS zuidi,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS zuigao
FROM emp;

LAST_VALUE:回的最后一个结果
SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER () AS zuihou
FROM emp;

SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal ) AS zuidi
FROM emp;

指定边界
SELECT empno,deptno,sal,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS zuidi
FROM emp;

查询最高和最低工资
SELECT empno,deptno,sal,
FIRST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal) AS zidi,
LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS zuidi
FROM emp;

1：偏移量 表示结果集的前一行，没有前一行显示成0
SELECT empno,deptno,sal,
LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev
FROM emp;
它去除第一行，显示成设置的0

用于计算
SELECT empno,deptno,sal,
LAG(sal, 1,0) OVER (ORDER BY sal) AS sal_prev,
sal-LAG(sal, 1,0) OVER (ORDER BY sal) AS sal_diff
FROM emp;

SELECT empno,deptno,sal,
LAG(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS sal_prev
FROM emp;

LEAD：函数
它是把0补下面
SELECT empno,deptno,sal,
LEAD(sal, 1, 0) OVER (ORDER BY sal) AS sal_next,
LEAD(sal, 1, 0) OVER (ORDER BY sal) - sal AS sal_diff
FROM emp;

RANK函数

进行大小排列
SELECT empno,deptno,sal,
RANK() OVER ( ORDER BY sal) AS myrank
FROM emp;

SELECT empno，deptno，sal，
DENSE_RANK（）OVER（ ORDER BY sal）as myrank from
emp;

SELECT empno，deptno，sal，
RANK（）OVER（PARTITION BY deptno ORDER BY sal）as myrank from
emp;

LISTAGG函数

SELECT deptno, LISTAGG(ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

插入两条重名的人
INSERT INTO emp VALUES (7940,‘MILLER’,‘CLERK’,7782,to_date(‘23-1-1982’,‘dd-mm-yyyy’),1300,NULL,10);

取消重名的人

增加DISTINCT关键字
SELECT deptno, LISTAGG(DISTINCT ename, ‘,’) WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno
ORDER BY deptno;

19C以前的解决方法，我是记不下来
SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM (SELECT DISTINCT e.deptno, e.ename
FROM emp e) e2
GROUP BY e2.deptno
ORDER BY e2.deptno;

SELECT e2.deptno, LISTAGG(e2.ename, ‘,’) WITHIN GROUP (ORDER BY e2.ename) AS employees
FROM (SELECT e.*,
ROW_NUMBER() OVER (PARTITION BY e.deptno, e.ename ORDER BY e.empno) AS myrank
FROM emp e) e2
WHERE e2.myrank = 1
GROUP BY e2.deptno
ORDER BY e2.deptno;