구간별 데이터 검색이 가능할까요? 0 6 1,620

by Gusto [SQLServer] 쿼리 구간 데이터 [2015.05.14 11:16:58]


id time date hour minute sleep
TEST1 2014-12-08 13:24 2014-12-08 13 24 0
TEST1 2014-12-08 13:25 2014-12-08 13 25 0
TEST1 2014-12-08 13:26 2014-12-08 13 26 0
TEST1 2014-12-08 13:27 2014-12-08 13 27 0
TEST1 2014-12-08 13:28 2014-12-08 13 28 0
TEST1 2014-12-08 13:29 2014-12-08 13 29 0
TEST1 2014-12-08 13:30 2014-12-08 13 30 0
TEST1 2014-12-08 13:31 2014-12-08 13 31 0
TEST1 2014-12-08 13:32 2014-12-08 13 32 0
TEST1 2014-12-08 13:33 2014-12-08 13 33 0
TEST1 2014-12-08 13:34 2014-12-08 13 34 0
TEST1 2014-12-08 13:35 2014-12-08 13 35 0
TEST1 2014-12-08 13:36 2014-12-08 13 36 0
TEST1 2014-12-08 13:37 2014-12-08 13 37 0
TEST1 2014-12-08 13:38 2014-12-08 13 38 0
TEST1 2014-12-08 13:39 2014-12-08 13 39 0
TEST1 2014-12-08 13:40 2014-12-08 13 40 0
TEST1 2014-12-08 13:41 2014-12-08 13 41 0

위와 같은 데이터가 있을 경우(시계열 자료) 해당 자료의 ID별로 10분당 sleep 을 카운트하고자 합니다.

즉 TEST1 아이디의 2014-12-08일 13:00 ~ 13:09분, 13:10 ~ 13:19, 13:20 ~ 13:29 .... 이렇게 시간별로 10분당 구간에 해당하는 sleep 을 카운트 하고자 할 때 어떤 형식으로 접근을 해야될지 모르겠습니다.

쿼리문으로 알려주시는 것보다는 "이런 형식으로 접근해야 될 것 같다"라는 로직 위주로 알려주시면 직접 해보도록 하겠습니다. 감사합니다.

by 창조의날개 [2015.05.14 11:30:48]

1. TIME 컬럼을 10분단위로 그룹핑 합니다.

   : 그룹핑 하는 방법은 한번 생각해 보세요..

2. 해당 그룹기준으로 GROUP BY해서 COUNT합니다..

 

여기서 관건은 당연히 10분 단위로 그룹을 만드는 방법이겠죠..

여러가지 방법이 있을 수 있으니 한번 생각해 보세요..

 

 


by jkson [2015.05.14 12:28:39]

음.. 쿼리는 적지 말라고 하셨으나 약간의 힌트는 드리는 게 좋을 것 같군요.

(time- trunc(time)) * 1440 이렇게 하시면 시간 단위가 분단위로 바뀌겠죠?

이걸 10분 단위로 묶는 쿼리를 만들어보세요.

아 뒤에 date, hour, minute까지 쪼개놓으셨군요. 그러면 뭐 minute 앞 자리만 가지고 grouping 하시면 되겠네요.


by Gusto [2015.05.14 14:43:36]
SELECT id
  , currentdate
  , date
  , hour
  , count(sleep) as Sleep_count_ten_intervals
FROM t
WHERE sleep = 1
GROUP BY DATEPART(YEAR, time)
  , DATEPART(MONTH, time)
  , DATEPART(DAY, time)
  , DATEPART(HOUR, time)
  , (DATEPART(MINUTE, time) / 10)
  , id
  , currentdate
  , date
  , hour
ORDER BY id, date, hour, currentdate

창조의날개님과 jkson 님께서 말씀해주신 방법은 감을 못 잡고 있어서 다른 방법으로 시도해봤습니다. 위 쿼리문으로 할 경우 아래의 테이블처럼 값이 나옵니다. 이 부분에서 sleep 이 계산된 시작분 ~ 종료 분을 따로 집어넣으려면 어떻게 해야될까요?

id currentdate date hour Sleep_count_ten_intervals
test 2 2014-12-09 0 4
test 2 2014-12-09 1 10
test 2 2014-12-09 1 7
test 2 2014-12-09 1 10
test 2 2014-12-09 1 10
test 2 2014-12-09 1 10
test 2 2014-12-09 1 10
test 2 2014-12-09 2 10
test 2 2014-12-09 2 10
test 2 2014-12-09 2 10
test 2 2014-12-09 2 10
test 2 2014-12-09 2 10
test 2 2014-12-09 2 10

 

또한, 창조의날개님과 jkson님께서 확인하신다면 조금만 더 힌트를 부탁드려도 될까요?

창조의날개님께서 말씀해주신 방법으로 하기 전에 jkson 님께서 말씀해주신 분단위로 바꾸는 방법을 먼저 수행한 다음에 해야되는 것 같은데... 어떤 로직인지 감이 잘 안 잡히네요.


by 창조의날개 [2015.05.14 15:28:28]

GROUP BY를 너무 많이 한거 같은데요..

 

아래 쿼리를 참고해서 한번 생각해 보세요..

 


SELECT GP, MIN(RN) START_RN, MAX(RN) END_RN
FROM (SELECT ROWNUM RN, CEIL(ROWNUM/10) GP 
      FROM DUAL CONNECT BY LEVEL <= 30)
GROUP BY GP
;

 


by 마농 [2015.05.14 15:36:12]
-- 작성하신 쿼리를 보면 이미 테이블 내에
-- date, hour, minute 항목을 따로 가지고 있는듯 한데요?
-- 그렇다면 굳이 time 항목을 DATEPART 로 다시 계산할 필요가 없을 듯 하네요.
SELECT id
     , currentdate
     , date
     , hour
     , minute / 10 * 10     AS minute_from
     , minute / 10 * 10 + 9 AS minute_to
     , COUNT(*) AS sleep
  FROM t
 GROUP BY id
     , currentdate
     , date
     , hour
     , minute / 10 * 10
;

 


by Gusto [2015.05.14 17:05:32]

힌트주신 내용으로도 계속 해보고 추가 댓글 달도록 하겠습니다.

답변 감사합니다.

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