Oracle 窗口函数 02 (排名问题)

目录

一、什么是窗口函数

1.语法里每部分表示什么

2.窗口函数可以解决这几类经典问题

二、排名问题

1.学生成绩排名

2.去除最大值、最小值后求平均值

知识点：

---

一、什么是窗口函数

窗口函数也叫作OLAP（Online Analytical Processing，联机分析处理）函数，可以对数据库中的数据进行复杂分析。

窗口函数的通用语法如下：

<窗口函数> over(partition by<用于分组的列名>
order by <用于排序的列名>)

1.语法里每部分表示什么

（1）<窗口函数>的位置可以放两种函数：一种是专用窗口函数，比如用于排名的函数，比如rank()、dense_rank()、row_number()；另一种是汇总函数，比如sum()、avg()、count()、max()、min()。

（2）<窗口函数>后面的over关键字括号里的内容有两部分：一个是partition by，表示按某列分组；另一个是order by，表示对分组后的结果按某列排序。

（3）因为窗口函数通常是对where或者group by子句处理后的结果进行操作的，所以窗口函数原则上只能写在select子句中。

2.窗口函数可以解决这几类经典问题

排名问题、Top N问题、前百分之N问题、累计问题、每组内比较问题、连续问题

二、排名问题

1.学生成绩排名

1.1抛真题：现有“成绩表”，需要我们取得每名学生不同课程的成绩排名.

已知条件 分数表				结果
student_name	course_name	score		student_name	course_name	score	rn
小明	数学	85		小明	物理	92	1
小明	英语	78		小明	数学	85	2
小明	物理	92		小明	英语	78	3
小红	数学	90		小李	数学	90	1
小红	英语	80		小李	英语	85	2
小李	数学	90		小李	物理	85	3
小李	数学	60		小李	数学	60	4
小李	英语	85		小红	数学	90	1
小李	物理	85		小红	英语	80	2

建表+数据导入

-- 删除表格
DROP TABLE t_score;

-- 创建表格
CREATE TABLE t_score (
    student_name VARCHAR2(50),
    course_name VARCHAR2(50),
    score NUMBER
);

-- 插入数据
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '数学', 85);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '英语', 78);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '物理', 92);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小红', '数学', 90);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小红', '英语', 80);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '数学', 90);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '数学', 60);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '英语', 85);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '物理', 85);


-- 查询数据
SELECT * FROM t_score;

实现代码

-- 需求： 现有“成绩表”，需要我们取得每名学生不同课程的成绩排名.
-- 每名 -- 分组 --> 1 group by 2 partition by
-- 排名 -- 1 row_number rank dense_rank
select
   t.*,
   row_number() over(partition by student_name order by score desc) as 排名
from t_score t
;

2.去除最大值、最小值后求平均值

2.1抛真题：“薪水表”中记录了雇员编号、部门编号和薪水。要求查询出每个部门去除最高、最低薪水后的平均薪水。

已知条件 薪资表				结果
employee_id	department_id	salary		department_id	avg_salary
1	1	50000		1	50000
2	1	52000		2	60000
3	1	48000
4	1	51000
5	1	49000
6	2	60000
7	2	58000
8	2	62000
9	2	59000
10	2	61000

建表+数据导入

-- 创建表格
CREATE TABLE t_salary_table (
    employee_id NUMBER,
    department_id NUMBER,
    salary NUMBER
);

-- 插入数据
-- 插入数据
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (1, 1, 50000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (2, 1, 52000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (3, 1, 48000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (4, 1, 51000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (5, 1, 49000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (6, 2, 60000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (7, 2, 58000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (8, 2, 62000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (9, 2, 59000);
INSERT INTO t_salary_table (employee_id, department_id, salary) VALUES (10, 2, 61000);


-- 查询数据
SELECT * FROM t_salary_table;

-- 查询每个部门去除最高、最低薪水后的平均薪水

实现代码

-- 查询每个部门去除最高、最低薪水后的平均薪水
select
   t.*,
   row_number() over(partition by department_id order by salary asc) rn1,
   row_number() over(partition by department_id order by salary desc) rn2
from t_salary_table t
;

with t1 as (
  select
     t.*,
     row_number() over(partition by department_id order by salary asc) rn1,
     row_number() over(partition by department_id order by salary desc) rn2
  from t_salary_table t
)
select 
  department_id,
  avg(salary) avg_salary
from t1
where rn1>1 and rn2>1
group by department_id
;

知识点：

关注sum字段和sum1,sum2的区别（order by 对每个记录排序后，由sum并累加到当前记录，其他计算函数也一样）

FROM (
    SELECT *,
        sum(frequency) over(order by num) sum1,
        sum(frequency) over(order by num desc) sum2,
        sum(frequency) over() sum
FROM Numbers