출처1 : https://dba.stackexchange.com/questions/169186/mysql-MERGE-OR-split-DATETIME-intervals-START-DATE-TO-END-DATE

출처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;





Posted by motolies

댓글을 달아 주세요