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

mariadb query 문으로 행을 열로 변환하려고 하는 데 null 이 조회됩니다.

by tosswin [SQL Query] mariadb 행->열 [2023.09.27 14:54:37]

table : tbl_test 에 다음과 같은 형태로 데이터가 있습니다.

rundatetime	idxNo	accu
2023-09-25 00:00:00	1	10
2023-09-25 00:00:00	2	20
2023-09-25 00:00:00	3	30
2023-09-25 00:00:00	4	40
2023-09-25 00:00:00	5	50
2023-09-25 00:00:00	6	60
2023-09-25 00:00:00	7	70
2023-09-25 00:00:00	8	80
2023-09-25 00:00:00	9	90
2023-09-25 00:00:00	10	100
2023-09-25 00:10:00	1	24.6
2023-09-25 00:10:00	2	27.9
2023-09-25 00:10:00	3	31.2
2023-09-25 00:10:00	4	53.7
2023-09-25 00:10:00	5	55.4
2023-09-25 00:10:00	6	71.7
2023-09-25 00:10:00	7	75.9
2023-09-25 00:10:00	8	86.2
2023-09-25 00:10:00	9	104.4
2023-09-25 00:10:00	10	107.9
2023-09-25 00:20:00	1	25.6
2023-09-25 00:20:00	2	42.5
2023-09-25 00:20:00	3	33.8
2023-09-25 00:20:00	4	66.6
2023-09-25 00:20:00	5	64.1
2023-09-25 00:20:00	6	77.5
2023-09-25 00:20:00	7	89.4
2023-09-25 00:20:00	8	88.1
2023-09-25 00:20:00	9	106.4
2023-09-25 00:20:00	10	112.8
2023-09-25 00:30:00	1	39.5
2023-09-25 00:30:00	2	54.3
2023-09-25 00:30:00	3	42.7
2023-09-25 00:30:00	4	74.4
2023-09-25 00:30:00	5	74.6
2023-09-25 00:30:00	6	79.2
2023-09-25 00:30:00	7	91
2023-09-25 00:30:00	8	98.2
2023-09-25 00:30:00	9	107.7
2023-09-25 00:30:00	10	124.1
2023-09-25 00:40:00	1	41.5
2023-09-25 00:40:00	2	67.4
2023-09-25 00:40:00	3	47.8
2023-09-25 00:40:00	4	74.7
2023-09-25 00:40:00	5	82.1
2023-09-25 00:40:00	6	87.6
2023-09-25 00:40:00	7	96.3
2023-09-25 00:40:00	8	102.3
2023-09-25 00:40:00	9	119.4
2023-09-25 00:40:00	10	127.5
2023-09-25 00:50:00	1	55.1
2023-09-25 00:50:00	2	80.3
2023-09-25 00:50:00	3	53.5
2023-09-25 00:50:00	4	81.5
2023-09-25 00:50:00	5	92.9
2023-09-25 00:50:00	6	98.9
2023-09-25 00:50:00	7	107.6
2023-09-25 00:50:00	8	108.1
2023-09-25 00:50:00	9	128.8
2023-09-25 00:50:00	10	139.6
2023-09-25 01:00:00	1	56.1
2023-09-25 01:00:00	2	87.3
2023-09-25 01:00:00	3	58.3
2023-09-25 01:00:00	4	92.8
2023-09-25 01:00:00	5	94.2
2023-09-25 01:00:00	6	113.7
2023-09-25 01:00:00	7	114.2
2023-09-25 01:00:00	8	112
2023-09-25 01:00:00	9	131.2
2023-09-25 01:00:00	10	145
2023-09-25 01:10:00	1	65.1
2023-09-25 01:10:00	2	96.4
2023-09-25 01:10:00	3	70.5
2023-09-25 01:10:00	4	101.2
2023-09-25 01:10:00	5	108.7
2023-09-25 01:10:00	6	121.8
2023-09-25 01:10:00	7	127
2023-09-25 01:10:00	8	122.8
2023-09-25 01:10:00	9	135.6
2023-09-25 01:10:00	10	159
2023-09-25 01:20:00	1	70.1
2023-09-25 01:20:00	2	97.8
2023-09-25 01:20:00	3	77.1
2023-09-25 01:20:00	4	112.8
2023-09-25 01:20:00	5	121.8
2023-09-25 01:20:00	6	134.6
2023-09-25 01:20:00	7	132
2023-09-25 01:20:00	8	128.4
2023-09-25 01:20:00	9	150.4
2023-09-25 01:20:00	10	173.5
2023-09-25 01:30:00	1	80.7
2023-09-25 01:30:00	2	97.9
2023-09-25 01:30:00	3	90.9
2023-09-25 01:30:00	4	120.9
2023-09-25 01:30:00	5	125
2023-09-25 01:30:00	6	137.2
2023-09-25 01:30:00	7	140.5
2023-09-25 01:30:00	8	134.3
2023-09-25 01:30:00	9	157.8
2023-09-25 01:30:00	10	174
2023-09-25 01:40:00	1	92.4
2023-09-25 01:40:00	2	103.1
2023-09-25 01:40:00	3	101.9
2023-09-25 01:40:00	4	125.3
2023-09-25 01:40:00	5	125.5
2023-09-25 01:40:00	6	145.1
2023-09-25 01:40:00	7	148.1
2023-09-25 01:40:00	8	143.8
2023-09-25 01:40:00	9	159.9
2023-09-25 01:40:00	10	179.8
2023-09-25 01:50:00	1	94.9
2023-09-25 01:50:00	2	112.6
2023-09-25 01:50:00	3	109
2023-09-25 01:50:00	4	136.1
2023-09-25 01:50:00	5	133.8
2023-09-25 01:50:00	6	149.4
2023-09-25 01:50:00	7	161.8
2023-09-25 01:50:00	8	157
2023-09-25 01:50:00	9	161.2
2023-09-25 01:50:00	10	179.9
2023-09-25 02:00:00	1	103.1
2023-09-25 02:00:00	2	119.9
2023-09-25 02:00:00	3	119.3
2023-09-25 02:00:00	4	137.8
2023-09-25 02:00:00	5	134.7
2023-09-25 02:00:00	6	156.2
2023-09-25 02:00:00	7	174.4
2023-09-25 02:00:00	8	171.3
2023-09-25 02:00:00	9	168.6
2023-09-25 02:00:00	10	182

위의 데이터를 다음과 같이 변경하려고 합니다.

rundatetime

accu_01

accu_02

accu_03

accu_04

accu_05

accu_06

accu_07

accu_08

accu_09

accu_10

아래와 같이 쿼리문을 작성하였습니다.

시간별 데이터 차이를 각각에 대해 구하려고 합니다.

select 
  max(rundatetime) as msdatetime, 
  (case when idxNo = '1' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_01, 
  (case when idxNo = '2' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_02, 
  (case when idxNo = '3' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_03, 
  (case when idxNo = '4' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_04, 
  (case when idxNo = '5' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_05, 
  (case when idxNo = '6' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_06, 
  (case when idxNo = '7' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_07, 
  (case when idxNo = '8' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_08, 
  (case when idxNo = '9' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_09, 
  (case when idxNo = '10' 
        then (accu - lead(accu, 1) over (order by rundatetime desc)) end) as accu_10 
from tlb_test tt
group by rundatetime

그런데, 위와 같이 하면

rundatetime	accu_01	accu_02	accu_03	accu_04	accu_05	accu_06	accu_07	accu_08	accu_09	accu_10
00:00.0	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
10:00.0	14.6	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
20:00.0	1	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
30:00.0	13.9	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
40:00.0	2	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
50:00.0	13.6	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
00:00.0	1	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
10:00.0	9	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
20:00.0	5	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
30:00.0	10.6	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
40:00.0	11.7	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
50:00.0	2.5	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]
00:00.0	8.2	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]	[null]

accu_01 열만 제외하고 모두 null 입니다.

제가 무엇을 잘못하고 있을까요?

항상 잘 가르쳐 주셔서 감사합니다.

즐거운 한가위 보내세요.

by 마농 [2023.10.04 09:39:24]

WITH tbl_test AS
(
SELECT '2023-09-25 00:00:00' rundatetime, 1 idxNo, 10 accu
UNION ALL SELECT '2023-09-25 00:00:00',  2,  20
UNION ALL SELECT '2023-09-25 00:00:00',  3,  30
UNION ALL SELECT '2023-09-25 00:00:00',  4,  40
UNION ALL SELECT '2023-09-25 00:00:00',  5,  50
UNION ALL SELECT '2023-09-25 00:00:00',  6,  60
UNION ALL SELECT '2023-09-25 00:00:00',  7,  70
UNION ALL SELECT '2023-09-25 00:00:00',  8,  80
UNION ALL SELECT '2023-09-25 00:00:00',  9,  90
UNION ALL SELECT '2023-09-25 00:00:00', 10, 100
UNION ALL SELECT '2023-09-25 00:10:00',  1,  24.6
UNION ALL SELECT '2023-09-25 00:10:00',  2,  27.9
UNION ALL SELECT '2023-09-25 00:10:00',  3,  31.2
UNION ALL SELECT '2023-09-25 00:10:00',  4,  53.7
UNION ALL SELECT '2023-09-25 00:10:00',  5,  55.4
UNION ALL SELECT '2023-09-25 00:10:00',  6,  71.7
UNION ALL SELECT '2023-09-25 00:10:00',  7,  75.9
UNION ALL SELECT '2023-09-25 00:10:00',  8,  86.2
UNION ALL SELECT '2023-09-25 00:10:00',  9, 104.4
UNION ALL SELECT '2023-09-25 00:10:00', 10, 107.9
UNION ALL SELECT '2023-09-25 00:20:00',  1,  25.6
UNION ALL SELECT '2023-09-25 00:20:00',  2,  42.5
UNION ALL SELECT '2023-09-25 00:20:00',  3,  33.8
UNION ALL SELECT '2023-09-25 00:20:00',  4,  66.6
UNION ALL SELECT '2023-09-25 00:20:00',  5,  64.1
UNION ALL SELECT '2023-09-25 00:20:00',  6,  77.5
UNION ALL SELECT '2023-09-25 00:20:00',  7,  89.4
UNION ALL SELECT '2023-09-25 00:20:00',  8,  88.1
UNION ALL SELECT '2023-09-25 00:20:00',  9, 106.4
UNION ALL SELECT '2023-09-25 00:20:00', 10, 112.8
UNION ALL SELECT '2023-09-25 00:30:00',  1,  39.5
UNION ALL SELECT '2023-09-25 00:30:00',  2,  54.3
UNION ALL SELECT '2023-09-25 00:30:00',  3,  42.7
UNION ALL SELECT '2023-09-25 00:30:00',  4,  74.4
UNION ALL SELECT '2023-09-25 00:30:00',  5,  74.6
UNION ALL SELECT '2023-09-25 00:30:00',  6,  79.2
UNION ALL SELECT '2023-09-25 00:30:00',  7,  91
UNION ALL SELECT '2023-09-25 00:30:00',  8,  98.2
UNION ALL SELECT '2023-09-25 00:30:00',  9, 107.7
UNION ALL SELECT '2023-09-25 00:30:00', 10, 124.1
UNION ALL SELECT '2023-09-25 00:40:00',  1,  41.5
UNION ALL SELECT '2023-09-25 00:40:00',  2,  67.4
UNION ALL SELECT '2023-09-25 00:40:00',  3,  47.8
UNION ALL SELECT '2023-09-25 00:40:00',  4,  74.7
UNION ALL SELECT '2023-09-25 00:40:00',  5,  82.1
UNION ALL SELECT '2023-09-25 00:40:00',  6,  87.6
UNION ALL SELECT '2023-09-25 00:40:00',  7,  96.3
UNION ALL SELECT '2023-09-25 00:40:00',  8, 102.3
UNION ALL SELECT '2023-09-25 00:40:00',  9, 119.4
UNION ALL SELECT '2023-09-25 00:40:00', 10, 127.5
UNION ALL SELECT '2023-09-25 00:50:00',  1,  55.1
UNION ALL SELECT '2023-09-25 00:50:00',  2,  80.3
UNION ALL SELECT '2023-09-25 00:50:00',  3,  53.5
UNION ALL SELECT '2023-09-25 00:50:00',  4,  81.5
UNION ALL SELECT '2023-09-25 00:50:00',  5,  92.9
UNION ALL SELECT '2023-09-25 00:50:00',  6,  98.9
UNION ALL SELECT '2023-09-25 00:50:00',  7, 107.6
UNION ALL SELECT '2023-09-25 00:50:00',  8, 108.1
UNION ALL SELECT '2023-09-25 00:50:00',  9, 128.8
UNION ALL SELECT '2023-09-25 00:50:00', 10, 139.6
UNION ALL SELECT '2023-09-25 01:00:00',  1,  56.1
UNION ALL SELECT '2023-09-25 01:00:00',  2,  87.3
UNION ALL SELECT '2023-09-25 01:00:00',  3,  58.3
UNION ALL SELECT '2023-09-25 01:00:00',  4,  92.8
UNION ALL SELECT '2023-09-25 01:00:00',  5,  94.2
UNION ALL SELECT '2023-09-25 01:00:00',  6, 113.7
UNION ALL SELECT '2023-09-25 01:00:00',  7, 114.2
UNION ALL SELECT '2023-09-25 01:00:00',  8, 112
UNION ALL SELECT '2023-09-25 01:00:00',  9, 131.2
UNION ALL SELECT '2023-09-25 01:00:00', 10, 145
UNION ALL SELECT '2023-09-25 01:10:00',  1,  65.1
UNION ALL SELECT '2023-09-25 01:10:00',  2,  96.4
UNION ALL SELECT '2023-09-25 01:10:00',  3,  70.5
UNION ALL SELECT '2023-09-25 01:10:00',  4, 101.2
UNION ALL SELECT '2023-09-25 01:10:00',  5, 108.7
UNION ALL SELECT '2023-09-25 01:10:00',  6, 121.8
UNION ALL SELECT '2023-09-25 01:10:00',  7, 127
UNION ALL SELECT '2023-09-25 01:10:00',  8, 122.8
UNION ALL SELECT '2023-09-25 01:10:00',  9, 135.6
UNION ALL SELECT '2023-09-25 01:10:00', 10, 159
UNION ALL SELECT '2023-09-25 01:20:00',  1,  70.1
UNION ALL SELECT '2023-09-25 01:20:00',  2,  97.8
UNION ALL SELECT '2023-09-25 01:20:00',  3,  77.1
UNION ALL SELECT '2023-09-25 01:20:00',  4, 112.8
UNION ALL SELECT '2023-09-25 01:20:00',  5, 121.8
UNION ALL SELECT '2023-09-25 01:20:00',  6, 134.6
UNION ALL SELECT '2023-09-25 01:20:00',  7, 132
UNION ALL SELECT '2023-09-25 01:20:00',  8, 128.4
UNION ALL SELECT '2023-09-25 01:20:00',  9, 150.4
UNION ALL SELECT '2023-09-25 01:20:00', 10, 173.5
UNION ALL SELECT '2023-09-25 01:30:00',  1,  80.7
UNION ALL SELECT '2023-09-25 01:30:00',  2,  97.9
UNION ALL SELECT '2023-09-25 01:30:00',  3,  90.9
UNION ALL SELECT '2023-09-25 01:30:00',  4, 120.9
UNION ALL SELECT '2023-09-25 01:30:00',  5, 125
UNION ALL SELECT '2023-09-25 01:30:00',  6, 137.2
UNION ALL SELECT '2023-09-25 01:30:00',  7, 140.5
UNION ALL SELECT '2023-09-25 01:30:00',  8, 134.3
UNION ALL SELECT '2023-09-25 01:30:00',  9, 157.8
UNION ALL SELECT '2023-09-25 01:30:00', 10, 174
UNION ALL SELECT '2023-09-25 01:40:00',  1,  92.4
UNION ALL SELECT '2023-09-25 01:40:00',  2, 103.1
UNION ALL SELECT '2023-09-25 01:40:00',  3, 101.9
UNION ALL SELECT '2023-09-25 01:40:00',  4, 125.3
UNION ALL SELECT '2023-09-25 01:40:00',  5, 125.5
UNION ALL SELECT '2023-09-25 01:40:00',  6, 145.1
UNION ALL SELECT '2023-09-25 01:40:00',  7, 148.1
UNION ALL SELECT '2023-09-25 01:40:00',  8, 143.8
UNION ALL SELECT '2023-09-25 01:40:00',  9, 159.9
UNION ALL SELECT '2023-09-25 01:40:00', 10, 179.8
UNION ALL SELECT '2023-09-25 01:50:00',  1,  94.9
UNION ALL SELECT '2023-09-25 01:50:00',  2, 112.6
UNION ALL SELECT '2023-09-25 01:50:00',  3, 109
UNION ALL SELECT '2023-09-25 01:50:00',  4, 136.1
UNION ALL SELECT '2023-09-25 01:50:00',  5, 133.8
UNION ALL SELECT '2023-09-25 01:50:00',  6, 149.4
UNION ALL SELECT '2023-09-25 01:50:00',  7, 161.8
UNION ALL SELECT '2023-09-25 01:50:00',  8, 157
UNION ALL SELECT '2023-09-25 01:50:00',  9, 161.2
UNION ALL SELECT '2023-09-25 01:50:00', 10, 179.9
UNION ALL SELECT '2023-09-25 02:00:00',  1, 103.1
UNION ALL SELECT '2023-09-25 02:00:00',  2, 119.9
UNION ALL SELECT '2023-09-25 02:00:00',  3, 119.3
UNION ALL SELECT '2023-09-25 02:00:00',  4, 137.8
UNION ALL SELECT '2023-09-25 02:00:00',  5, 134.7
UNION ALL SELECT '2023-09-25 02:00:00',  6, 156.2
UNION ALL SELECT '2023-09-25 02:00:00',  7, 174.4
UNION ALL SELECT '2023-09-25 02:00:00',  8, 171.3
UNION ALL SELECT '2023-09-25 02:00:00',  9, 168.6
UNION ALL SELECT '2023-09-25 02:00:00', 10, 182
)
SELECT rundatetime
     , MIN(CASE idxNo WHEN  1 THEN x END) accu_01
     , MIN(CASE idxNo WHEN  2 THEN x END) accu_02
     , MIN(CASE idxNo WHEN  3 THEN x END) accu_03
     , MIN(CASE idxNo WHEN  4 THEN x END) accu_04
     , MIN(CASE idxNo WHEN  5 THEN x END) accu_05
     , MIN(CASE idxNo WHEN  6 THEN x END) accu_06
     , MIN(CASE idxNo WHEN  7 THEN x END) accu_07
     , MIN(CASE idxNo WHEN  8 THEN x END) accu_08
     , MIN(CASE idxNo WHEN  9 THEN x END) accu_09
     , MIN(CASE idxNo WHEN 10 THEN x END) accu_10
  FROM (SELECT rundatetime, idxNo, accu
             , accu - LAG(accu) OVER(PARTITION BY idxNo ORDER BY rundatetime) x
          FROM tbl_test
        ) a
 GROUP BY rundatetime
;

by tosswin [2023.10.05 15:18:53]

제가 완전히 잘못 알고 작성을 한 것이네요..
더 편한 방법을 가르쳐 주셔서 감사합니다.

필요한 부분에 잘 적용하였습니다.

댓글등록

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

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

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

데이터베이스 질문과 답변