꿈꾸는 개발자, DBA 커뮤니티 구루비

시작일 종료일 사이의 점유일 및 시간 계산

by 락군 [MySQL] mysql 시작일종료일 [2019.02.18 11:38:37]

안녕하세요.

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

by 마농 [2019.02.18 15:20:25]

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
;

by 락군 [2019.02.18 17:14:04]

감사합니다.!!

by 마농 [2019.02.18 17:46:19]

with 부분만 빼면 됩니다.
data_t 는 원래 테이블 이용하면 되구요.
copy_t 는 인라인뷰로 이용하시면 되구요.
아예 copy_t 를 하나 미리 만들어 두고 사용하시면 편리합니다.

by 락군 [2019.02.18 17:59:21]

copy_t 테이블 생성해서 성공 했습니다!!

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변