데이터베이스 질문과 답변

MySQL의 열과 행 합치기 0 1 690

by 도리비 [SQL Query] [2020.11.13 20:25:05]

15분에 한번씩 들어오는 24시간 데이터가 있습니다.

열이 시간이며 행이 15분 단위의 값입니다.

해당 값을 한줄 한줄의 같으로 변환하고 싶습니다.

어떻게 하면 될까요?

as-is 

DateTime 0H 1H 2H 3H 4H 5H 6H 7H 8H 9H 10H 11H 12H 13H 14H 15H 16H 17H 18H 19H 20H 21H 22H 23H
2020-11-10 00:00:00 979 958 954 956 956 956 975 956 975 996 989 1112 1116 1006 1044 1008 1008 858 1018 0 0 0 0 0
2020-11-10 00:15:00 987 954 956 956 960 956 985 947 972 950 1033 1010 1031 1021 1025 1008 1016 1167 1020 0 0 0 0 0
2020-11-10 00:30:00 987 958 960 956 958 954 979 948 989 1018 1025 1077 1012 1010 1014 1008 1000 1027 851 0 0 0 0 0
2020-11-10 00:45:00 960 956 956 954 954 956 962 956 981 1010 993 958 1060 1008 1014 993 1062 1035 0 0 0 0 0 0
2020-11-11 00:00:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1025 1020 1000 995 991 1023
2020-11-11 00:15:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1027 1043 1002 995 1000 1006
2020-11-11 00:30:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 657 1014 1025 989 998 996 991
2020-11-11 00:45:00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 989 1021 1010 1004 995 1014 995

 

to-be

DateTime Value
2020-11-10 00:00:00 979
2020-11-10 00:15:00 987
2020-11-10 00:30:00 987
2020-11-10 00:45:00 960
2020-11-10 01:00:00 958
2020-11-10 01:15:00 954
2020-11-10 01:30:00 958
2020-11-10 01:45:00 956
2020-11-10 02:00:00 954
2020-11-10 02:15:00 956
2020-11-10 02:30:00 960
2020-11-10 02:45:00 956
2020-11-10 03:00:00 956
2020-11-10 03:15:00 956
2020-11-10 03:30:00 956
2020-11-10 03:45:00 954
2020-11-10 04:00:00 956
2020-11-10 04:15:00 960
2020-11-10 04:30:00 958
2020-11-10 04:45:00 954
2020-11-10 05:00:00 956
2020-11-10 05:15:00 956
2020-11-10 05:30:00 954
2020-11-10 05:45:00 956
2020-11-10 06:00:00 975
2020-11-10 06:15:00 985
2020-11-10 06:30:00 979
2020-11-10 06:45:00 962
2020-11-10 07:00:00 956
2020-11-10 07:15:00 947
2020-11-10 07:30:00 948
2020-11-10 07:45:00 956
2020-11-10 08:00:00 975
2020-11-10 08:15:00 972
2020-11-10 08:30:00 989
2020-11-10 08:45:00 981
2020-11-10 09:00:00 996
2020-11-10 09:15:00 950
2020-11-10 09:30:00 1018
2020-11-10 09:45:00 1010
2020-11-10 10:00:00 989
2020-11-10 10:15:00 1033
2020-11-10 10:30:00 1025
2020-11-10 10:45:00 993
2020-11-10 11:00:00 1112
2020-11-10 11:15:00 1010
2020-11-10 11:30:00 1077
2020-11-10 11:45:00 958
2020-11-10 12:00:00 1116
2020-11-10 12:15:00 1031
2020-11-10 12:30:00 1012
2020-11-10 12:45:00 1060
2020-11-10 13:00:00 1006
2020-11-10 13:15:00 1021
2020-11-10 13:30:00 1010
2020-11-10 13:45:00 1008
2020-11-10 14:00:00 1044
2020-11-10 14:15:00 1025
2020-11-10 14:30:00 1014
2020-11-10 14:45:00 1014
2020-11-10 15:00:00 1008
2020-11-10 15:15:00 1008
2020-11-10 15:30:00 1008
2020-11-10 15:45:00 993
2020-11-10 16:00:00 1008
2020-11-10 16:15:00 1016
2020-11-10 16:30:00 1000
2020-11-10 16:45:00 1062
2020-11-10 17:00:00 858
2020-11-10 17:15:00 1167
2020-11-10 17:30:00 1027
2020-11-10 17:45:00 1035
2020-11-10 18:00:00 1018
2020-11-10 18:15:00 1020
2020-11-10 18:30:00 851
2020-11-10 18:45:00 0
2020-11-10 19:00:00 0
2020-11-10 19:15:00 0
2020-11-10 19:30:00 0
2020-11-10 19:45:00 0
2020-11-10 20:00:00 0
2020-11-10 20:15:00 0
2020-11-10 20:30:00 0
2020-11-10 20:45:00 0
2020-11-10 21:00:00 0
2020-11-10 21:15:00 0
2020-11-10 21:30:00 0
2020-11-10 21:45:00 0
2020-11-10 22:00:00 0
2020-11-10 22:15:00 0
2020-11-10 22:30:00 0
2020-11-10 22:45:00 0
2020-11-10 23:00:00 0
2020-11-10 23:15:00 0
2020-11-10 23:30:00 0
2020-11-10 23:45:00 0
2020-11-11 00:00:00 0
2020-11-11 00:15:00 0
2020-11-11 00:30:00 0
by 마농 [2020.11.16 08:51:27] 
WITH t
( `DateTime`
, `0H`, `1H`, `2H`, `3H`, `4H`, `5H`, `6H`, `7H`, `8H`, `9H`, `10H`, `11H`, `12H`, `13H`, `14H`, `15H`, `16H`, `17H`, `18H`, `19H`, `20H`, `21H`, `22H`, `23H`
) AS (    SELECT '2020-11-10 00:00:00', 979, 958, 954, 956, 956, 956, 975, 956, 975,  996,  989, 1112, 1116, 1006, 1044, 1008, 1008,  858, 1018, 0, 0, 0, 0, 0
UNION ALL SELECT '2020-11-10 00:15:00', 987, 954, 956, 956, 960, 956, 985, 947, 972,  950, 1033, 1010, 1031, 1021, 1025, 1008, 1016, 1167, 1020, 0, 0, 0, 0, 0
UNION ALL SELECT '2020-11-10 00:30:00', 987, 958, 960, 956, 958, 954, 979, 948, 989, 1018, 1025, 1077, 1012, 1010, 1014, 1008, 1000, 1027,  851, 0, 0, 0, 0, 0
UNION ALL SELECT '2020-11-10 00:45:00', 960, 956, 956, 954, 954, 956, 962, 956, 981, 1010,  993,  958, 1060, 1008, 1014,  993, 1062, 1035,    0, 0, 0, 0, 0, 0
UNION ALL SELECT '2020-11-11 00:00:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,   0, 1025, 1020, 1000, 995,  991, 1023
UNION ALL SELECT '2020-11-11 00:15:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,   0, 1027, 1043, 1002, 995, 1000, 1006
UNION ALL SELECT '2020-11-11 00:30:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 657, 1014, 1025,  989, 998,  996,  991
UNION ALL SELECT '2020-11-11 00:45:00', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 989, 1021, 1010, 1004, 995, 1014,  995
)
, copy_t AS
(
SELECT 0 h
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 `DateTime` + INTERVAL h HOUR `DateTime`
     , CASE h
       WHEN  0 THEN  `0H`
       WHEN  1 THEN  `1H`
       WHEN  2 THEN  `2H`
       WHEN  3 THEN  `3H`
       WHEN  4 THEN  `4H`
       WHEN  5 THEN  `5H`
       WHEN  6 THEN  `6H`
       WHEN  7 THEN  `7H`
       WHEN  8 THEN  `8H`
       WHEN  9 THEN  `9H`
       WHEN 10 THEN `10H`
       WHEN 11 THEN `11H`
       WHEN 12 THEN `12H`
       WHEN 13 THEN `13H`
       WHEN 14 THEN `14H`
       WHEN 15 THEN `15H`
       WHEN 16 THEN `16H`
       WHEN 17 THEN `17H`
       WHEN 18 THEN `18H`
       WHEN 19 THEN `19H`
       WHEN 20 THEN `20H`
       WHEN 21 THEN `21H`
       WHEN 22 THEN `22H`
       WHEN 23 THEN `23H`
        END v
  FROM t
     , copy_t
 ORDER BY `DateTime`
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입