SQL 开窗函数

---

⭐️前言

在开窗函数出现之前存在着很多用 SQL 语句很难解决的问题，很多都要通过复杂的相关子查询或者存储过程来完成。为了解决这些问题，在 2003 年 ISO SQL 标准加入了开窗函数，开窗函数的使用使得这些经典的难题可以被轻松的解决。

---

开窗函数简介:与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。

• Mysql 8.0以上【🔎 CentOS 7安装MySQL8.0】
• Clikchosue 不支持

---

🏆开窗函数–Mysql

📝 建表语句

CREATE TABLE `school_score`
(
    `id`     int(11) NOT NULL AUTO_INCREMENT,
    `name`   char(1)  DEFAULT NULL,
    `course` char(10) DEFAULT NULL,
    `score`  int(2)   DEFAULT NULL,
    PRIMARY KEY (`id`)
);

INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (1, 'A', 'Chinese', 80);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (10, 'D', 'Chinese', 80);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (2, 'B', 'Chinese', 90);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (3, 'C', 'Chinese', 70);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (4, 'A', 'Math', 70);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (5, 'B', 'Math', 100);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (6, 'C', 'Math', 80);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (7, 'A', 'English', 90);
INSERT INTO school_score(`id`, `name`, `course`, `score`)
VALUES (8, 'B', 'English', 85);
INSERT INTO school_score (`id`, `name`, `course`, `score`)
VALUES (9, 'C', 'English', 99);

✌ ROW_NUMBER()

🍖定义

对排序之后的数据每一行数据新增一个序号

🧀 SQL

中文成绩排名

-- 看每个课程的排名,成绩相同时排名不同
SELECT
    `name`,
    `course`,
    `score`,
    row_number ( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    school_score where course='Chinese';

🍟 说明

对排序之后的数据，每一条数据加一个序号

✌ RANK（）

🍖定义

对排序之后的数据排名，数据相同排名相同，整体名次不连续

🧀 SQL

-- 看每个课程的排名，成绩相同时排名相同，排名不连续
SELECT
    `name`,
    `course`,
    `score`,
    RANK( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    school_score where course='Chinese';

🍟 说明

注意点：

• 数据相同，名词相同
• 整体排名不连续。区别于DENSE_RANK（）

✌ DENSE_RANK（）

🍖定义

对排序之后的数据排名，数据相同排名相同，整体名次连续

🧀 SQL

-- 开窗函数和排名类函数结合，看每个课程的排名，成绩相同时排名相同，排名连续
SELECT
    `name`,
    `course`,
    `score`,
    DENSE_RANK( ) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    school_score where course='Chinese';

🍟 说明

注意点：

• 数据相同，名词相同
• 整体排名不连续。区别于RANK（）

✌ NTILE（n）

🍖定义

对排序之后的数据排名,平均分为n 个切片。如果切片不均匀，默认增加第一个切片的分布。

🧀 SQL

SELECT
    `name`,
    `course`,
    `score`,
    NTILE(3) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    school_score where course='Chinese';

🍟 说明

-- 取排名前1/3的数据
select * from (
              SELECT
    `name`,
    `course`,
    `score`,
    NTILE(3) over ( PARTITION BY `course` ORDER BY score DESC ) AS score_rank
FROM
    school_score where course='Chinese'
                  ) as ff where ff.score_rank=1;

---

☀️总结

• 单纯排序不考虑相同数据情况下使用：ROW_NUMBER()
• 排序考虑相同数据，整体排序不连续：RANK（）
• 排序考虑相同数据，整体排序连续：DENSE_RANK（）
• 按照排序，取整体的部分数据【1/3】: NTILE(n)