SQL강의 내용정리 4주차(feat MySQL)

본 포스팅은 스파르타코딩클럽 SQL강의를 참고하여 포스팅하였습니다.

4주차 강의 목표

1. Subquery(서브쿼리)의 사용 방법을 배워본다
2. 실전에서 유용한 SQL 문법을 더 배워본다
3. SQL을 사용하여 실전과 같은 데이터분석을 진행해본다

서브쿼리(Subquery)란?

SQL 문에서 다른 SQL 문 내부에 중첩된 형태의 쿼리를 의미한다. 주 쿼리에 비해 보조적인 역할을 하며 서브 쿼리에서 돌아온 결과 값을 기반으로 주 쿼리의 실행 흐름이 결정된다.

간단하게 말해서, 하나의 SQL 문안에 또 다른 SQL 문이 포함되어 있는 구문이라고 생각하면되며, 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해지기 때문에 사용한다.

자주 사용되는 서브쿼리 유형

1. where에 들어가는 서브쿼리

서브쿼리의 결과를 조건에 활용하는 방식으로 유용하게 사용한다.

where 필드명 in (subquery)

예시

select * from users u
where u.user_id in (select o.user_id from orders o 
					where o.payment_method = 'kakaopay');

예시쿼리의 실행순서

1. from 실행: users 데이터를 가져와줌
2. Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
3. where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
4. 조건에 맞는 결과 출력

2. Select 에 들어가는 서브쿼리

기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용한다.

select 필드명, 필드명, (subquery) from ..

예시

select c.checkin_id, c.user_id, c.likes, 
	(select avg(likes) from checkins c2
	where c2.user_id = c.user_id) as avg_like_user
from checkins c;

예시쿼리의 실행순서

1. 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
2. select 안의 subquery가 매 데이터 한줄마다 실행
3. 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계
4. 함께 출력

3. From 에 들어가는 서브쿼리

Select와 이미 있는 테이블을 Join하고 싶을 때 사용

예시

select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_like from checkins
	group by user_id
) a on pu.user_id = a.user_id

예시쿼리의 실행순서

1. 먼저 서브쿼리의 select가 실행
2. 서브쿼리의 select로 만든것을 테이블처럼 여기고 밖의 select가 실행

서브쿼리 연습문제(where)

1. 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

• 전체 유저의 포인트 평균을 구하는 서브쿼리를 먼저 작성하고
  select avg(pu2.point) from point_users pu
• 위에 작성한 서브쿼리를 비교 대상으로 본 쿼리를 작성한다.

select * from point_users pu2 
where pu2.point > (select avg(pu.point) from point_users pu);

2. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

• 이씨 성을 가진 유저의 포인트의 평균을 구하는 쿼리를 작성
  select avg(pu.point) from point_users pu
  inner join users u
  on pu.user_id = u.user_id
  where u.name = "이**"
• 위에 작성한 서브쿼리를 비교 대상으로 본 쿼리를 작성한다.

select * from point_users pu 
where pu.point > 
	(select avg(pu2.point) from point_users pu2
	inner join users u 
	on pu2.user_id = u.user_id 
	where u.name = "이**");

서브쿼리 연습문제(select)

3. checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

• 원하는 컬럼을 가진 checkins테이블을 먼저 작성
  selet checkin_id, course_id, user_id, likes
  from checkins c

• 위의 본 쿼리의 select 필드에 course_id별 평균 likes수를 구하는 서브쿼리 추가

select checkin_id, course_id, user_id, likes, 
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id) 
from checkins c;

4. checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

• checkins테이블에 과목명을 붙이기 위한 courses 테이블과 조인 SQL작성
  select checkin_id, c2.title, user_id, likes from checkins c
  inner join courses c2 on c.course_id = c2.course_id

• 위의 본 쿼리의 select 필드에 c2.title별 평균 likes수를 구하는 서브쿼리 추가

select checkin_id, c2.title, user_id, likes,
(select avg(c3.likes)from checkins c3 
where c.course_id = c3.course_id ) 
from checkins c
inner join courses c2
on c.course_id = c2.course_id

7-07 내용추가

서브쿼리 연습문제(from)

5. course_id별 like 개수에 전체 인원을 붙이기

• course_id별 like 개수 쿼리 작성
  select course_id, sum(likes) from checkins
  group by course_id
• course_id별 전체 인원수 쿼리 작성
  select course_id, count(*) as cnt_total from orders
  group by course_id
• 두 쿼리를 조인

select a.course_id, a.cnt_likes, b.cnt_user from
( 
	select course_id, sum(likes)as cnt_likes from checkins
	group by course_id
) a
inner join
(
	select course_id, count(`*`) as cnt_user from orders
	group by course_id
) b
on a. course_id = b.course_id