<SQL 개발 일지> SQL 4주 차

스파르타코딩클럽 [왕초보] 엑셀보다 쉬운 sql

대망의 막 주 차인 4주 차에 들어왔다.
마지막 주 차인 만큼 이전에 배웠던 것들을 혼용하여 사용해서 그런지 굉장히, 매우, 아주 헷갈렸다.😭
아무래도 반복이 해결해 주리라 믿는다.

1. SUBQUERY
-> 쿼리 안의 쿼리를 의미하며, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용하고 sql 쿼리를 더욱 간단하게 해준다.

---

2. 유형
1) where절에 들어가는 subquery
2) select절에 들어가는 subquery
3) from절에 들어가는 subquery(가장 많이 사용되는 유형)

1) Where은 조건문, Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용한다.

where 필드명 in (subquery)

ex> 카카오페이로 결제로 주문한 유저들만, 유저 테이블에서 출력할 때

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..

ex>

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

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

3) from은 내가 만든 select와 이미 있는 테이블을 join하고 싶을 때 사용한다.

ex1> 유저 별 좋아요 평균 구하기

select user_id, round(avg(llikes),1 as avg_like from chekins
group by user_id

ex1-1> 해당 유저 별 포인트를 보기
(포인트와 like의 상관정도를 알 수 있다.)

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

먼저 서브쿼리의 select가 먼저 실행되고,
이것을 테이블 처럼 여기고 select가 실행된다.

---

1] Where 절에 들어가는 Subquery 연습해보기

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

위의 사진 안 쿼리문을 앞에 적힌 숫자 순서로 입력하면 원하고자 하는 데이터가 추출된다.
1-1)문제 -> 1,2번 과정 쿼리문 / 3번 결과 쿼리문
1-2)문제 -> 1,2,3번 과정 쿼리문 / 4번 결과 쿼리문

또 하나의 방법으로 아래와 같이 쿼리문을 작성하면 된다.

select * from point_users pu
where point >
(
  select avg(point) from point_users pu
  where user_id in
    (
	  select user_id from users where name = '이**'
	)
)

위 쿼리문과 같이 작성하게 되면 서브쿼리 안에 또 다른 서브쿼리가 들어갈 수 있다.

---

2] Select 절에 들어가는 Subquery 연습해보기

2-1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙이기

위와 같은 쿼리문과 결과가 추출된다.

1. checkins 테이블의 평균 likes값을 구하는 select 쿼리문을 작성한다.
2. 내가 보고자 하는 필드 값을 select문 뒤에 차례로 입력한 후 먼저 작성했던 select 쿼리문을 뒤에 입력해주면 보고자 하는 데이터가 추출된다.

2-2) checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙이기

2-1 쿼리문이였던

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

->

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

위처럼 쿼리문을 입력해도 결과가 바뀌지 않는다.(필드를 지정해서 뽑기 때문에)

그럴 땐,

select c.checkin_id,
	   c.course_id,
       c.user_id,
       c.likes, 
	   (
       select round(avg(c2.likes),1) from checkins c2
        where c.course_id = c2.course_id
        ) as course_avg,
        c3.*
 from checkins c
inner join courses c3 on c.course_id = c3.course_id

c3.* 이라는 트릭을 써준다.(c3에 모든 것이 잘 붙었나 확인)

->

c.course_id 대신 c3.title을 입력해주면 원하고자하는 결과가 추출된다.

---

3] From 절에 들어가는 Subquery 연습해보기

3-1) course_id별 유저의 체크인 개수를 구하기

select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

3-2) course_id별 인원을 구하기

select course_id, count(*) as cnt_total from orders
group by course_id

3-3) course_id별 like 개수에 전체 인원 붙이기

3-1 쿼리문은 a, 3-2 쿼리문은 b로 정의한다.

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

3-3-1) 퍼센트 나타내기

select절 뒤에 (b.cnt_checkins/a.cnt_total)을 뒤에 이어 작성하면 구하고자 하는 퍼센트 값이 표출된다.(별칭= ratio)

3-3-2) 강의 제목 나타내기

3-3-1에서 courses로 inner join 해주고, select절 뒤 a.course_id 대신 c.title로 바꿔 입력해주면 원하고자 하는 결과값이 위와 같이 추출된다.

---

3. WITH절

3-3-2에서 했던 쿼리문을 이용하면,

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

-> a를 table1, b를 table2로 변경 후 with 절에 넣고 기존 쿼리문에 있던 a,b라 칭했던 쿼리문을 table1,2로 대체 입력해주면 된다.
즉, 각a,b라 칭했던 쿼리문을 상위로 빼면서 계속 subquery가 붙으면서 inner join 안의 내용이 헷갈리고 혼동이 와 쿼리문의 길이도 줄이면서 시인성 좋게 보기 위함이다.

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

계속 subquery가 붙으면, inner join 안의 내용이 헷갈리고 혼동이 와 그것을 보기 편하게 하고자 with절을 사용하는 것이다.

4. 실전에 유용한 sql 문법

1) SUBSTRING_INDEX -> 문자열 쪼개기

ex1> 이메일에서 아이디만 가져오기

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

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

ex2> 이메일에서 이메일 도메인만 가져와보기

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

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

2) SUBSTRING -> 문자열 일부만 출력하기

ex1> orders 테이블에서 날짜까지 출력해보기

select order_no, created_at, substring(created_at,1,10) as date from orders

ex2> 일별로 몇 개씩 주문이 일어났는지 출력해보기

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

substring(문자열, 출력하고 싶은 첫 글자 위치, 원하는 글자 출력 수)

3) CASE~WHEN 문 : 경우에 따라 원하는 값을 새 필드에 출력해준다.(조건에 따른 값을 다르게 출력하고 싶을 때 사용)
SELECT절에 쓰인다.

ex1> 10000점 보다 높은 포인트를 가지고 있으면 '잘하고 있어!', 평균보다 낮으면 '좀 더 분발해!' 라고 표시하고 싶다면?

select pu.point_user_id, pu.point,
	(
	case when pu.point > 10000 then '잘 하고 있어!'
	else '조금 분발해!' end
    ) as 'msg'
from point_users pu

-> subquery를 이용하여 통계내기

1]

2]

-> with절 사용하기!

---

※※ "쿼리는 정답을 맞추는 게임이 아니라 과정을 밟아가며 시행착오를 겪어가며 완성해 나가는 게임이다."