데이터베이스 질문과 답변
안녕하세요 매번 질문만 드리는 초보개발자입니다.
통계를 만들고 있는데 다음과 같은 요청사항이 있어서 풀어보다가 막혀서 도움을 요청드립니다.
with t as (select 'xxx' as user_id, 1 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 1 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 1 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'b' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'b' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'c' as val from dual) select * from t ;
다음의 데이터에서 user_id에 grp_cd별로 'a' 라는 value 가 가장 많이 분포되있는 그룹의 순서대로
랭크를 부여하고 싶은데,
원하는 결과의 표현방식은
| user_id | grp | rank |
| xxx | 1 | 1 |
| xxx | 2 | 2 |
| xxx | 3 | 3 |
의 형태로 표현하고 싶습니다.
어떻게 풀어나가야 할지 감이 잘 오질 않네요.
rank() 함수를 써서 풀어보려 했는데, 그룹의 데이터 분포에서 값을 구하는 거라 다른 방법이 필요한 것
같습니다. 고수님들의 도움을 부탁드립니다.
by 아발란체
[2014.10.27 14:09:24]
with t as ( select 'xxx' as user_id, 1 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 1 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 1 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'a' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'b' as val from dual union all select 'xxx' as user_id, 2 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'b' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'c' as val from dual union all select 'xxx' as user_id, 3 as grp_cd, 'c' as val from dual ) SELECT user_id, grp_cd AS grp, RANK() OVER(ORDER BY SUM(DECODE(val, 'a', 1, 0)) DESC) AS rank FROM t GROUP BY user_id, grp_cd ;
by 지누기
[2014.10.27 14:45:49]
아발란체님 감사합니다.
decode 사용해서 처리하면 되겠네요.
도움 많이 되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.