데이터베이스 질문과 답변

시계열 데이터 가공 질문드립니다. 0 8 590

by 주작전사 [SQL Query] 시계열 차트 [2020.08.08 23:35:59]

3분.jpg (241,282Bytes)

1분단위의 데이터가 DB에 저장 되어있습니다.   이것을 3분단위로 가공을 해서 Select 하는데

기본 룰은 이렇습니다.

이름과 날짜는 그룹화 되어야하고

시간 => 3분간격의 마지막 시간 

시 => 3분간격의 시 열의 첫번째 값

고 => 3분간격의 고 열의 가장 높은 값

저 => 3분간격의 저 열의 가장 낮은 값

종 => 3분간격의 종 열의 마지막 값

양 => 3분간의 양 열의 합

다만 1분단위의 데이터는 중간에  비어 있을 수 도있으며 

비어있을 시   가공된 결과는

한행이라도 존재 => 존재하는 데이터 안에서 가공이 이루어집니다. (시간은 3분간격에 맞춰서 가공)

3분간격의 3행이 모두 비어있음 => 데이터 가공 없음 (시간도 비게 가공)

이렇게 가공이 되어야합니다.

이렇게 가공하는것이 가능한가요? 

너무 복잡해서 질문드립니다. 

by pajama [2020.08.09 04:48:14]

DB는 오라클인지요? 허접하지만 시간을 문자열로 가정하고 SQL문을 짜봤습니다. 더 나은 방법이 있을것도 같은데.. 잘못된 부분이 있으면 지적 부탁드려요.

with t as (
select '2020-07-01' dat, 'A' nm, '0901' tim, 100 n1, 105 n2, 90 n3, 100 n4, 5500 n5 from dual
union all select '2020-07-01', 'A', '0902', 100, 100,  95,  95, 1000 from dual
union all select '2020-07-01', 'A', '0903',  95, 110,  95, 105,  600 from dual
union all select '2020-07-01', 'A', '0904', 105, 120, 100, 110,   20 from dual
union all select '2020-07-01', 'A', '0905', 110, 125, 100, 125,  800 from dual
union all select '2020-07-01', 'A', '0906', 125, 125, 100, 115,  300 from dual
union all select '2020-07-01', 'A', '0907', 115, 130, 110, 110,  750 from dual
union all select '2020-07-01', 'A', '0908', 110, 130, 100, 125, 2000 from dual
union all select '2020-07-01', 'A', '0909', 125, 125, 105, 105,  300 from dual
union all select '2020-07-01', 'A', '0910', 105, 105,  95,  95,  150 from dual
union all select '2020-07-01', 'B', '0901', 100, 105,  95, 100, 5000 from dual
union all select '2020-07-01', 'B', '0902', 100, 100,  95,  95, 1000 from dual
union all select '2020-07-01', 'B', '0903',  95, 115,  95, 105,  600 from dual
union all select '2020-07-01', 'B', '0908', 110, 130, 100, 125, 2000 from dual
union all select '2020-07-01', 'B', '0909', 125, 125, 105, 105,  300 from dual
union all select '2020-07-01', 'B', '0910', 105, 105,  90,  95,  150 from dual
)
select *
from (select dat,
             nm,
             max(tim)        over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v1,
             first_value(n1) over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) v2,
             max(n2)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v3,
             min(n3)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v4,
             last_value(n4)  over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v5,
             sum(n5)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v6,
             row_number()    over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) rn
      from t) t1
where rn = 1

 

by 주작전사 [2020.08.10 03:35:40]

답변 감사드립니다. 우선 DB는 오라클이고

답변주신 내용을 확인 해 보았는데, 3, 6, 9 분까진 정확히 의도 된 대로 가공이 되었지만 

10분부터 문제가 생깁니다.

DAT NM V1 V2 V3 V4 V5 V6 RN
2020-08-03 0:00 NAME 903 57800 57900 57700 57800 1038757 1
2020-08-03 0:00 NAME 906 57800 57800 57300 57500 935380 1
2020-08-03 0:00 NAME 909 57500 57700 57300 57500 516849 1
2020-08-03 0:00 NAME 910 57400 57500 57400 57400 53786 1
2020-08-03 0:00 NAME 913 57400 57500 57300 57500 348614 1
2020-08-03 0:00 NAME 916 57400 57500 57300 57300 317624 1
2020-08-03 0:00 NAME 919 57300 57500 57300 57300 259009 1
2020-08-03 0:00 NAME 920 57300 57500 57300 57400 95908 1
2020-08-03 0:00 NAME 923 57400 57500 57300 57400 224544 1
2020-08-03 0:00 NAME 926 57400 57500 57300 57400 251719 1
2020-08-03 0:00 NAME 929 57500 57500 57400 57500 99389 1
2020-08-03 0:00 NAME 930 57400 57500 57400 57400 23052 1

 

3, 6, 9, 12, 15, 18, 21, 24 이런식으로 가야하는데 위의 표처럼 3,6,9 만 맞춰 버리네요 ㅜㅜ

답변을 통해서 partytion by 에 컬럼명 말고 뒤에 시간을 잘라서 붙이는 방법을 배웠습니다만

제가 아직 스스로  힌트를 통해서 정답까진 도달 하지 못하겠네요. 

가능하시다면 조언을 좀더 부탁드립니다.

원글에 샘플 데이터를 올려봅니다.

 

by pajama [2020.08.10 09:12:55]

헉 자리수가 세자리라 그런것 같습니다.

제 쿼리에서는 자리수로 분기하면 어떨까 싶네요. 아니면 그냥 시간값을 3으로 나누어도 될듯하고..?

select *
from (select dat,
             nm,
             max(tim)        over (partition by dat, nm, substr(tim,1,2), case when length(tim)=4 then ceil(substr(tim,3,2)/3) when length(tim)=3 then ceil(substr(tim,2,2)/3) end) v1,
             first_value(n1) over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) v2,
             max(n2)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v3,
             min(n3)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v4,
             last_value(n4)  over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v5,
             sum(n5)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v6,
             row_number()    over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) rn
      from t) t1
where rn = 1


select *
from (select dat,
             nm,
             max(tim)        over (partition by dat, nm, ceil(tim/3)) v1,
             first_value(n1) over (partition by dat, nm, ceil(tim/3) order by tim) v2,
             max(n2)         over (partition by dat, nm, ceil(tim/3)) v3,
             min(n3)         over (partition by dat, nm, ceil(tim/3)) v4,
             last_value(n4)  over (partition by dat, nm, ceil(tim/3)) v5,
             sum(n5)         over (partition by dat, nm, ceil(tim/3)) v6,
             row_number()    over (partition by dat, nm, ceil(tim/3) order by tim) rn
      from t) t1
where rn = 1
by 주작전사 [2020.08.12 21:47:40]

이번에도 답변 감사드립니다. 안타깝지만 제시해주신 두 가지 방법으로도 3분간격의 정확한 

시계열은 표현을 못하더군요

00, 03,06,09,12,15,18,21,24,27,30,33,36,39,42,45,48,51,54,57,00

일단 이건 제가 연구 더 해보겠습니다. ㅜㅜ

by pajama [2020.08.12 22:18:01]

아이고 제가 질문을 잘못이해했었군요.. 표시가 3분단위로 되어야하는데..

조잡하긴 하지만 아래처럼 바꾸면 될까요?

with t as (
select '2020-07-01' dat, 'A' nm, '0901' tim, 100 n1, 105 n2, 90 n3, 100 n4, 5500 n5 from dual
union all select '2020-07-01', 'A', '0902', 100, 100,  95,  95, 1000 from dual
union all select '2020-07-01', 'A', '0903',  95, 110,  95, 105,  600 from dual
union all select '2020-07-01', 'A', '0904', 105, 120, 100, 110,   20 from dual
union all select '2020-07-01', 'A', '0905', 110, 125, 100, 125,  800 from dual
union all select '2020-07-01', 'A', '0906', 125, 125, 100, 115,  300 from dual
union all select '2020-07-01', 'A', '0907', 115, 130, 110, 110,  750 from dual
union all select '2020-07-01', 'A', '0908', 110, 130, 100, 125, 2000 from dual
union all select '2020-07-01', 'A', '0909', 125, 125, 105, 105,  300 from dual
union all select '2020-07-01', 'A', '0910', 105, 105,  95,  95,  150 from dual
union all select '2020-07-01', 'B', '0901', 100, 105,  95, 100, 5000 from dual
union all select '2020-07-01', 'B', '0902', 100, 100,  95,  95, 1000 from dual
union all select '2020-07-01', 'B', '0903',  95, 115,  95, 105,  600 from dual
union all select '2020-07-01', 'B', '0908', 110, 130, 100, 125, 2000 from dual
union all select '2020-07-01', 'B', '0909', 125, 125, 105, 105,  300 from dual
union all select '2020-07-01', 'B', '0910', 105, 105,  90,  95,  150 from dual
)
select dat,
       nm,
	   case when mod(substr(v1,3,2),3) = 0 then v1 else substr(v1,1,2) || ceil(substr(v1,3,2)/3)*3 end v1,
	   v2,
	   v3,
	   v4,
	   v5,
	   v6
from (select dat,
             nm,
             max(tim)        over (partition by dat, nm, substr(tim,1,2), case when length(tim)=4 then ceil(substr(tim,3,2)/3) when length(tim)=3 then ceil(substr(tim,2,2)/3) end) v1,
             first_value(n1) over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) v2,
             max(n2)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v3,
             min(n3)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v4,
             last_value(n4)  over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v5,
             sum(n5)         over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3)) v6,
             row_number()    over (partition by dat, nm, substr(tim,1,2), ceil(substr(tim,3,2)/3) order by tim) rn
      from t) t1
where rn = 1
by 주작전사 [2020.08.18 00:10:50]

답변 달아주신걸 이제 봤네요 

테스트 결과

1154
1157
1160
1200
1203

중간에 60분짜리가 나오고 그담 00 분이 나오네요 

열심히 고생해주셨는데 ㅜㅜ 밑에 마농님께서 달아주신 답변이 좀더 의도대로 잘나옵니다 ㅜㅜ

 고생해 주셔서 감사드립니다.

 

by 마농 [2020.08.14 11:20:50] 
WITH t AS
(
SELECT '2020-07-01' dt, 'A' nm, '0901' tm, 100 n1, 105 n2, 90 n3, 100 n4, 5500 n5 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0902', 100, 100,  95,  95, 1000 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0903',  95, 110,  95, 105,  600 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0904', 105, 120, 100, 110,   20 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0905', 110, 125, 100, 125,  800 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0906', 125, 125, 100, 115,  300 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0907', 115, 130, 110, 110,  750 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0908', 110, 130, 100, 125, 2000 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0909', 125, 125, 105, 105,  300 FROM dual
UNION ALL SELECT '2020-07-01', 'A', '0910', 105, 105,  95,  95,  150 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0901', 100, 105,  95, 100, 5000 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0902', 100, 100,  95,  95, 1000 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0903',  95, 115,  95, 105,  600 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0908', 110, 130, 100, 125, 2000 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0909', 125, 125, 105, 105,  300 FROM dual
UNION ALL SELECT '2020-07-01', 'B', '0910', 105, 105,  90,  95,  150 FROM dual
)
SELECT dt
     , nm
     , MAX(tm) tm
     , TO_CHAR(TO_DATE(gb * 60 * 3, 'sssss'), 'hh24mi') tm_1
     , MAX(n1) KEEP(DENSE_RANK FIRST ORDER BY tm) n1
     , MAX(n2) n2
     , MIN(n3) n3
     , MAX(n4) KEEP(DENSE_RANK LAST  ORDER BY tm) n4
     , SUM(n5) n5
  FROM (SELECT dt, nm, tm
             , n1, n2, n3, n4, n5
             , CEIL((TO_CHAR(TO_DATE(tm, 'hh24mi'), 'sssss') - 1) / (60*3)) gb
          FROM t
         WHERE dt = '2020-07-01'
        )
 GROUP BY dt, nm, gb
 ORDER BY dt, nm, gb
;

 

by 주작전사 [2020.08.18 00:12:06]

저번에 답변 달아주신거 잘 쓰고 있습니다. 

이번에도 도움을 주셨네요 감사드립니다.

많이 배워갑니다.

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