SUBQUERY

큰 쿼리문 안에 들어가는 쿼리문
순서는 가장 안쪽의 쿼리문부터 진행되어진다.

user_id 별로 평균 likes 수

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

user_id가 등장할때마다 평균 likes수가 옆에 붙는다.

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 c 
			GROUP BY user_id 
			) a ON pu.user_id = a.user_id

내가 만든 셀렉문을 마치 테이블인 것처럼 JOIN 가능
테이블 같은 셀렉문의 이름을 a라고 지정

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

with와 마지막 select는 떨어져선 안된다. 가독성을 위해 한 칸 띄우게 되면 아래의 select는 따로 작동돼 오류가 나게 된다.

실전에서 사용하는 SQL 문법(문자열,CASE)

SUBSTRING

SUBSTRING_INDEX(문자열, 구분자, 구분자 index)

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

SUBSTRING_INDEX를 사용하면 글자를 분리하여 보는게 가능하다.
SUBSTRING_INDEX(email, '@', -1) 이라고 기재하면 ‘email’필드를 @를 기준으로 나누고 마지막(-1)을 보여주라는 뜻 앞에를 보고싶으면 1을 기재하면 된다.
-1의 경우: naver.com, 1의경우: asdf123

SUBSTRING(문자열, 시작 위치, 시작 위치부터 가져올 문자수)

SELECT order_no, SUBSTRING(created_at,1,10) AS![](https://velog.velcdn.com/images/william741/post/233cd075-31f2-4f15-bef3-61c35c4b0e14/image.png)
 date, COUNT(*)  FROM orders o
GROUP BY date

SUBSTRING도 마찬가지로 글자를 분리하여 본다.
SUBSTRING(created_at,1,10): created_at필드를 1부터 10까지 가져오는것

CASE

조건에 따라 값을 지정해줌

WITH table1 AS (
	SELECT pu.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 a.LV, COUNT(*) AS cnt FROM table1 a 
GROUP BY a.LV

with는 임시 테이블을 구성함.
with문을 통해 table1 이라는 임시 테이블을 만들어준뒤 select에서 볼 자료를 만들기 위해 case문을 사용
case when을 쓰고 포인트가 10000,5000 을 기준으로 각각 문구를 작성 했으며 case문이 끝날때 꼭 end를 써야함.
case문을 통해 나온 필드를 as로 LV이라는 이름을 지어줌
이후 with문과 뒤에 select문을 연결해 완성시킴

평균을 기준으로 높거나 낮을 때 표시하기

SELECT pu.user_id,
	     pu.point,
	     (case when point > (select AVG(point) from point_users) THEN '잘 하고 있어요!'
	   		ELSE '조금 만 더 화이팅!' end) AS msg
	FROM point_users pu

case문 안에 select문은 ()를 씌워주지 않으면 오류가 나게 된다.

이메일 도메인별 유저 세어보기

SELECT domain, COUNT(*) AS cnt_domain from (
	SELECT SUBSTRING_INDEX(email, '@', -1) AS domain FROM users u
)a
GROUP BY DOMAIN