文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL实现查询分位值的示例代码

2023-04-24 09:03

关注

背景

分位值的概念

分位值:分位值(Quartile)是一种用于描述数据分布的统计概念,在统计学和数据分析中经常被用到。一般情况下,分位值分成四个等份,分别为第一分位数(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 

cn > orderNum 表示这条数据在计算分位值统计范围内。如果要计算0.1分位值,那么需要统计每分钟的前10%的发券数据。按照面额排序,分钟分组后,每条记录都会标记这条记录排在第几。每分钟发券总量再乘以10%得到cnt,这个值就是计算这一分钟0.1分钟均值的所需要数据量,当cnt<orderNum时,代表超出10%的限制,不再纳入统计0.1分位均值的范围。这样就可以实现过滤出计算分位值所需数据,然后再通过avg函数就可以实现计算均值。最终结果也就是分位值的结果了。

以上就是我在业务上遇到计算分位值指标,然后通过MySQL实现计算的实现过程。

效果

由程序查询明细数据计算分位值 --> MySQL实现直接查询分位值

性能从>1min --> 15s以内;性能得到极大提升

参考

https://geek-docs.com/sql/sql-examples/sql-to-calculate-the-median.html

到此这篇关于MySQL实现查询分位值的示例代码的文章就介绍到这了,更多相关MySQL 查询分位值内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯