[SQL문의] 최근 시험(테스트)를 본 점수들을 보여주는 통계를 만들고 싶습니다.
airwindtree 수험생

답변 채택시 : 0코아

답변 2 추천 0 조회 3106 2014.10.16


오라클 : 11g

각  학생들의 최근 시험을 치른 특정과목(수학) 의 점수가 5점 이상인것을 보여주고 싶습니다.

각 학생의 테스트일, 테스트번호가 중복인 테이터가 있는 경우 점수가 높은것을 가져옵니다.


학생  (테이블)

학생ID(PK)

학생명 

학생학년 

학생반

활성유무 

 1 

홍길동 

 1

 1

 활성 

 2

 임꺽정

 1

 2

 삭제

 3

 배트맨

 2

 1

 활성

 4

 슈퍼맨

 2

 2

 활성

 5

 히트맨

 3

 1

 활성

 6

 원더우먼

 3

 2

 활성 

 7

 스파이더맨

 1

 1

 삭제

 8

 와치맨

 1

 3

 삭제

 9

 원펀맨

 4

 1

 활성

 10

 헐크

 4

 1

 활성




점수 (테이블)


점수ID

학생ID(FK)

과목

테스트일

테스트일번호

점수

활성유무

1

1

수학

2014-01-01

1

1

활성

2

1

수학

2014-01-01

2

2

활성

3

1

수학

2014-01-01

3

3

삭제

4

1

수학

2014-10-16

1

4

활성

5

1

수학

2014-10-16

1

5

활성

6

1

수학

2014-10-16

2

6

삭제

7

1

국어

2014-02-02

1

1

활성

8

1

국어

2014-02-03

1

2

활성

9

1

국어

2014-05-06

1

3

활성

10

1

국어

2014-05-06

2

4

삭제

11

1

국어

2014-05-06

3

5

활성

12

1

국어

2014-09-02

1

6

삭제

13

1

국어

2014-09-02

2

7

활성

14

2

국어

2014-10-02

2

10

활성

15

3

수학

2014-01-01

1

3

활성

16

3

수학

2014-01-01

2

4

활성

17

3

수학

2014-01-01

3

5

활성

18

3

수학

2014-01-01

4

6

활성

19

3

수학

2014-10-16

1

7

활성

20

3

수학

2014-10-16

2

8

삭제

21

3

국어

2014-02-02

1

9

활성

22

3

국어

2014-02-03

1

10

활성

23

3

국어

2014-05-06

1

11

활성

24

3

국어

2014-05-06

2

12

활성

25

3

국어

2014-05-06

3

13

활성

26

3

국어

2014-09-02

1

14

삭제

27

3

국어

2014-09-02

2

15

삭제

28

7

국어

2014-05-06

3

13

활성

29

7

국어

2014-09-02

1

14

삭제

30

9

수학

2009-01-01

1

15

활성



원하는 결과

학생ID

학생명

학생학년

학생반

활성유무

과목

테스트일

테스트일 번호

점수

1

홍길동

1

1

활성





3

배트맨

2

1

활성

수학

2014-10-16

1

7

4

슈퍼맨

2

2

활성

5

히트맨

3

1

활성

6

원더우먼

3

2

활성

9

원펀맨

4

1

활성

수학

2009-01-01

1

15

10

헐크

4

1

활성



쿼리 1)

- 문제점

1) 모든 점수테이블에 있는 데이터를 건드린다

2) 활성이 되지 않은 학생들것 까지 데이터를 온다


SELECT
 학생.학생ID
,학생.학생명
,학생.학생학년
,학생.학생반
,학생.활성유무
,R점수.과목
,R점수.테스트일
,R점수.테스트일번호
,R점수.점수
FROM 학생
WHERE 학생.활성유무='활성'
LEFT JOIN (
  SELECT * FROM (

    SELECT
     점수.*
    ,ROW_NUMBER() OVER (PARTITION BY 학생ID ORDER BY 테스트일 DESC, 테스트일번호 DESC,점수 DESC) AS RN
    FROM 점수
    WHERE 점수.과목='수학'
    AND 점수.활성유무='활성'
    /*AND 점수.학생ID =학생.학생ID  오류가 나서 주석 처리*/
  ) WHERE RN=1
) R점수
ON 학생.학생ID = R점수.학생ID

WHERE R점수.점수 > 5
ORDER BY 학생.학생ID


쿼리2)


- 문제점

1) 각 학생의 필요없는 점수데이터를 가져와서 추출한다.

SELECT * FROM

(

  SELECT * FROM  
  ( 
    SELECT
     학생.학생ID 
    ,학생.학생명 
    ,학생.학생학년 
    ,학생.학생반 
    ,학생.활성유무 
    ,점수.과목 
    ,점수.테스트일 
    ,점수.테스트일번호 
    ,점수.점수 
    ,ROW_NUMBER() OVER (PARTITION BY 점수.학생ID ORDER BY 점수.테스트일 DESC, 점수.테스트일번호 DESC,점수 DESC) AS RN 
    FROM 학생 
    LEFT JOIN 점수 
    ON 학생.학생ID = 점수.학생ID  
       AND 점수.과목='수학'
       AND 점수.활성유무='활성'
  ) R학생점수 
  WHERE RN=1 

) 통계 WHERE 점수 > 5
ORDER BY 통계.학생ID 



문의 :

위의 쿼리1), 쿼리2) 방식은 필요없는 데이터를  가져오게 됨으로써 쿼리 속도가 상당히 느리다는 점입니다.

학생데이터, 점수데이터가 적으면 상관 없겠지만 (학생 1000건 이하), 대용량 데이터(100만건 이상) 라고 가정했을때

상당히 많은 시간이 소요됩니다.


문제는 가져온 테이블에서

WHERE 조건을 점수.점수 > 5

이런 조건을 걸수 있어야 한다는 점입니다.


효율적인 방안이 있는지요?




댓글 (0)
목록 답변등록
[SQL문의] 최근 시험(테스트)를 본 점수들을 보여주는 통계를 만들고 싶습니다.
zzubzzub 2014.11.06

위에 분이 쿼리2)로 해결방법을 제시해 주셨구요.

 

저는 쿼리1)을 수정해 보겠습니다.

질문자께서 짜신 쿼리도 원하는 데이터는 나오구요(구문에러 나는 WHERE 구문 위치는 수정해야 합니다)

단지 위에 분 말씀처럼 요구사항이 "점수 5점 이상"이기 때문에 >=로 했습니다.

 

SELECT
 학생.학생ID
,학생.학생명
,학생.학생학년
,학생.학생반
,학생.활성유무
,R점수.과목
,R점수.테스트일
,R점수.테스트일번호
,R점수.점수
FROM 학생
WHERE 학생.활성유무='활성'
LEFT JOIN (
  SELECT * FROM (

    SELECT
     점수.*
    ,ROW_NUMBER() OVER (PARTITION BY 학생ID ORDER BY 테스트일 DESC, 테스트일번호 DESC,점수 DESC) AS RN
    FROM 점수
    WHERE 점수.과목='수학'
    AND 점수.활성유무='활성'

    AND 점수.점수 >= 5
  ) WHERE RN=1
) R점수
ON 학생.학생ID = R점수.학생ID
ORDER BY 학생.학생ID

;

 

수정한 것은 단순히 점수>=5 라는 조건을 SUB-QUERY 안으로 넣은 것 뿐 입니다.

 

어차피 5점 이상이 아닌 점수는 결과집합에 전혀 필요없는 데이터이므로 서브쿼리 안에서 먼저 필터링을 하는 것이 성능상 유리할 것 입니다.

 

또한 대용량일 경우 서브쿼리 결과 데이터만 테이블로 따로 만들고 그 테이블과 학생 테이블을 다시 조인하는 방법을 사용할 수 있겠습니다.

 

물론 위에분이 설명해주신 INDEX도 고려해 보심이 좋을거 같구요^^

 

P.S : 쿼리1)에서 점수 테이블에 비활성화 학생 데이터까지 읽는 것이 문제점이라고 하셨는데. 이것을 해결하려면 먼저 비활성화 학생을 제외한 학생 테이블과 점수 테이블을 먼저 조인하고, 그 이후에 ROW_NUMBER로 최근,높은 점수 데이터를 가져오시면 되겠습니다.

하지만 비활성화 학생의 숫자가 전체 학생 수에 대비해 많지 않다면 추천할 방법은 아니겠네요.

댓글 (0)
[SQL문의] 최근 시험(테스트)를 본 점수들을 보여주는 통계를 만들고 싶습니다.
jcnahm 2014.10.20


쿼리1, 쿼리2번 쿼리 모두 원하는 결과와 다른 결과가 나오며,
추가로, 쿼리1번은 "WHERE 학생.활성유무='활성'"가 LEFT JOIN이 앞에 있어서 구문에러까지 발생합니다.

쿼리2번 쿼리 기준으로 수정을 해보면,
1) "OVER (PARTITION BY 점수.학생ID ORDER BY 점수.테스트일 DESC, 점수.테스트일번호 DESC,점수 DESC)"
    처럼 점수.학생ID로 파티션하면 점수가 없는 경우는 점수.학생ID는 NULL이 됨으로 활성화된 수학 점수가 없는 학생ID는
    모두 같은 NULL로 ROW_NUMBER가 부여되어서 RN=1의 WHERE 조건을 적용하면 활성화된 수학 점수가 없는 학생ID는 한건만
    나오게 됩니다.
    따라서, 원하는 결과를 가져오려면 "PARTITION BY 점수.학생ID"를 "PARTITION BY 학생.학생ID"로 수정해야 합니다.

2) 쿼리1번에는 있는데, 쿼리2번에는 "WHERE 학생.활성유무='활성'"가 조건이 누락되어 있습니다.

3) 특정과목(수학) 의 점수가 5점 이상인것이라고 했는데, 이상은 그 값도 포함함으로
   "점수.점수 > 5"을 "점수.점수 >= 5"로 수정하는게 맞습니다. 아니면 "이상"를 "초과"로 수정해야 합니다.
   그리고 이 조건은 ON 절에 넣어야 활성화된 수학 점수가 없는 학생ID도 가져올수 있습니다.
  
4) 점수 테이블의 인덱스는 쿼리2만에 최적화 한다면 "학생ID, 테스트일, 테스트일번호, 점수"으로 구성해야
   합니다. 그러면 필요한 데이터만 SORT없이 가져올수 있습니다.
  

5) 점수 테이블을 모델관점에서 보면 점수ID라는 인조키(?)가 생성되어 있는데, 질문의 쿼리에서는 딱히 용도가 없고
   일반적인 상황에서도 인조키가 필요한 상황이 딱히 떠오르지 않습니다.
   점수 테이블의 본질 식별자는 "학생ID, 과목(개설강의), 테스트일시"로 보이는데,
   보통은 "학생ID, 테스트일시"로 만으로  유니크 함으로,
   (시간으로도 유니크하지 않다면 분까지 포함시키면 특수한 경우를 제외하면 유니크할 것으로 보임.)
    "학생ID, 테스트일시"를 PK로 구성하면 추가 인덱스 필요없이
    "ROW_NUMBER() OVER (PARTITION BY 학생.학생ID ORDER BY 점수.테스트일 DESC, 점수.테스트일번호 DESC,점수 DESC)"를
    "ROW_NUMBER() OVER (PARTITION BY 학생.학생ID ORDER BY 테스트일시 DESC, 점수 DESC)"으로 수정하는 것만으로도
    괜찮은 성능이 나올것으로 보입니다. 조회시 SORT부하를 줄이겠다면
    점수 테이블에 "학생ID, 테스트일시, 점수"의 인덱스를 생성하면 될 것으로 보입니다.
   
6) 최근 시험 조회하는데, 점수 테이블의 모든 데이터가 아니라 특정 시점 이후의 데이터일 가능성이 높을 것으로 보입니다.
   그렇게 되면 "테스트일"이 조회조건에 추가되어야 될 것으로 보입니다.
  


-- 1. 테이블 수정없는 정답 쿼리
SELECT * FROM 
  (
    SELECT
     학생.학생ID
    ,학생.학생명
    ,학생.학생학년
    ,학생.학생반
    ,학생.활성유무
    ,점수.과목
    ,점수.테스트일
    ,점수.테스트일번호
    ,점수.점수
    ,ROW_NUMBER() OVER (PARTITION BY 학생.학생ID ORDER BY 점수.테스트일 DESC, 점수.테스트일번호 DESC,점수 DESC) AS RN
    FROM 학생
    LEFT JOIN 점수
    ON 학생.학생ID = 점수.학생ID 
       AND 점수.과목='수학'
       AND 점수.활성유무='활성'
       AND 점수.점수 >= 5
    WHERE 학생.활성유무='활성'
  ) R학생점수
  WHERE RN=1

 


-- 2. 테이블 수정(인조키 삭제 및 PK수정) 및 테스트일시 조건 추가(예, 2014년 이후데이터)  정답 쿼리
SELECT * FROM 
  (
    SELECT
     학생.학생ID
    ,학생.학생명
    ,학생.학생학년
    ,학생.학생반
    ,학생.활성유무
    ,점수.과목
    ,점수.테스트일시
    ,점수.점수
    ,ROW_NUMBER() OVER (PARTITION BY 학생.학생ID ORDER BY 점수.테스트일시, 점수.점수 DESC) AS RN
    FROM 학생
    LEFT JOIN 점수
    ON 학생.학생ID = 점수.학생ID 
       AND 점수.과목='수학'
       AND 점수.활성유무='활성'
       AND 점수.점수 >= 5
       AND 점수.테스트일시 >= '2014-01-01'
    WHERE 학생.활성유무='활성'
  ) R학생점수
  WHERE RN=1

댓글 (0)