TIL-28

MYSQL 마지막 주차

 

subquery

where문 안의 서브쿼리

SELECT user_id, name, email FROM users u 
WHERE user_id in (
	select user_id from orders o 
	WHERE payment_method = 'kakaopay'
)

이래 하면 user_id와 일치하는 값들의 order들만 들어오게 된다.

 

서브쿼리는 where, select, from절 어디에도 들어갈 수 있다.

 

SELECT c.checkin_id,
	   c.user_id, 
	   c.likes, 
	   (
	   	SELECT AVG(likes) FROM checkins
		WHERE user_id = c.user_id 
	   ) as avg_likes_user
	FROM checkins c

select절에 들어간 subquery

 

from절에 들어가는 것이 가장 유용하게 쓰인다.

 

SELECT pu.user_id, pu.point, a.avg_likes FROM point_users pu 
inner join (
	SELECT user_id, ROUND(AVG(likes), 1) as avg_likes FROM checkins a
	group by user_id 
) a on pu.user_id = a.user_id

 

이런 from절 subquery를

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 c 
	group by course_id 
) a
inner JOIN 
(
	SELECT course_id, COUNT(*) as cnt_total FROM orders o 
	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 c 
	group by course_id 
), table2 as(
	SELECT course_id, COUNT(*) as cnt_total FROM orders o 
	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

 

문자열 다루기

이메일에서 주소만 뽑아내려고 하면

 

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

이렇게 적으면 편하게 쓸 수 있다.

 

 

날짜에서 일자까지만

SELECT order_no, created_at, SUBSTRING(created_at, 1, 10) from orders o

 

날짜별로 카운트

SELECT SUBSTRING(created_at, 1, 10) as date, count(*) from orders o 
group by date

 

CASE

SELECT pu.user_id, pu.point,
	   (case when point > 10000 then '잘 하고 있어요'
	   ELSE '조금만 더 화이팅' END) as msg 
FROM point_users pu