데이터베이스 질문과 답변
RANK 후 상대평가 A,B,C 점수 부여 방법 문의(%별할당)
1
4
2,493
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]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | WITH A AS(SELECT 'KIM1' AS NAME , 90 AS SCORE FROM DUAL UNION ALLSELECT 'KIM2' AS NAME , 100 AS SCORE FROM DUAL UNION ALLSELECT 'KIM3' AS NAME , 80 AS SCORE FROM DUAL UNION ALLSELECT 'KIM4' AS NAME , 70 AS SCORE FROM DUAL UNION ALLSELECT 'KIM5' AS NAME , 50 AS SCORE FROM DUAL UNION ALLSELECT '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]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | with a as(select 'KIM1' as NAME , 90 as SCORE from dualunion allselect 'KIM2' as NAME , 100 as SCORE from dualunion allselect 'KIM3' as NAME , 80 as SCORE from dualunion allselect 'KIM4' as NAME , 70 as SCORE from dualunion allselect 'KIM5' as NAME , 50 as SCORE from dualunion allselect '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 EVALFROM(select NAME, SCORE, RANK() OVER (ORDER BY SCORE DESC) AS RANKS , RANK() OVER (ORDER BY SCORE DESC) / MAX(ROWNUM) OVER() AS RATIOFROM A) a |
by 김성수
[2013.11.13 12:08:13]
이런방법도 잇네요 ^^ 감사합니다.^_^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(
) 버튼을 클릭하여 작성 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.