MySQL按时间统计数据

大家好，今天本人给大家带来文章《MySQL按时间统计数据》，文中内容主要涉及到MySQL、数据，如果你对数据库方面的知识点感兴趣，那就请各位朋友继续看下去吧~希望能真正帮到你们，谢谢！

背景

在做数据库的统计时，经常会需要根据年、月、日来统计数据，然后配合

`timestamp` datetime DEFAULT NULL,

• 在MySQL中对于时间日期的处理的函数主要是

  SELECT SYSDATE() FROM DUAL;
  SELECT NOW() FROM DUAL;

  • 按照实际需求使用

    CREATE TABLE `apilog` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(64) DEFAULT NULL,
      `action` varchar(64) DEFAULT NULL,
      `params` text,
      `result` text,
      `timestamp` datetime DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) 

    • 统计时间范围内不同分类

      # 当日
      SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%j') = DATE_FORMAT(now(),'%j') ORDER BY count desc;
      # 当周
      SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%u') = DATE_FORMAT(now(),'%u') ORDER BY count desc;
      # 当月
      SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%m') = DATE_FORMAT(now(),'%m') ORDER BY count desc;
      # 当年
      SELECT action, COUNT(id) count FROM apilog WHERE DATE_FORMAT(`timestamp`,'%Y') = DATE_FORMAT(now(),'%Y') ORDER BY count desc;

    • 统计某分类

      # 按日
      SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%j')
      # 按周
      SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%u')
      # 按月
      SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%m')
      # 按年
      SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog WHERE action = 'xxx' GROUP BY DATE_FORMAT(`timestamp`,'%Y')

    • 同时按

      # 按日
      SELECT action, DATE_FORMAT(`timestamp`,'%j'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%j')
      # 按周
      SELECT action, DATE_FORMAT(`timestamp`,'%u'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%u')
      # 按月
      SELECT action, DATE_FORMAT(`timestamp`,'%m'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%m')
      # 按年
      SELECT action, DATE_FORMAT(`timestamp`,'%Y'), COUNT(id) count FROM apilog GROUP BY action, DATE_FORMAT(`timestamp`,'%Y')

    本篇关于《MySQL按时间统计数据》的介绍就到此结束啦，但是学无止境，想要了解学习更多关于数据库的相关知识，请关注golang学习网公众号！