数据统计在日常的工作中偶有需求,但每次搞起来都要进行查阅,难免有些头大,为了下次遇到同样问题的时候可以更轻松便捷的找到使用的方法,索性做个记录。内容此文的基础稍有调整而成,如有侵权,可联系本人删除。

准备工作

为了测试方便,先创建一个测试表,在插入两条数据(注:时间为 datetime` 类型,Unix 时间戳需要转换)

  1. 创建表
create table if not exists t (
    id int,
    addTime datetime default '0000-00-00 00:00:00'
);
  1. 添加两条初始数据:
insert t values(1, '2017-03-12 02:00:00');
insert t values(2, '2017-03-22 02:00:00');

开始

  1. 统计当天(当日)的数据:

    • 常规写法(对比判断):
    SELECT count(*) FROM t
    WHERE DATE_FORMAT(addTime, '%Y-%m-%d') = date_format(now(),'%Y-%m-%d'));
    
    • 常规写法的简化:
    SELECT * FROM t WHERE addTime >= date_format(NOW(), '%Y-%m-%d');
    
    • 使用函数判断(推荐此方法):
    SELECT * FROM t WHERE DATEDIFF(addTime, NOW()) =0;
    
    • 注:MySQL 返回当天时间的函数或常量有:
    第一类,CURDATE(),CURRENT_DATE()、CURRENT_DATE(常量);返回 '0000-00-00'(年-月-日)格式
    第二类,NOW(); 返回格式 '0000-00-00 00:00:00'(年-月-日 时:分:秒)格式
    
  2. 统计当周的数据:

    • 获取今天是一周第几天(星期几):
    SELECT WEEKDAY(now()); # (返回的是数字:0为周一,6为周日)
    
    • 获取本周第一天的日期:
    SELECT DATE_SUB(now(), INTERVAL WEEKDAY(now()) day); #(格式 '0000-00-00 00:00:00')
    或
    SELECT DATE_ADD(now(),INTERVAL -WEEKDAY(now()) day); #(格式 '0000-00-00 00:00:00')
    或
    SELECT CURDATE() - WEEKDAY(CURDATE()); # (格式 '0000 00 00')
    
    • 获取上周的第一天至现在的数据:(以表 t 为数据表)
    SELECT * FROM t 
    WHERE 
    addTime >= date_format(
        date_sub(date_sub(now(), INTERVAL WEEKDAY(NOW()) DAY), INTERVAL 1 WEEK), '%Y-%m-%d'
    );
    

    注:

    • 若你是以时间戳保存的时间字段,那么请用 FROM_UNIXTIME()函数转换为时间格式;

    • INTERVAL 的用户说明:

      • 作为函数使用时,即 INTERVAL(),为比较函数,如:INTERVAL(10,1,3,5,7); 结果为4;

        **原理:**10为被比较数,后面1,3,5,7为比较数,将后面四个依次与10比较,看后面数字组有多少个少于10,则返回其个数。前提是后面数字组为从小到大排列,否则返回结果0。

      • 作为关键词使用时,表示为设置时间间隔,常用在 date_add() 与 date_sub() 函数里,如:INTERVAL 1 DAY,解释为将时间间隔设置为1天。

    • 弄清楚了上面几个重要的日期运算函数,下面再来一个混合的日期运算。
      上个月的第一天时间:

      SELECT DATE_SUB(
          DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) -1 DAY), INTERVAL 1 MONTH
      );
      

      这样是不是就一目了然了!

  3. 统计当月的数据:

    • 获取今天是当月的第几天:
    SELECT DAYOFMONTH(NOW());
    
    • 获取当月的第一天时间:
    SELECT DATE_SUB(NOW(), INTERVAL DAYOFMONTH(NOW()) –1 DAY);
    

    注:
    日期运算函数:
    DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type),date 为一个 datetime 或date 值,可当作起始时间;expr 为一个表达式,用来指定增加或减少的时间间隔数目,可为一个字符串,若为负值,则在表达式前加个"-"符号。type 为关键词,它表示了表达式被解释的方式,通常用 年(year)、月(month)、日(day)、周(week)等。

前三点的方法,讲的都是将日期转为相应时间常量,并没有提到时间戳与时间的转换,这里把它写出来,

  • 时间戳转日期,方法是:
SELECT FROM_UNIXTIME(1336542121); # Output 2012-05-09 13:42:01
  • 日期转时间戳,方法是:
SELECT UNIX_TIMESTAMP('2013-04-08'); # Ouput 1365350400

例如,按天统计新增记录:

SELECT COUNT(*) as count, FROM_UNIXTIME(addTime, '%Y-%m-%d') as datetime from t;

结合前面例子,对时间戳进行相应的转换就可以了。


标题:MySQL 按天、星期、按月数据统计 SQL 集合 (转)
作者:Tristana
地址:https://520tristana.com/articles/2017/03/15/1489536000028.html