SQL存储过程——求每小时的99分位数
来源:互联网 发布:咸鱼质量问题淘宝介入 编辑:程序博客网 时间:2024/05/22 03:11
第二篇博客,想分享一下在实习工作中一个SQL的存储过程。在写这个存储过程的时候,查阅了各大百度,贴吧,博客上的资料,但是均是没有找到99分位数的写法。可能是这个的应用场景较少吧。但是也希望会对一些童鞋有帮助。
99分位数的逻辑想必大家都清楚,总共三步:
第一,
针对这一小时内的所有数据,按照从小到大进行排序
第二,
计算99分位所在的位置,0.99×(数据的总数)
(向上取整还是向下取整看自身的需求)
第三,
最后取该位置所在的数据。
下面开始进行正文了,写存储过程。(99分位数的情况逻辑简单,若是用PYTHON写是很简单的,写个函数就能解决,但是在SQL中还是比较复杂的,普通的SQL语句还是不容易实现)
DELIMITER $CREATE PROCEDURE cal_time_minute()BEGIN DECLARE _index_cal BIGINT(11); --索引位置 DECLARE _group_count BIGINT(11); --每组数量 DECLARE _create_time VARCHAR(20); --时间 --遍历数据结束标志 DECLARE done INT DEAFAULT FALSE; --游标 DECLARE curor_99 CURSOR FOR SELECT DATE_FORMAT(create_time,'%Y-%M-%D %H'),COUNT(*),COUNT(*)-(CEIL(0.99*COUNT(*))) FROM decision.a GROUP BY DATE_FORMAT(create_time,'%Y-%M-%D %H'); --将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; --打开游标 OPEN curor_99; --遍历 read_loop:LOOP --取值 多个字段 FETCH NEXT FROM curor_99 INTO _create_time,_group_count,_index_cal; IF done THEN LEAVE read_loop; END IF; CREATE TABLE decision.b AS (SELECT DATE_FORMAT(create_time,'%Y-%M-%D %H'),total_cal_time AS 99_cal_time FROM (SELECT * FROM decision.a ORDER BY total_cal_time DESC) c WHERE DATE_FORMAT(create_time,'%Y-%M-%D %H')=_create_time ORDER BY total_cal_time DESC LIMIT _index_cal,1); END LOOP; CLOSE curor_99;END $
主要注意两个点:
一是为了优化存储过程,减少时效,根据99分位数的逻辑,按降序方式进行排序,从后往前循环求99分位数
二是LIMIT的使用,不是说LIMIT的使用方式很难,而是limit怎么跟游标进行结合使用
这个存储过程和我原本写的有些不一样,在这里我把表进行了替换,另外源代码中不仅仅是求99分位数,还有平均值,最大值和最小值,不过平均值这些都很好求,再INSERT一下就好了,所以就不细写啦~~
这些都是本人自己的观点,欢迎有质疑的童鞋们留言探讨~~~
阅读全文