数据库面试:开窗函数（敲详细解读，含大厂面试题sql经典题)

一只傻小白，

已于 2024-04-30 12:56:56 修改

阅读量1k

点赞数 44

文章标签：数据库 sql

于 2024-04-29 20:00:48 首次发布

本文链接：https://blog.csdn.net/weixin_54673155/article/details/138322689

版权

开窗函数的RANK语法

建表语句学生成绩表

create table tb_score
(
    SNO     INT,
    SCLASS  INT,
    CHINESE INT,
    ENGLISH INT,
    ARITH   INT
);

INSERT INTO TB_SCORE
VALUES (1, 1, 83, 57, 73);
INSERT INTO TB_SCORE
VALUES (2, 1, 58, 76, 90);
INSERT INTO TB_SCORE
VALUES (3, 3, 89, 84, 96);
INSERT INTO TB_SCORE
VALUES (4, 2, 79, 86, 82);
INSERT INTO TB_SCORE
VALUES (5, 1, 58, 81, 93);
INSERT INTO TB_SCORE
VALUES (6, 1, 56, 90, 59);
INSERT INTO TB_SCORE
VALUES (7, 3, 77, 66, 77);
INSERT INTO TB_SCORE
VALUES (8, 2, 63, 56, 55);
INSERT INTO TB_SCORE
VALUES (9, 1, 91, 78, 91);
INSERT INTO TB_SCORE
VALUES (10, 3, 95, 81, 50);

# todo：开窗函数的排序语法
# todo  1.row_number() over(partition by xxx order by yyy)
# todo  1.1 partition by xxx 根据哪一列进行分堆,这个是可选参数
# todo  1.2 order by yyy 根据哪一列进行排序,这个是可选参数
# todo 不考虑并列  row_number()
# todo 考虑并列（按人头数)  rank()
# todo 考虑并列(按名次)  dense_rank()

# todo 目标1:先根据班级分堆 再根据语文成绩排序（倒序)
select sno,
       SCLASS,
       CHINESE,
       row_number() over (partition by SCLASS order by CHINESE desc ) as rn1_chinese,
       rank() over (partition by SCLASS order by CHINESE desc )       as rn2_chinese,
       dense_rank() over (partition by SCLASS order by CHINESE desc ) as rn3_chinese
from tb_score;

题目一：运行结果

# todo 目标2:先根据班级分堆 再根据语文成绩排序（倒序) 数学成绩 英语成绩 排序
select sno,
       sclass,
       chinese,
       english,
       arith,
       rank() over (partition by SCLASS order by CHINESE desc) rn1_chinese,
       rank() over (partition by SCLASS order by ENGLISH desc) rn2_english,
       rank() over (partition by SCLASS order by ARITH desc)   rn3_arith
from tb_score;

题目二：运行结果

# todo 目标3:想要每个班级语文成绩的前两名
# todo 知识点 sql执行顺序： from 1 where 2 group by 3 having 4 select 5 开窗函数 6
# todo 方式一 子查询
select*
from (select sno,
             sclass,
             chinese,
             english,
             arith,
             rank() over (partition by SCLASS order by CHINESE desc) rn1_chinese
      from tb_score) t1
where t1.rn1_chinese <= 2
order by SCLASS, rn1_chinese;

#todo 方式二
with t1 as (select sno,
                   sclass,
                   chinese,
                   english,
                   arith,
                   rank() over (partition by SCLASS order by CHINESE desc) rn1_chinese
            from tb_score)

select *
from t1
where rn1_chinese <= 2
order by SCLASS, CHINESE desc;

create table tb_user
(
    name  varchar(32),
    month varchar(32),
    amt   int
);

题目三：运行结果

开窗函数的SUM语法

建表语句用户存款表

create table tb_user
(
    name  varchar(32),
    month varchar(32),
    amt   int
);

insert into tb_user
values ('张三', '01', 100);
insert into tb_user
values ('李四', '02', 120);
insert into tb_user
values ('王五', '03', 150);
insert into tb_user
values ('赵六', '04', 500);
insert into tb_user
values ('张三', '05', 400);
insert into tb_user
values ('李四', '06', 350);
insert into tb_user
values ('王五', '07', 180);
insert into tb_user
values ('赵六', '08', 400);

#todo 计算每个人的存款金额 排名 与占比
#todo round(xxx,2) 含义:让数字保留两位小数
#todo concat(xxx,yyy) 含义:让xxx和yyy 拼接到一起
select s.*,
       rank() over (order by s.personSum),
       concat(round((s.personSum / sum(s.personSum) over ()) * 100, 2), '%') as radio
from (select distinct name, sum(amt) over (partition by name ) as personSum from tb_user) s;

题目四：运行结果

建表语句

create table emp
(
    empno    varchar(32),
    ename    varchar(32),
    hiredate varchar(32),
    sal      int,
    dpetno   int
);

INSERT INTO emp VALUES
('7934', '刘备', '1982-01-23', 1300, 10),
('7844', '关羽', '1981-09-08', 1500, 10),
('7782', '张飞', '1981-06-09', 2450, 10),
('7839', '曹操', '1981-11-17', 5000, 10),
('7521', '张三', '1981-02-22', 1250, 20),
('7566', '李四', '1981-04-02', 2975, 20),
('7876', '王五', '1987-07-13', 1100, 20),
('7369', '赵六', '1980-12-17', 800, 20),
('7902', '孙悟空', '1981-12-03', 3000, 30),
('7499', '猪八戒', '1981-02-20', 1600, 30),
('7654', '沙和尚', '1981-09-28', 1250, 30),
('7900', '唐僧', '1981-12-03', 950, 30),
('7788', '杨过', '1987-07-13', 3000, 30),
('7698', '小龙女', '1981-05-01', 2850, 30);

with t1 as (select empno,
                   ename,
                   hiredate,
                   sal,
                   dpetno,
                   row_number() over (partition by dpetno order by sal desc ) as rn,
                   sum(sal) over (partition by dpetno)                        as sum_sal
            from emp)

select  ename,sal, dpetno, rn, round(sal / sum_sal, 2) as rate
from t1
where rn <= 3

题目五：运行结果

-- 问题: count(*) 和 count(1) 区别 占用空间区别 null不会被聚合函数统计
-- 如果没有partition by xxx 只有 order by 就会按照排序 进行累计求和
-- order by 默认范围：重头到尾 ，范围是可以改的
WITH
  t1 AS (
    SELECT SUBSTR(hiredate, 1, 4) AS year
    FROM emp
  ),
  t2 AS (
    SELECT t1.year, COUNT(*) AS cnt -- 这里应该是 COUNT(*) 而不是 COUNT(1)，因为我们在分组
    FROM t1
    GROUP BY t1.year
  )
SELECT
  t2.year,
  t2.cnt,
  SUM(t2.cnt) OVER (ORDER BY t2.year) AS cnt2
FROM t2;