안녕하세요.
mysql sql문을 고민하다가 도저히 해결이 되지 않아 도움을 요청 드립니다.
입력 데이터 테이블이 아래와 같이 구성 되어 있스며
해당 데이터를 쿼리하여 출력 데이터 테이블을 만들려고 합니다.
-입력 데이터-
seq | id | start | end | reg_date |
1289334 | 1001002658 | 2018-11-14 19:45 | 2018-11-16 2:00 |
2018-11-14 19:46 |
-출력 데이터-
시작일과 종료일 사이의 이용 일자와 해당 일자의 사용 시간(min분),해당일자의 주말,평일 구분자(week_type 1은 평일,2는 주말)
id | week_type | 0시 | 1시 | 2시 | 3시 | 4시 | 5시 | 6시 | 7시 | 8시 | 9시 | 10시 | 11시 | 12시 | 13시 | 14시 | 15시 | 16시 | 17시 | 18시 | 19시 | 20시 | 21시 | 22시 | 23시 | 사용일 |
1001002658 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 14 | 60 | 60 | 60 | 60 | 2018-11-14 |
1001002658 | 1 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 60 | 2018-11-15 |
1001002658 | 1 | 60 | 60 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2018-11-16 |
WITH data_t AS ( SELECT 1289334 seq , 1001002658 id , CAST('2018-11-14 19:45' AS DATETIME) start , CAST('2018-11-16 02:00' AS DATETIME) end , CAST('2018-11-14 19:46' AS DATETIME) reg_date ) , copy_t AS ( SELECT 0 lv UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 ) SELECT id , dt , CASE WHEN DAYOFWEEK(dt) IN (1, 7) THEN 2 ELSE 1 END weektype , IFNULL(SUM(CASE h WHEN 0 THEN mi END), 0) h00 , IFNULL(SUM(CASE h WHEN 1 THEN mi END), 0) h01 , IFNULL(SUM(CASE h WHEN 2 THEN mi END), 0) h02 , IFNULL(SUM(CASE h WHEN 3 THEN mi END), 0) h03 , IFNULL(SUM(CASE h WHEN 4 THEN mi END), 0) h04 , IFNULL(SUM(CASE h WHEN 5 THEN mi END), 0) h05 , IFNULL(SUM(CASE h WHEN 6 THEN mi END), 0) h06 , IFNULL(SUM(CASE h WHEN 7 THEN mi END), 0) h07 , IFNULL(SUM(CASE h WHEN 8 THEN mi END), 0) h08 , IFNULL(SUM(CASE h WHEN 9 THEN mi END), 0) h09 , IFNULL(SUM(CASE h WHEN 10 THEN mi END), 0) h10 , IFNULL(SUM(CASE h WHEN 11 THEN mi END), 0) h11 , IFNULL(SUM(CASE h WHEN 12 THEN mi END), 0) h12 , IFNULL(SUM(CASE h WHEN 13 THEN mi END), 0) h13 , IFNULL(SUM(CASE h WHEN 14 THEN mi END), 0) h14 , IFNULL(SUM(CASE h WHEN 15 THEN mi END), 0) h15 , IFNULL(SUM(CASE h WHEN 16 THEN mi END), 0) h16 , IFNULL(SUM(CASE h WHEN 17 THEN mi END), 0) h17 , IFNULL(SUM(CASE h WHEN 18 THEN mi END), 0) h18 , IFNULL(SUM(CASE h WHEN 19 THEN mi END), 0) h19 , IFNULL(SUM(CASE h WHEN 20 THEN mi END), 0) h20 , IFNULL(SUM(CASE h WHEN 21 THEN mi END), 0) h21 , IFNULL(SUM(CASE h WHEN 22 THEN mi END), 0) h22 , IFNULL(SUM(CASE h WHEN 23 THEN mi END), 0) h23 FROM (SELECT id , dt , h , SUBSTR(TIMEDIFF(e, s), 1, 2) * 60 + SUBSTR(TIMEDIFF(e, s), 4, 2) AS mi FROM (SELECT a.id , c.lv h , CAST(start AS DATE) + INTERVAL b.lv DAY dt , GREATEST(CAST(start AS DATE) + INTERVAL b.lv DAY + INTERVAL c.lv HOUR, start) s , LEAST (CAST(start AS DATE) + INTERVAL b.lv DAY + INTERVAL c.lv + 1 HOUR, end ) e FROM data_t a , copy_t b , copy_t c WHERE b.lv <= DATEDIFF(end, start) ) a WHERE s < e ) a GROUP BY id, dt ;