다음과 같은 형식의 테이블이 있습니다. 각 SPEED별로 MAX([T]) 값에서 1분을 뺀 특정 범위의 값을 구하고자 합니다.
어떤 형식으로 접근해야 할까요..?
간단하게 뽑으려면
SELECT ID, PHASE, SPEED, LAG([T], 3, 0) OVER (PARTITION BY ID, PHASE, SPEED ORDER BY ID, T) as '3' ,LAG([T], 2, 0) OVER (PARTITION BY ID, PHASE, SPEED ORDER BY ID, T) as '2' ,LAG([T], 1, 0) OVER (PARTITION BY ID, PHASE, SPEED ORDER BY ID, T) as '1' , T as '0'
이런식으로 뽑아서 사용해도 되긴 하는데... 당장 급하게 사용하려니 이 방법 밖에 안 떠오르네요. 혹시 다른 방법으로 접근할 수 있도록 어떤식으로 접근하면 될지 코멘트 좀 부탁드려도 될까요?
Comment 12
-
항해자™
2016.01.26 11:46
-
Terry
2016.01.26 11:50
휴..억지로 이해했네요 -,-;;;
각 ID 별로 speed 의 Max 값을 뽑아와서
Between 맥스값-1분 And 맥스값
인 데이터를 다른 방법으로 가지고 올수 있는지를 문의하시는거죠?
---추가---
다시 보니 1분이 아닌듯..
1분보다는 크고 max 값보다는 작거나 같은 조건인듯..
-
항해자™
2016.01.26 11:55
between 맥스-1분+1초 and 맥스 도 맞을듯 하네요ㅋㅋ
-
Gusto
2016.01.26 14:07
조금 있다가 수정해서 댓글 달도록 하겠습니다.^^;;
-
항해자™
2016.01.26 11:51
;with cteSample as (
select cSeed
,dateadd(minute,-1,max(cTime)) as cStartTime
,max(tTime) as cLimitTime
from dbo.tSample
group by cSeed
)
select b.*
from cteSample as a
join dbo.tSample as b
on b.cSeed = a.cSeed
where b.cTime > a.cStartTime
and b.cTime <= a.cLimitTime
-
항해자™
2016.01.26 11:53
이런거 같은데, 맞는지 모르겠네요ㅎㅎ
-
Gusto
2016.01.26 13:32
넵.. 이렇게 접근하니까 잘 되네요. 부족한 설명은 조금 있다가 글수정과 댓글로 하도록 하겠습니다.
-
Gusto
2016.01.26 13:06
아.. 제가 글을 이상하게 써놔서 죄송합니다.
SPEED 27 일 때, 00:02:15 ~ 00:03:00
SPEED 40 일 때, 00:05:15 ~ 00:06:00
SPEED 55 일 때, 00:08:15 ~ 00:09:00
SPEED 68 일 때 00:09:30 ~ 00:10:15
이 구간의 값만을 조회하고자 합니다.
-
Terry
2016.01.26 18:32
tblA 가 덧글에 적어두신 규칙(?) 을 적용하기 위한
with 절입니다.
규칙이 현재대로 고정이라면 그대로 쓰시면 될테구..
늘어날 가능성이 있다..
라고 하면 예외 테이블로 하나 생성해두시고
추가되는 규칙들을 넣어두시면
차후 규칙이 변경되어도
해당 테이블과 Join 만 하면 되니
유지보수 측면에서 용이할듯하네요..
하기쿼리 참고하세요.
---쿼리시작---
;with tblA(SPEED,S_T,E_T) As
(
Select 27,'00:02:15','00:03:00' Union All
Select 40,'00:05:15','00:06:00' Union All
Select 55,'00:08:15','00:09:00' Union All
Select 68,'00:09:30','00:10:15'
)
,tblB(ID,T,PHASE,SPEED) As
(
Select '11','00:00:15','3',27 Union All
Select '12','00:00:30','3',27 Union All
Select '13','00:00:45','3',27 Union All
Select '14','00:01:00','3',27 Union All
Select '15','00:01:15','3',27 Union All
Select '16','00:01:30','3',27 Union All
Select '17','00:01:45','3',27 Union All
Select '18','00:02:00','3',27 Union All
Select '19','00:02:15','3',27 Union All
Select '20','00:02:30','3',27 Union All
Select '201','00:02:45','3',27 Union All
Select '202','00:03:00','3',27 Union All
Select '21','00:03:15','3',40 Union All
Select '22','00:03:30','3',40 Union All
Select '23','00:03:45','3',40 Union All
Select '24','00:04:00','3',40 Union All
Select '25','00:04:15','3',40 Union All
Select '26','00:04:30','3',40 Union All
Select '27','00:04:45','3',40 Union All
Select '28','00:05:00','3',40 Union All
Select '29','00:05:15','3',40 Union All
Select '291','00:05:30','3',40 Union All
Select '292','00:05:45','3',40 Union All
Select '293','00:06:00','3',40 Union All
Select '31','00:06:15','3',55 Union All
Select '32','00:06:30','3',55 Union All
Select '33','00:06:45','3',55 Union All
Select '34','00:07:00','3',55 Union All
Select '35','00:07:15','3',55 Union All
Select '36','00:07:30','3',55 Union All
Select '37','00:07:45','3',55 Union All
Select '38','00:08:00','3',55 Union All
Select '39','00:08:15','3',55 Union All
Select '391','00:08:30','3',55 Union All
Select '392','00:08:45','3',55 Union All
Select '393','00:09:00','3',55 Union All
Select '31','00:09:15','3',68 Union All
Select '32','00:09:30','3',68 Union All
Select '33','00:09:45','3',68 Union All
Select '34','00:10:00','3',68 Union All
Select '35','00:10:15','3',68
)
Select a.*
From tblB a
Inner Join tblA b
On a.SPEED = b.SPEED
And a.T Between b.S_T And b.E_T
---쿼리끝--- -
Gusto
2016.01.27 09:58
<수정>
목적 - 속도(SPEED)의 마지막 1분 동안의 값만을 그룹으로 묶어 연산하고자 함
설명 - 속도와 마지막 1분 동안의 구간은 다음과 같습니다.
27 (00:02:15 ~ 00:03:00)
40 (00:05:15 ~ 00:06:00)
55 (00:08:15 ~ 00:09:00)
68 (00:09:30 ~ 00:10:15)
해당하는 1분의 구간만을 그룹으로 묶어서 연산하고자 하였습니다.
<예제 데이터>
<결과 데이터>
-
Terry
2016.01.27 11:14
하기 쿼리 참고하세요..
---쿼리시작---
;with tblA(SPEED,S_T,E_T) As
(
Select 27,'00:02:15','00:03:00' Union All
Select 40,'00:05:15','00:06:00' Union All
Select 55,'00:08:15','00:09:00' Union All
Select 68,'00:09:30','00:10:15'
)
,tblB(ID,T,PHASE,SPEED,example) As
(
Select 'ID','00:00:15','3',27,1 Union All
Select 'ID','00:00:30','3',27,2 Union All
Select 'ID','00:00:45','3',27,3 Union All
Select 'ID','00:01:00','3',27,4 Union All
Select 'ID','00:01:15','3',27,5 Union All
Select 'ID','00:01:30','3',27,6 Union All
Select 'ID','00:01:45','3',27,7 Union All
Select 'ID','00:02:00','3',27,8 Union All
Select 'ID','00:02:15','3',27,9 Union All
Select 'ID','00:02:30','3',27,10 Union All
Select 'ID','00:02:45','3',27,11 Union All
Select 'ID','00:03:00','3',27,12 Union All
Select 'ID','00:03:15','3',40,13 Union All
Select 'ID','00:03:30','3',40,14 Union All
Select 'ID','00:03:45','3',40,15 Union All
Select 'ID','00:04:00','3',40,16 Union All
Select 'ID','00:04:15','3',40,17 Union All
Select 'ID','00:04:30','3',40,18 Union All
Select 'ID','00:04:45','3',40,19 Union All
Select 'ID','00:05:00','3',40,20 Union All
Select 'ID','00:05:15','3',40,21 Union All
Select 'ID','00:05:30','3',40,22 Union All
Select 'ID','00:05:45','3',40,23 Union All
Select 'ID','00:06:00','3',40,24 Union All
Select 'ID','00:06:15','3',55,25 Union All
Select 'ID','00:06:30','3',55,26 Union All
Select 'ID','00:06:45','3',55,27 Union All
Select 'ID','00:07:00','3',55,28 Union All
Select 'ID','00:07:15','3',55,29 Union All
Select 'ID','00:07:30','3',55,30 Union All
Select 'ID','00:07:45','3',55,31 Union All
Select 'ID','00:08:00','3',55,32 Union All
Select 'ID','00:08:15','3',55,33 Union All
Select 'ID','00:08:30','3',55,34 Union All
Select 'ID','00:08:45','3',55,35 Union All
Select 'ID','00:09:00','3',55,36 Union All
Select 'ID','00:09:15','3',68,37 Union All
Select 'ID','00:09:30','3',68,38 Union All
Select 'ID','00:09:45','3',68,39 Union All
Select 'ID','00:10:00','3',68,40 Union All
Select 'ID','00:10:15','3',68,41
)
Select a.ID
,a.PHASE
,a.SPEED
,SUM(a.example) As example
From tblB a
Inner Join tblA b
On a.SPEED = b.SPEED
And a.T Between b.S_T And b.E_T
Group By a.ID
,a.PHASE
,a.SPEED
---쿼리끝--- -
Gusto
2016.01.28 16:01
Terry님과 항해자™님 답변 참고하여 해결하였습니다. 친절한 답변 감사합니다.
무슨 말인지 이해가 잘 안되네요,,,
샘플 데이터를 활용할 수 있게 텍스트로 올려주시고, 결과값에 대한 샘플도 올려주시면 답변하기가 좋지 않을까요?