프로그램 자료/MySQL & MariaDB
[MySQL & MariaDB] 시간 합치기 Merging StartTime EndTime
motolies
2019. 11. 18. 15:55
출처2 : https://rextester.com/EIJOI20983
아래와 같은 데이터를 병합해서 보여주고 싶을 때가 있다.
Id 1, 2를 보면 1번 안에 2번이 포함된다.
원하는 결과물은 아래와 같다.
다음과 같이 테스트를 해보자.
-- make test data
DROP TABLE IF EXISTS LogTable;
CREATE TABLE IF NOT EXISTS LogTable (
Id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
UserId VARCHAR(45),
StartTime DATETIME,
EndTime DATETIME
);
INSERT INTO LogTable (UserId, StartTime, EndTime) VALUES
('me','2017-04-03 11:00','2017-04-03 11:30'),
('me','2017-04-03 11:10','2017-04-03 11:15'),
('me','2017-04-03 23:45','2017-04-04 00:15'),
('me','2017-04-04 10:00','2017-04-04 11:00'),
('me','2017-04-04 10:30','2017-04-04 11:30'),
('me','2017-04-05 23:00','2017-04-05 23:30'),
('me','2017-04-05 23:15','2017-04-07 00:45'),
('you','2017-04-05 21:00','2017-04-05 23:30');
-- start end merge
SELECT UserId, MIN(StartTime) AS StartTime, EndTime, TIMESTAMPDIFF(MINUTE, MIN(StartTime), EndTime) AS TIME
FROM (
SELECT x.UserId, x.StartTime, MIN(y.EndTime) AS EndTime
FROM LogTable AS X
JOIN LogTable AS Y
ON x.UserId = y.UserId
AND x.StartTime <= y.EndTime
AND NOT EXISTS (
SELECT 1
FROM LogTable AS z
WHERE y.UserId = z.UserId
AND y.EndTime >= z.StartTime
AND y.EndTime < z.EndTime
-- 추가적으로 그룹바이 조건을 넣고 싶다면(1)
)
WHERE NOT EXISTS (
SELECT 1
FROM LogTable AS u
WHERE x.UserId = u.UserId
AND x.StartTime > u.StartTime
AND x.StartTime <= u.StartTime
-- 추가적으로 그룹바이 조건을 넣고 싶다면(2)
)
-- 정리하고 싶은 데이터에 조건을 넣고 싶다면 (and 를 붙이고 조건 추가)
-- AND ...
-- 추가적으로 그룹바이 조건을 넣고 싶다면(3) Group by 순서에 맞게 기술
GROUP BY x.UserId, x.StartTime
) AS v GROUP BY UserId, EndTime;