統計時間分布腳本（mysql）

xb68 11年前發布 | 9K 次閱讀 SQL MySQL

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


</pre>
 本文由用戶 xb68 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！