12/28 CROSS JOIN 활용, NULL과 0

SQL 코드카타

 

문제88. 1280. Students and Examinations

CROSS JOIN 활용

Write a solution to find the number of times each student attended each exam. Return the result table ordered by student_id and subject_name. The result format is in the following example.

 

오답

select e.student_id,
       s.student_name,
       e.subject_name,
       count(subject_name) attended_exams
from examinations e join students s using(student_id)
group by 1, 3
order by 1

 

 

결과값이 큰 틀에서 틀린 것은 아니라고도 할 수 있겠지만(그렇다고 믿고 싶었겠지만), 문제를 자세히 다시 봐보니 요구되는 것은 전체 학생 및 전체 과목에 대한 것으로 참여 횟수가 0인 학생과 과목도 조회하는 것이었다.

 

관건은 결과값이 사실상 NULL인 것들도 모두 조회하는 것!

 

 

해결 과정

select s.student_id,
       s.student_name,
       u.subject_name
from students s cross join subjects u    ----MySQL에서는 JOIN 조건 없으면 CROSS를 생략해도 무방
order by 1, 3

우선 전체적인 과정을 헷갈리지 않기 위해 정답이 요구하는 맨 오른쪽 칼럼의 집계 부분은 제외하고, 나머지 부분부터 차근차근 만들어보기로 생각했다. 그에 따라 CROSS JOIN을 사용해 위와 같이 코드를 입력했고, 그 결과는 아래와 같았다.

 

select s.student_id,
       s.student_name,
       u.subject_name,
       count(e.subject_name) attended_exams            -------------추가부분(맨 오른쪽 칼럼)
from students s cross join subjects u left join examinations e on e.student_id = s.student_id
group by 1, 3
order by 1, 3

 

그래서 위와 같이 입력했더니 또 웬걸..또 오답이 나왔다. examination의 student_id부분만 이전의 JOIN한 테이블과 매칭해주니, 매칭하지 않은 subject_name이 cross join 한 것 같이 돼서 카운팅 된 것이 많은듯하다. 그래서 subject_name도 제대로 매칭해주니 정답이 나왔다.

 

최종정답

select s.student_id,
       s.student_name,
       u.subject_name,
       count(e.subject_name) attended_exams
from students s cross join subjects u    ------- MySQL에선 cross 생략해도 무방
	left join examinations e on e.student_id = s.student_id
	and u.subject_name=e.subject_name    -------일치하는 곳 전부 매칭 중요!
group by 1, 3
order by 1, 3

요약

-CROSS JOIN 수행

-JOIN 수행 시 일치 시켜야하는 칼럼 명시해줄 것! 그렇지 않으면 CROSS JOIN처럼 출력될 수 있음!  

 

 

 

문제90. 1934. Confirmation Rate

null과 0

The confirmation rate of a user is the number of 'confirmed' messages divided by the total number of requested confirmation messages. The confirmation rate of a user that did not request any confirmation messages is 0. Round the confirmation rate to two decimal places.

Write a solution to find the confirmation rate of each user.

Return the result table in any order.

The result format is in the following example.

 

크게 어렵진 않지만 NULL처리 때문에 잠깐 헷갈렸던 문제. SUM과 COUNT를 사용해서 평균을 구하면 NULL이 나오고, 애초에 AVG를 사용하면 0이 나오는게 특이해서 기록. 물론 전자의 경우에도 IFNULL 등을 사용하면 되긴 하지만, 후자가 훨씬 단순하므로 더 자주 활용해야겠다.

select s.user_id user_id,
       round(sum(if(c.action = 'confirmed', 1, 0))/count(c.time_stamp), 2) confirmation_rate
from   signups s left join confirmations c using(user_id)
group by 1

 

 

나머진 순서 때문에 빨간색 처리, user_id 6만 틀린 것.

 

select s.user_id user_id,
       round(avg(if(c.action = 'confirmed', 1, 0)), 2) confirmation_rate
from   signups s left join confirmations c using(user_id)
group by 1

 

 

 

 

---

 

 

SQLD 자격증반

 

---

 

12주차

 

1. 집합연산자

 : * ORDER BY는 마지막에 한 번만 기술

 1)UNION (ALL)

 2)INTERSECT(ION) : 교집합 제외

 3)MINUS(EXCEPT) : 차집합

 

2. 서브쿼리

 작동방식 따른 분류

  1)연관쿼리 : 서브쿼리 값이 메인쿼리 값에 의존

  2)비연관쿼리 : 서브쿼리 우선 실행, 단독 실행도 가능

 반환형태 따른 분류

  1)단일행

  2)다중행 : 비교연산자들

     IN (   ) : 서브쿼리 결과 내 임의 값과 동일

     ALL (   ) : 서브 결과 내 모든 값 만족

     ANY (   ) / SOME (   ) :  서브 결과 어느 하나라도 만족

     EXISTS (   ) : 서브 결과 만족하는 값 존재 여부 확인

  3)다중칼럼

 

3. 기타 서브쿼리와 뷰

 1.위치별 서브쿼리

   1)SELECT : '스칼라 쿼리'. 하나의 행과 칼럼만 출력되게 입력해야 함.

   2)FROM : '인라인 뷰'

   3)WHERE : '중첩 서브 쿼리'

   4)HAVING : 그룹핑된 결과에 부가 조건 추가 역할

   5)UPDATE 문의 SET

• 예시 : 지역이 DALLAS인 부서에 속한 직원들의 급여를 1000만큼 증가

UPDATE emp
SET sal = sal + 1000
WHERE deptno IN (
    SELECT deptno
    FROM dept
    WHERE loc = 'DALLAS'
);

 

   6)INSERT 문의 VALUES

• 실습 : partic_id는 8 그리고 nation_id=2 남자 100미터 달리기 선수 'Ryan Thomas' 를 추가해주세요. sport 테이블에서 찾아서 main_sport_id를 작성해보세요.

INSERT INTO participant_B
(partic_id, nation_id, gender, first_name, last_name, main_sport_id)
VALUES (210, 2, 'M', 'Ryan', 'Thomas', 
        (SELECT sport_id 
         FROM sport_B
         WHERE sport_name='100미터 달리기' ));

 2.VIEW

  -가상의 테이블.

  -하나 이상의 테이블 혹은 다른 뷰의 데이터를 보기 위함

  -장점 : 독립성, 편리성, 보안성

 

 

---

13주차

1. 그룹함수

  1)ROLLUP : 소그룹간 소계 구함 -> 중간합계 및 전체합계 출력

   -형식 : GROUP BY ROLLUP(A, B)

   -GROUPING 함수 : 합계인 곳에 1, 아닌 곳에 0 표시해줌 => CASE 혹은 IF문 통해 활용!

  2)CUBE : 결합가능한 모든 값에 대해 다차원 집계 구함. 함수인자 n개이면 2^n만큼 소계 생성

  3)GROUPING SET(A, B) : 다양한 소계집합 구함

 

2. 윈도우 함수

 : SQL은 칼럼 간 연산 및 비교에 특화돼있음 =>행과 행 간 연산 기능 보완

 *형식 : '함수' OVER ([PARTITION BY '칼럼명'] [ORDER BY 절] [WINDOWING 절])

  1)순위함수 :

    (1)RANK : 동일순위 동일값

    (2)DENSE_RANK : 동일순위 동일값 But, 각 한 건 취급

    (3)ROW_NUMBER : 동일 순위에도 각각 고유값 부여(정렬순서)

  2)일반집계함수 : SUM, AVG, COUNT, MAX/MIN

  3)행순서함수

    (1)FIRST_VALUE : 최초값 출력

    (2)LAST_VALUE : 최후값 출력

    (3)LAG : 이전 행 출력

    (4)LEAD : 이후 행 출력

  4)비율함수 :

    (1)RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)에 대한 행렬 백분율 출력

    (2)PERCENT_RANK : 파티션 최초 0, 최후 1 기준 순서의 백분율 출력

    (3)CUME_DIST : 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율

    (4)NTILE : 파티션별 전체 건수를 Argument 값으로 n등분한 결과 조회

 

3. 절차형 SQL

 

---

 

TMI

-감기는 나았는데 비염 탓인지...어제오늘 졸려서 죽을맛..

-그래도 그럭저럭 나쁘지 않게 공부했다.