데이터베이스 질문과 답변
RANK 후 상대평가 A,B,C 점수 부여 방법 문의(%별할당)
1
4
2,450
by 김성수 [2013.11.13 09:25:33]
[DATA]
with a as
(
select 'KIM1' as NAME , 90 as SCORE from dual
union all
select 'KIM2' as NAME , 100 as SCORE from dual
union all
select 'KIM3' as NAME , 80 as SCORE from dual
union all
select 'KIM4' as NAME , 70 as SCORE from dual
union all
select 'KIM5' as NAME , 50 as SCORE from dual
union all
select 'KIM6' as NAME , 40 as SCORE from dual
)
select NAME, SCORE, RANK() OVER (ORDER BY SCORE DESC) AS RANKS
, ' ' AS EVAL
FROM A;
* EVAL 컬럼의 기준
A = 전체 인원의 20%
B = 전체인원의 40%
C = 전체인원의 40%
[설명]
위 내용과 같이 성적대로 순위를 나열 후 EVAL 컬럼에 A,B,C 라는 점수를 주고 싶습니다.
with a as
(
select 'KIM1' as NAME , 90 as SCORE from dual
union all
select 'KIM2' as NAME , 100 as SCORE from dual
union all
select 'KIM3' as NAME , 80 as SCORE from dual
union all
select 'KIM4' as NAME , 70 as SCORE from dual
union all
select 'KIM5' as NAME , 50 as SCORE from dual
union all
select 'KIM6' as NAME , 40 as SCORE from dual
)
select NAME, SCORE, RANK() OVER (ORDER BY SCORE DESC) AS RANKS
, ' ' AS EVAL
FROM A;
| [결과] | |||
| NAME | SCORE | RANKS | EVAL |
| KIM2 | 100 | 1 | A |
| KIM1 | 90 | 2 | B |
| KIM3 | 80 | 3 | B |
| KIM4 | 70 | 4 | C |
| KIM5 | 50 | 5 | C |
* EVAL 컬럼의 기준
A = 전체 인원의 20%
B = 전체인원의 40%
C = 전체인원의 40%
[설명]
위 내용과 같이 성적대로 순위를 나열 후 EVAL 컬럼에 A,B,C 라는 점수를 주고 싶습니다.
by 혼수상태
[2013.11.13 09:35:55]
WITH A AS
(
SELECT 'KIM1' AS NAME , 90 AS SCORE FROM DUAL UNION ALL
SELECT 'KIM2' AS NAME , 100 AS SCORE FROM DUAL UNION ALL
SELECT 'KIM3' AS NAME , 80 AS SCORE FROM DUAL UNION ALL
SELECT 'KIM4' AS NAME , 70 AS SCORE FROM DUAL UNION ALL
SELECT 'KIM5' AS NAME , 50 AS SCORE FROM DUAL UNION ALL
SELECT 'KIM6' AS NAME , 40 AS SCORE FROM DUAL
)
SELECT NAME
, SCORE
, RANK() OVER (ORDER BY SCORE DESC) AS RANKS
, CASE WHEN COUNT(*) OVER (ORDER BY SCORE DESC)/COUNT(*) OVER(PARTITION BY 1) <= 0.2
THEN 'A'
WHEN COUNT(*) OVER (ORDER BY SCORE DESC)/COUNT(*) OVER(PARTITION BY 1) <= 0.6
THEN 'B'
ELSE 'C'
END AS EVAL
FROM A;
by 김성수
[2013.11.13 12:07:53]
감사합니다 ! ^^
by 우리집아찌
[2013.11.13 09:37:23]
with a as
(
select 'KIM1' as NAME , 90 as SCORE from dual
union all
select 'KIM2' as NAME , 100 as SCORE from dual
union all
select 'KIM3' as NAME , 80 as SCORE from dual
union all
select 'KIM4' as NAME , 70 as SCORE from dual
union all
select 'KIM5' as NAME , 50 as SCORE from dual
union all
select 'KIM6' as NAME , 40 as SCORE from dual
)
SELECT a.*
, CASE WHEN RATIO <= 0.2 THEN 'A'
WHEN RATIO <= 0.6 THEN 'B'
ELSE 'C' END AS EVAL
FROM
(select NAME, SCORE, RANK() OVER (ORDER BY SCORE DESC) AS RANKS
, RANK() OVER (ORDER BY SCORE DESC) / MAX(ROWNUM) OVER() AS RATIO
FROM A) a
by 김성수
[2013.11.13 12:08:13]
이런방법도 잇네요 ^^ 감사합니다.^_^
) 버튼을 클릭하여 작성 하시면 됩니다.