2023.10.11) SQL 문법 정리(4)

1. Subquery

 - 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미

 - 예) kakaopay로 결제한 유저들의 정보 보기

•    inner join 방법

select u.user_id, u.name, u.email from users u 
inner join orders o on u.user_id = o.user_id 
where o.payment_method = "kakaopay"

• Subquery 방법

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

 

2. Subquery 유형

 1) where (where 필드명 in (subquery))

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

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 = "이**"
	)

 2) select

  [퀴즈] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select c.checkin_id, 
	c3.title,
	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

 

 3) from

  [퀴즈] 코스제목별 check in개수, 전체, 비율

• course_id별 유저의 체크인 개수를 구하기

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

• course_id별 인원을 구하기

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

• course_id별 check in 개수에 전체 인원을 붙이기

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

• 퍼센트와 강의제목 나타내기

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

 

3. Wtith

 - 서브쿼리를 보기 좋게 만듬.

 - 위의 퀴즈를 이용

with table1 as (
	select c.course_id, count(distinct(c.user_id)) as cnt_checkins 
	from checkins c
	group by c.course_id 
), 
table2 as (
	select o.course_id, count(*) as cnt_total 
	from orders o 
	group by o.course_id 
)
select c2.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 c2 on c2.course_id = a.course_id

 

4. 문자열

 [퀴즈] 이메일에서 아이디만 가져와보기

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

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

 

[퀴즈] orders 테이블에서 날짜까지 출력하게 해보기

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

  - 1번째 글자부터 10번쨰 글자까지 출력

 

 [퀴즈] 일별로 몇 개씩 주문이 일어났는지 살펴보기

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

 

5. Case

[퀴즈] 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시하기

select pu.point_user_id, pu.point,
case
when pu.point > 10000 then "잘 하고 있어요!"
else "조금 더 달려주세요!"
end as 구분
from point_users pu

 

6. 퀴즈

 [퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기

select pu.point_user_id, pu.point,
CASE 
when pu.point > (select avg(pu2.point) from point_users pu2) then "잘 하고 있어요!"
else "열심히 합시다!"
END as msg
from point_users pu

 

  [퀴즈] 이메일 도메인별 유저의 수 세어보기

select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain

 

 [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 진도 출력해보기

with table1 as (
	select ed1.enrolled_id, count(*) as cnt_done
	from enrolleds_detail ed1
	where ed1.done = 1
	group by ed1.enrolled_id
), table2 as (
	select ed2.enrolled_id, count(*) as cnt_total 
	from enrolleds_detail ed2
	group by ed2.enrolled_id	
)
select a.enrolled_id, a.cnt_done, b.cnt_total,
	round((a.cnt_done / b.cnt_total),2) as ratio
from table1 a 
inner join table2 b on a.enrolled_id = b. enrolled_id