不同的SQL平台,如何取前百分之N的记录?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 最近帮业务部门梳理业务报表,其中有个需求是就算某指标等待时间最长的前百分之十,其实就是对等待时长进行倒序排序后,取结果集的前百分之十。这个需求在SQL Server和Oracle上都很容易实现,甚至是在MySQL 8.0也很容易实现,只是恰好我们业务数据库是MySQL 5.7先给大家介绍下不同数据库平台的实现方法。

SQL Server实现方法

SQL Server上有个TOP Percent的方法可以直接取结果的前(或后)百分之N例如有如下一张City表

100.jpg

我们取前10%的数据记录可以这样写:

SELECT

TOP 10PERCENT  

*

FROM City

ORDERBYIDDESC


结果如下:


101.jpg


Oracle实现方法

Oracle有个ROWNUM伪列可以用来帮助我们计算前百分之N。ROWNUM伪列的特点:

  • ROWNUM是按照记录插入时的顺序排序的
  • ROWNUM并不实际存在,是对筛选后的结果集的一个排序,如果不存在结果集就不会有ROWNUM
  • ROWNUM不能用基表名作为前缀
  • 在使用ROWNUM进行查询时,请注意:

   1)若使用大于号(>),则只能使用(>0),其他均不可以    2)若使用小于号(<),同一般情况    3)若使用等于号(=),则只能使用等于1(=1)我们可以先计算出整个表的记录行数量


SELECTCOUNT(*) CNT FROM City


然后根据count聚合查询总条数乘以百分比,来确定要查询的条数

SELECT0.1*COUNT(*) CNT FROM City


最后取出伪列小于共有数据的百分比的数据

SELECT * FROM CITY

WHEREIDIN

(

SELECTIDFROM

(

SELECTIDFROM CITY ORDERBYIDDESC

)

WHEREROWNUM < (SELECTCOUNT(*)*0.1FROM CITY)

)


注意:Oracle不支持子查询内ORDER BY,需要在外面再嵌套一层。



MySQL 8.0的实现方法

MySQL 8.0的实现方法主要是借助窗口函数ROW_NUMBER() OVER()。其实就是给排好序的集合添加一个自增长列,与Oracle的ROWNUM有点类似

SELECT * FROM

(

SELECT *,

ROW_NUMBER() OVER(ORDERBYIDDESC) rn

FROM  City

ORDERBYIDDESC

) a

WHERE a.rn<=(SELECT0.1*COUNT(*) FROM City)



MySQL 5.X的实现方法

我们知道MySQL 5.X是没有开窗函数ROW_NUMBER() OVER()的,那该如何实现呢?

这里我们需要借助变量来实现,其实思路还是创建一个自增长列,只是方法不同。

SELECT

 A.*,

 @row_num:=@row_num+1AS ROW_NUM

FROM

 City A , (SELECT @row_num:=0) B

ORDERBYIDDESC


这样我们就可以得到一张有自增长列的结果集了,接下来还是按照上面类似的方法,取前10%即可。


SELECT * FROM

(

SELECT

 A.*,

 @row_num:=@row_num+1AS ROW_NUM

FROM

 City A , (SELECT @row_num:=0) B

ORDERBYIDDESC

) C

WHERE C.ROW_NUM<=(@row_num*0.1)


其实MySQL 5.X也挺简单的,只是当时不怎么想用变量,想看看有没有其他办法,最后发现还是得用变量


以上就是不同平台的数据库求前百分之N的方法了,代码可以验证一下收藏起来留着下次直接套用。


总结

其中有涉及一些知识点,需要小伙伴们自己去进一步了解:

  • SQL Server的TOP PERCENT
  • Oracle的ROWNUM,子查询排序
  • ROW_NUMBER() OVER()
  • MySQL的变量




相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
SQL 关系型数据库 MySQL
Linux系统部署Yearning SQL审核平台结合内网穿透实现公网访问
Linux系统部署Yearning SQL审核平台结合内网穿透实现公网访问
189 0
|
SQL 分布式计算 NoSQL
【SQL 审核查询平台】Archery使用介绍
【SQL 审核查询平台】Archery使用介绍
838 0
【SQL 审核查询平台】Archery使用介绍
|
SQL 运维 程序员
一个功能丰富的SQL审核查询平台
一个功能丰富的SQL审核查询平台
189 2
|
SQL HIVE UED
【Hive SQL 每日一题】分析电商平台的用户行为和订单数据
作为一名数据分析师,你需要分析电商平台的用户行为和订单数据。你有三张表:`users`(用户信息),`orders`(订单信息)和`order_items`(订单商品信息)。任务包括计算用户总订单金额和数量,按月统计订单,找出最常购买的商品,找到平均每月最高订单金额和数量的用户,以及分析高消费用户群体的年龄和性别分布。通过SQL查询,你可以实现这些分析,例如使用`GROUP BY`、`JOIN`和窗口函数来排序和排名。
1077 2
|
SQL 关系型数据库 Java
实时计算 Flink版操作报错之在阿里云DataHub平台上执行SQL查询GitHub新增star仓库Top 3时不显示结果,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
SQL Java 关系型数据库
【Spring Boot+Thymeleaf+MyBatis+mysql】实现电子商务平台实战(附源码)持续更新~~ 包括sql语句、java、html代码
【Spring Boot+Thymeleaf+MyBatis+mysql】实现电子商务平台实战(附源码)持续更新~~ 包括sql语句、java、html代码
410 2
|
SQL 数据挖掘 数据处理
「SQL面试题库」 No_65 用户购买平台
「SQL面试题库」 No_65 用户购买平台
|
SQL 消息中间件 关系型数据库
从0到1构建一个Flink SQL流式计算平台
从0到1构建一个Flink SQL流式计算平台
129 0
|
SQL 存储 安全
CloudQuery一体化数据库SQL操作安全管控平台
CloudQuery一体化数据库SQL操作安全管控平台
557 0
|
SQL 算法 JavaScript
在线就能用的 SQL 练习平台(附SQL学习文档)
在线就能用的 SQL 练习平台(附SQL学习文档)
1234 0

热门文章

最新文章