统计时间分布脚本（mysql）代码,MYSQL代码库,德仔网

代码语言
代码分类
【MYSQL】统计时间分布脚本（mysql）
作者:雅竹 / 发布于2016/12/19/ 894
-- 查询超时记录  
select * from visit_record t where t.accesstime>to_date('2013-09-1','yyyy-MM-dd') and t.username is not null and t.result='成功' and t.totaltimecost>5000 and t.clientip <> '192.168.112.53' and t.clientip <> '192.168.112.200' and t.clientip <> '192.168.112.245' order by t.totaltimecost desc ;  
  
  
-- 导出日期范围内的日志信息（已xml格式导出为stat.xml，使用navicat工具导入进stat表）  
select t.username,t.accesstime,t.totaltimecost,t.accessurl from visit_record t where t.accesstime>to_date('2013-09-1','yyyy-MM-dd') and t.username is not null and t.result='成功' and t.clientip <> '192.168.112.53' and t.clientip <> '192.168.112.200' and t.clientip <> '192.168.112.245'   
 and (t.accessurl='/newOA/m_flow/navPage.do' or   
  t.accessurl='/newOA/m_commWorkflow/submitNode_2p0.do' or   
  t.accessurl='/newOA/user/showDaiBanxiangJsp.do' or   
  t.accessurl='/newOA/user/showDaiBanXiangList.do'  )  
order by t.totaltimecost desc ;  
  
  
-- 统计超时率(myql库中)  
select   
    t.ACCESSURL as url   
    ,COUNT(t.ACCESSURL) as 发生次数  
    ,COUNT(IF(t.TOTALTIMECOST>5000,t.TOTALTIMECOST,null)) as 超时次数  
    ,COUNT(IF(t.TOTALTIMECOST>5000,t.TOTALTIMECOST,null))/COUNT(t.ACCESSURL) as 超时率  
    ,ROUND(avg(t.TOTALTIMECOST)) as 平均耗时  
    ,max(t.TOTALTIMECOST) as 最大耗时   
                from stat t   
                group by t.ACCESSURL order by 平均耗时 desc;  
  
-- 时间分布（异常统计）  
  
select   
    t.ACCESSURL as url   
    ,COUNT(IF(t.TOTALTIMECOST>5000 ,t.TOTALTIMECOST,null))  as 超时次数  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '09:00:00' and '10:00:00'  ,t.TOTALTIMECOST,null)) as '9'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '10:00:00' and '11:00:00'  ,t.TOTALTIMECOST,null)) as '10'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '11:00:00' and '12:00:00'  ,t.TOTALTIMECOST,null)) as '11'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '12:00:00' and '13:00:00'  ,t.TOTALTIMECOST,null)) as '12'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '13:00:00' and '14:00:00'  ,t.TOTALTIMECOST,null)) as '13'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '14:00:00' and '15:00:00'  ,t.TOTALTIMECOST,null)) as '14'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '15:00:00' and '16:00:00'  ,t.TOTALTIMECOST,null)) as '15'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '16:00:00' and '17:00:00'  ,t.TOTALTIMECOST,null)) as '16'  
    ,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '17:00:00' and '18:00:00'  ,t.TOTALTIMECOST,null)) as '17'   
                from stat t   
                group by t.ACCESSURL with ROLLUP;  
  
  
select   
    t.ACCESSURL as url   
    ,COUNT(t.ACCESSURL) as 发生次数  
    ,COUNT(IF( time(t.ACCESSTIME) between '09:00:00' and '10:00:00'  ,t.TOTALTIMECOST,null)) as '9'  
    ,COUNT(IF( time(t.ACCESSTIME) between '10:00:00' and '11:00:00'  ,t.TOTALTIMECOST,null)) as '10'  
    ,COUNT(IF( time(t.ACCESSTIME) between '11:00:00' and '12:00:00'  ,t.TOTALTIMECOST,null)) as '11'  
    ,COUNT(IF( time(t.ACCESSTIME) between '12:00:00' and '13:00:00'  ,t.TOTALTIMECOST,null)) as '12'  
    ,COUNT(IF( time(t.ACCESSTIME) between '13:00:00' and '14:00:00'  ,t.TOTALTIMECOST,null)) as '13'  
    ,COUNT(IF( time(t.ACCESSTIME) between '14:00:00' and '15:00:00'  ,t.TOTALTIMECOST,null)) as '14'  
    ,COUNT(IF( time(t.ACCESSTIME) between '15:00:00' and '16:00:00'  ,t.TOTALTIMECOST,null)) as '15'  
    ,COUNT(IF( time(t.ACCESSTIME) between '16:00:00' and '17:00:00'  ,t.TOTALTIMECOST,null)) as '16'  
    ,COUNT(IF( time(t.ACCESSTIME) between '17:00:00' and '18:00:00'  ,t.TOTALTIMECOST,null)) as '17'  
      
                from stat t   
                group by t.ACCESSURL with ROLLUP;  
  
  
-- 日期分布（正常统计）  
select   
    t.ACCESSURL as url   
    ,COUNT(t.ACCESSURL) as 发生次数  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=0  ,t.ACCESSTIME,null)) as '星期1'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=1  ,t.ACCESSTIME,null)) as '星期2'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=2  ,t.ACCESSTIME,null)) as '星期3'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=3  ,t.ACCESSTIME,null)) as '星期4'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=4  ,t.ACCESSTIME,null)) as '星期5'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=5  ,t.ACCESSTIME,null)) as '星期6'  
    ,COUNT(IF( WEEKDAY(t.ACCESSTIME)=6  ,t.ACCESSTIME,null)) as '日'  
                from stat t   
                group by t.ACCESSURL   
  
  
select   
    t.ACCESSURL as url   
    ,COUNT(t.ACCESSURL) as 发生次数  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=0  ,t.ACCESSTIME,null)) as '星期1'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=1  ,t.ACCESSTIME,null)) as '星期2'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=2  ,t.ACCESSTIME,null)) as '星期3'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=3  ,t.ACCESSTIME,null)) as '星期4'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=4  ,t.ACCESSTIME,null)) as '星期5'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=5  ,t.ACCESSTIME,null)) as '星期6'  
    ,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=6  ,t.ACCESSTIME,null)) as '日'  
                from stat t   
                group by t.ACCESSURL
试试其它关键字
同语言下
可能有用的
雅竹贡献的其它代码(14)