데이터베이스 질문과 답변
A반 성적을 가지고 상위% 별로 평균을 구할려고 하는데요. 과목별로 구하는건 어떻게 했는데 각과목별, 전체 결과를 한번에 구하는 방법은 없을까요?
지금은 한과목씩 5단위로 루프돌려서 구하고 있는데요..이건 영 아닌거 같고 ㅠ.ㅠ
SELECT AVG(총점) FROM (SELECT 총점, RANK() OVER(ORDER BY 총점 DESC) AS prank FROM 성적)
WHERE prank <= TRUNC((SELECT COUNT(*) FROM 성적) * 0.05);
고수님들의 도움 부탁드립니다. 새해 복 많이 받으실꺼에요^^
[데이터: A반 성적]
| 국어 | 영어 | 수학 | 총점 | |
|---|---|---|---|---|
| 홍길동 | 40 | 82 | 75 | 197 |
| 춘향 | 85 | 92 | 78 | 255 |
| 몽룡 | 65 | 48 | 64 | 177 |
| 슈퍼맨 | 80 | 78 | 88 | 246 |
| ~ | ~ | ~ | ~ | ~ |
| ~ | ~ | ~ | ~ | ~ |
| ~ | ~ | ~ | ~ | ~ |
| ~ | ~ | ~ | ~ | ~ |
| 김길동 | 32 | 28 | 40 | 100 |
[결과]
| 국어 | 영어 | 수학 | 총점 | |
|---|---|---|---|---|
| 상위 5% | 평균 | 평균 | 평균 | 평균 |
| 상위 10% | ||||
| 상위 15% | ||||
| 상위 20% | ||||
| 상위 25% | ||||
| 상위 30% | ||||
| 상위 35% | ||||
| 상위 40% | ||||
| 상위 45% | ||||
| 상위 50% | ||||
| 상위 55% | ||||
| 상위 60% | ||||
| 상위 65% | ||||
| 상위 70% | ||||
| 상위 75% | ||||
| 상위 80% | ||||
| 상위 85% | ||||
| 상위 90% | ||||
| 상위 95% | ||||
| 상위 100% |
by 우리집아찌
[2014.01.03 10:37:55]
현재의 자료로는 20% 기준으로 들어가는데.. 어디에 위치할지
by 마농
[2014.01.03 11:37:33]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | WITH t AS(SELECT /*+ materialize */ LEVEL id , DBMS_RANDOM.STRING('U', 3) nm , ROUND(DBMS_RANDOM.VALUE(60, 100)) kor , ROUND(DBMS_RANDOM.VALUE(60, 100)) eng , ROUND(DBMS_RANDOM.VALUE(60, 100)) mat FROM dual CONNECT BY LEVEL <= 100)SELECT '상위 ' || a.p || '%' gb , ROUND(AVG(kor), 2) kor , ROUND(AVG(eng), 2) eng , ROUND(AVG(mat), 2) mat , ROUND(AVG(tot), 2) tot FROM (SELECT LEVEL * 5 p FROM dual CONNECT BY LEVEL <= 20 ) a , (SELECT id, nm , kor, eng, mat , kor + eng + mat tot , RANK() OVER(ORDER BY kor + eng + mat DESC) / COUNT(*) OVER() * 100 p FROM t ) b WHERE a.p >= b.p(+) GROUP BY a.p ORDER BY a.p; |
by 리미루
[2014.01.03 12:02:46]
마농님은 천사!! 새해 복 엄청, 겁나, 만땅 받으세요~ 감사합니다.
by 리미루
[2014.01.08 11:46:58]
아 이제 보니 실제 원하는 결과랑 다르네요..이건 총점기준으로만 나오는데요..과목별, 총점 각각의 상위%정보를 구하려면 어떻게 하나요? 일단은 급한데로 처음 생각했던데로 한과목씩 5단위로 루프돌려서 결과값을 받고 있긴한데..다시 한번 도움 부탁드립니다.
마농님이 주신 쿼리를 돌려보면 영어 상위5%가 91점인데 상위10%가 94.1로 점수가 더 높게 나오네요.
아 그리고 평균이 아닌 각 %에 맞는 점수만 나오게 하려면 어떻게 하나요?
100명이라고 한다면 5등의 값이 5%, 10등의 값이 10%
by 마농
[2014.01.08 14:02:17]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | WITH t AS(SELECT /*+ materialize */ LEVEL id , DBMS_RANDOM.STRING('U', 3) nm , ROUND(DBMS_RANDOM.VALUE(60, 100)) kor , ROUND(DBMS_RANDOM.VALUE(60, 100)) eng , ROUND(DBMS_RANDOM.VALUE(60, 100)) mat FROM dual CONNECT BY LEVEL <= 100)SELECT '상위 ' || a.p || '%' gb , ROUND(AVG(DECODE(gb, 'KOR', v)), 2) kor , ROUND(AVG(DECODE(gb, 'ENG', v)), 2) eng , ROUND(AVG(DECODE(gb, 'MAT', v)), 2) mat , ROUND(AVG(DECODE(gb, 'TOT', v)), 2) tot FROM (SELECT LEVEL * 5 p FROM dual CONNECT BY LEVEL < 20) a , (SELECT gb, v , RANK() OVER(PARTITION BY gb ORDER BY v DESC) / COUNT(*) OVER(PARTITION BY gb) * 100 p FROM (SELECT kor, eng, mat , kor + eng + mat tot FROM t ) UNPIVOT (v FOR gb IN (kor, eng, mat, tot)) ) b WHERE a.p >= b.p(+) GROUP BY a.p ORDER BY a.p; |
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(
) 버튼을 클릭하여 작성 하시면 됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.