데이터베이스 질문과 답변

pk 로 조회 시 rank 관련 질문드립니다. 0 4 9,304

by 커피요쿠르트d [SQL Query] 오라클 oracle 랭크 rank [2015.02.02 17:29:24]

수고하십니다. 

특정 자료의 순위 관련 정보를 콕 집어낼려는데 잘 안되서 질문드립니다. 

 

원 자료는 다음과 같은 행태입니다. 

 

WITH t1 AS (                                   
SELECT 'aaa01' pk, 'aaa' grp FROM dual UNION all
SELECT 'aaa02' pk, 'aaa' grp FROM dual UNION all
SELECT 'bbb01' pk, 'bbb' grp FROM dual UNION all
SELECT 'bbb02' pk, 'bbb' grp FROM dual 
), t2 AS (                                        
SELECT 'aaa01' pk, 'p1' userId FROM dual UNION ALL 
SELECT 'aaa02' pk, 'p1' userId FROM dual UNION ALL
SELECT 'bbb01' pk, 'p2' userId FROM dual UNION ALL 
SELECT 'bbb02' pk, 'p3' userId FROM dual   
) 
SELECT * FROM t1 a, t2 b
WHERE a.pk = b.pk

pk 값과 그룹 (grp) 정보를 알수 있는 테이블이 t1 

또다른 테이블 t2 에는 

pk에 매칭되는 사용자 정보(userid)가 있습니다. 

원하는 것은 다음과 같이 pk를 날렸을 때 해당 그룹에서 사용자 순번을 알고 싶습니다. 
 

WITH result AS (                                   
SELECT 'aaa01' pk, '1' rnk  FROM dual UNION all
SELECT 'aaa02' pk, '2' rnk  FROM dual UNION all
SELECT 'bbb01' pk, '1' rnk  FROM dual UNION all
SELECT 'bbb02' pk, '1' rnk  FROM dual 
)SELECT * FROM result WHERE pk = 'aaa02'

 

aaa그룹에는 aaa01과 aaa02가 있는데 둘다 사용자가 p1으로 되어 있으므로 pk순번에 따라 1,2가 떨어지면 되고
bbb그룹에는 bbb01과 bbb02가 각각 p2 와 p3가 있으므로 각각 1,1 이 떨어지면 됩니다. 

이게 윈도우 함수인rank()를 사용해봤는데 걸리는게 pk로 딱 찍어서 select 쿼리로 날리다 보니 
그룹핑이 안되므로 당연히 1이 떨어지네요;;

쿼리 짜다보니 바보같은 짓을 하나 싶어서 질문드립니다. 

좋은 방법이 있을까요?

가르침 부탁드립니다. 

by 마농 [2015.02.02 17:50:06]

- 원인 : 조건으로 걸러내고 랭킹을 구해서 그래요.
- 해결 : 랭킹부터 구해놓고 조건을 주셔야 해요.
- 문제 : 전체 랭킹부터 구하는데 발생하는 비용에 대한 개선 노력이 필요해요.
- 기타 : 질문의 예시자료 잘못 올리신 듯(t2 의 bbb01 이 중복)

by 커피요쿠르트d [2015.02.02 18:10:09]

//마농

감사합니다!!
어렴풋이 말씀하신 "원인"이 문제일거라고 생각했는데 머리속이 꼬여서인지 "해결"이 딱 생각나지 않았네요 

스칼라 서브 쿼리로 전체 랭킹 후 필터링 해보는 방법을 해봐야겠습니다. 그러면 알려주신 "문제"에 걸리는 상황일 거 같은데요.. 문제 내용 관련해서는 전체 랭킹 구하는 칼럼의 INDEX 처리로 가능할까요? 아니면 프로시저나 펑션으로 커서로 랭킹을 직접 구하는게 더 비용상 유리할까요?
란 질문은 바보 같군요 ㅡㅡ;;ㅋ 
이래저래 해봐야겠습니다. 

기타 내용은 잘못기입한게 맞아요 ㅋ 수정했습니다. 

감사합니다! 즐거운 저녁 보내셔요.

by 커피요쿠르트d [2015.02.02 18:36:09]

음...
먼저 rank 정보를 추출하고 pk별로 필터링하는 방법으로 해서 일단 원하는 결과는 나오게 되었습니다.. 
아래에 쿼리 붙여넣습니다.

WITH t1 AS (                                   
SELECT 'aaa01' pk, 'aaa' grp FROM dual UNION all
SELECT 'aaa02' pk, 'aaa' grp FROM dual UNION all
SELECT 'bbb01' pk, 'bbb' grp FROM dual UNION all
SELECT 'bbb02' pk, 'bbb' grp FROM dual 
), t2 AS (                                        
SELECT 'aaa01' pk, 'p1' userId FROM dual UNION ALL 
SELECT 'aaa02' pk, 'p1' userId FROM dual UNION ALL
SELECT 'bbb01' pk, 'p2' userId FROM dual UNION ALL 
SELECT 'bbb02' pk, 'p3' userId FROM dual   
)                

SELECT 
  (
    SELECT RNK FROM ( SELECT t1.PK, Rank() OVER (PARTITION BY t1.GRP, t2.USERID  ORDER BY t1.PK ) AS RNK FROM  t1 , t2  WHERE t1.PK = t2.PK /* and t1.grp = 'aaa' 이런식으로 직접 입력.. */) BASE
    WHERE BASE.PK = a.pk
   ) AS rnk
, a.*,b.* FROM t1 a, t2 b
WHERE a.pk = b.pk
AND a.pk = 'aaa02'

문제는 마농님 말씀대로 랭크 정보 추출 시 적절한 필터링 정보가 없어서 비용이 많이 소요되겠네요. 
이건 프로시저나 펑션으로 처리하면 될 듯 싶습니다.
먼저 pk의 grp 정보를 조회해온 후 랭킹 정보 추출 시 파라미터로 직접 넣어주면 조금 나을 듯 싶네요.
감사합니다.
 

by 마농 [2015.02.02 19:19:12] 
SELECT a.pk
     , a.grp
     , b.userid
     , (SELECT COUNT(*) + 1
          FROM t1 c
             , t2 d
         WHERE c.pk     = d.pk
           AND c.grp    = a.grp
           AND d.userid = b.userid
           AND c.pk     < b.pk
        ) rnk
  FROM t1 a
     , t2 b
 WHERE a.pk  = b.pk
   AND a.pk = 'aaa02'
;

 

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