oracle—排名函数与窗口函数
一、排名函数
1.rank（）——跳跃排名
按照年龄进行排名;
SQL> select * from employees ; EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH ----------- ------------- ----------------- ------------ -------------------- -------------- 10 江小白 4 大鳄 工程师 26 巴南 1988-07-01 3 昂呜 高级工程师 27 渝北 1998-06-01 2 李四 高级工程师 32 渝北 1994-09-01 1 张三 开发经理 37 巴南 1987-11-01 5 过户 工程师 26 渝中 1985-08-01 6 问题 工程师 25 渝中 1980-02-09 7 语句 测试工程师 24 九龙坡 2010-05-03 8 陈武 测试工程师 25 江北 2004-01-23 9 六六 测试工程师 32 南岸 1994-12-21 10 rows selected SQL> select employee_name,employee_age,rank() over(order by employee_age) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_AGE RANK_AGE ------------- ------------ ---------- 语句 24 1 陈武 25 2 问题 25 2 大鳄 26 4 过户 26 4 昂呜 27 6 李四 32 7 六六 32 7 张三 37 9 江小白 10 10 rows selected
2.dense_rank——不跳跃排名
按照年龄进行排名;
SQL> select employee_name,employee_age,dense_rank() over(order by employee_age) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_AGE RANK_AGE ------------- ------------ ---------- 语句 24 1 陈武 25 2 问题 25 2 大鳄 26 3 过户 26 3 昂呜 27 4 李四 32 5 六六 32 5 张三 37 6 江小白 7 10 rows selected
3.row_number()——排序
按照年龄进行排名;
SQL> select employee_name,employee_age,row_number() over(order by employee_age) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_AGE RANK_AGE ------------- ------------ ---------- 语句 24 1 陈武 25 2 问题 25 3 大鳄 26 4 过户 26 5 昂呜 27 6 李四 32 7 六六 32 8 张三 37 9 江小白 10 10 rows selected
二、窗口函数
求各岗位的平均年龄
SQL> select employee_name,employee_position,avg(employee_age) over(partition by employee_position) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION RANK_AGE ------------- ----------------- ---------- 陈武 测试工程师 27 六六 测试工程师 27 语句 测试工程师 27 昂呜 高级工程师 29.5 李四 高级工程师 29.5 大鳄 工程师 25.6666666 过户 工程师 25.6666666 问题 工程师 25.6666666 张三 开发经理 37 江小白 10 rows selected
各岗位按年龄排名
SQL> select employee_name,employee_position,employee_age,dense_rank() over(partition by employee_position order by employee_age) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE RANK_AGE ------------- ----------------- ------------ ---------- 语句 测试工程师 24 1 陈武 测试工程师 25 2 六六 测试工程师 32 3 昂呜 高级工程师 27 1 李四 高级工程师 32 2 问题 工程师 25 1 大鳄 工程师 26 2 过户 工程师 26 2 张三 开发经理 37 1 江小白 1 10 rows selected
三、窗口子句
使用前须对窗口内的数据进行排序。
1.rows子句
语法：over(order by 列名 rows between 位移量 preceding and 位移量 following)
需求：获取当前员工前一位、后一位员工年龄和。
SQL> select employee_name,employee_position,employee_age,sum(employee_age) over(order by employee_age rows between 1 preceding and 1 following) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE RANK_AGE ------------- ----------------- ------------ ---------- 语句 测试工程师 24 49 陈武 测试工程师 25 74 问题 工程师 25 76 大鳄 工程师 26 77 过户 工程师 26 79 昂呜 高级工程师 27 85 李四 高级工程师 32 91 六六 测试工程师 32 101 张三 开发经理 37 69 江小白 37 10 rows selected
2.range子句
语法：over(order by 列名 range between 差值 preceding and 差值 following)
需求：
SQL> select employee_name,employee_position,employee_age,count(1) over(order by employee_age range between 1 preceding and 1 following) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE RANK_AGE ------------- ----------------- ------------ ---------- 语句 测试工程师 24 3 陈武 测试工程师 25 5 问题 工程师 25 5 大鳄 工程师 26 5 过户 工程师 26 5 昂呜 高级工程师 27 3 李四 高级工程师 32 2 六六 测试工程师 32 2 张三 开发经理 37 1 江小白 1 10 rows selected
3.current row与unbounded
需求：获取第一条记录至当前窗口大小
SQL> select employee_name,employee_position,employee_age,count(1) over(order by employee_age rows between unbounded preceding and current row) last_value from employees; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE LAST_VALUE ------------- ----------------- ------------ ---------- 语句 测试工程师 24 1 陈武 测试工程师 25 2 问题 工程师 25 3 大鳄 工程师 26 4 过户 工程师 26 5 昂呜 高级工程师 27 6 李四 高级工程师 32 7 六六 测试工程师 32 8 张三 开发经理 37 9 江小白 10 10 rows selected
需求：不限制当前窗口
SQL> select employee_name,employee_position,employee_age,count(1) over(order by employee_age rows between unbounded preceding and unbounded following) last_value from employees; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE LAST_VALUE ------------- ----------------- ------------ ---------- 语句 测试工程师 24 10 陈武 测试工程师 25 10 问题 工程师 25 10 大鳄 工程师 26 10 过户 工程师 26 10 昂呜 高级工程师 27 10 李四 高级工程师 32 10 六六 测试工程师 32 10 张三 开发经理 37 10 江小白 10 10 rows selected
四、分析函数
1.first_value()——排序窗口中第一条数据。
获取同龄人中姓名靠前的名字
SQL> select employee_name,employee_age,first_value(employee_name) over(partition by employee_age order by employee_name) first_value from employees ; EMPLOYEE_NAME EMPLOYEE_AGE FIRST_VALUE ------------- ------------ ----------- 语句 24 语句 陈武 25 陈武 问题 25 陈武 大鳄 26 大鳄 过户 26 大鳄 昂呜 27 昂呜 李四 32 李四 六六 32 李四 张三 37 张三 江小白 江小白 10 rows selected
2.last_value()——排序窗口中最后一条数据。
SQL> select employee_name,employee_age,last_value(employee_name) over(partition by employee_age order by employee_name) last_value from employees ; EMPLOYEE_NAME EMPLOYEE_AGE LAST_VALUE ------------- ------------ ---------- 语句 24 语句 陈武 25 陈武 问题 25 问题 大鳄 26 大鳄 过户 26 过户 昂呜 27 昂呜 李四 32 李四 六六 32 六六 张三 37 张三 江小白 江小白 10 rows selected SQL> select employee_name,employee_age,last_value(employee_name) over(partition by employee_age order by employee_name rows between unbounded preceding and unbounded following) last_value from employees ; EMPLOYEE_NAME EMPLOYEE_AGE LAST_VALUE ------------- ------------ ---------- 语句 24 语句 陈武 25 问题 问题 25 问题 大鳄 26 过户 过户 26 过户 昂呜 27 昂呜 李四 32 六六 六六 32 六六 张三 37 张三 江小白 江小白 10 rows selected
第一条SQL，由于窗口为默认窗口；借助rows子句指定无限制窗口来实现。也可以通过order by desc来获取。
3.lag()——获取向前的记录
语法：lag(列名，位移，默认值)
需求：获取前一位员工信息。
SQL> select employee_name,employee_age,lag(employee_name,1,'N/A') over(order by employee_age) lag_name,lag(employee_age,1,null) over(order by employee_age) lag_age from employees ; EMPLOYEE_NAME EMPLOYEE_AGE LAG_NAME LAG_AGE ------------- ------------ -------- ---------- 语句 24 N/A 陈武 25 语句 24 问题 25 陈武 25 大鳄 26 问题 25 过户 26 大鳄 26 昂呜 27 过户 26 李四 32 昂呜 27 六六 32 李四 32 张三 37 六六 32 江小白 张三 37 10 rows selected
4.lead()——获取向后的记录
语法：lead(列名，位移，默认值)
需求：获取后一位员工信息
SQL> select employee_name,employee_age,lead(employee_name,1,'N/A') over(order by employee_age) lead_name,lead(employee_age,1,null) over(order by employee_age) lead_age from employees ; EMPLOYEE_NAME EMPLOYEE_AGE LEAD_NAME LEAD_AGE ------------- ------------ --------- ---------- 语句 24 陈武 25 陈武 25 问题 25 问题 25 大鳄 26 大鳄 26 过户 26 过户 26 昂呜 27 昂呜 27 李四 32 李四 32 六六 32 六六 32 张三 37 张三 37 江小白 江小白 N/A 10 rows selected
整理于《oracle入门很简单》一书
