一、准备数据
1、创建表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for data_report
-- ----------------------------
DROP TABLE IF EXISTS `data_report`;
CREATE TABLE `data_report` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`app_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '租户ID',
`record_num` int(11) NULL DEFAULT NULL COMMENT '记录条数',
`create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`) USING BTREE,
INDEX `appId`(`app_id`, `create_time`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2646 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '数据报表' ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
2、表中填充数据
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (1, 'P15942995247454', 1, '2023-05-04 11:11:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (24, 'P15942995247454', 1, '2023-05-04 12:12:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (47, 'P15942995247454', 1, '2023-05-04 13:13:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (70, 'P15942995247454', 1, '2023-05-04 14:14:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (93, 'P15942995247454', 1, '2023-05-04 15:15:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (116, 'P15942995247454', 10, '2023-05-04 16:16:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (139, 'P15942995247454', 1, '2023-05-04 17:17:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (162, 'P15942995247454', 1, '2023-05-04 18:18:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (185, 'P15942995247454', 1, '2023-05-04 19:19:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (208, 'P15942995247454', 1, '2023-05-04 20:20:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (231, 'P15942995247454', 1, '2023-05-04 21:21:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (254, 'P15942995247454', 1, '2023-05-04 22:22:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (277, 'P15942995247454', 1, '2023-05-04 23:23:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (300, 'P15942995247454', 1, '2023-05-05 00:24:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (323, 'P15942995247454', 1, '2023-05-05 01:25:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (346, 'P15942995247454', 1, '2023-05-05 02:26:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (369, 'P15942995247454', 1, '2023-05-05 03:27:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (392, 'P15942995247454', 1, '2023-05-05 04:28:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (415, 'P15942995247454', 1, '2023-05-05 05:29:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (438, 'P15942995247454', 1, '2023-05-05 06:30:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (461, 'P15942995247454', 1, '2023-05-05 07:31:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (484, 'P15942995247454', 1, '2023-05-05 08:32:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (507, 'P15942995247454', 1, '2023-05-05 09:33:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (530, 'P15942995247454', 1, '2023-05-05 10:34:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (553, 'P15942995247454', 1, '2023-05-05 11:35:00');
二、获取前24小时每小时数据
你可以使用以下 SQL 查询语句来获取 MySQL 数据库中当前前24小时每小时的数据:
SELECT
DATE_FORMAT( create_time, '%Y-%m-%d %H:00:00' ) AS HOUR,
COUNT(*) AS count
FROM
data_report
WHERE
create_time>= DATE_SUB( NOW(), INTERVAL 24 HOUR )
GROUP BY
HOUR;
这个查询语句会将 data_report
表中过去24小时内每个小时的数据进行统计,并按照小时分组。其中,create_time
是你表中记录创建时间的字段名,你需要将其替换成实际的字段名。DATE_FORMAT
函数用于格式化时间,将分钟和秒钟部分都设置为0,只保留小时部分。COUNT
函数用于统计每个小时内的数据量。
执行这个查询语句后,你会得到一个结果集,其中包含了过去24小时内每个小时的数据量。
三、获取24小时内的每分钟数据总数
要获取 MySQL 数据库中当前前24小时每分钟的数据,可以使用以下 SQL 查询语句:文章来源:https://www.toymoban.com/news/detail-434700.html
SELECT
DATE_FORMAT( create_time, '%Y-%m-%d %H:%i:00' ) AS MINUTE,
COUNT(*) AS count
FROM
data_report
WHERE
create_time>= DATE_SUB( NOW(), INTERVAL 24 HOUR )
GROUP BY
MINUTE;
这个查询语句与前面的查询语句类似,只是将时间格式化的方式改为了保留分钟部分,并且将分组的单位改为了分钟。执行这个查询语句后,你会得到一个结果集,其中包含了过去24小时内每分钟的数据量。文章来源地址https://www.toymoban.com/news/detail-434700.html
到了这里,关于MySQL 获取前24小时每小时数据总数的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!