mysql - 如何在过去 7 天内获得每小时的条目数?
问题描述
我正在为 MySQL 中的某个查询而苦苦挣扎。我已经尝试了一切!如果有人能指出我正确的方向,我将永远感激不尽。我的目标是生成一个查询,显示过去 7 天每小时的请求数。我遇到的问题之一是显示当天的名称,而不管数据库中是否已经存在这一天。换句话说,我希望星期天、星期一、星期二、星期三、星期四、星期五和星期六已经存在,如果没有特定日期的数据,则显示 0,如示例中所示。我希望查询的输出如下所示:
我希望查询输出的示例
我试过这个:
SELECT
HOUR(`VISIT_TIME`),
COUNT(*)
FROM
`JURL_Analytics`
WHERE
`VISIT_TIME` >= DATE(NOW()) - INTERVAL 7 DAY AND `URL_ID` = 'westonsupermare'
GROUP BY
HOUR(`VISIT_TIME`);
这是数据库结构的示例
`VISIT_ID`, `VISIT_TIME`
--------------------------
705, '2020-08-26 15:20:26'
704, '2020-08-26 13:19:08'
703, '2020-08-25 21:40:22'
702, '2020-08-25 17:34:29'
701, '2020-08-25 17:34:23'
700, '2020-08-25 10:12:39'
699, '2020-08-25 09:26:57'
698, '2020-08-25 09:26:57'
697, '2020-08-25 09:26:32'
696, '2020-08-25 09:22:43'
695, '2020-08-25 09:18:58'
694, '2020-08-25 09:12:25'
693, '2020-08-24 16:18:45'
692, '2020-08-24 14:22:20'
691, '2020-08-24 14:19:39'
690, '2020-08-24 14:19:03'
689, '2020-08-24 13:48:00'
688, '2020-08-24 06:21:50'
687, '2020-08-23 20:22:26'
686, '2020-08-23 19:55:26'
685, '2020-08-23 19:54:09'
684, '2020-08-23 07:34:47'
683, '2020-08-22 23:28:29'
682, '2020-08-22 13:24:44'
681, '2020-08-22 09:56:58'
680, '2020-08-22 06:54:50'
679, '2020-08-22 06:53:37'
678, '2020-08-22 03:13:39'
677, '2020-08-22 03:11:30'
676, '2020-08-21 16:47:31'
675, '2020-08-20 18:13:02'
674, '2020-08-20 17:28:48'
673, '2020-08-20 10:57:27'
672, '2020-08-19 15:21:29'
671, '2020-08-19 14:57:21'
670, '2020-08-19 10:25:26'
669, '2020-08-18 18:53:22'
668, '2020-08-18 18:04:46'
667, '2020-08-18 18:00:13'
666, '2020-08-18 17:59:59'
665, '2020-08-18 14:35:04'
664, '2020-08-18 11:49:22'
663, '2020-08-18 11:43:47'
662, '2020-08-18 11:25:02'
661, '2020-08-18 08:07:09'
660, '2020-08-18 07:57:51'
659, '2020-08-18 01:09:30'
658, '2020-08-17 21:43:36'
657, '2020-08-17 21:43:07'
656, '2020-08-17 20:53:50'
655, '2020-08-17 20:15:12'
654, '2020-08-17 18:08:16'
653, '2020-08-17 18:02:36'
652, '2020-08-17 17:36:27'
651, '2020-08-17 16:57:07'
650, '2020-08-17 16:43:29'
649, '2020-08-17 16:11:44'
648, '2020-08-17 15:58:23'
如果您希望复制数据库以有效地生成查询,这里是表创建和一些示例数据。
CREATE TABLE `JURL_Analytics` (
`VISIT_ID` int(8) NOT NULL,
`VISIT_TIME` timestamp NOT NULL DEFAULT current_timestamp(),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `JURL_Analytics` (`VISIT_ID`, `VISIT_TIME`) VALUES
(705, '2020-08-26 15:20:26'),
(704, '2020-08-26 13:19:08'),
(703, '2020-08-25 21:40:22'),
(702, '2020-08-25 17:34:29'),
(701, '2020-08-25 17:34:23'),
(700, '2020-08-25 10:12:39'),
(699, '2020-08-25 09:26:57'),
(698, '2020-08-25 09:26:57'),
(697, '2020-08-25 09:26:32'),
(696, '2020-08-25 09:22:43'),
(695, '2020-08-25 09:18:58'),
(694, '2020-08-25 09:12:25'),
(693, '2020-08-24 16:18:45'),
(692, '2020-08-24 14:22:20'),
(691, '2020-08-24 14:19:39'),
(690, '2020-08-24 14:19:03'),
(689, '2020-08-24 13:48:00'),
(688, '2020-08-24 06:21:50'),
(687, '2020-08-23 20:22:26'),
(686, '2020-08-23 19:55:26'),
(685, '2020-08-23 19:54:09'),
(684, '2020-08-23 07:34:47'),
(683, '2020-08-22 23:28:29'),
(682, '2020-08-22 13:24:44'),
(681, '2020-08-22 09:56:58'),
(680, '2020-08-22 06:54:50'),
(679, '2020-08-22 06:53:37'),
(678, '2020-08-22 03:13:39'),
(677, '2020-08-22 03:11:30'),
(676, '2020-08-21 16:47:31'),
(675, '2020-08-20 18:13:02'),
(674, '2020-08-20 17:28:48'),
(673, '2020-08-20 10:57:27'),
(672, '2020-08-19 15:21:29'),
(671, '2020-08-19 14:57:21'),
(670, '2020-08-19 10:25:26'),
(669, '2020-08-18 18:53:22'),
(668, '2020-08-18 18:04:46'),
(667, '2020-08-18 18:00:13'),
(666, '2020-08-18 17:59:59'),
(665, '2020-08-18 14:35:04'),
(664, '2020-08-18 11:49:22'),
(663, '2020-08-18 11:43:47'),
(662, '2020-08-18 11:25:02'),
(661, '2020-08-18 08:07:09'),
(660, '2020-08-18 07:57:51'),
(659, '2020-08-18 01:09:30'),
(658, '2020-08-17 21:43:36'),
(657, '2020-08-17 21:43:07'),
(656, '2020-08-17 20:53:50'),
(655, '2020-08-17 20:15:12'),
(654, '2020-08-17 18:08:16')
解决方案
这是你要找的东西吗?
SELECT
DATE(`VISIT_TIME`) as 'Date',
SUM(IF(HOUR(`VISIT_TIME`) = 0, 1,0)) as '00:00 - 00:59',
SUM(IF(HOUR(`VISIT_TIME`) = 1, 1,0)) as '01:00 - 01:59',
SUM(IF(HOUR(`VISIT_TIME`) = 2, 1,0)) as '02:00 - 02:59',
SUM(IF(HOUR(`VISIT_TIME`) = 3, 1,0)) as '03:00 - 03:59',
SUM(IF(HOUR(`VISIT_TIME`) = 4, 1,0)) as '04:00 - 04:59',
SUM(IF(HOUR(`VISIT_TIME`) = 5, 1,0)) as '05:00 - 05:59',
SUM(IF(HOUR(`VISIT_TIME`) = 6, 1,0)) as '06:00 - 06:59',
SUM(IF(HOUR(`VISIT_TIME`) = 7, 1,0)) as '07:00 - 07:59',
SUM(IF(HOUR(`VISIT_TIME`) = 8, 1,0)) as '08:00 - 08:59',
SUM(IF(HOUR(`VISIT_TIME`) = 9, 1,0)) as '09:00 - 09:59',
SUM(IF(HOUR(`VISIT_TIME`) = 10, 1,0)) as '10:00 - 10:59',
SUM(IF(HOUR(`VISIT_TIME`) = 11, 1,0)) as '11:00 - 11:59',
SUM(IF(HOUR(`VISIT_TIME`) = 12, 1,0)) as '12:00 - 12:59',
SUM(IF(HOUR(`VISIT_TIME`) = 13, 1,0)) as '13:00 - 13:59',
SUM(IF(HOUR(`VISIT_TIME`) = 14, 1,0)) as '14:00 - 14:59',
SUM(IF(HOUR(`VISIT_TIME`) = 15, 1,0)) as '15:00 - 15:59',
SUM(IF(HOUR(`VISIT_TIME`) = 16, 1,0)) as '16:00 - 16:59',
SUM(IF(HOUR(`VISIT_TIME`) = 17, 1,0)) as '17:00 - 17:59',
SUM(IF(HOUR(`VISIT_TIME`) = 18, 1,0)) as '18:00 - 18:59',
SUM(IF(HOUR(`VISIT_TIME`) = 19, 1,0)) as '19:00 - 19:59',
SUM(IF(HOUR(`VISIT_TIME`) = 20, 1,0)) as '20:00 - 20:59',
SUM(IF(HOUR(`VISIT_TIME`) = 21, 1,0)) as '21:00 - 21:59',
SUM(IF(HOUR(`VISIT_TIME`) = 22, 1,0)) as '22:00 - 22:59',
SUM(IF(HOUR(`VISIT_TIME`) = 23, 1,0)) as '23:00 - 23:59'
FROM `JURL_Analytics`
GROUP BY DATE(`VISIT_TIME`);
数据的结果:MariaDB [bernd]>
SELECT
-> DATE(`VISIT_TIME`) as 'Date',
-> SUM(IF(HOUR(`VISIT_TIME`) = 0, 1,0)) as '00:00 - 00:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 1, 1,0)) as '01:00 - 01:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 2, 1,0)) as '02:00 - 02:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 3, 1,0)) as '03:00 - 03:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 4, 1,0)) as '04:00 - 04:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 5, 1,0)) as '05:00 - 05:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 6, 1,0)) as '06:00 - 06:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 7, 1,0)) as '07:00 - 07:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 8, 1,0)) as '08:00 - 08:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 9, 1,0)) as '09:00 - 09:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 10, 1,0)) as '10:00 - 10:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 11, 1,0)) as '11:00 - 11:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 12, 1,0)) as '12:00 - 12:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 13, 1,0)) as '13:00 - 13:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 14, 1,0)) as '14:00 - 14:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 15, 1,0)) as '15:00 - 15:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 16, 1,0)) as '16:00 - 16:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 17, 1,0)) as '17:00 - 17:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 18, 1,0)) as '18:00 - 18:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 19, 1,0)) as '19:00 - 19:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 20, 1,0)) as '20:00 - 20:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 21, 1,0)) as '21:00 - 21:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 22, 1,0)) as '22:00 - 22:59',
-> SUM(IF(HOUR(`VISIT_TIME`) = 23, 1,0)) as '23:00 - 23:59'
->
-> FROM `JURL_Analytics`
-> GROUP BY DATE(`VISIT_TIME`);
+------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| Date | 00:00 - 00:59 | 01:00 - 01:59 | 02:00 - 02:59 | 03:00 - 03:59 | 04:00 - 04:59 | 05:00 - 05:59 | 06:00 - 06:59 | 07:00 - 07:59 | 08:00 - 08:59 | 09:00 - 09:59 | 10:00 - 10:59 | 11:00 - 11:59 | 12:00 - 12:59 | 13:00 - 13:59 | 14:00 - 14:59 | 15:00 - 15:59 | 16:00 - 16:59 | 17:00 - 17:59 | 18:00 - 18:59 | 19:00 - 19:59 | 20:00 - 20:59 | 21:00 - 21:59 | 22:00 - 22:59 | 23:00 - 23:59 |
+------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
| 2020-08-17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 | 2 | 0 | 0 |
| 2020-08-18 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 3 | 0 | 0 | 1 | 0 | 0 | 1 | 3 | 0 | 0 | 0 | 0 | 0 |
| 2020-08-19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2020-08-20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2020-08-21 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2020-08-22 | 0 | 0 | 0 | 2 | 0 | 0 | 2 | 0 | 0 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| 2020-08-23 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 1 | 0 | 0 | 0 |
| 2020-08-24 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2020-08-25 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 1 | 0 | 0 |
| 2020-08-26 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------+
10 rows in set (0.01 sec)
MariaDB [bernd]>
抱歉:我的查询中没有 WHERE
WHERE
`VISIT_TIME` >= DATE(NOW()) - INTERVAL 7 DAY AND `URL_ID` = 'westonsupermare'
推荐阅读
- python - 使用正则表达式从熊猫数据框python中删除在单个列的所有行中找到的唯一单词
- mysql - 根据另一个数据点是否不为空来更新MYSQL数据点?
- java - 基于两个变量创建请求并委托它的最优雅方式
- sql - 插入同一张表的 SQL IF 语句
- go - 如何在一个关键部分同步多个 goroutine?
- python - Python Selenium:如何在页面不显示为“未找到”、“禁止访问”或“权限被拒绝”的情况下转到谷歌搜索 URL
- excel - getElementById - 远程服务器机器不存在或不可用
- node.js - 通过玩笑模拟 jsforce SObject
- java - 如何将带有接口的java代码转换为kotlin,
- smartsheet-api - 如何使用 JAVA 在 SmartSheet 中获取整数的数量