mysql如何分别按年/月/日/周分组统计数据详解

2024-12-02 21:29:23

1.统计

我们可以使用date_format()函数格式化时间，然后进行分组操作

例如有一个学生表，结构如下

id	name	age	height	gender	create_time
1	pan	27	169	1	2022-01-13 10:20:22
2	yang	18	177	1	2022-03-14 09:16:42
3	daisy	25	156	2	2022-07-19 19:58:03

-- 按年
select date_format(create_time, '%Y') years,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by years;
-- 按月
select date_format(create_time, '%Y-%m') months,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by months;
-- 按周
select date_format(create_time, '%Y-%u') weeks,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by weeks;
-- 按日
select date_format(create_time, '%Y-%m-%d') days,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by days;

如果不想用date_format函数，可以使用对应的year()/month()/week()/day()函数替代

2.占位符

date_format()需要传入一个特定的占位符，mysql常用的占位符可参考下表

占位符	说明
%Y	年(4位)
%y	年(2位)
%M	月（英文名，如January）
%m	月（数字，如01）
%D	日（英文名，如1st）
%d	日（数字，如01）
%e	日（数字，如1）
%U	一年中的第几周，从0开始，周日是第一天
%u	一年中的第几周，从0开始，周一是第一天
%H	时，24小时制，例如15
%h	时，12小时制，例如01
%i	分
%s	秒

补充：Mysql如何指定日期按周分组，并按次数分类统计

需要统计今年以来，每周用户的提问次数，并按提问次数进行分类统计，格式如下：

需求不能按自然周算，所以不适合用week()函数计算周数，只能根据提问时间和指定日期的时间差相除

第几周 =floor((提问时间-指定日期)/7)+1

确定了计算方式，首先按周和用户ID分组，查询周，提问次数，然后通过case when对查询结果按提问次数分类，sql如下：

SELECT weeks,
CONCAT(LEFT(MIN(create_time),10),"至",LEFT(MAX(create_time),10)) AS date_range,
SUM(CASE WHEN nums <= 5 THEN 1 ELSE 0 END) AS '提问次数<=5',
 SUM(CASE WHEN nums > 5 AND nums<=10 THEN 1 ELSE 0 END) AS '5<提问次数<=10',
 SUM(CASE WHEN nums > 10 THEN 1 ELSE 0 END) AS '提问次数>10'
 FROM
 (SELECT FLOOR(DATEDIFF(create_time,'2021-01-01')/7)+1 weeks,
 create_time,uid,COUNT(*) AS nums
  FROM `youTable`
   WHERE create_time > "2021-01-01"
   GROUP BY weeks,uid ORDER BY create_time ASC LIMIT 100000)
    AS topic_table GROUP BY weeks

总结

到此这篇关于mysql如何分别按年/月/日/周分组统计数据的文章就介绍到这了,更多相关mysql分组统计数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

mysql通过group by分组取最大时间对应数据的两种有效方法

1.项目记录表project_record的结构和数据如下: 以下为项目记录表project_record的所有数据.project_id为项目Id,on_project_time为上项目时间.(每一条数据代表着上某个项目(project_id)的时间(on_project_time)记录) 2.我们的需求是:取出每个项目中最大上项目时间对应的那条数据.(即根据project_id分组,取出每组中最大的on_project_time对应的数据.)上方红框是我们要查出的数据. 3.错误代码: SE
Mysql分组查询每组最新一条数据的三种实现方法

目录前言注意事项准备SQL 错误原因方法一方法二(适用于自增ID和创建时间排序一致) 方法三(适用于自增ID和创建时间排序一致) 总结 MAX()函数和MIN()这一类函数和GROUP BY配合使用存在问题前言在写报表功能时遇到一个需要根据用户id分组查询最新一条钱包明细数据的需求,在写sql测试时遇到一个有趣的问题,开始使用子查询根据时间倒序+group by customer_id发现查询出来的数据一直都是最旧的一条,而不是我需要的最新一条数据我明明已经倒序排了,后来总结出了三
MySQL数据库使用mysqldump导出数据详解

mysqldump是mysql用于转存储数据库的客户端程序.它主要产生一系列的SQL语句,可以封装到文件,该文件包含有所有重建您的数据库所需要的 SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等.可以用来实现轻量级的快速迁移或恢复数据库.是mysql数据库实现逻辑备份的一种方式. 在日常维护工作当中经常会需要对数据进行导出操作,而mysqldump是导出数据过程中使用非常频繁的一个工具:它自带的功能参数非常多,文章中会列举出一些常用的操作,在文章末尾会将所有
mysql 5.7 zip 文件在 windows下的安装教程详解

1.下载mysql最新版本. http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.15-winx64.zip 2.解压到文件夹. D:\software\mysql\mysql5.7a 将my-default.ini 复制为 my.ini 3.编辑my.ini # These are commonly set, remove the # and set as required. basedir ="D:/software/mysql/mysql
MySQL 5.6.36 Windows x64位版本的安装教程详解

1,目标环境 Windows 7 64位 2,材料 (1)VC++2010 发布包(64位) (2)MySQL 5.6.36 Windows x64位版本(非MSI,可从官网获取免费版本) (3)EditPlus(可选) 3,基础操作本文中部分操作需以管理员身份+命令行进行执行. 4,步骤 (1)(解压到当前文件夹方式)解压安装包,编辑其中的my-default.ini文件,主要是2项: ①basedir即为mysql基础文件夹,形如:C:\mysql-5.6.36-winx64 ②datad
MySQL null与not null和null与空值''''的区别详解

相信很多用了MySQL很久的人,对这两个字段属性的概念还不是很清楚,一般会有以下疑问: 我字段类型是not null,为什么我可以插入空值为毛not null的效率比null高判断字段不为空的时候,到底要 select * from table where column <> '' 还是要用 select * from table wherecolumn is not null 呢. 带着上面几个疑问,我们来深入研究一下null 和 not null 到底有什么不一样. 首先,我们要搞清楚
MySql分表、分库、分片和分区知识深入详解

一.前言数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以
mysql触发器实时检测一条语句进行备份删除思路详解

问题描述:用户有一个这样一个需求,在一张表里会不时出现 "违规" 字样的字段,需要在出现这个字段的时候,把整行的数据删掉.这是个采集任务,如果发现有"违规"字样的数据,会整点或者什么时间进行统一上报,也无法对源头进行控制让这种数据不生成. 现在需要实现以下需求: 1.实时检测这条数据的产生,发现后删除 2.在删除之前作备份这条数据解决思路: 需要明确解决思路, 1.首先是如何实时探测删除?询问开发,这条数据的生成方式为insert,就可以做一个当表做插入的时候,然
MySQL系列多表连接查询92及99语法示例详解教程

目录 1.笛卡尔积现象 2.连接查询知识点概括 1)什么是连接查询? 2)连接查询的分类 3.内连接讲解 1)等值连接:最大特点是,连接条件为等量关系. 2)sql92语法和sql99语法的区别. 3)非等值连接:最大特点是,连接条件为非等量关系. 4)自连接:最大特点是,一张表看作两张表. 4.外连接讲解 1)什么是外连接,和内连接有什么区别? 2)外连接的分类前面两天带着大家换了一个口味,带着大家学习了pyecharts的原理和部分图形制作.今天我们继续回归带你学MySQL系列,带着大家继
Mysql数据库事务的脏读幻读及不可重复读详解

目录一.什么是数据库事务二.事务的ACID原则 1. 原子性(Atomicity) 2. 一致性(Consistency) 3. 持久性(Durability) 4. 隔离性(Isolation) 三.隔离带来的问题 1. 脏读 2. 不可重复读 3.幻读四.手动测试下事务的过程一.什么是数据库事务数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列,这些操作要么全部执行,要么全部不执行,是一个不可分割的工作单位.事务由事务开始与事务结束之间执行的全部数
MySQL索引优化之适合构建索引的几种情况详解

目录结论建立索引的场景小结结论在where后面的过滤字段上建立索引(select/update/delete后面的where都是适用的),使用索引加快过滤效率,不用进行全表扫描在具有唯一要求的字段上添加唯一索引,加快查询效率,查到即可直接返回 group by或者order by后面的字段添加索引,由于索引是排好序的,所以建立索引就等同于在查询之前已经是排好序了(这里需要注意建立的联合索引建立中字段的顺序,可以结合具体案例场景7进行学习) 在DISTINCT(去重字段)后面的字段添加
MySQL中dd::columns表结构转table过程及应用详解

目录一.MySQL的dd表介绍二.代码跟踪三.知识应用四.总结一.MySQL的dd表介绍 MySQL的dd表是用来存放表结构和各种建表信息的,客户端建的表都存在mysql.table和mysql.columns表里,还有一个表mysql.column_type_elements比较特殊,用来存放SET和ENUM类型的字段集合值信息.看一下下面这张表的mysql.columns表和mysql.column_type_elements信息.为了缩短显示长度,这里只展示几个重要的值. #建表

mysql如何分别按年/月/日/周分组统计数据详解

目录

1.统计

2.占位符

补充：Mysql如何指定日期按周分组，并按次数分类统计

总结

相关推荐

随机推荐