1개의 테이블 : T
컬럼 : rsd_id , time(date 타입), power
데이터가 너무 많아 예를 들어 간략하게)
rsd_id / time / power
100101010001 2016/08/01 00:00:00 120
100101010001 2016/08/01 00:30:00 137
100101010001 2016/08/01 01:00:00 148
100101010001 2016/08/01 01:30:00 162
-
-
100101010001 2016/08/01 11:30:00 267
100101010001 2016/08/02 00:00:00 278
100101010001 2016/08/02 00:30:00 283
-
-
100101010002 2016/08/01 00:00:00 341
100101010002 2016/08/01 00:30:00 347
100101010002 2016/08/01 01:00:00 354
-
-
100101010002 2016/08/01 11:30:00 382
100101010002 2016/08/02 00:00:00 388
100101010002 2016/08/02 00:30:00 397
-
-
-
....................
이런식으로 앞에 1001이 붙고 뒷자리가 다른 아이디가 수백개 있고
시간은 30분간격으로 사용량 정보를 DB로 가져옵니다.
power 값이 0으로 못받아 올때도 있고요.
제가 궁금한건
100101010001 이 8월 1일 사용한량을 구하고 싶습니다.
그래서
8월 2일 사용한 power 중 최댓값 - 8월 1일 사용한 power값
으로 8월 1일 사용량을 구하고자합니다.
( ex: id 1번은 278-120 = 158 이 나와야하고
2번은 388 - 341 = 47 이 나와야겠죠..)
저 나름대로 머리굴려서 사용한 쿼리는 하루의 최댓값밖에 못구하겠습니다.
(데이터가 누적되기때문에)
아래 쿼리는
8월1일 00시 ~ 8월2일00시 데이타중에서
최대값을 받아오기때문에
결과값은 8월 2일 00시 데이타만 나옵니다.
|
select id, MAX(TO_CHAR(time,'yyyy/mm/dd hh24:mi:ss')) MAX(power) KEEP (DENSE_RANK FIRST ORDER BY lp_cptr_time DESC) from T where rsd_id LIKE('1001%') AND time BETWEEN TO_DATE('2016/08/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') AND TO_DATE('2016/08/02 00:00:00', 'yyyy/mm/dd hh24:mi:ss') group by rsd_id; |
그래서 이 쿼리를 또 재사용하여
ex)
(8월 2일~8월3일 최댓값) - (8월 1일~8월2일 최댓값)
240-160
= 80 이런식으로
하게되면 8월 3일의 순수사용량을 출력하고싶습니다.
(아래처럼 출력하고 싶은데 순수사용량 값은 막넣었어요)
이렇게 id는 밑으로 쭉있고, 날짜는 8/1~31일까지 옆으로 쭉뻗어나가는식으로요.
|
rsd_id |
8/1 순수 사용량 |
8/2 순수 사용량 |
8/3일 순수사용량 |
|
100101010001 |
36 |
34 |
32 |
|
100101010002 |
15 |
42 |
24 |
|
100101010003 |
48 |
67 |
12 |
저 나름대로 삽질도 많이 하긴했는데 잘안되네요ㅠㅠ
sql 고수님들 부탁드립니다.
두 값을 추출하여 빼서
순수사용량만 보이게하려면
어떻게해야될까요.
제발 도움 부탁드립니다 ㅎㅎ
Comment 5
-
건우아빠
2016.08.23 21:25
with resas(select '100101010001' rsd_id ,'2016/08/01 00:00:00' utime ,120 pow union allselect '100101010001' rsd_id ,'2016/08/01 00:30:00' utime ,137 union allselect '100101010001' rsd_id ,'2016/08/01 01:00:00' utime ,148 union allselect '100101010001' rsd_id ,'2016/08/01 01:30:00' utime ,162 union allselect '100101010001' rsd_id ,'2016/08/01 11:30:00' utime ,267 union allselect '100101010001' rsd_id ,'2016/08/02 00:00:00' utime ,278 union allselect '100101010001' rsd_id ,'2016/08/02 00:30:00' utime ,283 union allselect '100101010002' rsd_id ,'2016/08/01 00:00:00' utime ,341 union allselect '100101010002' rsd_id ,'2016/08/01 00:30:00' utime ,347 union allselect '100101010002' rsd_id ,'2016/08/01 01:00:00' utime ,354 union allselect '100101010002' rsd_id ,'2016/08/01 11:30:00' utime ,382 union allselect '100101010002' rsd_id ,'2016/08/02 00:00:00' utime ,388 union allselect '100101010002' rsd_id ,'2016/08/02 00:30:00' utime ,397 ) ,result as(select a.rsd_id, ROW_NUMBER() over( partition by a.rsd_id order by a.utime ) seq, a.utime, a.powfrom res a )select b.rsd_id , MIN(b.pow) , MAX(a.pow) , MAX(a.pow) - MIN(b.pow)from result a right join result b on a.rsd_id = b.rsd_id and a.seq = b.seq + 1where left(b.utime,10) BETWEEN '2016/08/01' AND '2016/08/01'group by b.rsd_id -
hunit
2016.08.24 09:33
댓글 달아주셔서 정말정말 감사합니다ㅎㅎ
하지만 제가 능력이 부족한건지
where left(b.utime,10) BETWEEN '2016/08/01' AND '2016/08/01'
group by b.rsd_id구절에서 left가 오류가나네요ㅠㅠㅠORA-00904: "LEFT": 부적합한 식별자
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
26행, 7열에서 오류 발생저는 기본테이블이 있기때문에 제 컬럼명대로 바꿔서 쿼리를 짜보니with result as(
select a.rsd_id, ROW_NUMBER () OVER (PARTITION BY a.rsd_id ORDER BY a.lp_cptr_time) seq,
a.lp_cptr_time, a.act_pwrer
from emst_raw_data a)
select b.rsd_id, MIN(b.act_pwrer), MAX(a.pwrer), MAX(a.pwrer)-MIN(b.pwerer)
from result a right join result b on a.rsd_id = b.rsd_id and a.seq = b.seq + 1
where left(b.lp_cp_time,10) BETWEEN '2016/08/01' AND '2016/08/01'
group by b.rsd_id;이렇게 됬고 밑에서 두번째줄 left에서 에러가났습니다. 무엇이 문제일까요??ㅜㅜ -
건우아빠
2016.08.24 10:56
아마 컬럼이 날짜형이서 그런듯 합니다.
convert(varchar(8) , 컬럼, 112) BETWEEN '20160801' AND '20160801'
-
hunit
2016.08.24 16:33
건우아빠님 답변 달아주셔서 정말 거듭 감사드립니다.
죄송하지만 마지막으로 한번만 더 여쭤봐도 될까요??ㅠㅠ
제가 구하고자 하는건 순수사용량이 맞지만 현재 아래와같이
rsd_id
min pwrer
maxpwrer
max-min pwer
하루 일자의 사용량만 출력됩니다.
궁금한건 아래 표와같이 select문안에 2일 3일 4일 컬럼을 추가하여 값을 넣고 싶은데요.
rsd_id
max-min pwrer ( 8월 1일값)
max-min pwrer(8월2일 값)
(8월3일값)
(8월4일값)
(8월5일값)
(8월6일)
현재 rsd_id와 max-min pwrer은 출력시키지만 한 쿼리내에서 select문도 추가하게되면 where절에서 날짜를 추가해야되는건가요??
도무지 감이 안옵니다.
하나의 날짜만 선언해놓고 8월 1일, 2일, 3일, 4일 출력 계산이 안될 것 같은데 무엇을 추가해야하고 수정해야할지 ..
한번만 더 부탁드립니다. ㅠㅠ
현재 제 쿼리는 아래와 같습니다
with result as(
select a.rsd_id, ROW_NUMBER () OVER (PARTITION BY a.rsd_id ORDER BY to_char(a.lp_cptr_time)) seq, a.lp_cptr_time, a.act_pwrer
from emst_raw_data a)
select b.rsd_id, MIN(b.act_pwrer), MAX(a.act_pwrer), MAX(a.act_pwrer)-MIN(b.act_pwrer)
from result a right join result b on a.rsd_id = b.rsd_id and a.seq = b.seq + 1
where b.lp_cptr_time BETWEEN to_date('2016/08/01', 'yyyy/mm/dd') and to_date('2016/08/01', 'yyyy/mm/dd')
group by b.rsd_id; -
건우아빠
2016.08.24 23:18
데이타가 어떻게 들어가 있고 어떤게 결과값이 나오는지를 기술해 주시면 좋을듯 합니다. 오라클이라 적용이 될런지는...
with res
as
(
select '100101010001' rsd_id ,'2016/08/01 00:00:00' utime ,120 pow union all
select '100101010001' rsd_id ,'2016/08/01 00:30:00' utime ,137 union all
select '100101010001' rsd_id ,'2016/08/01 01:00:00' utime ,148 union all
select '100101010001' rsd_id ,'2016/08/01 01:30:00' utime ,162 union all
select '100101010001' rsd_id ,'2016/08/01 11:30:00' utime ,267 union all
select '100101010001' rsd_id ,'2016/08/02 00:00:00' utime ,278 union all
select '100101010001' rsd_id ,'2016/08/02 00:30:00' utime ,283 union all
select '100101010002' rsd_id ,'2016/08/01 00:00:00' utime ,341 union all
select '100101010002' rsd_id ,'2016/08/01 00:30:00' utime ,347 union all
select '100101010002' rsd_id ,'2016/08/01 01:00:00' utime ,354 union all
select '100101010002' rsd_id ,'2016/08/01 11:30:00' utime ,382 union all
select '100101010002' rsd_id ,'2016/08/02 00:00:00' utime ,388 union all
select '100101010002' rsd_id ,'2016/08/02 00:30:00' utime ,397 ) ,
result as
(
select a.rsd_id
, ROW_NUMBER() over( partition by a.rsd_id order by a.utime ) seq
, a.utime
, a.pow
from res a )
select rsd_id
, SUM( [01]) [01]
, SUM( [02]) [02]
from ( select b.rsd_id
, ( case when left(b.utime,10) = '2016/08/01' then MAX(a.pow) - MIN(b.pow) end ) [01]
, ( case when left(b.utime,10) = '2016/08/02' then MAX(a.pow) - MIN(b.pow) end ) [02]
-- MIN(b.pow) , MAX(a.pow) , MAX(a.pow) - MIN(b.pow)
from result a right join result b on a.rsd_id = b.rsd_id and a.seq = b.seq + 1
where left(b.utime,10) BETWEEN '2016/08/01' AND '2016/08/02'
group by b.rsd_id , left(b.utime,10) ) r
group by rsd_id
