과목별 등급 산출하는 쿼리 0 6 252

by 세라비 [2018.06.14 10:40:11]


안녕하세요~

과목별로 성적에따른 등급을 구하는 쿼리를 작성하려고하는데

도움부탁드립니다.

아래는 등급산출을 하기위한 룰입니다.

 

<9단계 평가 기준표> 

석차등급

석차누적비율 

1등급

~ 4% 이하

2등급

4% 초과 ~ 11% 이하

3등급

11% 초과 ~ 23% 이하

4등급

23% 초과 ~ 40% 이하

5등급

40% 초과 ~ 60% 이하

6등급

60% 초과 ~ 77% 이하

7등급

77% 초과 ~ 89% 이하

8등급

89% 초과 ~ 96% 이하

9등급

96% 초과 ~ 100% 이하

 

단, 동점자가 발생할 경우 위의 석차 등급에 왜곡이 발생. 

이러한 동점자 처리를 위해 중간석차라는 것을 구하고 이 중간석차의 백분율로 석차 등급을 부여함.  

중간석차 = 석차 + (동석차 인원수 - 1) / 2

중간석차 백분율= 중간석차/전체수강생x100

 

예시)

100명이 수강중인 수업의 중간고사에서 100점 만점자 8명이 나왔을 경우. 

- 100점 만점자의 석차는 공동 1등.

- 그러나 석차 등급은 4%이하, 즉 4명까지만 1등급을 부여하게 되어 있어 8명 모두에게 1등급 부여할 수 없음. 

- 중간석차, 중간석차 백분율 산출 

  중간석차 = 1 + (8-1)/2 = 4.5 

  중간석차 백분율 = 4.5/100 x 100 = 4.5% 

- 이들의 중간석차 백분율은 4.5%로 4%를 초과하기에 전원 2등급 처리. 

 

결론 :: 정확한 등급 산출 방법은 중간석차 산출 후 중간석차 백분율에 따라 석차 등급 부여

 

----------------------------------------------

해당 성적 정보 테이블에는 다음과 같이 데이터가 들어가있습니다.

학번 / 과목 / 학기 / 시험구분 / 점수

20180001 / 수학 / 1학기 / 중간 / 89

20180001 / 수학 / 1학기 / 기말 / 77

20180001 / 국어 / 1학기 / 중간 / 100

20180001 / 국어 / 1학기 / 기말 / 100

20180002 / 수학 / 1학기 / 중간 / 58

20180002 / 수학 / 1학기 / 기말 / 88

20180002 / 국어 / 1학기 / 중간 / 99

20180002 / 국어 / 1학기 / 기말 / 55

 

제가 원하는 결과물은

학번 / 과목 / 학기 / 시험구분 / 점수 / 등급

20180001 / 수학 / 1학기 / 중간 / 89  / 등급

20180001 / 수학 / 1학기 / 기말 / 77  / 등급

20180001 / 국어 / 1학기 / 중간 / 100  / 등급

20180001 / 국어 / 1학기 / 기말 / 100  / 등급

 

 

등급 기준표 때문에 안그래도 힘들게 느껴졌던 쿼리짜기가 더더욱힘드네요ㅠㅠ

도움 부탁드립니다.

참고로 현재 작업하고 있는 DB는 MySQL입니다.

by 마농 [2018.06.14 13:00:10]
WITH t AS
(
SELECT LEVEL id
     , 100 - CEIL(LEVEL/8) score
     , '수학'  gwamok
     , '1학기' hakgi
     , '중간'  gubun
  FROM dual
 CONNECT BY LEVEL <= 100
)
SELECT gwamok, hakgi, gubun
     , id, score
     , rk
     , rk2
     , rat
     , CASE WHEN              rat <=  4 THEN 1
            WHEN rat >  4 AND rat <= 11 THEN 2
            WHEN rat > 11 AND rat <= 23 THEN 3
            WHEN rat > 23 AND rat <= 40 THEN 4
            WHEN rat > 40 AND rat <= 60 THEN 5
            WHEN rat > 60 AND rat <= 77 THEN 6
            WHEN rat > 77 AND rat <= 89 THEN 7
            WHEN rat > 89 AND rat <= 96 THEN 8
            WHEN rat > 96               THEN 9
        END grd
  FROM (SELECT gwamok, hakgi, gubun
             , id, score
             , rk
             , (rk + (cnt - 1) / 2) rk2
             , (rk + (cnt - 1) / 2) / tot * 100 rat
          FROM (SELECT gwamok, hakgi, gubun
                     , id, score
                     , RANK() OVER(PARTITION BY gwamok, hakgi, gubun ORDER BY score DESC) rk
                     , COUNT(*) OVER(PARTITION BY gwamok, hakgi, gubun, score) cnt
                     , COUNT(*) OVER(PARTITION BY gwamok, hakgi, gubun) tot
                  FROM t
                )
        )
;

 


by 세라비 [2018.06.14 13:24:34]

감사합니다!

근데 지금사용하고있는 DB가 MySQL 이라서 PARTITION BY 와  CONNECT BY LEVEL 

이부분이 적용이 안되는거 같은데 다른방법이 있을까요?


by 마농 [2018.06.14 13:34:53]

with 부분은 테스트 샘플 만드는 쿼리이니 신경 안쓰셔도 됩니다.
분석함수 OVER 부분이 없으면 쿼리가 복잡해집니다.
요구조건이 좀더 명확해야 할 것 같습니다.
조건 없이 전체 조회해야 하나요?
아니면 특정 조건 걸고 조회하나요?


by 세라비 [2018.06.14 13:41:03]

해당학기의 모든 과목에 대한 학생의 점수 및 등급을 한번에 그리드로 확인하고 싶습니다.

ex) 학생 A( 1학기 )

과목 / 점수 / 과목평균 / 등급

수학 / 100  /  90 /  1

국어 /  90  / 88 / 3

 

이런식으로 말입니다.

조건이라면 

1학기/2학기, 중간/기말/전체

이정도로 제어해서 위와같은 결과값이 나와야합니다.


by 마농 [2018.06.14 13:59:30]
SELECT id
     , gwamok, hakgi, gubun
     , score
     , avg_score
     , rk
     , rk2
     , rat
     , CASE WHEN              rat <=  4 THEN 1
            WHEN rat >  4 AND rat <= 11 THEN 2
            WHEN rat > 11 AND rat <= 23 THEN 3
            WHEN rat > 23 AND rat <= 40 THEN 4
            WHEN rat > 40 AND rat <= 60 THEN 5
            WHEN rat > 60 AND rat <= 77 THEN 6
            WHEN rat > 77 AND rat <= 89 THEN 7
            WHEN rat > 89 AND rat <= 96 THEN 8
            WHEN rat > 96               THEN 9
        END grd
  FROM (SELECT id
             , gwamok, hakgi, gubun
             , score
             , avg_score
             , rk
             , (rk + (cnt - 1) / 2) rk2
             , (rk + (cnt - 1) / 2) / tot * 100 rat
          FROM (SELECT a.id
                     , a.gwamok, a.hakgi, a.gubun
                     , a.score
                     , AVG(b.score) avg_score
                     , COUNT(CASE WHEN a.score < b.score THEN 1 END) + 1 rk
                     , COUNT(CASE WHEN a.score = b.score THEN 1 END)     cnt
                     , COUNT(*) tot
                  FROM t a
                 INNER JOIN t b
                    ON a.gwamok = b.gwamok
                   AND a.hakgi  = b.hakgi 
                   AND a.gubun  = b.gubun 
                 WHERE a.id     = :v_id
                   AND a.hakgi LIKE CONCAT(:v_hakgi, '%')
                   AND a.gubun LIKE CONCAT(:v_gubun, '%')
                 GROUP BY a.id, a.gwamok, a.hakgi, a.gubun, a.score
                ) a
        ) a
;

 


by 세라비 [2018.06.14 14:06:28]

우와! 됐습니다!! 정말감사합니다!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입