51工具盒子

依楼听风雨
笑看云卷云舒,淡观潮起潮落
当前位置：工具盒子 > 白嫖帮 > 正文

【MySQL 系列】MySQL 按照当前年月周日统计数据

2025-01-17 分类：白嫖帮 阅读(15) 评论(0)

前言： {#前言：}

接了一个小需求，获取用电统计的数据，要求获取最近月，周，天统计数据，MySQL 本来就包含处理这种需求的函数，这里记录下。

查询当天数据 {#查询当天数据}

SELECT * FROM 表名 WHERE TO_DAYS( 表中时间字段) = TO_DAYS(NOW());

查询昨天数据 {#查询昨天数据}

SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(FROM_UNIXTIME( 表中时间字段)) = 1;
  • 方式 2
SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(表中日期字段) = 1;

查询本周数据 {#查询本周数据}

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT( 表中时间字段,'%Y-%m-%d')) = YEARWEEK(NOW());

查询上周数据 {#查询上周数据}

SELECT * FROM 表名 WHERE YEARWEEK(FROM_UNIXTIME( 表中时间字段,'%Y-%m-%d')) = YEARWEEK(NOW())-1;

查询近 30 天数据（包含当天） {#查询近 30 天数据（包含当天）}

SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= FROM_UNIXTIME(表中时间字段)

查询当月数据 {#查询当月数据}

SELECT * FROM 表名 WHERE YEARWEEK(DATE_FORMAT( 表中时间字段,'%Y-%m')) = DATE_FORMAT(CURDATE(),'%Y-%m');
  • 方式 2
SELECT
	* 
FROM
	表名 
WHERE
	date_format( 表中时间字段, '%Y -%m' )= date_format(NOW(), '%Y -%m' ) 
GROUP BY
	create_time DESC;
  • 方式 3
SELECT * FROM 表名 WHERE FROM_UNIXTIME(表中时间字段, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m');

查询上月数据 {#查询上月数据}

SELECT * FROM 表名 WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), FROM_UNIXTIME( 表中时间字段,'%Y%m')) = 1;

查询最近一周数据 {#查询最近一周数据}

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 WEEK) <= DATE( 表中时间字段);

中间的 1 是一周的意思，2 周就填写 2

查询最近一月内数据 {#查询最近一月内数据}

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(),INTERVAL 1 MONTH) <= DATE( 表中时间字段);

查询当年每月的统计数据 {#查询当年每月的统计数据}

SELECT MONTH('表中日期字段'), COUNT(*) FROM '表名' WHERE YEAR(CURDATE()) GROUP BY MONTH('表中日期字段');

PS：在复制 SQL 的时候需要注意，Mybatis 无法解析 <= | >= 这样的符号，需要使用 <![CDATA[ <=]]> 包裹。

不然会报：org.xml.sax.SAXParseException: 元素内容必须由格式正确的字符数据或标记组成（具体可以看下这篇文章：传送门）

查询本季度的数据 {#查询本季度的数据}

SELECT * FROM 表名 WHERE QUARTER(FROM_UNIXTIME( 表中时间字段 ))=QUARTER(NOW()) AND YEAR(FROM_UNIXTIME( 表中时间字段))=YEAR(NOW());

查询上季度的数据 {#查询上季度的数据}

SELECT * FROM 表名 WHERE QUARTER(FROM_UNIXTIME( 表中时间字段 )) = QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(FROM_UNIXTIME( 表中时间字段))=YEAR(NOW());

查询今年的数据 {#查询今年的数据}

SELECT * FROM 表名 WHERE YEAR(FROM_UNIXTIME( 表中时间字段))=YEAR(NOW());

查询去年的数据 {#查询去年的数据}

SELECT * FROM 表名 WHERE YEAR(FROM_UNIXTIME( 表中时间字段)) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

统计某年每个月的数据量 {#统计某年每个月的数据量}

SELECT MONTH( 日期字段 ),COUNT( 1 ) FROM 表名 WHERE YEAR ( 表中时间字段 ) = 查询年份  GROUP BY MONTH ( 表中时间字段 );

其他统计 SQL {#其他统计 -SQL}

查询本年度数据 {#查询本年度数据}

SELECT *  FROM 表名  WHERE YEAR ( FROM_UNIXTIME( 日期字段 ) ) = YEAR (curdate( ));

查询数据附带季度数 {#查询数据附带季度数}

SELECT *, QUARTER ( FROM_UNIXTIME( ` 日期字段` ) )  FROM 表名;

查询本季度的数据 {#查询本季度的数据 -1}

SELECT * FROM 表名 WHERE quarter( FROM_UNIXTIME( 日期字段 ) ) = quarter(curdate( ));

查询 7 天的数据 {#查询 7 天的数据}

SELECT * FROM 表名 WHERE DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( 日期字段 );

查询本周统计 {#查询本周统计}

SELECT *  FROM 表名  WHERE MONTH ( 日期字段 ) = MONTH (curdate()) AND WEEK ( 日期字段 ) = WEEK (curdate());

统计每天数据总量 {#统计每天数据总量}

SELECT count(*) FROM 表名  GROUP BY date( 日期字段);

下面的内容不重要，没地方写了所以就记录在这里吧！

MySQL 获取全部每天最后一条记录 {#MySQL 获取全部每天最后一条记录}

如果时间列（create_time）是 datetime 格式（2020-08-24 15:57:57） {#如果时间列（create-time）是 -datetime- 格式（2020-08-24-15-57-57）}

SELECT
	a.id,
	a.data,
	a.create_time,
	b.date 
FROM
	test_table a
	JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) date, 
          MAX( create_time ) max_time FROM test_table GROUP BY date ) b ON b.max_time = a.create_time 
ORDER BY
	b.date DESC
  • 查询结果
+------+----------------------+---------------------+------------+
| id   | data                 | create_time         | date       |
+------+----------------------+---------------------+------------+
| 1184 |   0.3599999999999852 | 2022-07-18 23:59:00 | 2022-07-18 |
| 1160 |   0.3599999999999852 | 2022-07-17 23:59:00 | 2022-07-17 |
| 1136 |   0.3499999999999943 | 2022-07-16 23:59:00 | 2022-07-16 |
| 1112 |  0.35999999999999943 | 2022-07-15 23:59:00 | 2022-07-15 |
| 1098 |                    0 | 2022-07-14 13:59:00 | 2022-07-14 |
| 1085 |                    0 | 2022-07-13 23:59:00 | 2022-07-13 |
| 1062 |  0.35999999999999943 | 2022-07-12 23:59:00 | 2022-07-12 |
| 1038 |  0.35999999999999943 | 2022-07-11 23:59:00 | 2022-07-11 |
| 1014 |  0.35999999999999943 | 2022-07-10 23:59:00 | 2022-07-10 |
+------+----------------------+---------------------+------------+

如果时间列（create_time）是时间戳格式（1598255877） {#如果时间列（create-time）是时间戳格式（1598255877）}

SELECT
	a.id,
	a.create_time,
	a.test_value,
	a.create_date,
	b.date 
FROM
	test_table a
	JOIN ( SELECT FROM_UNIXTIME( create_time, '%Y-%m-%d' ) date, MAX( create_time ) max_time FROM test_table GROUP BY date ) b ON b.max_time = a.create_time 
ORDER BY
	b.date DESC

MySQL 获取本月每天最后一条记录 {#MySQL 获取本月每天最后一条记录}

SELECT
	a.id,
	a.data,
	a.create_time,
	b.date 
FROM
	table_name a
	JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) date, MAX( create_time ) max_time FROM table_name GROUP BY date ) b ON b.max_time = a.create_time 
WHERE
	DATE_FORMAT( create_time, '%Y -%m' )= DATE_FORMAT( NOW(), '%Y -%m' ) 
ORDER BY
	b.date DESC
  • 结果
+---------------------+---------------------+---------------------+------------+
|      id          |       data          |      create_time    | date       |
+---------------------+---------------------+---------------------+------------+
|                1 |                   0 | 2022-07-22 10:59:00 | 2022-07-22 |
|                2 |                   0 | 2022-07-21 23:59:00 | 2022-07-21 |
|                3 |                   0 | 2022-07-20 23:59:00 | 2022-07-20 |
|                4 |                   0 | 2022-07-19 13:59:01 | 2022-07-19 |
|                5 |  0.3599999999999852 | 2022-07-18 23:59:00 | 2022-07-18 |
|                6 |  0.3599999999999852 | 2022-07-17 23:59:00 | 2022-07-17 |
|                7 |  0.3499999999999943 | 2022-07-16 23:59:00 | 2022-07-16 |
|                8 | 0.35999999999999943 | 2022-07-15 23:59:00 | 2022-07-15 |
|                9 |                   0 | 2022-07-14 13:59:00 | 2022-07-14 |
|               10 |                   0 | 2022-07-13 23:59:00 | 2022-07-13 |
|               11 | 0.35999999999999943 | 2022-07-12 23:59:00 | 2022-07-12 |
|               12 | 0.35999999999999943 | 2022-07-11 23:59:00 | 2022-07-11 |
|               13 | 0.35999999999999943 | 2022-07-10 23:59:00 | 2022-07-10 |
|               14 | 0.35999999999999943 | 2022-07-09 23:59:00 | 2022-07-09 |
|               15 |  0.3499999999999943 | 2022-07-08 23:59:00 | 2022-07-08 |
|               16 |  0.3499999999999943 | 2022-07-07 23:59:00 | 2022-07-07 |
|               17 | 0.35999999999999943 | 2022-07-06 23:59:00 | 2022-07-06 |
|               18 |  0.3500000000000014 | 2022-07-05 23:59:00 | 2022-07-05 |
|               19 | 0.35999999999999943 | 2022-07-04 23:59:00 | 2022-07-04 |
|               20 |  0.3500000000000014 | 2022-07-03 23:59:00 | 2022-07-03 |
|               21 | 0.35999999999999943 | 2022-07-02 23:59:00 | 2022-07-02 |
|               22 | 0.35999999999999943 | 2022-07-01 23:59:00 | 2022-07-01 |
+---------------------+---------------------+---------------------+------------+

获取上月每天最后一条数据 {#获取上月每天最后一条数据}

SELECT
	a.id,
	a.data,
	a.create_time,
	b.date 
FROM
	table_name a
	JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) date, MAX( create_time ) max_time FROM table_name GROUP BY date ) b ON b.max_time = a.create_time 
WHERE
DATE_FORMAT( create_time, '%Y %m' ) = DATE_FORMAT(DATE_SUB( curdate(), INTERVAL 1 MONTH ),'%Y %m')
ORDER BY b.date DESC;
  • 结果
+------------+---------------------+-------------+
| project_id |         data	       | create_time |
+------------+---------------------+-------------+
|         28 |                   0 | 2022-07-14  |
|         28 |                   0 | 2022-07-13  |
|         28 |                   0 | 2022-07-12  |
|         28 | 0.35999999999999943 | 2022-07-11  |
|         28 | 0.35999999999999943 | 2022-07-10  |
|         28 | 0.35999999999999943 | 2022-07-09  |
|         28 |  0.3499999999999943 | 2022-07-08  |
|         28 |  0.3499999999999943 | 2022-07-07  |
|         28 | 0.35999999999999943 | 2022-07-06  |
|         28 |  0.3500000000000014 | 2022-07-05  |
|         28 | 0.35999999999999943 | 2022-07-04  |
|         28 |  0.3500000000000014 | 2022-07-03  |
|         28 | 0.35999999999999943 | 2022-07-02  |
|         28 | 0.35999999999999943 | 2022-07-01  |
+------------+---------------------+-------------+

获取本周每天最后一条数据 {#获取本周每天最后一条数据}

SELECT
	a.id,
	a.data,
	a.create_time,
	b.date 
FROM
	table_name a
	JOIN ( SELECT DATE_FORMAT( create_time, '%Y-%m-%d' ) date, MAX( create_time ) max_time FROM table_name GROUP BY date ) b ON b.max_time = a.create_time 
WHERE
	YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW());
ORDER BY
	b.date DESC
  • 结果
+---------------------+---------------------+---------------------+------------+
|      id          |       data          |      create_time    | date       |
+---------------------+---------------------+---------------------+------------+
|               13 | 0.35999999999999943 | 2022-07-10 23:59:00 | 2022-07-10 |
|               14 | 0.35999999999999943 | 2022-07-09 23:59:00 | 2022-07-09 |
|               15 |  0.3499999999999943 | 2022-07-08 23:59:00 | 2022-07-08 |
|               16 |  0.3499999999999943 | 2022-07-07 23:59:00 | 2022-07-07 |
|               17 | 0.35999999999999943 | 2022-07-06 23:59:00 | 2022-07-06 |
|               18 |  0.3500000000000014 | 2022-07-05 23:59:00 | 2022-07-05 |
|               19 | 0.35999999999999943 | 2022-07-04 23:59:00 | 2022-07-04 |
+---------------------+---------------------+---------------------+------------+

非日期统计查询 {#非日期统计查询}

查询表的数据量大小 {#查询表的数据量大小}

SELECT TABLE_NAME,( data_length + index_length )/ 1024 / 1024 AS TABLE_MB,
TABLE_ROWS 
FROM
	information_schema.`TABLES` 
WHERE
	TABLE_SCHEMA = 'database_name';
  • 结果
+--------------------------------------------+--------------+------------+
| TABLE_NAME                                 | TABLE_MB     | TABLE_ROWS |
+--------------------------------------------+--------------+------------+
| sys_role_menu                              | 0.04687500   |        465 |
| sys_user                                   | 0.01562500   |          4 |
| sys_user_post                              | 0.01562500   |         13 |
| sys_user_role                              | 0.01562500   |         26 |
+--------------------------------------------+--------------+------------+

查询阻塞语句 {#查询阻塞语句}

SELECT
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_Id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query 
FROM
	information_schema.innodb_lock_waits w
	INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
	INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

统计数据库访问量前 10 的 IP 地址 {#统计数据库访问量前 -10- 的 -IP- 地址}

SELECT
	SUBSTRING_INDEX( HOST, ':', 1 ) AS ip,
	COUNT(*) 
FROM
	information_schema.PROCESSLIST 
GROUP BY
	ip 
ORDER BY
	COUNT(*) DESC 
	LIMIT 10;
  • 结果
+---------------+----------+
| ip            | COUNT(*) |
+---------------+----------+
| 1.74.3.122    |       33 |
| 113.51.32.63  |        9 |
| 8.132.10.2    |        2 |
+---------------+----------+

赞(0)
未经允许不得转载：工具盒子 » 【MySQL 系列】MySQL 按照当前年月周日统计数据
标签：

厉飞雨

众生皆苦，唯有自渡！

相关推荐

最新文章

猜你喜欢

快捷分类

云服务器 日常运维 在线免费 开源工具 科学上网 开发笔记 区块链 白嫖帮 新视野 人工智能 网赚思路 Python笔记 Php笔记 Java笔记 链知识 币知识 IOS笔记 Android笔记 Mysql C语言笔记 前端开发 R笔记 Go笔记 mybatis笔记 Golang笔记 数据库 市场营销 软件教程 vps Rust笔记 软件使用 经验分享 Stable Diffusion chatgpt Midjourney 操作系统 Photoscape 加密货币 挖矿 Coze Meta 网络安全 网络运营 开源软件 Github ComfyUI 数字人 Docker笔记 Claude Oracle web3 linux windows mac redis git笔记 nginx kafka maven spring RocketMQ Zabbix ubuntu centos kubesphere ffmpeg jenkins hbase JavaScript笔记 WordPress nginx Hadoop vuejs mongo SQL Server PostgreSQL SQLite ElasticSearch Spark netty zookeeper RabbitMQ ClickHoust cocos2d gradle tomcat Markdown MinIO react Hexo git intellij idea android studio svn C++ spdlog nodejs C WordPress 宝塔面板 vim shell logstash 软件渗透 NeoDB CSS3 HTML Lua笔记 数据算法 嵌入式 debian 红帽RHEL Neo4j jquery nas 1panel aapanel 浏览器 谷歌 .net 微信 telegram Cloudflare 大数据 rust layui JumpServer DataEase TiDB DM数据库 虚拟机 elk sqlmap nacos Firefox C# Swagger Foxmail