데이터베이스 질문과 답변
안녕하세요.
취미로 오라클을 배우고 있는 개발과 전혀 상관없는 직종의 직장인입니다.
엑셀로 대용량의 Data 계산이 필요하여 배우기 시작했습니다.
구글에서 검색해가면서 덕지덕지 붙여서 쿼리를 짜고 있구요.
아무리 덕지덕지 짜더라도 엑셀보다는 속도가 훨씬 빨라서 아직까지는 퍼포먼스 같은 것은 생각치도 않고
그냥 날쿼리를 짜고 있습니다.
그러던 중 Model이라는 구분을 사용하면 마치 엑셀처럼 Data 계산이 가능하다는 것을 알고 아래처럼 짜보았습니다.
SELECT ProductCode, In, Out, time_input, 직전대비유입량
FROM Table1
WHERE DATE1 = TO_DATE('20150801', 'YYYYMMDD')
MODEL
PARTITION BY (ProductCodeORDER )
DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY ProductCodeORDER BY time_input ASC) rn)
MEASURES (In, Out, input_time, 0 직전대비유입량, 0 시간당유입량)
RULES (
직전대비유입량[ANY] ORDER BY rn = In[CV(rn)] - In[CV(rn)-1]
)
요렇게 하니까 엑셀에서 In이라는 셀에서 ( 현재ROW의 값 - 이전ROW의 값 ) 을 계산한 것이 직전대비유입량 이라는 곳에 나타나더라구요.
여기까지는 잘 되었는데..시간당유입량을 구하려면 어떻게 해야할지 막막합니다.
일단 time_input에는 In이나 Out의 수량이 변경되면 그때의 시간이 입력되어 있구요. (TimeStamp 형식)
지금 ROW의 time_input - 1/24/60 에 해당하는 rn 값을 불러와서
그 rn값 부터 지금 ROW의 rn값의 In의 변화량을 구하면 될 것 같은데,
어떤 방식으로 하면 될런지 질문드립니다.
개발이 본업도 아닌데, 업무시간에 짬내어서 이렇게 힘겹게 살아가고 있습니다.
도움부탁드려요~
Model 절도 좋지만, 분석함수도 좋습니다.
분석함수를 이용해 보세요.
input_time 의 간격이 좁아야만 정확한 근사치가 나오겠네요.
- 시간당유입량1 : 1시간 이내 가장 작은(최초)값
- 시간당유입량2 : 1시간 이전 자료중 가장 큰(최종)값
SELECT ProductCode
, In
, Out
, input_time
, ROW_NUMBER() OVER(PARTITION BY ProductCode ORDER BY time_input) rn
, In - LAG(In) OVER(PARTITION BY ProductCode ORDER BY time_input) 직전대비유입량
, In - MIN(In) OVER(PARTITION BY ProductCode ORDER BY time_input
RANGE INTERVAL '1' HOUR PRECEDING) 시간당유입량1
, In - MAX(In) OVER(PARTITION BY ProductCode ORDER BY time_input
RANGE BETWEEN UNBOUNDED PRECEDING
AND INTERVAL '1' HOUR PRECEDING) 시간당유입량2
FROM table1
WHERE date1 = TO_DATE('20150801', 'yyyymmdd')
;
마농님 감사합니다.
가까운 길이 있는데 멀리 돌아가고 있었다는 느낌이네요.
일단 원하는 결과는 얻었습니다.
하지만 계산된 시간당유입량의 현재값, 이전값, n번째 이전값 등을 이용하여
가속도나 가속도의 변화량 등 구해야 할 값들이 더 있습니다.
그러려면 답변해 주신 쿼리를 감싸서 서브쿼리(?)로 필요에 따라 몇 번씩 감싸줘야 할 것 같습니다.
Database의 기초가 없어서 Model절이 엑셀처럼 친숙하게 쓸 수 있을 것 같은데,
Model을 활용하는 방법은 없을까요?
와~ 본업도 아닌데 대단하십니다. 파이팅이요~
감사합니다.
화이팅합시다!
필요한게 생길때마다 서브쿼리로 감싼다는 것은 너무나도 절차적인 발상입니다.
절차적 프로그래밍에 익숙한 개발자 마인드인데요.
개발과 무관하신 분이라면 오히려 구조적, 집합적 언어인 SQL에 대한 접근이 쉬울수도 있습니다.
굳이 서브쿼리 안하고도 충분히 가능한 일입니다.
이전값은 Lag 를 이용하면 되고요
n 번째 이전값은 분석함수를 이용해 가능합니다.
1시간 전 자료를 구해 왔듯이 n Row 전 자료를 구할 수 있습니다.
MIN(In) OVER(PARTITION BY ProductCode ORDER BY time_input ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING)
또는 LAG 함수에서도 n Row 전 자료 구하기가 가능합니다.
LAG(In, 3) OVER(PARTITION BY ProductCode ORDER BY time_input)
Model 절에서는 CV()-1, CV()-3 을 이용하시면 되지요.
이 부분은 알고 계시는 것 같은데요.
다만 Model 절에서는 1시간전 자료 구하기가 어렵죠.
와.... 일반인이 모델절을... OTL 본업으로 하신다면.... 대박 잘하실 것 같네요. 와...
참고하게 표시 좀 하겠습니다.
[oracle Model절, 분석함수 (20150804)]
) 버튼을 클릭하여 작성 하시면 됩니다.