背景
分位值的概念
在统计学和数据分析中,分位数(或四分位数)经常用于描述数据分布的统计特征。一般情况下,分位值分成四个等份,分别为第一分位数(Q1)、第二分位数(Q2)(也就是中位数)、第三分位数(Q3)以及极差(IQR)。其中,1/4的数据小于第一分位数,1/4的数据大于第三分位数,中间50%的数据处于第一分位数和第三分位数之间。 在统计学中,第一分位数是指将一组数据按照大小顺序排列后,处于整个数列中最靠前的25%位置的数;第二分位数是指一组数据按大小顺序排列后,处于中间位置的那个数;而第三分位数是指将一组数据按照大小顺序排列后,处于整个数列中最靠后的25%位置的数。中位数是第二分位数。 在数据分析中,分位值可以帮助我们了解数据分布情况以及通过分位值来判断数据是否偏向一侧或者分散程度等问题。当数据分配不均匀的时候,分位值可以更准确的表现数据的差异。
业务背景
商家发放的优惠券的面额分布区间是[1, 20],每张优惠券都会被标记其对应的面额。要准确控制券的成本,必须实时了解券的发放情况,以便进行比较准确的评估。对券的发放量、发券金额均值、以及发放金额分位值(了解不同区间发放金额均值)进行实时的监控,就可以比较清楚的了解券的发放情况。
目前,业务梳理出如下指标需要数据的同学提供,所有指标均以分钟为统计粒度:
发放量:发券总量
发券金额均值:发放总额/发放总量
发券金额0.1分位均值:每分钟发券金额按照面额大小排序,面额大的在前,面额小的在后,计算每分钟发券金额靠前占比10%的那部分券的均值[如,发券面额排序为:10,9,8,8,6,5,4,4,2,2,那么0.1分位均值就是10]
发券金额0.2分位均值:每分钟发券金额按照面额大小排序,面额大的在前,面额小的在后,计算每分钟发券金额靠前占比20%的那部分券的均值[如,发券面额排序为:10,9,8,8,6,5,4,4,2,2,那么0.2分位均值就是(10+9)/2=9.5]
发放量和发券金额均值这类指标都可以用MySQL实现,那么如何实现使用MySQL查询分位值呢?
思考
MySQL实现排序
row_number() over ( partition by a1.min order by metric_value desc) as orderNum
metric_value表示发券金额,通过以上函数即可实现按照发券金额排序,而且是每分钟的发券数据按照金额排序
MySQL实现topN
SELECT * FROM sales ORDER BY amount DESC LIMIT 10;
很明显,这种topN方式并不能实现按分钟排序,取前N%。为了知道N%的数量,我们需要先确定总量,因此我们需要先计算每分钟的总量。然后再乘以N%,就知道我们需要提取N%有多少数据了。
select hour,min, count(1) as cn from table where dt=20230423 and hour=11 and min>=0 and min<=30 group by hour,min
然后,我们再把统计结果乘以N%
select dt,a2.hour,a2.min as min,metric_value, round(cn*N%) as cn, orderNum from ( select dt,hour,a1.min as min, metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum from table a1 where dt=20230423 and hour=11 and min>=0 and min<=30 ) as a2 inner join ( select hour,min , count(1) as cn from table c where dt=20230423 and hour=11 and min>=0 and min<=30 group by hour,min ) a3 on a2.hour=a3.hour and a2.min=a3.min
这样就可以通过比较cn(计算分位值所需要的数据量)和orderNum(当前券按面额大小排序所在顺序)的大小来获取得到前N%的数据,然后对这部分数据做avg处理,就能得到分位值数据。
调整计算逻辑融合到一起就可以得出分位值的SQL如下:
select dt,hour,min, round(avg(metric_value)) as metric_value from ( select dt,a2.hour,a2.min as min,metric_value, round(cn*?) as cn, orderNum from ( select dt,hour,a1.min as min, metric_value, row_number() over ( partition by a1.min order by metric_value desc) as orderNum from table a1 where dt=20230423 and hour=11 and min>=0 and min<=30 ) as a2 inner join ( select hour,min, count(1) as cn from table a1 where dt=20230423 and hour=11 and min>=0 and min<=30 ) as a3 on a2.hour=a3.hour and a2.min=a3.min ) as q where cn>orderNum group by dt,hour,min order by dt,hour,min
This data is within the range of calculating percentile value statistics if cn > orderNum.。为了计算0.1分位值,需要收集每分钟发券数据的前10%。按照面额排序,分钟分组后,每条记录都会标记这条记录排在第几。每分钟发券总量再乘以10%得到cnt,这个值就是计算这一分钟0.1分钟均值的所需要数据量,当cntc5d6c57b0b28b8a38f86dc6774b8b542 MySQL实现直接查询分位值
性能从>1min --> 15s以内;性能得到极大提升
以上是MySQL怎么实现查询分位值的详细内容。更多信息请关注PHP中文网其他相关文章!

要优化MySQL慢查询,需使用slowquerylog和performance_schema:1.启用slowquerylog并设置阈值,记录慢查询;2.利用performance_schema分析查询执行细节,找出性能瓶颈并优化。

MySQL和SQL是开发者必备技能。1.MySQL是开源的关系型数据库管理系统,SQL是用于管理和操作数据库的标准语言。2.MySQL通过高效的数据存储和检索功能支持多种存储引擎,SQL通过简单语句完成复杂数据操作。3.使用示例包括基本查询和高级查询,如按条件过滤和排序。4.常见错误包括语法错误和性能问题,可通过检查SQL语句和使用EXPLAIN命令优化。5.性能优化技巧包括使用索引、避免全表扫描、优化JOIN操作和提升代码可读性。

MySQL异步主从复制通过binlog实现数据同步,提升读性能和高可用性。1)主服务器记录变更到binlog;2)从服务器通过I/O线程读取binlog;3)从服务器的SQL线程应用binlog同步数据。

MySQL是一个开源的关系型数据库管理系统。1)创建数据库和表:使用CREATEDATABASE和CREATETABLE命令。2)基本操作:INSERT、UPDATE、DELETE和SELECT。3)高级操作:JOIN、子查询和事务处理。4)调试技巧:检查语法、数据类型和权限。5)优化建议:使用索引、避免SELECT*和使用事务。

MySQL的安装和基本操作包括:1.下载并安装MySQL,设置根用户密码;2.使用SQL命令创建数据库和表,如CREATEDATABASE和CREATETABLE;3.执行CRUD操作,使用INSERT,SELECT,UPDATE,DELETE命令;4.创建索引和存储过程以优化性能和实现复杂逻辑。通过这些步骤,你可以从零开始构建和管理MySQL数据库。

InnoDBBufferPool通过将数据和索引页加载到内存中来提升MySQL数据库的性能。1)数据页加载到BufferPool中,减少磁盘I/O。2)脏页被标记并定期刷新到磁盘。3)LRU算法管理数据页淘汰。4)预读机制提前加载可能需要的数据页。

MySQL适合初学者使用,因为它安装简单、功能强大且易于管理数据。1.安装和配置简单,适用于多种操作系统。2.支持基本操作如创建数据库和表、插入、查询、更新和删除数据。3.提供高级功能如JOIN操作和子查询。4.可以通过索引、查询优化和分表分区来提升性能。5.支持备份、恢复和安全措施,确保数据的安全和一致性。

全表扫描在MySQL中可能比使用索引更快,具体情况包括:1)数据量较小时;2)查询返回大量数据时;3)索引列不具备高选择性时;4)复杂查询时。通过分析查询计划、优化索引、避免过度索引和定期维护表,可以在实际应用中做出最优选择。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

Atom编辑器mac版下载
最流行的的开源编辑器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。

禅工作室 13.0.1
功能强大的PHP集成开发环境

SecLists
SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3汉化版
中文版,非常好用