admin

记一次 Mysql 查询统计问题
前言最近发现在公司之前写的日统计功能出现了各种问题,测试环境测试的时候发现没问题,一到线上数据量较大时,统计的数据...
扫描右侧二维码阅读全文
16
2018/08

记一次 Mysql 查询统计问题

前言

最近发现在公司之前写的日统计功能出现了各种问题,测试环境测试的时候发现没问题,一到线上数据量较大时,统计的数据要么不对要么就像之前所说的统计任务死锁卡住了。
这次的问题是统计结果出错的问题。

问题分析

下面是有问题的 sql 代码:

select vehicle_id,
            count(case when sort = 1 and alarm_type=1 then alarm_type_id end) alarm1_count,
            count(case when sort = 1 and alarm_type=2 then alarm_type_id end) alarm1_count2,
            sum(case when sort = 2 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm2_count,
            sum(case when sort = 2 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm2_count2,
            sum(case when sort = 3 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm3_count,
            sum(case when sort = 3 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm3_count2,
            count(case when sort = 4 and alarm_type=1 then alarm_type_id end) alarm4_count,
            count(case when sort = 4 and alarm_type=2 then alarm_type_id end) alarm4_count2,
            count(case when sort = 5 and alarm_type=1 then alarm_type_id end) alarm5_count,
            count(case when sort = 5 and alarm_type=2 then alarm_type_id end) alarm5_count2,
            count(case when sort = 6 and alarm_type=1 then alarm_type_id end) alarm6_count,
            count(case when sort = 6 and alarm_type=2 then alarm_type_id end) alarm6_count2,
            count(case when sort = 7 and alarm_type=1 then alarm_type_id end) alarm7_count,
            count(case when sort = 7 and alarm_type=2 then alarm_type_id end) alarm7_count2,
            count(case when sort = 8 and alarm_type=1 then alarm_type_id end) alarm8_count,
            count(case when sort = 8 and alarm_type=2 then alarm_type_id end) alarm8_count2,
            count(case when sort = 9 and alarm_type=1 then alarm_type_id end) alarm9_count,
            count(case when sort = 9 and alarm_type=2 then alarm_type_id end) alarm9_count2,
            count(case when sort = 11 and alarm_type=1 then alarm_type_id end) alarm11_count,
            count(case when sort = 11 and alarm_type=2 then alarm_type_id end) alarm11_count2,
            count(case when sort = 12 and alarm_type=1 then alarm_type_id end) alarm12_count,
            count(case when sort = 12 and alarm_type=2 then alarm_type_id end) alarm12_count2,
            count(case when sort = 13 and alarm_type=1 then alarm_type_id end) alarm13_count,
            count(case when sort = 13 and alarm_type=2 then alarm_type_id end) alarm13_count2,
            sum(case when sort = 14 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm14_count,
            sum(case when sort = 14 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm14_count2,
            count(case when sort = 15 and alarm_type=1 then alarm_type_id end) alarm15_count,
            count(case when sort = 15 and alarm_type=2 then alarm_type_id end) alarm15_count2,
            count(case when sort = 16 and alarm_type=1 then alarm_type_id end) alarm16_count,
            count(case when sort = 16 and alarm_type=2 then alarm_type_id end) alarm16_count2,
            #{yesterday} alarm_date
        from
            (select VALUE id,sort from nd_dict where dict_type = 'alarm_type_') temp,
            nd_attend_behavior_detail abd
        where
            vehicle_id = #{vehicleId}
            and partition_key = #{partitionKey}
            and alarm_type_id = temp.id
            and date_format(machine_time, '%Y-%m-%d') = #{yesterday}
        group by vehicle_id

简单解释一下上面日统计的 sql:根据车辆 ID 、日期统计该车辆某天各类告警的数量,nd_attend_behavior_detail是告警表,nd_dict是字典表(存储了告警类型),不要问我为什么表设计的这么傻逼,我只是按照上面要求办事 - -~!
告警表字典表由告警类型的 ID 关联(在nd_attend_behavior_detail 告警表中字段是alarm_type_idnd_dict字典表中的字段是value),alarm1_count-alarm16_count 以及 alarm1_count2-alarm16_count2 存储的是各类告警的一级告警与二级告警的总数,原本是想偷个懒,用字典表中的sort字段来进行区分不同的告警类型,所以关联了字典表来进行统计,没想到出现了 BUG,到底为什么会这样目前我也还找不到原因,上面的查询结果就是有些告警类型的统计是正确的,还有一部分告警类型的统计全部是 0 。
经过反复调试 sql,发现改成如下 sql 问题得以解决:

select vehicle_id,
            count(case when alarm_type_id=9600003 and alarm_type=1 then alarm_type_id end) alarm1_count,
            count(case when alarm_type_id=9600003 and alarm_type=2 then alarm_type_id end) alarm1_count2,
            sum(case when alarm_type_id=9600008 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm2_count,
            sum(case when alarm_type_id=9600008 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm2_count2,
            sum(case when alarm_type_id=9600007 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm3_count,
            sum(case when alarm_type_id=9600007 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm3_count2,
            count(case when alarm_type_id=9700001 and alarm_type=1 then alarm_type_id end) alarm4_count,
            count(case when alarm_type_id=9700001 and alarm_type=2 then alarm_type_id end) alarm4_count2,
            count(case when alarm_type_id=9700002 and alarm_type=1 then alarm_type_id end) alarm5_count,
            count(case when alarm_type_id=9700002 and alarm_type=2 then alarm_type_id end) alarm5_count2,
            count(case when alarm_type_id=9700006 and alarm_type=1 then alarm_type_id end) alarm6_count,
            count(case when alarm_type_id=9700006 and alarm_type=2 then alarm_type_id end) alarm6_count2,
            count(case when alarm_type_id=9001 and alarm_type=1 then alarm_type_id end) alarm7_count,
            count(case when alarm_type_id=9001 and alarm_type=2 then alarm_type_id end) alarm7_count2,
            count(case when alarm_type_id=9003 and alarm_type=1 then alarm_type_id end) alarm8_count,
            count(case when alarm_type_id=9003 and alarm_type=2 then alarm_type_id end) alarm8_count2,
            count(case when alarm_type_id=9002 and alarm_type=1 then alarm_type_id end) alarm9_count,
            count(case when alarm_type_id=9002 and alarm_type=2 then alarm_type_id end) alarm9_count2,
            count(case when alarm_type_id=9700007 and alarm_type=1 then alarm_type_id end) alarm11_count,
            count(case when alarm_type_id=9700007 and alarm_type=2 then alarm_type_id end) alarm11_count2,
            count(case when alarm_type_id=9600002 and alarm_type=1 then alarm_type_id end) alarm12_count,
            count(case when alarm_type_id=9600002 and alarm_type=2 then alarm_type_id end) alarm12_count2,
            sum(case when alarm_type_id=6000 and speed > 10 and alarm_type=1 then 1 else 0 end) alarm14_count,
            sum(case when alarm_type_id=6000 and speed > 10 and alarm_type=2 then 1 else 0 end) alarm14_count2,
            count(case when alarm_type_id=6001 and alarm_type=1 then alarm_type_id end) alarm15_count,
            count(case when alarm_type_id=6001 and alarm_type=2 then alarm_type_id end) alarm15_count2,
            count(case when alarm_type_id=9600009 and alarm_type=1 then alarm_type_id end) alarm16_count,
            count(case when alarm_type_id=9600009 and alarm_type=2 then alarm_type_id end) alarm16_count2,
            #{yesterday} alarm_date
        from
            nd_attend_behavior_detail abd
        where
            vehicle_id = #{vehicleId}
            and partition_key = #{partitionKey}
            and date_format(machine_time, '%Y-%m-%d') = #{yesterday}
        group by vehicle_id

上面的 sql 去掉了与字典表的关联,直接用告警表中的告警类型 ID 类进行区分不同告警进行统计。
到底为什么会出现前面那种有些数据统计到了有些统计不到是 0 的问题,最终还是搞不清楚原因,不过好在问题得以解决,由于公司用的 Mycat,有可能是 Mycat 的原因吧(被 Mycat 坑过太多次了,所以只能甩锅给它了)。

Last modification:August 16th, 2018 at 04:53 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment