異世界

2014年12月22日 星期一

MySql 按日期進行統計

今天的紀錄筆數 : SELECT COUNT(*) FROM `log` WHERE (to_days(now()) - to_days(`time`) =0);

昨天的紀錄筆數 : SELECT COUNT(*) FROM `log` WHERE (to_days(now()) - to_days(`time`) =1);

前天的紀錄筆數 : SELECT COUNT(*) FROM `log` WHERE (to_days(now()) - to_days(`time`) =2);

  • 統計各CH 的 Event 次數，
    • ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="CTV" and `AlarmOnOff`="1" )
    UNION
    ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="PTS" and `AlarmOnOff`="1"  )
    UNION
    ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="FTV" and `AlarmOnOff`="1" )
    UNION
    ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="HiHd" and `AlarmOnOff`="1" )
    UNION
    ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="TTV" and `AlarmOnOff`="1" )
    UNION
    ( SELECT `ChName`, 
        SUM( IF((to_days(now()) - to_days(`time`) =1),1 ,0 ) ) as yesterday, 
        SUM( IF((to_days(now()) - to_days(`time`) =2),1 ,0 ) ) as today_2, 
        SUM( IF((to_days(now()) - to_days(`time`) =3),1 ,0 ) ) as today_3,
        SUM( IF((to_days(now()) - to_days(`time`) =4),1 ,0 ) ) as today_4, 
        SUM( IF((to_days(now()) - to_days(`time`) =5),1 ,0 ) ) as today_5, 
        SUM( IF((to_days(now()) - to_days(`time`) =6),1 ,0 ) ) as today_6, 
        SUM( IF((to_days(now()) - to_days(`time`) =7),1 ,0 ) ) as today_7 
      FROM `log` WHERE `ChName`="CTS" and `AlarmOnOff`="1" )
     

 




image



function WeeklyReport(){
    $sql = "CALL `getLog7Day`();";
    $result = SqlCommand($sql);
    showTable($result);
  
    //釋放查詢結果所佔用的記憶體
    mysql_free_result($result);
}

 




以下資料聲明:

聲明：原創文章，轉載時必須以超鏈接的形式註明作者和原始出處。 作者：YoviSun
標題：MySql按日期進行統計（前一天、本週、某一天）
鏈接：http://www.yovisun.com/mysql-date-statistics.html



在mysql數據庫中，常常會遇到統計當天的內容。

例如，在user表中，日期字段為：log_time


  • 統計當天

sql語句為： 



select  *  from user where date (log_time) = curdate();

curdate() 表示當天日期

 


  • 統計前一天

如果表示前一天的數據，則不能使用curdate()-1，因為當日期為月初時，curdate()-1 日期就不是上一個月的月末日期。

例如：今天是6月1日，理論上curdate()-1為5月31日，但是curdate()-1得到不是5月31日，而是6月0日。那麼統計前一天的日期就不能使用curdate()-1了，mysql數據庫又有一個新方法統計前一天的數據。

統計前一天的日誌sql語句：

 

select  *  from  bean  where date (log_time) = date_sub(curdate(),interval 1  day );

 

括號中為當天時間的前一天，如果統計前幾天就將括號中的'1'改成相應的天數。如果要算月或年，直接將day改為month或year即可

 


  • 統計本週

要求： 統計從昨天開始統計前7天的日誌包括昨天 

select  *  from user where date (log_time) >= date_sub(curdate(),interval 7  day ) and date (log_time) <= date_sub(curdate(),interval 1  day )


在網上找的使用week統計一周信息，只能統計到5天的信息，不符合要求，所以改用這種方法。

 


  • 統計某一天

統計歷史某一天的日誌，將date_sub(curdate(),interval 0 day)函數中的curdate()替換為某一天的日期
比如：要統計2012-05-25日期的信息 


date_sub('2012-05-25',interval 0 day)


關於 date_sub() 函數的例子：

今天是2013年5月20日。 

date_sub('2012-05-25',interval 1 day)表示2012-05-24
date_sub('2012-05-25',interval 0 day)表示2012-05-25
date_sub('2012-05-25', interval -1 day)表示2012-05-26
date_sub('2012-05-31',interval -1 day)表示2012-06-01
date_sub(curdate(),interval 1 day)表示2013-05-19
date_sub( curdate(),interval -1 day)表示2013-05-21
date_sub(curdate(),interval 1 month)表示2013-04-20
date_sub(curdate(),interval -1 month)表示2013-06-20
date_sub( curdate(),interval 1 year)表示2012-05-20
date_sub(curdate(),interval -1 year)表示2014-05-20

張貼者： 於
標籤： ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)