mysql - 按周和月获取一天的平均小时数
问题描述
我坚持这一点，我不知道该怎么做。
我有这张桌子
create table events(event_datetime datetime,event_code varchar(40),val varchar(40));
insert into events (event_datetime, event_code, val)
values ('2018-01-01 06:00:00', '50', '1'),
('2018-01-01 07:00:00', '54', null),
('2018-01-01 11:00:00', '50', '2'),
('2018-01-01 13:00:00', '54', null),
('2018-01-02 07:00:00', '50', '1'),
('2018-01-02 23:00:00', '54', null),
('2018-01-24 07:00:00', '50', '1'),
('2018-01-25 23:00:00', '54', null),
('2018-02-02 10:00:00', '50', '1'),
('2018-02-02 12:00:00', '54',null)
Event_code 50 表示事件开始。Event_code 54 表示事件停止。
我想总结一天 54 到 50 之间的小时数（有时一天有不止一个 50-54 小时）
比我想做 1 周的 AVG 天数，四次。
例如上面第 1 周的数据：
2018-01-01 07:00:00 - 2018-01-01 06:00:00 = 1hour
2018-01-01 13:00:00 - 2018-01-01 11:00:00 = 2hours
2018-01-02 23:00:00 - 2018-01-02 07:00:00 = 16hours
Day 1 = 3hours
Day 2 = 16hours
Day 3-7 = 0hours
Week 1 = AVG(days) = 19/7
输出将类似于：
| Month | Week 1 | Week 2 | Week 3 | Week 4 |
|---------|------------|------------|-------------|------------|
| Jan |avghours/day|avghours/day|avghours/day |avghours/day|
| Feb |avghours/day|avghours/day|avghours/day |avghours/day|
编辑：
事件 50 总是紧跟在 54 之后。
事件永远不会持续超过一天。
编辑 2：
我创建了一个存储过程：
DELIMITER #
CREATE PROCEDURE tablediff()
BEGIN
DECLARE done int default false;
DECLARE v_day DATETIME;
DECLARE v_code VARCHAR(40);
DECLARE v_day_1 DATETIME;
DECLARE v_code_1 VARCHAR(40);
DECLARE start_time DATETIME;
DECLARE stop_time DATETIME;
DECLARE code VARCHAR(40);
DECLARE duration TIME;
DECLARE cur1 CURSOR FOR
SELECT event_datetime, event_code as code, event_param_1
FROM events
AND event_code =50
ORDER BY event_datetime ASC;
DECLARE cur2 CURSOR FOR
SELECT event_datetime, event_code as code, event_param_1
FROM events
AND event_code = 50
ORDER BY event_datetime ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
CREATE TEMPORARY TABLE tmp
(
start_time DATETIME;
stop_time DATETIME;
code VARCHAR(40);
duration TIME;
);
FETCH cur2 INTO v_day_1, v_code_1;
forLoop: LOOP
FETCH cur1 INTO v_day, v_code;
FETCH cur2 INTO v_day_1, v_code_1;
CASE
WHEN v_code = 50 THEN
IF v_code_1 = 54 THEN
SET start_time = v_day;
SET stop_time = v_day_1;
SET code = v_code;
SET duration = duration + TIMEDIFF(stop_time, start_time);
ELSE
SET start_time = null;
SET stop_time = null;
SET code = null;
SET duration = duration;
END IF;
END CASE;
INSERT INTO tmp VALUES (start_time, stop_time, code, duration);
IF done
THEN
LEAVE forLoop;
END IF;
END LOOP;
CLOSE cur1;
CLOSE cur2;
END#
即使没有 54，它也可以显示开始和停止时间，但我需要进行时间计算，持续时间还不起作用，它在每一行显示为空。
解决方案
代码后的 ID 字段（作为键）和事件标识字段将是理想的。
如果这是不可能的，一个函数会帮助你：
DELIMITER $$
CREATE FUNCTION event_stoptime (start_time datetime)
RETURNS datetime
BEGIN
DECLARE stop_time datetime;
SET stop_time = (SELECT MIN(event_datetime) FROM events WHERE
event_datetime > start_time);
RETURN stop_time;
END $$
DELIMITER ;
构建函数后，您可以将其与 MySQL 日期函数结合使用以获取所需的数据：
SELECT event_datetime as event_start,
event_stoptime(event_datetime) as event_stop,
TIMEDIFF(event_stoptime(event_datetime), event_datetime) as event_duration,
WEEK(event_datetime) as event_week,
MONTH(event_datetime) as event_month
from events
where event_code = 50;
我应该得到的结果是
| event_start | event_stop | duration | week| month|
'2018-01-01 06:00:00', '2018-01-01 07:00:00', '01:00:00', '0', '1'
'2018-01-01 11:00:00', '2018-01-01 13:00:00', '02:00:00', '0', '1'
'2018-01-02 07:00:00', '2018-01-02 23:00:00', '16:00:00', '0', '1'
'2018-01-24 07:00:00', '2018-01-25 23:00:00', '40:00:00', '3', '1'
'2018-02-02 10:00:00', '2018-02-02 12:00:00', '02:00:00', '4', '2'
GREAT BIG CAVEAT：如果给定的 50 没有对应的 54 事件（由于错误等），则时间将严重倾斜。
这是使用 ID 字段的更好解决方案。为了便于测试，我在插入语句中包含了 ID：
CREATE TABLE `events` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`event_datetime` datetime DEFAULT NULL,
`event_code` varchar(40) DEFAULT NULL,
`val` varchar(40) DEFAULT NULL,
`event_id` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
);
INSERT INTO `events`
VALUES (1,'2018-01-01 06:00:00','50','1',NULL),
(2,'2018-01-01 07:00:00','54',NULL,1),
(3,'2018-01-01 11:00:00','50','2',NULL),
(4,'2018-01-01 13:00:00','54',NULL,3),
(5,'2018-01-01 09:00:00','50',NULL,NULL),
(6,'2018-01-02 23:00:00','54',NULL,5),
(7,'2018-01-24 07:00:00','50','1',NULL),
(8,'2018-01-25 23:00:00','54','3',7),
(9,'2018-02-02 10:00:00','50','1',NULL),
(10,'2018-02-02 12:00:00','54',NULL,9),
(11,'2018-01-30 23:00:00','54','3',NULL);
视图将表连接到自身以选择正确的“54”到“50”。请注意创建日期以查找该月的第一周：
CREATE VIEW `event_list` AS
select `events`.`ID` AS `ID`,`events`.`event_datetime` AS `event_start`,
`end_events`.`event_datetime` AS `event_stop`,
timediff(`end_events`.`event_datetime`,`events`.`event_datetime`) AS `event_duration`,
week(`events`.`event_datetime`) AS `event_week`,
month(`events`.`event_datetime`) AS `month_number`,
monthname(`events`.`event_datetime`) AS `event_month`,
WEEK(STR_TO_DATE(concat(year(events.event_datetime), '-', month(events.event_datetime), '-', '01'), '%Y-%m-%d')) as first_week
from (`events` left join `events` `end_events` on((`events`.`ID` = `end_events`.`event_id`)))
where (`events`.`event_code` = '50');
select 语句使用 IF 为周摘要选择正确的数据。将时间分解为秒以计算持续时间，然后重新组合以显示；
select event_month,
sec_to_time(sum(time_to_sec(if(event_week = first_week, event_duration, 0)))) as week_one,
sec_to_time(sum(time_to_sec(if(event_week = first_week + 1, event_duration, 0)))) as week_two,
sec_to_time(sum(time_to_sec(if(event_week = first_week + 2, event_duration, 0)))) as week_three,
sec_to_time(sum(time_to_sec(if(event_week = first_week + 3, event_duration, 0)))) as week_four,
sec_to_time(sum(time_to_sec(if(event_week = first_week + 4, event_duration, 0)))) as week_five
from event_list
group by event_month
order by month_number
不优雅，但它有效。
推荐阅读
- html - 从access数据库中读取数据并显示在html网页中
- python - 为什么使用多个条件进行过滤不起作用？
- html - 圆形图像作为html中的按钮
- powerbi - 如何在 Power Bi/DAX 中对代表在每个日期接听的电话总数进行排名？
- sql-server - Laravel MS SQL DB::RAW 查询返回“无效的列名”
- azure - Powershell 脚本继续要求我使用 Select-AzureSubscription 虽然我已经调用它
- swift - 唯一路径 II - DFS 方法
- java - Maven多模块依赖编译问题
- r - 当列存储在字符向量中时如何使用 cbind()？
- c++ - 如何在 Visual Studio 代码中启用 C++ 语言支持？