MySQL按时间统计数据的方法总结

在做数据库的统计时,经常会需要根据年、月、日来统计数据,然后配合echarts来制作可视化效果。

数据库:MySQL

思路

按照时间维度进行统计的前提是需要数据库中有保留时间信息,建议是使用MySQL自带的datetime类型来记录时间。

`timestamp` datetime DEFAULT NULL,

在MySQL中对于时间日期的处理的函数主要是DATE_FORMAT(date,format)。可用的参数如下

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

注:当涉及到按日统计是,需要使用%j,而如果使用%d, %e, %w的话,那么不同月份/周里的相同值会统计在一起。

涉及到获取当前时间,则可以通过now()或者sysdate()来获取。

SELECT SYSDATE() FROM DUAL;

SELECT NOW() FROM DUAL;

按照实际需求使用group by查询即可。

结论
需统计的表结构如下:

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`)

)

统计时间范围内不同分类action的数量

# 当日

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;

统计某分类action的时间维度数量

# 按日

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')

同时按action和时间维度统计

# 按日

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')

以上就是比较常用的时间统计了,更多的时间维度,可以参考上面的参数表类似处理即可。

(0)

相关推荐

  • MySQL主从延迟现象及原理分析详解

    一.现象 凌晨对线上一张表添加索引,表数据量太大(1亿+数据,数据量50G以上),造成主从延迟几个小时,各个依赖从库的系统无法查询数据,最终影响业务. 现在就梳理下主从延迟的原理. 二.原理 根据 MySQL 官方文档 MySQL Replication Implementation Details 中的描述,MySQL 主从复制依赖于三个线程:master一个线程(Binlog dump thread),slave两个线程(I/O thread和SQL thread).主从复制流程如下图: m

  • MySQL 8忘记密码的最佳处理方式浅析

    前言 对MySQL有研究的读者,可能会发现MySQL更新很快,在安装方式上,MySQL提供了两种经典安装方式:解压式和一键式,虽然是两种安装方式,但我更提倡选择解压式安装,不仅快,还干净.在操作系统上,MySQL也支持多类型操作系统,如linux,windows等,如下为MySQL几个重大变化的操作系统. 通过研究分析,不难发现:MySQL从低版本向高版本迭代变化的过程,越来越严谨的安全性是其一大特点之一,我们举个例子,在版本6前,当忘记密码,重置密码,非常方面,只需使用执行如下两步即可: 步骤

  • Mysql主键和唯一键的区别点总结

    什么是主键? 主键是表中唯一标识该表中每个元组(行)的列.主键对表实施完整性约束.表中只允许使用一个主键.主键不接受任何重复值和空值.表中的主键值很少更改,因此在选择主键是需要小心,要选择很少发生更改的地方.一个表的主键可以被另一个表的外键引用. 为了更好地理解主键,我们创建一个名为Student的表,它具有roll_number.name.batch.phone_number.citizen_id等属性. 在上面的示例中,roll_number属性永远不能具有相同的NULL值,因为在每个大学中

  • Docker创建MySQL的讲解

    1.下载MySQL Image 命令: docker pull mysql 2.创建容器 命令样例: sudo docker run -p 3306:3306 --name mysql -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -d mysql 命令说明: 总结 以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具

  • MySQL limit性能分析与优化

    一.结论 语法结构: limit offset, rows 结论:rows 相同条件下,offset 值越大,limit 语句性能越差 二.测试 执行测试: 5750000 条数据 sql 1 执行时间: sql 6执行时间: 三.优化 方式一:可根据主键ID等其他索引字段定位到数据位置,然后使用 limit 0, rows 如: select * from gift_record_0 where id >= 43611207 limit 1000 0.14 s 方式二(根据业务需求):针对不需

  • 实例讲解MySQL中乐观锁和悲观锁

    数据库管理系统中并发控制的任务是确保在多个事务同时存取数据库中同一数据不破坏事务的隔离性和统一性以及数据库的统一性 乐观锁和悲观锁式并发控制主要采用的技术手段 悲观锁 在关系数据库管理系统中,悲观并发控制(悲观锁,PCC)是一种并发控制的方法.它可以阻止一个事务以影响其他用户的方式来修改数据.如果一个事务执行的操作的每行数据应用了锁,那只有当这个事务锁释放,其他事务才能够执行与该锁冲突的操作 悲观并发控制主要应用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本环境

  • MySQL普通索引和唯一索引的深入讲解

    场景 1.维护一个市民系统,有一个字段为身份证号 2.业务代码能保证不会写入两个重复的身份证号(如果业务无法保证,可以依赖数据库的唯一索引来进行约束) 3.常用SQL查询语句:SELECT name FROM CUser WHERE id_card = 'XXX' 4.建立索引 身份证号比较大,不建议设置为主键 从性能角度出发,选择普通索引还是唯一索引? 假设字段k上的值都不重复 查询过程 1.查询语句:SELECT id FROM T WHERE k=5 2.查询过程 通过B+树从树根开始,按

  • 美团DB数据同步到数据仓库的架构与实践

    背景 在数据仓库建模中,未经任何加工处理的原始业务层数据,我们称之为ODS(Operational Data Store)数据.在互联网企业中,常见的ODS数据有业务日志数据(Log)和业务DB数据(DB)两类.对于业务DB数据来说,从MySQL等关系型数据库的业务数据进行采集,然后导入到Hive中,是进行数据仓库生产的重要环节. 如何准确.高效地把MySQL数据同步到Hive中?一般常用的解决方案是批量取数并Load:直连MySQL去Select表中的数据,然后存到本地文件作为中间存储,最后把

  • MySQL和Redis实现二级缓存的方法详解

    redis简介 Redis 是完全开源免费的,遵守BSD协议,是一个高性能的key-value数据库 Redis 与其他 key - value 缓存产品有以下三个特点: Redis支持数据的持久化,可以将内存中的数据保存在磁盘中,重启的时候可以再次加载进行使用 Redis不仅仅支持简单的key-value类型的数据,同时还提供list,set,zset,hash等数据结构的存储 Redis支持数据的备份,即master-slave模式的数据备份 优势 性能极高 - Redis能读的速度是110

  • mybatis学习之路mysql批量新增数据的方法

    接下来两节要探讨的是批量插入和批量更新,因为这两种操作在企业中也经常用到. mysql新增语句 insert into 表名(字段,字段...) values ( 值,值 ...):此种适合单条插入. 批量插入,一种可以在代码中循环着执行上面的语句,但是这种效率太差,下面会有对比,看看它有多差. 另一种,可以用mysql支持的批量插入语句, insert into 表名(字段,字段...) values ( 值,值 ...),( 值,值 ...),( 值,值 ...).... 这种方式相比起来,

随机推荐