记一次简单的sql优化

最新推荐文章于 2024-08-14 13:50:42 发布

meavoChen

最新推荐文章于 2024-08-14 13:50:42 发布

阅读量105

点赞数

分类专栏：基础

本文链接：https://blog.csdn.net/looo000ngname/article/details/113564570

版权

基础专栏收录该内容

13 篇文章 0 订阅

订阅专栏

需求：

统计报警点位所属设备的设备报警总数top100的设备的报警总数

需求分许：

需要先找出所有设备的报警次数，按照报警次数进行排序，取前100，然后再对前100的设备的报警次数进行统计

SQL思路

将报警次数TOP100的设备找出来，再对整个数据进行一个inner join，再叠加一个count。可以计算出

select count(1)
from (select substring_index(alarm_description," ",1) siad,id,alarm_description  from equipment_history_alarm_record where gc_id='AHAQWY' and substring(alarm_time,1,10)>='2020-01-01' and substring(alarm_time,1,10)<'2021-01-01') t1
inner join
(select substring_index(alarm_description," ",1) siad ,count(1) cnt from equipment_history_alarm_record  where gc_id='AHAQWY' and substring(alarm_time,1,10)>='2020-01-01' and substring(alarm_time,1,10)<'2021-01-01' group by siad order by cnt desc limit 100) t2
on t1.siad=t2.siad;

计算时间为1.733s
在这里插入图片描述

优化思路

将报警次数TOP100的设备找出来，将其作为主表，再对这个主表进行sum运算

select sum(t.cnt) from
  (select substring_index(alarm_description," ",1) siad ,count(1) cnt from equipment_history_alarm_record  where gc_id='AHAQWY' and substring(alarm_time,1,10)>='2020-01-01' and substring(alarm_time,1,10)<'2021-01-01' group by siad order by cnt desc limit 100) t;