꿈꾸는 개발자, 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]

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

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(

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

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

데이터베이스 질문과 답변