학원 외주 현황 221111

우정이형이 시킨 학원 외주를 수요일 부터 했는데, 오늘까지 하라고 해서 새벽까지 디비 설계하고 쿼리를 짰다. 살면서 이렇게 복잡한 건 처음 해봤다. 뇌에 쥐나는 줄 알았다.

오늘 역시나 우정이형이 일을 또 시켰다. 새로운 데이터를 뽑아내야 한다는 것이었다. 그래도 어제 디비를 예쁘게 잘 만들어놓아서 금방 해결할 수 있었다. 원교수님이 잘 가르쳐주신 덕이다.

아래가 그 내용이다. 나름 함수 종속 관계를 고려해서 정규화된 테이블이다. 3NF까지 했는데, 그 이상은 할 줄 몰라서 안했다.

STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
STUDENT_RESPONSE({stu_no}, {item_no}, response) // multiple response or no response -> null + 전원 정답도 일단 IRT에 넣는다.
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)
SHEET_UNIT({sheet_no}, {unit_no}, unit_name)
SHEET_NAME({sheet_no}, sheet_name)
STUDENT_ERRATA({stu_no}, {item_no}, errata)
STUDENT_REPORT({stu_no}, {unit_no}, score) // unit 0 is total score

다음은 핵심 기능을 구현하기 위한 쿼리이다. 아직 돌려본 건 아니라서 틀릴 수도 있다.

Q1. 학생의 응답과 그에 맞는 정답을 출력(문제 수는 같다. sheet 1로 고정)

STUDENT_RESPONSE({stu_no}, {item_no}, response)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT R.stu_no, R.item_no, R.response, A.answer
FROM STUDENT_RESPONSE AS R inner join SHEET_ANSWER AS A
ON R.item_no = A.item_no and A.sheet_no = 1;

Q2. 학생의 응답과 학생의 학교, 학년, 레벨에 맞는 정답을 출력

STUDENT_RESPONSE({stu_no}, {item_no}, response)
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)

SELECT R.stu_no, R.item_no, R.response, A.answer
FROM STUDENT_RESPONSE AS R
INNER JOIN STUDENT_PERSONAL AS P ON R.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON R.item_no = A.item_no and M.sheet_no = A.sheet_no;

Q3. 학생의 응답과 학생의 학교, 학년, 레벨에 맞는 정답과 비교하여 맞으면 1 틀리면 0으로 (null 처리 포함) 하여 출력

STUDENT_RESPONSE({stu_no}, {item_no}, response)
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)

SELECT R.stu_no, R.item_no, R.response, A.answer, 'errata' = 
CASE
WHEN R.response = A.answer then 1
WHEN R.response <> A.answer then 0
ELSE 0
END
FROM STUDENT_RESPONSE AS R
INNER JOIN STUDENT_PERSONAL AS P ON R.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON R.item_no = A.item_no and M.sheet_no = A.sheet_no;

Q4. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 매칭한다.

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, E.item_no, E.errata, A.point 
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no;

Q5. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 바탕으로 하여 총점을 계산

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, SUM(E.errata * A.point)
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no
GROUP BY E.stu_no;

Q6. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 바탕으로 특정 단원(unit1)의 총점만 계산

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, A.unit_no, SUM(E.errata * A.point)
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no;
GROUP BY E.stu_no, A.unit_no
HAVING A.unit_no = 1;

Q7. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 바탕으로 모든 단원 별 총점을 계산

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, A.unit_no, SUM(E.errata * A.point)
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no;
GROUP BY E.stu_no, A.unit_no;

Q8. Sheet 1에 해당되는 학생들의 정오표를 출력

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)

SELECT E.stu_no, E.item_no, E.errata, M.sheet_no
FROM STUDENT_ERRATA AS E 
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
WHERE M.sheet_no = 1;

Q9. 학생들의 정오표를 sheet 번호에 따라 묶어서 아이템 번호에 따라 오름차순 정렬하여 출력

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)

SELECT E.stu_no, E.item_no, E.errata, M.sheet_no
FROM STUDENT_ERRATA AS E 
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
ORDER BY M.sheet_no ASC, E.stu_no ASC, E.item_no ASC;

Q10. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 바탕으로 특정 배점(2점)의 총점만 계산

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, A.point, SUM(E.errata * A.point)
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no;
GROUP BY E.stu_no, A.point
HAVING A.point = 2;

Q11. 학생의 정오표와 학생의 학교, 학년, 레벨에 맞는 정답의 배점을 바탕으로 모든 배점 별 총점을 계산

STUDENT_ERRATA({stu_no}, {item_no}, errata)  
STUDENT_PERSONAL({stu_no}, name, birth_date, phone_num, teacher_code, level, class_num, station, school, grade)
SHEET_MATCH({level}, {school}, {grade}, sheet_no)
SHEET_ANSWER({item_no}, {sheet_no}, unit_no, answer, difficulty, point)

SELECT E.stu_no, A.point, SUM(E.errata * A.point)
FROM STUDENT_ERRATA AS E
INNER JOIN STUDENT_PERSONAL AS P ON E.stu_no = P.stu_no
INNER JOIN SHEET_MATCH AS M ON P.level = M.level and P.school = M.school and P.grade = M.grade
INNER JOIN SHEET_ANSWER AS A ON M.sheet_no = A.sheet_no and E.item_no = A.item_no;
GROUP BY E.stu_no, A.point;

.
.
.
오늘 배운 점은 다음과 같다.
1. ORDER BY는 여러 컬럼을 지정할 수 있고, 순서에 따라 다른 결과가 나온다.
2. GROUP BY는 중첩해서 쓸 수 있다.
3. SQL에서 SWITCH CASE를 쓸 수 있다.
4. 괜히 SELECT * 해서 어플리케이션 단에서 조립하지 말고, 테이블 잘 쪼개서 쿼리로 한방에 끝내자.

다음으로 해야 할 일은 다음과 같다.
1. 스키마 보고 테이블 만들기
2. 파이썬으로 엑셀 데이터 접근하는거 구현
3. 쿼리 테스트 및 IRT 구현하기