MAX() OVER(PARTITION BY .. ORDER BY .. DESC) 排序规则只能为desc，否则不起作用，将查询到目前为止排序值最高字段的对应值
MIN() OVER(PARTITION BY .. ORDER BY .. ASC ) 排序规则只能为asc，否则不起作用，将查询到目前为止排序值最低的字段的对应值，
如下：
SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
MIN(E.SAL)
OVER(PARTITION
BY E.DEPTNO) MIN_SAL01,
MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO) MAX_SAL01,
MIN(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL) MIN_SAL02,
MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL) MAX_SAL02,
--不起作用
MIN(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL
DESC) MIN_SAL03,
--不起作用
MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL
DESC) MAX_SAL03,
MIN(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL
ASC) MIN_SAL04,
MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL
ASC) MAX_SAL04,
--不起作用
NVL(E.SAL
- MIN(E.SAL)
OVER(PARTITION
BY E.DEPTNO),
0) DIFF_MIN_SAL,
NVL(MAX(E.SAL)
OVER(PARTITION
BY E.DEPTNO)
- E.SAL,
0) DIFF_MAX_SAL
FROM EMP E;
结果如下：
3、lead()/lag() over(partition by ... order by ...) 取前面/后面第n行记录
说明：
lead(列名,n,m): 当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。
lag(列名,n,m): 当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。
示例：查询个人工资与比自己高一位、第一位的工资的差额
使用分析函数：
SELECT E.ENAME,
E.JOB,
E.SAL,
E.DEPTNO,
LEAD(E.SAL, 1,
0)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL) LEAD_SAL,
--记录后面第n行记录
LAG(E.SAL,
1,
0)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL) LAG_SAL,
--记录前面第N行记录
NVL(LEAD(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL)
- E.SAL,
0) DIFF_LEAD_SAL,
NVL(E.SAL - LEAD(E.SAL)
OVER(PARTITION
BY E.DEPTNO
ORDER BY E.SAL),
0) DIFF_LAG_SAL
FROM EMP E;
查询结果：
4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取首尾记录
示例：
