MySQL 查询七天每天的数据总数

---

一、准备数据

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-04-25 11:11:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (24, 'P15942995247454', 1, '2023-04-26 12:12:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (47, 'P15942995247454', 1, '2023-04-27 13:13:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (70, 'P15942995247454', 1, '2023-04-28 14:14:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (93, 'P15942995247454', 1, '2023-04-29 15:15:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (116, 'P15942995247454', 10, '2023-4-30 16:16:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (139, 'P15942995247454', 1, '2023-05-01 17:17:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (162, 'P15942995247454', 1, '2023-05-02 18:18:00');
INSERT INTO `ice`.`data_report` (`id`, `app_id`, `record_num`, `create_time`) VALUES (185, 'P15942995247454', 1, '2023-05-03 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');

二、获取7天内每天的数据总和

要获取 MySQL 数据库中前7天每天的数据，可以使用以下 SQL 查询语句：

SELECT COALESCE
	( d.app_id, 'P15942995247454' ) AS app_id,
	COALESCE ( SUM( d.record_num ), 0 ) record_num,
	a.day_count AS day_count 
FROM
	(
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 6 DAY ), '%Y-%m-%d' ) AS day_count UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 5 DAY ), '%Y-%m-%d' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 4 DAY ), '%Y-%m-%d' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 3 DAY ), '%Y-%m-%d' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 2 DAY ), '%Y-%m-%d' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 DAY ), '%Y-%m-%d' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 0 DAY ), '%Y-%m-%d' ) 
	) a
	LEFT JOIN data_report d ON a.day_count = DATE_FORMAT( d.create_time, '%Y-%m-%d' ) 
	AND d.app_id = 'P15942995247454' 
GROUP BY
	day_count 
ORDER BY
	day_count ASC;

这个查询语句会将数据按照日期进行分组，并统计每个日期内的数据量。执行这个查询语句后，你会得到一个结果集，其中包含了过去7天内每天的数据量。

如果要获取每分钟的数据，可以使用类似的查询语句，只需要将时间格式化的方式改为保留分钟部分，并将分组的单位改为分钟即可。

三、获取24小时内的数据

要获取 MySQL 数据库中前24小时内的数据，可以使用以下 SQL 查询语句：

SELECT COALESCE
	( d.app_id, 'P15942995247454' ) AS app_id,
	COALESCE ( SUM( d.record_num ), 0 ) record_num,
	a.hour_count AS hour_count 
FROM
	(
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 24 HOUR ), '%Y-%m-%d %H:00:00' ) AS hour_count UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 23 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 22 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 21 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 20 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 19 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 18 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 17 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 16 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 15 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 14 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 13 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 12 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 11 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 10 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 9 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 8 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 7 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 6 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 5 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 4 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 3 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 2 HOUR ), '%Y-%m-%d %H:00:00' ) UNION ALL
	SELECT
		DATE_FORMAT( DATE_SUB( NOW(), INTERVAL 1 HOUR ), '%Y-%m-%d %H:00:00' ) 
	) a
	LEFT JOIN data_report d ON a.hour_count = DATE_FORMAT( d.create_time, '%Y-%m-%d %H:00:00' ) 
	AND d.app_id = 'P15942995247454' 
GROUP BY
	hour_count 
ORDER BY
	hour_count ASC;

这个查询语句与前面的查询语句类似，只是将时间格式化的方式改为了保留分钟部分