IT虾米网

统计时间分布脚本(mysql)详解

shasha 2018年06月24日 数据库 708 0
mysql
-- 查询超时记录 
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  
 

发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

C实现PHP的MySQL数据库连接池详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。