SQL 4주차 강의 노트정리[국비지원_스파르타 코딩클럽]

스파르타 코딩클럽 SQL 4주차 강의 '핵심' 노트정리

스파르타 코딩클럽 SQL 4주차 강의 '핵심' 노트정리

 

1. Subquery 개념

: Subquery(서브쿼리)는 하나의  SQL쿼리 안에 또 다른 SQL쿼리가 있는 것.

ex. kakaopay로 결제한 유저들의 정보 보기(users테이블과 orders테이블 참고)

* 문제풀이 방법1) 이제까지 배운 것을 토대로 할 수 있는 방법

 

* 문제풀이 방법2) Subquery를 이용한 방법

 

1) 먼저 kakaopay로 결제한 유저들을 where절을 이용해서 출력해보기

( → 이걸 A라고 해보자)

↑ kakaopay로 결제한 user_id를 잘 보여주고 싶어서

Select문에 user_id랑 payment_method 를 적어서 출력한 결과임.

 

2) user_id가 A에 있는 유저들만 골라보기

( → 이게 서브쿼리)

select u.user_id, u.name, u.email from users u 
where u.user_id in (
	select user_id from orders 
	where payment_method = 'kakaopay'
)

: 즉, 1) [kakaopay로 결제한 user_id] 데이터를 출력했는데

       2) 이 데이터 안에 있는 user_id만 다시 뽑아서 출력하는 것.

 

---

2. Subquery 종류 (Where절, Select문, From 뒤)

* Where절에 들어가는 서브쿼리

: Where절은 조건문. → 서브쿼리의 결과를 조건에 활용

▶ where 필드명 in (서브쿼리)

ex. 위에 카카오페이로 결제한 유저들 정보 출력문제에서 문제풀이방법2 참고

---

* Select문에 들어가는 서브쿼리

: Select는 결과를 출력해줌. → 기존 테이블과 함께 보고싶은 데이터를 붙일 때 사용

▶ select 필드명, 필드명, (서브쿼리) from ..

ex. '오늘의 다짐' 좋아요 수가 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 볼 수 있게하기
(checkins 테이블 참고)

1) 일단 한 케이스에 대한 평균 좋아요 구해보기.

(user_id = '4b8a10e6'인 경우)

01

: user_id가 4b8a10e6인 분의 likes 필드값은 강좌 각각에 4랑 3이었음.

: 이것을 평균은 avg(likes)로 구하면 3.5가 나와야 함.

: 잘 나오는 게 확인됐으면 4b8a10e6 넣었던 자리를 일반화 해야 함.

 

2) 1번의 평균 좋아요 구하는 식을 일반화한 후에 바깥 Select문의 서브쿼리로 넣기.

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

---

*From에 들어가는 서브쿼리(가장 많이 사용되는 유형)

: 내가 만든 Select문과 이미 있는 테이블을 Join하고 싶을 때 사용

ex. 유저 별 좋아요 평균을 먼저 구하고, 해당 유저 별 포인트를 구해보기
(checkins 테이블과 point_users 테이블 참고)
→ 좋아요 수와 포인트의 상관 정도를 알 수 있음

1) 유저 별 좋아요 평균 구하기

: 유저 별 좋아요 평균이니까 group by로 user_id를 묶고 avg(likes)를 구함.

: round(값, 소수점 자리 수)는 소수점 뒷자리 수가 너무 길 때 사용해주면 좋음.

ex. avg(likes)값이 3.33333...인 경우

→ round(avg(likes),1) = 3.3 

→ round(avg(likes),0) = 3 

 

2) 1번 + 해당 유저 별 포인트 구하기

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

: 1번에서 구한 유저 별 평균 좋아요 수를 a 테이블이라고 정의함.

: a테이블을 point_users 테이블에 Inner Join 해주고, Select문으로 필요한 필드 출력.

 

---

3. with절 연습하기

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from
(
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
) a
inner join
(
	select course_id, count(*) as cnt_total from orders
	group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

→ 이렇게 계속 서브쿼리가 붙으면 복잡하고 헷갈릴 수 있음. 이걸 with으로 정리해주면..

with table1 as (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
), table2 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id
)

select c.title,
       a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins/b.cnt_total) as ratio
from table1 a inner join table2 b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id

→ 복잡한 쿼리문을 table1과 table2로 정리해준 뒤에 최종 Select문에 적어주면 보기좋은 쿼리문 완성!

 

---

4. 실전에서 유용한 SQL 문법 (문자열, Case)

 

* 문자열

▶ 문자열 쪼개기(SUBSTRING_INDEX)

ex. 이메일 주소에서 @ 앞의 아이디만 가져오기

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

: @을 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻.

ex. 이메일 주소에서 @ 뒤의 이메일 도메인만 가져오기

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

: @을 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻.

 

▶ 문자열 일부만 출력하기(SUBSTRING)

: SUBSTRING(문자열, 출력을 하고 싶은 첫 글자의 위치, 몇 개의 글자를 출력하고 싶은지)

ex. orders 테이블의 created_at 필드에서 날짜만 출력해서 date필드 만들기

ex. 일 별로 몇 개의 주문이 일어났는지 살펴보기

select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date

---

* Case

: 경우에 따라 원하는 값을 새 필드에 출력.

ex. 유저들의 포인트가 10000점 이상이면 '1만 이상' , 5000점 이상이면 '5천이상',
그 외에는 '5천 미만으로 표시하기 (point_users 테이블 참고) 

select pu.point_user_id, pu.point,
	case 
	when pu.point > 10000 then '1만 이상'
	when pu.point > 5000 then '5천 이상'
	else '5천 미만'
	END as lv
from point_users pu

▶서브쿼리를 이용해서 group by로 통계 낼 수 도 있음.

select lv, count(*) as cnt from (
	select pu.point_user_id, pu.point,
        case 
        when pu.point > 10000 then '1만 이상'
        when pu.point > 5000 then '5천 이상'
        else '5천 미만'
        END as lv
	from point_users pu
) a
group by lv

▶ with절을 써주면 더 심플해진 쿼리문이 됨.

with table1 as (
	select pu.point_user_id, pu.point,
		case 
		when pu.point > 10000 then '1만 이상'
		when pu.point > 5000 then '5천 이상'
		else '5천 미만'
		END as lv
	from point_users pu
)
select lv, count(*) as cnt from table1
group by lv