데이터베이스 질문과 답변
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]
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]
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
;
) 버튼을 클릭하여 작성 하시면 됩니다.