[TIL] 2023.05.01 MySQL_Subquery

Today I Learned

• MySQL Subquery 공부하기
• 프로젝트 초기 세팅하기

 

---

MySQL

Subquery란?

• 쿼리 안의 쿼리라는 의미
• 하위 쿼리의 결과를 상위 쿼리에서 사용할 수 있다.
• Subquery는 where, select, from 절에서 유용하게 사용할 수 있다.

 

INNER JOIN 대신 Subquery 사용해 보기

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'

• INNER JOIN 사용했을 경우

 

SELECT * FROM users u 
WHERE u.user_id IN (
	SELECT user_id FROM orders o 
	WHERE payment_method = 'kakaopay'
)

• IN 사용했을 경우
• 우선 kakaopay로 결제한 user_id를 모두 구해보기 → K라고 지칭
• 그 후에, user_id가 K에 있는 유저들만 골라보기

 

Where에 들어가는 Subquery

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

• where 필드명 in (subquery)

쿼리 실행 순서

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

 

Select에 들어가는 Subquery

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;

• 기존 테이블에 함께 보고 싶은 통계 데이터를 손쉽게 붙이는 것에 사용
• select 필드명, 필드명, (subquery) from..

쿼리 실행 순서

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

 

From에 들어가는 Subquery (가장 많이 사용)

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와 이미 있는 테이블을 Join 하고 싶을 때 사용

쿼리 실행 순서

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

 

Subquery 중첩 사용

 SELECT * FROM point_users pu 
 WHERE pu.point > (
 	SELECT AVG(point) FROM point_users pu2 
 	WHERE pu2.user_id IN (
 		 SELECT u.user_id FROM users u 
		 WHERE u.name = '이**'
 	)
 )

SELECT * ROM point_users pu 
 WHERE pu.point > (
 	SELECT AVG(pu2.`point`) FROM users u 
	INNER JOIN point_users pu2
	ON u.user_id = pu2.user_id
	WHERE u.name = '이**'
 )

• 두 개의 쿼리문이 같은 결과를 출력함

 

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

SELECT c.title, 
       a.cnt_checkins, 
       b.cnt_total, 
       (a.cnt_checkins/b.cnt_total) AS ratio
  FROM (
  	SELECT c1.course_id, 
		   COUNT(DISTINCT(user_id)) AS cnt_checkins
	  FROM checkins c1
	INNER JOIN courses c2 ON c1.course_id = c2.course_id 
	GROUP BY c1.course_id
  ) a
INNER JOIN (
	SELECT o.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 c.course_id = a.course_id

 

MySQL WITH

WITH table1 AS (
	SELECT c1.course_id, 
		   COUNT(DISTINCT(user_id)) AS cnt_checkins
	  FROM checkins c1
	INNER JOIN courses c2 ON c1.course_id = c2.course_id 
	GROUP BY c1.course_id
), table2 AS (
	SELECT o.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 c.course_id = a.course_id;

• with 절로 쿼리문을 깔끔하고 가독성 좋게 정리할 수 있다.

 

MySQL SUBSTRING_INDEX (문자열 쪼개기)

SELECT SUBSTRING_INDEX(email, '@', 1)  FROM users

• 이메일에서 아이디만 가져오기
• @를 기준으로 텍스트를 쪼개고, 그중 첫 번째 조각을 가져오라는 뜻

 

SELECT SUBSTRING_INDEX(email, '@', -1) FROM users

• 이메일에서 이메일 도메인만 가져오기 ex) naver.com

 

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

SELECT order_no, created_at, substring(created_at,1,10) as date 
  FROM orders;

• created_at을 날짜만 잘라서 출력하기
• SUBSTRING(문자열, 출력을 하고 싶은 첫 글자의 위치, 몇 개의 글자를 출력하고 싶은지)

 

SELECT SUBSTRING(created_at, 1, 10) AS date, 
       COUNT(*) AS cnt_date 
  FROM orders
GROUP BY date;

• 일별로 몇 개씩 주문이 일어났는지 출력하기

 

MySQL CASE

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 level
  	  FROM point_users pu 
 )
 SELECT level, COUNT(*)
   FROM table1
GROUP BY level;

• 경우에 따라 원하는 값을 새 필드에 출력하기
• CASE-WHEN-THEN-(ELSE)-END

 

쿼리문 간단하게 작성하기

WITH lecture_done AS (
	SELECT enrolled_id, COUNT(*) AS done_cnt 
	  FROM enrolleds_detail
	 WHERE done = 1
	 GROUP BY enrolled_id
 ), lecture_total AS (
 	 SELECT enrolled_id, COUNT(*) AS cnt_total
	   FROM enrolleds_detail
	GROUP BY enrolled_id
 )
 SELECT a.enrolled_id, 
        a.done_cnt, 
        b.cnt_total,
        ROUND((a.done_cnt/b.cnt_total), 1) AS ratio
   FROM lecture_done a
 INNER JOIN lecture_total b
 ON a.enrolled_id = b.enrolled_id;

이렇게 복잡하게 작성한 쿼리를

 

 SELECT enrolled_id,
        SUM(done) AS cnt_done,
        COUNT(*) AS cnt_total
   FROM enrolleds_detail 
GROUP BY enrolled_id;

이렇게 간단하게도 작성할 수 있었다!

 

---

회고

Subquery까지 들어가면서 쿼리문이 너무 복잡해져서 머리가 어지럽다. 일단은 최대한 많이 직접 써보면서 문제를 풀려고 노력했고 완벽하려 하지 말고 익숙해지는 과정이라고 생각해야겠다. 그래도 며칠 만에 MySQL이랑 많이 친해진 듯!