统计时刻漫衍剧本（mysql）

以下代码由PHP站长网 52php.cn网络自互联网

此刻PHP站长网小编把它分享给各人，仅供参考

-- 查询超时记录
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 超时次数,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,null))  as 超时次数,COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '09:00:00' and '10:00:00',null)) as '9',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '10:00:00' and '11:00:00',null)) as '10',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '11:00:00' and '12:00:00',null)) as '11',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '12:00:00' and '13:00:00',null)) as '12',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '13:00:00' and '14:00:00',null)) as '13',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '14:00:00' and '15:00:00',null)) as '14',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '15:00:00' and '16:00:00',null)) as '15',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '16:00:00' and '17:00:00',null)) as '16',COUNT(IF(t.TOTALTIMECOST>5000 and time(t.ACCESSTIME) between '17:00:00' and '18:00:00',null)) as '17'	
				from stat t 
				group by t.ACCESSURL with ROLLUP;


select 
	t.ACCESSURL as url,COUNT(IF( time(t.ACCESSTIME) between '09:00:00' and '10:00:00',COUNT(IF( time(t.ACCESSTIME) between '10:00:00' and '11:00:00',COUNT(IF( time(t.ACCESSTIME) between '11:00:00' and '12:00:00',COUNT(IF( time(t.ACCESSTIME) between '12:00:00' and '13:00:00',COUNT(IF( time(t.ACCESSTIME) between '13:00:00' and '14:00:00',COUNT(IF( time(t.ACCESSTIME) between '14:00:00' and '15:00:00',COUNT(IF( time(t.ACCESSTIME) between '15:00:00' and '16:00:00',COUNT(IF( time(t.ACCESSTIME) between '16:00:00' and '17:00:00',COUNT(IF( time(t.ACCESSTIME) between '17:00:00' and '18:00:00',null)) as '17'
	
				from stat t 
				group by t.ACCESSURL with ROLLUP;


-- 日期漫衍（正常统计）
select 
	t.ACCESSURL as url,COUNT(IF( WEEKDAY(t.ACCESSTIME)=0,t.ACCESSTIME,null)) as '礼拜1',COUNT(IF( WEEKDAY(t.ACCESSTIME)=1,null)) as '礼拜2',COUNT(IF( WEEKDAY(t.ACCESSTIME)=2,null)) as '礼拜3',COUNT(IF( WEEKDAY(t.ACCESSTIME)=3,null)) as '礼拜4',COUNT(IF( WEEKDAY(t.ACCESSTIME)=4,null)) as '礼拜5',COUNT(IF( WEEKDAY(t.ACCESSTIME)=5,null)) as '礼拜6',COUNT(IF( WEEKDAY(t.ACCESSTIME)=6,null)) as '日'
				from stat t 
				group by t.ACCESSURL 


select 
	t.ACCESSURL as url,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=0,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=1,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=2,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=3,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=4,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=5,COUNT(IF( t.TOTALTIMECOST>5000 and WEEKDAY(t.ACCESSTIME)=6,null)) as '日'
				from stat t 
				group by t.ACCESSURL 

以上内容由PHP站长网【52php.cn】网络清算供各人参考研究

假如以上内容对您有辅佐，接待保藏、点赞、保举、分享。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!