oracle学习笔记(六):oracle中排序函数及其应用

初学oracle时,关于排序问题经常使用order by或者使用rownum进行分页,但是在TopN(N>2)时上述方法使用不是特别方便。在刷题过程中了解并学习了row_number,dense_rank,rank等函数,以下是对这几个问题的简单整理。



基础介绍

1. 基本用法

基本用法:

row_number() over (partition by col1 order by col2)

-- 表示根据col1分组,在分组内部根据col2排序,而此函数计算的值就表示每组内部排序后的顺序编号
(组内连续唯一)

rank,dense_rank,row_number,以及分组排名partition,lag(前向(首)),lead(向后(末))
函数 描述
rank 排名会出现并列第n名,它之后的会跳过空出的名次,例如:1,2,2,4
dense_rank 排名会出现并列第n名,它之后的名次为n+1,例如:1,2,2,3
row_number: :排名采用唯一序号连续值,例如1,2,3,4
partition 将排名限制到某一分组
lag arg1是检索列,arg2是偏移量(可以理解为滑动平均的长度),arg3表示超出当前范围的处理
2. 基本格式
  • 基本格式
row_number() over(partition by x order by y) ...
row_number() over(order by x) ...
dense_rank() over(order by x) ...
rank() over(order by x)...
3.常见查询组合
  • 常见的查询引用:
--按id进行排序
select id,val,row_number() over (order by id) x from a; 
--//按val分组,分组内按id排序
select id,val,row_number() over (partition by val order by id) x from a; 
-- id排序值 减去 val分组内id排序值 = 连续相同值的排序值
select id,val,row_number() over (order by id) - row_number() over (partition by val order by id) x from a;

应用案例

1.连续值查询问题
  • 1编写一个 SQL 查询,查找所有至少连续出现三次的数字。
    表数据样例:
create table Logs(
id number primary key,
    num number 
);

insert into Logs values(1,1);
insert into Logs values(2,1);
insert into Logs values(3,1);
insert into Logs values(4,2);
insert into Logs values(5,1);
insert into Logs values(6,2);
insert into Logs values(7,2);

对应的查询语句:

/* Write your PL/SQL query statement below */
select distinct(num) "ConsecutiveNums"
from (
	select num,(row_number() over(order by id )-row_number() over(partition by num order by id)) rank_
	from Logs
) tmp
group by rank_,num
having count(rank_)>=3;

类似的案例:603. 连续空余座位 - 力扣(LeetCode)

seat_id free
1 1
2 0
3 1
4 1
5 1

其中,free中0表示已占位,1表示空缺
写一个查询语句,获取连续出现两次的空位,并返回seat_id !

/* Write your PL/SQL query statement below */
select seat_id
from (
        select tmp.*,count(*) over(partition by x )  y
        from (
            select c.*,rownum-row_number() over(partition by free order by rownum) x
                from cinema c) tmp
        where free=1)
where y>=2
order by seat_id;

或者:

/* Write your PL/SQL query statement below */
select seat_id
from (
        select tmp.*,count(*) over(partition by x )  y
        from (select c.*,row_number() over(order by seat_id)-row_number() over(partition by free order by seat_id) x
                from cinema c) tmp
        where free=1)
where y>=2
order by seat_id;

603问题的核心,在于检测到连续序列,下述查询语句是解决该问题的关键.

-- 第一种
-- id排序值 减去 val分组内id排序值 = 连续相同值的排序值
select c.*,row_number() over(order by seat_id)-row_number() over(partition by free order by seat_id) x from cinema c;

-- 第二种:
select c.*,rownum-row_number() over(partition by free order by rownum) x from cinema c;

这条语句的查询语句各变量的统计结果如下图所示:
在这里插入图片描述
其中,A表示行号;B表示free变量分组排序;X表示A和B的差值.

  • 两种写法有何区别?
    以该例子为例,当seat_id有序排列时,两者没有任何差异.
    以下是乱序的结果验证,对cinema选择部分,然后乱序排列,组成表cinema2.结果显示两者结果相同
    cinema2:
seat_id free
1 1
2 0
4 1
3 1
create table cinema2(
	seat_id number(3),
	free number(3));
	
insert into cinema2 values(1,1);
insert into cinema2 values(2,0);
insert into cinema2 values(4,1);
insert into cinema2 values(3,1);

以下是两组语句检测的连续序列相同,最终得结果也相同.
A

B
所以接下来选其一解释,为何上述X可以表示连续值呢?
个人理解:可以将free可以看作是列表,Afree中个元素出现的位置排序;X=A-B则表示元素距初始(0)位置的距离,若X出现连续等值说明该序列存在连续,如下图所示.
在这里插入图片描述

上述案例的变形
601. 体育馆的人流量 - 力扣(LeetCode)
请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于100。

/* Write your PL/SQL query statement below */
SELECT a.id, to_char(a.visit_date, 'yyyy-MM-dd') AS visit_date, a.people
FROM (
	SELECT id, visit_date, people, COUNT(*) OVER (PARTITION BY a.id - a.rk ) AS allCount
	FROM (
		SELECT id, visit_date, people, rownum AS rk
		FROM stadium
		WHERE people >= 100
	) a
) a
WHERE a.allCount >= 3
ORDER BY a.id
2.TopN问题
  • 2.TopN问题

编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。
样表:

Id Salary
1 100
2 200
3 300
CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
    select distinct(salary) into result
    from (select salary,dense_rank() over(order by salary desc) rank_ from employee) tmp
    where tmp.rank_=N;
    RETURN result;
END;
3.分组TopN问题
  • 3Employee 表包含所有员工信息,每个员工有其对应的工号 Id,姓名 Name,工资 Salary 和部门编号 DepartmentId 。

样表:

Id Name Salary DepartmentId
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
5 Janet 69000 1
6 Randy 85000 1
7 Will 70000 1

Department 表包含公司所有部门的信息。

Id Name
1 IT
2 Sales
编写一个 SQL 查询,找出每个部门获得前三高工资的所有员工
--查询语句
/* Write your PL/SQL query statement below */

select de.name department,tmp1.name employee,salary
from (
    select e.* ,
    dense_rank() over(partition by departmentid order by salary desc) rank_
    from employee e ) tmp1,
    department de
where tmp1.departmentid=de.id and tmp1.rank_<=3;
4.累加,累减,累乘,累商问题

oracle 下实现累计求和_数据库_iechenyb专栏-CSDN博客

累加方法实现

select t.*,
	sum(col) over(partition by target_col order by target_col) result_rename
from table t

当只需要每一组最终的统计结果时,可以加distinct即可.
完整代码:

select distinct t.*,
	sum(col) over(partition by target_col order by target_col) result_rename
from table t

累减的实现:
减法通过加法实现.如a-b=a+(-b),所以累减问题,只需要将偶数列变为负值,然后再累加即可.

select distinct t.*,
	sum(decode(mod(行号,2),1,col,-col)) over(partition by target_col order by target_col) result_rename
from table(使用子查询,并增加行号) t

累乘,累商问题与累加,累减类似,oracle 下实现累计求和_数据库_iechenyb专栏-CSDN博客 该博客中按照先取对数(ln),然后取指数exp,但是这种情况下容易出现对数真数为0的情况,所以将顺序调换以下可能会更好


上述应用案例及表数据均来自leetcode

参考:
oracle排序使用,很多中函数,不同的效果 - NewLife365 - 博客园
oracle中的排名函数用法 - 随风而行 - 博客园
oracle 下实现累计求和_数据库_iechenyb专栏-CSDN博客

posted @ 2020-04-03 17:04  LgRun  阅读(655)  评论(0编辑  收藏  举报