mysql窗口函数

最新推荐文章于 2024-07-25 23:12:50 发布

kiuan_795

最新推荐文章于 2024-07-25 23:12:50 发布

阅读量124

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/kk_12345z/article/details/120022743

版权

sql语句:

CREATE TABLE `student`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(0) NULL DEFAULT NULL,
  `sex` enum('男','女') CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` int(0) NULL DEFAULT NULL,
  `class` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `sex`(`sex`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `student` VALUES (1, '大源坏', 28, '男', 45, '1班');
INSERT INTO `student` VALUES (2, '路源好', 20, '女', 85, '2班');
INSERT INTO `student` VALUES (3, '子怡', 18, '男', 70, '1班');
INSERT INTO `student` VALUES (4, '袁建鑫', NULL, '男', 82, '3班');
INSERT INTO `student` VALUES (5, '匡鑫', 21, '男', 68, '1班');
INSERT INTO `student` VALUES (6, 'aaaaa', 23, '男', 58, '2班');
INSERT INTO `student` VALUES (7, 'bbb', 34, '女', 74, '3班');
INSERT INTO `student` VALUES (8, 'aacc', 32, '男', 79, '1班');
INSERT INTO `student` VALUES (9, 'cc', 31, '男', 70, '2班');


CREATE TABLE `order1`  (
  `id` int(0) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_num` int(0) UNSIGNED NOT NULL,
  `amount` float UNSIGNED NULL DEFAULT NULL,
  `dt` date NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `order1` VALUES (1, 1, 100, '2021-05-31');
INSERT INTO `order1` VALUES (2, 1, 210, '2021-05-13');
INSERT INTO `order1` VALUES (3, 2, 231, '2021-06-11');
INSERT INTO `order1` VALUES (4, 2, 543, '2021-07-11');
INSERT INTO `order1` VALUES (5, 2, 325, '2021-07-30');
INSERT INTO `order1` VALUES (6, 3, 212, '2021-07-21');
INSERT INTO `order1` VALUES (7, 3, 323, '2021-06-11');
INSERT INTO `order1` VALUES (8, 3, 333, '2021-08-02');
INSERT INTO `order1` VALUES (9, 3, 110, '2021-07-28');
INSERT INTO `order1` VALUES (10, 3, 90, '2021-08-03');

一.窗口函数有什么用？

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

二.什么是窗口函数？

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。

窗口函数的基本语法如下：

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

<窗口函数>的位置，可以放以下两种函数：

1）专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。

2）聚合函数，如sum,avg, count, max, min等

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

主要分5类：

用法：窗口函数 over(partition by 分组字段 order by 排序字段)

（1）序号函数：

rank(): 返回数据集中每个值的排名。排名值是根据当前行之前的行数加1，不包含当前行。因此，排序的关联值可能产生顺序上的空隙。这个排名会对每个窗口分区进行计算；

dense_rank() : 返回一组数值中每个数值的排名。这个函数与 rank() ，相似，除了关联值不会产生顺序上的空隙；

row_number() : 为每行数据返回一个唯一的顺序的行号，从1开始，根据行在窗口分区内的顺序；

需求:按照班级将学生分组,然后将每个班级的学生按照成绩排名.

例如下图，是班级表中的内容

如果我们想在每个班级内按成绩排名，得到下面的结果。

实现:

SELECT
    *,
    RANK() over ( PARTITION BY class ORDER BY score DESC ) AS ranking 
FROM
    student;

我们来解释下这个sql语句里的select子句。rank是排序的函数。要求是“每个班级内按成绩排名”，这句话可以分为两部分：

(1）每个班级内：按班级分组partition by用来对表分组。在这个例子中，所以我们指定了按“班级”分组（partition by 班级）(2）按成绩排名order by子句的功能是对分组后的结果进行排序，默认是按照升序（asc）排列。在本例中（order by 成绩 desc）是按成绩这一列排序，加了desc关键词表示降序排列。

窗口函数具备了我们之前学过的group by子句分组的功能和order by子句排序的功能。那么，为什么还要用窗口函数呢？

这是因为，group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。例如下面统计每个班级的人数。

现在我们说回来，为什么叫“窗口”函数呢？这是因为partition by分组后的结果称为“窗口”，这里的窗口不是我们家里的门窗，而是表示“范围”的意思。

简单来说，窗口函数有以下功能：

同时具有分组和排序的功能.

不减少原表的行数.

专用窗口函数rank, dense_rank, row_number有什么区别呢？

它们的区别我举个例子，你们一下就能看懂：

SELECT
    *,
    RANK() over ( PARTITION BY class ORDER BY score DESC ) AS ranking, 
    DENSE_RANK() over ( PARTITION BY class ORDER BY score DESC ) AS ranking,
    ROW_NUMBER() over ( PARTITION BY class ORDER BY score DESC ) AS ranking 
FROM
    student;

从上面的结果可以看出：

rank函数：这个例子中是5位，5位，7位，8位，9位也就是如果有并列名次的行，会占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，4。

dense_rank函数：这个例子中是5位，5位，6位，7位，8位也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是现在前3名是并列的名次，结果是：1，1，1，2。

row_number函数：这个例子中是5位，6位，7位，8位，9位也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

最后，需要强调的一点是：在上述的这三个专用窗口函数中，函数后面的括号不需要任何参数，保持()空着就可以。

（2）分布函数：

percent_rank() : 返回数据集中每个数据的排名百分比。结果是根据 (r - 1) / (n - 1) 其中 r 是由 rank() 计算的当前行排

名， n 是当前窗口分区内总的行数；(不常用)

cume_dist()：表示当前行及小于当前行在窗口分区总行数中的占比。

需求:按照订单数统计在总订单数中的占比.

SELECT
    *,
    cume_dist() over ( PARTITION BY user_num ORDER BY amount DESC ) AS cume 
FROM
    order1;

这个数值是怎么计算到的呢？答案是:(rank-1) / (rows-1)

rank就是我们前面使用rank()函数计算出来的排名，rows就是行数

（3）前后函数：

lag() 向上偏移 lead() 向下偏移

应用场景:

1.取时间间隔为N天的记录
2.计算本次记录与上次记录的差值
3.取某一字段的前N行数据或后N行数据

三个参数: 第一个参数是表达式或者字段, 第二个参数是偏移量第三个参数就是控制赋值.

需求:查询上一个订单距离当前订单的时间间隔。

select *,datediff(dt,last_date) as diff from(
    select user_num,dt,lag(dt,1,dt) over w as last_date
    from order1
    WINDOW w as (order by dt)   给窗口函数起别名
)t;  t起别名

(4) 头尾函数：first_val()/ last_val()

按时间排序记录每个用户的最早订单和最晚订单，并不是最小金额和最大金额订单。

需求:查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。

select * from (select *,
    FIRST_VALUE(amount) over w as first_amount,
    LAST_VALUE(amount) over w as last_amount
    from order1
    WINDOW w as (PARTITION BY user_num order by dt)
)t;

(5) 其他函数：nth_value() / ntile()

nth_value(expr,n)
用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。

需求:每个用户订单中显示本用户金额排名第二和第三的订单金额。
select * from (select *,
nth_value(amount,2)  over w as second_amount,
nth_value(amount,3)  over w as third_amount
from order1
WINDOW w as (partition by user_num order by amount desc)
) t;

ntile
1.将数据按照某一字段进行分组
2.分组排名,取前N%,注意区分和Top N不同

需求:将每个用户的订单按照订单金额分成3组。
select * from (select 
ntile(3) over w as nf,
id,amount,dt,user_num
from order1
WINDOW w as (partition by user_num order by amount desc)
) t;