TIL_[SQL] SELF JOIN , CROSS JOIN

Average Time of Process per Machine

table : activity
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

Q. 각 머신 당 프로세스를 완료가기까지 걸리는 평균 시간을 구하시오.
결과 테이블에는 machine_id와 함께 평균시간을 processing_time으로 표시되어야 하며, 값은 소수점 이사 3자리로 반올림되어야 함.

또 다른 self join 문제였다.
조인을 할때 조건이 이번엔 3가지다.

1. machine_id 가 일치할 것
2. process_id 가 일치할 것
3. (b.timestamp에서 a.timestamp를 빼니)
   a.activity_type = 'start' and b.activity_type = 'end' 일 것

select a.machine_id, round(avg(b.timestamp - a.timestamp), 3) as processing_time
from activity a join activity b 
on a.machine_id=b.machine_id and a.process_id = b.process_id and a.activity_type = 'start' and b.activity_type = 'end'
group by 1

---

Students and Examiantions

table : students
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     | pk
| student_name  | varchar |
+---------------+---------+

table : subjects
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar | pk
+--------------+---------+

table : examinations
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
Each student from the Students table takes every course from the Subjects table.

Q. 각 학생들이 시험에 참석한 횟수를 찾으시오
결과는 student_id 와 subject_name 순으로 정렬할 것

대충 3개의 테이블을 조인해서 실행해보니 결과는 오답.
기댓값을 보니 시험에 참석하지 않은 학생들까지 카운트가 되어있었다.
그래서 null 값을 제외하지 말고 다시 실행시켜봤으나 그래도 오답@@

hint를 통해 다른 사람들이 어떻게 풀었는지 봤더니

cross join을 활용해서 푸는 문제였었다.

CROSS JOIN (= 카타시안의 곱)
: 두 테이블에 있는 모든 값을 각각 합치기

• 양쪽 집합의 M * N 건의 데이터 조합 발생
• on절 조인조건 필요 없음

이 문제에서는 각 학생들은 모든 과목을 수강하기 때문에
(Each student from the Students table takes every course from the Subjects table.참고 ),

students 테이블과 subjects 테이블을 CROSS JOIN 해서 모든 학생이 모든 과목과 짝을 만들어 놔야 한다.

그런 다음
LEFT JOIN을 사용해서 각 학생이 각 시험에 몇번 참석했는지 계산한다.

select a.student_id, a.student_name, sub.subject_name,
       count(b.student_id) attended_exams
from students a
cross join subjects sub
left join examinations b on a.student_id=b.student_id and sub.subject_name = b.subject_name
group by 1, 2, 3
order by 1, 3