当前位置：首页 > 数据库 > Mysql > 详细页面

mysql 窗口函数 ROW_NUMBER、NTILE详解

时间：2023-10-29来源：系统城装机大师作者：佚名

ROW_NUMBER()：对数据进行编号比如有一张保存app发布版本的表

1
2
3
4
5
6 CREATE TABLE `app_release` (
`Id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',
`Platform` varchar(10) COLLATE utf8mb4_bin NOT NULL COMMENT '平台,ios,android',
`Version` varchar(10) COLLATE utf8mb4_bin NOT NULL COMMENT '版本号',
PRIMARY KEY (`Id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='app发版信息';

现在需要查询ios和android最新的版本。用platform字段先分组，再按照版本排序

1
2
3 select * from
(select *, ROW_NUMBER() OVER (partition by PLATFORM order by Version desc) as num from app_release) tmp
where num = 1

不使用num=1条件，结果是

id   platform   version   num
8   android       6       1
7   android       5       2
6   android       4       3
5   android       3       4
4   android       2       5
3   android       1       6
12   ios           5.5.6   1
13   ios           5.5.6   2
1   ios           5.5.4   3
9   ios           5.5.4   4
10   ios           5.5.4   5
11   ios           5.5.4   6

那么num=1的记录就是各个版本的最新记录了。

NTILE() 函数按照规则进行分片比如要查询所有表记录中的前70%的数据

1
2
3
4
5 CREATE TABLE `test` (
`id` int NOT NULL AUTO_INCREMENT,
`value` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

执行sql

1
2
3
4
5
6 SELECT
*
FROM
( SELECT *, NTILE( 10 ) OVER ( ORDER BY `value` DESC ) AS _ntile FROM test ) a
WHERE
_ntile <= 7