꿈꾸는 개발자, DBA 커뮤니티 구루비

이전자료 참고한 값구하기 query

by 청새치 [SQL Query] [2013.06.13 16:14:53]

쿼리에 도움을 구합니다. ㅠㅠ

WITH t AS
(
SELECT '0001' r_no, to_date('20000105','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0001' r_no, to_date('20010501','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0001' r_no, to_date('20051205','yyyymmdd') r_date, 'Y' status FROM dual
UNION ALL SELECT '0001' r_no, to_date('20060130','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0002' r_no, to_date('20000101','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0002' r_no, to_date('20080830','yyyymmdd') r_date, 'Y' status FROM dual
UNION ALL SELECT '0003' r_no, to_date('20050513','yyyymmdd') r_date, 'Y' status FROM dual
UNION ALL SELECT '0003' r_no, to_date('20100105','yyyymmdd') r_date, 'Y' status FROM dual
UNION ALL SELECT '0003' r_no, to_date('20130130','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('19991202','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('20040303','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('20050110','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('20100705','yyyymmdd') r_date, 'Y' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('20111227','yyyymmdd') r_date, 'N' status FROM dual
UNION ALL SELECT '0004' r_no, to_date('20120305','yyyymmdd') r_date, 'Y' status FROM dual
)
select * from t
order by r_no, r_date;

R_NO : 등록자
R_DATE : 등록일
STATUS : 접속상태 (Y-성공, N-실패)

의 기초 데이터로 개인별 접속시도 횟수를 구하고 싶습니다.
(등록자가 바뀌거나 접속성공후에는 0으로 리셋)

등록자, 등록일, 접속상태,  이전접속시도횟수
------------------------------------------------
0001,   2000/01/05 00:00:00,   N,   0
0001,   2001/05/01 00:00:00,   N,   1
0001,   2005/12/05 00:00:00,   Y,   2
0001,   2006/01/30 00:00:00,   N,   0
0002,   2000/01/01 00:00:00,   N,   0
0002,   2008/08/30 00:00:00,   Y,   1
0003,   2005/05/13 00:00:00,   Y,   0
0003,   2010/01/05 00:00:00,   Y,   0
0003,   2013/01/30 00:00:00,   N,   0
0004,   1999/12/02 00:00:00,   N,   0
0004,   2004/03/03 00:00:00,   N,   1
0004,   2005/01/10 00:00:00,   N,   2
0004,   2010/07/05 00:00:00,   Y,   3
0004,   2011/12/27 00:00:00,   N,   0
0004,   2012/03/05 00:00:00,   Y,   1

by 용근님 [2013.06.13 16:21:01]

lead() over() 로 검색해보세요

by 청새치 [2013.06.13 17:01:54]

lag 함수를 사용하여 쿼리를 짜보았으나 원하는 결과가 나오지 않아서 질문드립니다.

처음부터 제가 짠 쿼리를 보여드리면 제대로 된 접근이 어려울수도 있겠다 싶어 질문만 드린점 죄송합니다.

답변 주셔서 감사합니다.

by 신이만든짝퉁 [2013.06.13 16:34:15]

select r_no
     , r_date
     , status
     , decode(lag(status) over(partition by r_no order by r_no, r_date), 'Y', 0
              , count(status) over(partition by r_no order by r_no, r_date range unbounded preceding)-1) try_no
from t

by 청새치 [2013.06.13 17:00:26]

답변감사합니다.

마지막 데이터( 예시가 부족하여 추가하였습니다.) 의 TRY_NO 가 1이 되어야 하는데 R_NO 로 그룹핑된 전체 합계가 나오고있습니다.

저도 비슷하게 접근하였으나 STATUS가 'Y' 된 이후에는 0부터 다시 리셋되어 카운트 되어야 하는 부분이 잘 해결이 안되네요

by 아린 [2013.06.13 17:21:09]

좀더 깔끔한 SQL 이 있을듯 한데..

SELECT r_no, r_date, status

, NVL(LAG(val) OVER(PARTITION BY r_no ORDER BY r_date), 0) val

FROM (SELECT r_no, r_date, status

, DECODE(status, 'N',
ROW_NUMBER() OVER(PARTITION BY r_no, rn ORDER BY r_date), 0) val

FROM (SELECT r_no, r_date, status

, ROW_NUMBER() OVER(PARTITION BY r_no ORDER BY r_date)

- DECODE(status, 'N',
SUM(DECODE(status, 'N', 1))
OVER(PARTITION BY r_no ORDER BY r_date)) rn

FROM t

)

ORDER BY r_no, r_date

)

by 케를로스 [2013.06.13 17:54:21]

이거 쿼리로 처리하기보다는.... 함수 만들어서 처리해야할꺼 같은데요 그냥 일반 쿼리는 COUNT 값을 초기화를 못하니깐요 ㅡㅡ;;

by 마농 [2013.06.13 18:20:56]

-- MODEL --
SELECT *
  FROM t
 MODEL
 PARTITION BY (r_no)
 DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY r_no ORDER BY r_date) rn)
 MEASURES (r_date, status, 0 cnt)
 RULES AUTOMATIC ORDER (cnt[ANY] = DECODE(NVL(status[cv()-1], 'Y'), 'Y', 0, cnt[cv()-1] + 1))
 ORDER BY r_no, r_date
;

-- 분석함수 --
SELECT r_no, r_date, status
     , COUNT(DECODE(status, 'N', 1))
       OVER(PARTITION BY r_no, grp ORDER BY r_date
       ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) cnt
  FROM (SELECT r_no, r_date, status
             , SUM(DECODE(status, 'N', 0, 1))
               OVER(PARTITION BY r_no ORDER BY r_date DESC) grp
          FROM t
        )
 ORDER BY r_no, r_date
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변