Pori_IT

문제

학생이 각 시험에 참석한 횟수를 구하기

테이블

[Students]

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
student_id is the primary key (column with unique values) for this table.
Each row of this table contains the ID and the name of one student in the school.

[Subjects]

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
subject_name is the primary key (column with unique values) for this table.
Each row of this table contains the name of one subject in the school.

[Students]

| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
There is no primary key (column with unique values) for this table. It may contain duplicates.
Each student from the Students table takes every course from the Subjects table.
Each row of this table indicates that a student with ID student_id attended the exam of subject_name.

[예제]

Input: 
Students table:
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1          | Alice        |
| 2          | Bob          |
| 13         | John         |
| 6          | Alex         |
+------------+--------------+
Subjects table:
+--------------+
| subject_name |
+--------------+
| Math         |
| Physics      |
| Programming  |
+--------------+
Examinations table:
+------------+--------------+
| student_id | subject_name |
+------------+--------------+
| 1          | Math         |
| 1          | Physics      |
| 1          | Programming  |
| 2          | Programming  |
| 1          | Physics      |
| 1          | Math         |
| 13         | Math         |
| 13         | Programming  |
| 13         | Physics      |
| 2          | Math         |
| 1          | Math         |
+------------+--------------+
Output: 
+------------+--------------+--------------+----------------+
| student_id | student_name | subject_name | attended_exams |
+------------+--------------+--------------+----------------+
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |
+------------+--------------+--------------+----------------+

풀이

1. Cross Join

크로스 조인은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.

여기서는 각 학생이 듣는 과목에 따라서 행을 만들어주어야하기에 다음과 같이 크로스 조인을 수행한다.

SELECT *
FROM Students as s
CROSS JOIN Subjects as sub;

결과

| student_id | student_name | subject_name |
| ---------- | ------------ | ------------ |
| 1          | Alice        | Programming  |
| 1          | Alice        | Physics      |
| 1          | Alice        | Math         |
| 2          | Bob          | Programming  |
| 2          | Bob          | Physics      |
| 2          | Bob          | Math         |
| 13         | John         | Programming  |
| 13         | John         | Physics      |
| 13         | John         | Math         |
| 6          | Alex         | Programming  |
| 6          | Alex         | Physics      |
| 6          | Alex         | Math         |

2. CASE WHEN

학생이 수업을 들었는지 알기위해서 Examinations테이블과의 조인을 수행한다. 결과가 다음과 같이 나오는데 문제가 있다.

과목 하나에 count를 1로 올려야하는데 기존의 COUNT를 사용하기에는 null값을 구분하기가 어렵다.

SELECT *
FROM Students as s
CROSS JOIN Subjects as sub
LEFT JOIN Examinations as e
    ON s.student_id = e.student_id AND sub.subject_name = e.subject_name;

| student_id | student_name | subject_name | student_id | subject_name |
| ---------- | ------------ | ------------ | ---------- | ------------ |
| 1          | Alice        | Programming  | 1          | Programming  |
| 1          | Alice        | Physics      | 1          | Physics      |
| 1          | Alice        | Physics      | 1          | Physics      |
| 1          | Alice        | Math         | 1          | Math         |
| 1          | Alice        | Math         | 1          | Math         |
| 1          | Alice        | Math         | 1          | Math         |
| 2          | Bob          | Programming  | 2          | Programming  |
| 2          | Bob          | Physics      | null       | null         |
| 2          | Bob          | Math         | 2          | Math         |
| 13         | John         | Programming  | 13         | Programming  |
| 13         | John         | Physics      | 13         | Physics      |
| 13         | John         | Math         | 13         | Math  ...

null은 학생이 수업을 안들었다는 의미로 사용되어야한다. 따라서 null인지 아닌지 여부를 파악하여 새로운 행을 생성해준다.

CASE 구문을 사용해서 조건을 걸어준다. join한 학생의 id => 수업을 안들었다 => null 이면 0으로, 아니면 1로 생성한다.

그리고 결과는 합산을 요구하니 SUM을 사용해주고, 각 과목별, 학생이름별로 그룹을 걸어주면 정상적으로 결과가 나오게 된다.

SELECT s.student_id, s.student_name, sub.subject_name,SUM(CASE WHEN e.student_id is null then 0 ELSE 1 END) AS attended_exams
FROM Students as s
CROSS JOIN Subjects as sub
LEFT JOIN Examinations as e
    ON s.student_id = e.student_id AND sub.subject_name = e.subject_name
GROUP BY s.student_name,sub.subject_name
ORDER BY 1,2,3,4

| student_id | student_name | subject_name | attended_exams |
| ---------- | ------------ | ------------ | -------------- |
| 1          | Alice        | Math         | 3              |
| 1          | Alice        | Physics      | 2              |
| 1          | Alice        | Programming  | 1              |
| 2          | Bob          | Math         | 1              |
| 2          | Bob          | Physics      | 0              |
| 2          | Bob          | Programming  | 1              |
| 6          | Alex         | Math         | 0              |
| 6          | Alex         | Physics      | 0              |
| 6          | Alex         | Programming  | 0              |
| 13         | John         | Math         | 1              |
| 13         | John         | Physics      | 1              |
| 13         | John         | Programming  | 1              |

테이블을 조금씩 join하면서 발생하는 결과들을 생각해보는것이 문제 풀이를 하는데 도움이 많이 되었다.

또한 SELECT를 먼저 작성하지않고 FROM과 WHERE부터 쓰는 습관을 잡고있는데 확실히 코드 작성이 편해진 기분이다.