[TIL] WEEK 4 MON - CROSS JOIN, 같은 테이블 JOIN

1. TABLE 3개 CROSS JOIN, COUNT

COUNT조건을 줄 때, 목적에 맞는 지표를 넣어야 원하는 수를 도출 할 수 있다.

SELECT students.student_id
       , student_name
       , subjects.subject_name
       , COUNT(examinations.subject_name) attended_exams
FROM students 
     CROSS JOIN subjects
     LEFT JOIN examinations 
     ON students.student_id = examinations.student_id
     AND subjects.subject_name = examinations.subject_name
GROUP BY 1,2,3
ORDER BY students.student_id, subjects.subject_name

students and examinations

 

2. 같은 테이블 JOIN

테이블을 시작시간만 있는 테이블과 종료시간만 있는 테이블로 나눈 뒤 두 테이블의 시간을 계산해준다.

SELECT start.machine_id, ROUND(avg(end.timestamp-start.timestamp), 3) as processing_time 
FROM Activity start, Activity end
WHERE start.machine_id=end.machine_id 
      AND start.process_id=end.process_id
      AND start.activity_type='start' 
      AND end.activity_type='end'
GROUP BY 1

1661. Average Time of Process per Machine