데이터베이스 질문과 답변
특정 속성이 0 또는 1의 값을 가질때
시간조건에 따라 첫번째 0값이 나타나는 row와 1이후에 0값이 나오는 row의 값을 알고싶을때 어떻게 접근해야될까요?
시간조건이라면 컬럼 말씀인가요?
샘플데이터와 원하는 결과를 올려주세요.
.
.
id, time, status
1, 09:57, 1
1, 09:56, 0
1, 09:55, 1
1, 09:54, 0
이런컬럼에서
1, 09:55, 09:56 값을 뽑고싶어요
WITH T (id, time, status ) AS
(
SELECT 1, '09:57', 1 FROM DUAL UNION ALL
SELECT 1, '09:56', 0 FROM DUAL UNION ALL
SELECT 1, '09:55', 1 FROM DUAL UNION ALL
SELECT 1, '09:54', 0 FROM DUAL
)
SELECT *
FROM (SELECT ID
, TIME
, LEAD(TIME) OVER(PARTITION BY ID ORDER BY TIME DESC ) TIME2
, STATUS
, ROW_NUMBER() OVER(PARTITION BY ID , STATUS ORDER BY TIME DESC ) RN
FROM T )
WHERE RN = 1
AND STATUS = 0
-- scalar subquery 이용
WITH T (id, time, status ) AS
(
SELECT 1, '09:57', 1 FROM DUAL UNION ALL
SELECT 1, '09:56', 0 FROM DUAL UNION ALL
SELECT 1, '09:55', 1 FROM DUAL UNION ALL
SELECT 1, '09:54', 0 FROM DUAL
)
SELECT A.*
, (SELECT MAX(TIME) FROM T WHERE T.ID = A.ID AND T.STATUS = 1 AND T.TIME < A.TIME ) TIME2
FROM (SELECT ID
, TIME
, STATUS
, ROW_NUMBER() OVER(PARTITION BY ID , STATUS ORDER BY TIME DESC ) RN
FROM T
) A
WHERE RN = 1
AND STATUS = 0
아찌님 답변
1번 답변은 status 가 같은 값이 연속으로 나올 경우에 원하는 결과가 안나오구요.
2번 답변은 0 이전에 1 이 여러개 나오면 서브쿼리 에러납니다. MAX(time) 해야 합니다.
아이고 급하게 하느라.. 뺴먹었네요.. ^^;
수정했어요.. ㅡㅡ;
예를 들어주세요.
원본 대비 결과표.
.
.
id, time, status
1, 09:57, 1
1, 09:56, 0
1, 09:55, 1
1, 09:54, 0
이런컬럼에서
1, 09:55, 09:56 값을 뽑고싶어요
0 과 1 은 항상 번갈아 나오나요?
연속으로 나오는 경우는 없는 모양이네요?
인덱스 구성은 어떻게 되나요?
항상 번갈아 나오지않고 0이 연속되기도 1이 연속되기도 합니다.
제가 찾는건 맨 처음 0의 시간과 이 레코드 기준으로 다음레코드에 처음나오는 1이 나오는 시간값을 찾고있습니다.
인덱스는 id,time pk로 인덱스 구성되어있어요
예를 들어주실 때는 모든 가능한 경우를 들어주셔야 합니다.
특정 일부분만 예를 들면 특정부분에만 부합하는 답변을 받을 수 있습니다.
앗 틀렸다.. ㅡㅡ;
틀린게 아니라 질문의 예시만 봤을 때는 정답입니다.
예시자료가 실제와 달랐을 뿐이죠.
질문이 정확해야만 하는 이유입니다.
네... ^^*
WITH t AS
(
SELECT 1 id, '09:57' time, 1 status FROM dual
UNION ALL SELECT 1, '09:56', 0 FROM dual
UNION ALL SELECT 1, '09:55', 1 FROM dual
UNION ALL SELECT 1, '09:54', 0 FROM dual
UNION ALL SELECT 2, '09:57', 1 FROM dual
UNION ALL SELECT 2, '09:56', 0 FROM dual
UNION ALL SELECT 2, '09:55', 0 FROM dual
UNION ALL SELECT 2, '09:54', 0 FROM dual
UNION ALL SELECT 2, '09:53', 1 FROM dual
UNION ALL SELECT 2, '09:52', 0 FROM dual
UNION ALL SELECT 2, '09:51', 1 FROM dual
)
SELECT id
, s_time
, e_time
FROM (SELECT id, status
, time e_time
, LEAD(DECODE(status, 1, time)) IGNORE NULLS
OVER(PARTITION BY id ORDER BY time DESC) s_time
, ROW_NUMBER() OVER(PARTITION BY id, status ORDER BY time DESC) rn
FROM t
)
WHERE status = 0
AND rn = 1
;
전체자료를 모두 읽는 방식인데요.
인덱스 구성에 따라 최적화 쿼리는 다른 형태로 구현될 수 있습니다.
DBMS 는 오라클인가요?
DB는 postgresql 입니다.
마농님과 아찌님 query보면서 다시 해보고 있는데 아직 부족하네요ㅠㅠ
왜 질문하실 때 DBMS 를 명시하지 않을까요? 문법이 다 다른데.
포스그래에서는 IGNORE NULLS 구문이 안되므로 아찌님 방법을 사용하면 될텐데요?
뭐가 안될까요?
WITH t AS
(
SELECT 1 id, '09:57' vtime, 1 status
UNION ALL SELECT 1, '09:56', 0
UNION ALL SELECT 1, '09:55', 1
UNION ALL SELECT 1, '09:54', 0
UNION ALL SELECT 2, '09:57', 1
UNION ALL SELECT 2, '09:56', 0
UNION ALL SELECT 2, '09:55', 0
UNION ALL SELECT 2, '09:54', 0
UNION ALL SELECT 2, '09:53', 1
UNION ALL SELECT 2, '09:52', 0
UNION ALL SELECT 2, '09:51', 1
)
SELECT id
, vtime
, (SELECT MAX(b.vtime)
FROM t b
WHERE b.id = a.id
AND b.vtime < a.vtime
AND b.status = 1
) s_time
FROM (SELECT id, status
, vtime
, ROW_NUMBER() OVER(PARTITION BY id ORDER BY vtime DESC) rn
FROM t
WHERE status = 0
) a
WHERE rn = 1
;
WITH t AS
(
SELECT 1 id, '09:57' vtime, 1 status
UNION ALL SELECT 1, '09:56', 0
UNION ALL SELECT 1, '09:55', 1
UNION ALL SELECT 1, '09:54', 0
UNION ALL SELECT 2, '09:57', 1
UNION ALL SELECT 2, '09:56', 0
UNION ALL SELECT 2, '09:55', 0
UNION ALL SELECT 2, '09:54', 0
UNION ALL SELECT 2, '09:53', 1
UNION ALL SELECT 2, '09:52', 0
UNION ALL SELECT 2, '09:51', 1
)
SELECT id
, s_time
, e_time
FROM (SELECT id, status
, vtime e_time
, MAX(CASE status WHEN 1 THEN vtime END) OVER(PARTITION BY id ORDER BY vtime) s_time
, ROW_NUMBER() OVER(PARTITION BY id, status ORDER BY vtime DESC) rn
FROM t
) a
WHERE status = 0
AND rn = 1
;
DBMS를 명시하지 않은건 방법접근을 어떻게 하는지 물어보고 싶었기때문이에요.
쿼리를 직접적으로 받기보다는... 아무튼 공부더해보겠습니다. 감사합니다.
완료
SELECT id, vtime s_time, e_time
FROM ( select *,(CASE status WHEN 1 THEN LEAD(vtime) OVER ( partition by id order by vtime asc ) END ) e_time
from (SELECT id, vtime, status, lag( status ) over ( order by vtime asc ) tt
FROM t
WHERE id = 2 and vtime > '09:50' and vtime < '10:00'
ORDER BY vtime asc)tmp
where (status = 1 and tt = 0) or (status = 0 and tt = 1 ) ) a
WHERE e_time is not null;
음?
제가 제시한 예제 WITH 문을 가지고 돌려보면.
2, 09:53 ~ 09:54 이 나오는데 이 결과가 원하는 결과었던건가요?
첫번째 0 이라는 최초 설명과 좀 달라서... 저는 2, 09:53 ~ 09:56 이라고 생각했는데?
아앗.. 조금 헷갈리게 썼네요.
죄송합니다.
status가 1이면 장비가 켜진거고 0이면 꺼진건데요
가장 최근시간에 장비가 켜져있다가 꺼진 시간을 찾는 쿼리를 짤려고요.. 즉 1이 시작된 시간부터 0이되는 시간까지를 구하고 있었습니다.
설명이 서툰..
켜졌다 꺼진 시간
1,0,0,0 의 경우 (1,0),0,0 을 찾으면 되는데
1,1,0,0 의 경우 어떤 결과가 나와야 하나요?
- (1,1,0),0 ?
- 1,(1,0),0 ?
시간순서일때 위에 결과가 나와야합니다
) 버튼을 클릭하여 작성 하시면 됩니다.