oracle—排名函数与窗⼝函数⼀、排名函数
1.rank（）——跳跃排名
按照年龄进⾏排名;
SQL>select*from employees ;
EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE EMPLOYEE_ADD EMPLOYEE_BIRTH
----------- ------------- ----------------- ------------ -------------------- --------------
10江⼩⽩
rank函数怎么排名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
------------- ------------ ----------
语句241
陈武252
问题252
⼤鳄264
过户264
昂呜276
李四327
六六327
张三379
江⼩⽩10
10 rows selected
View Code
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
------------- ------------ ----------
语句241
陈武252
问题252
⼤鳄263
过户263
昂呜274
李四325
六六325
张三376
江⼩⽩7
10 rows selected
View Code
按照年龄进⾏排名;
SQL>select employee_name,employee_age,row_number() over(order by employee_age) rank_age from employees ;
EMPLOYEE_NAME EMPLOYEE_AGE RANK_AGE
------------- ------------ ----------
语句241
陈武252
问题253
⼤鳄264
过户265
昂呜276
李四327
六六328
张三379
江⼩⽩10
10 rows selected
View Code
⼆、窗⼝函数
求各岗位的平均年龄
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
View Code
各岗位按年龄排名
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
------------- ----------------- ------------ ----------
语句测试⼯程师241
陈武测试⼯程师252
六六测试⼯程师323
昂呜⾼级⼯程师271
李四⾼级⼯程师322
问题⼯程师251
⼤鳄⼯程师262
过户⼯程师262
张三开发经理371
江⼩⽩1
10 rows selected
View Code
三、窗⼝⼦句
使⽤前须对窗⼝内的数据进⾏排序。
语法：over(order by 列名 rows between 位移量 preceding and 位移量 following)
需求：获取当前员⼯前⼀位、后⼀位员⼯年龄和。
SQL>select employee_name,employee_position,employee_age,sum(employee_age) over(order by e
mployee_age rows between1 preceding and1 following) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE RANK_AGE
------------- ----------------- ------------ ----------
语句测试⼯程师2449
陈武测试⼯程师2574
问题⼯程师2576
⼤鳄⼯程师2677
过户⼯程师2679
昂呜⾼级⼯程师2785
李四⾼级⼯程师3291
六六测试⼯程师32101
张三开发经理3769
江⼩⽩37
10 rows selected
View Code
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 between1 preceding and1 following) rank_age from employees ; EMPLOYEE_NAME EMPLOYEE_POSITION EMPLOYEE_AGE RANK_AGE
------------- ----------------- ------------ ----------
语句测试⼯程师243
陈武测试⼯程师255
问题⼯程师255
⼤鳄⼯程师265
过户⼯程师265
昂呜⾼级⼯程师273
李四⾼级⼯程师322
六六测试⼯程师322
张三开发经理371
江⼩⽩1
10 rows selected
View Code
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
------------- ----------------- ------------ ----------
语句测试⼯程师241
陈武测试⼯程师252
问题⼯程师253
⼤鳄⼯程师264
过户⼯程师265
昂呜⾼级⼯程师276
李四⾼级⼯程师327
六六测试⼯程师328
张三开发经理379
江⼩⽩10
10 rows selected
View Code
需求：不限制当前窗⼝
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
------------- ----------------- ------------ ----------
语句测试⼯程师2410
陈武测试⼯程师2510
问题⼯程师2510
⼤鳄⼯程师2610
过户⼯程师2610
昂呜⾼级⼯程师2710
李四⾼级⼯程师3210
六六测试⼯程师3210
张三开发经理3710
江⼩⽩10
10 rows selected
View Code
四、分析函数
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
View Code
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
View Code
第⼀条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
View Code
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
View Code
整理于《oracle⼊门很简单》⼀书
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论