[SQL] 데이터 분석 연습 4주차 - Fin

오늘은 SubQuery에 대해서 학습하였다.

> 3주차까지 배웠던 SQL 문법 총 정리

Show Table - 데이터 베이스 내에 어떤 테이블이 있는지 살펴볼 수 있다.

Select - DB 테이블에서 보고싶은 데이터를 꺼내오는 쿼리문

From - 데이터를 꺼내올 테이블을 설정하는 쿼리문

Where - 가져올 데이터에 조건을 걸어주는 쿼리문

Limit - 일부 데이터만 가져오는 것

Distinct - 중복 데이터 제거하고 가져오기

Count - 데이터 몇 개인지 세는 것

Group by - 컬럼에 있는 내용들을 묶어서 한 번에 보여줄 수 있는 쿼리

Order by - 추출된 내용들을 칼럼 기준으로 오름차 순, 내림차 순으로 정리해 준다.

Inner Join - 테이블 간의 교집합, 양쪽 테이블 데이터 집합의 공통적으로 존재하는 데이터만 결합하여 보여준다.

Left Join (Left Outer Join) - 테이블의 교집합 및 (왼쪽 기준) 차집합을 합친 것. ( (A ∩ B) ∪ (A - B) )

 

Subquery란?

쿼리 내부에 존재하는 또 하나의 쿼리를 말한다.
하위 쿼리의 결과를 상위 쿼리에서 사용했을 때, 데이터의 결과값을 보다 간단하고 쉽게 구현할 수 있다.

물론, Subquery를 이용하지 않더라도 원하는 데이터를 얻을 수 있지만, 더 편하고 간단하게 얻기 위해
Subquery를 이용한다고 보면 된다.

 

이 외에도 With, Case when, Substring 등 실전에서 유용한 문법등을 함께 학습해보았다.

With - From에 들어갈 서브쿼리를 미리 선언하여 약어화 시킨다.

Case when - 구간 별로 표기를 다르게 하고 싶을 때 사용한다.

substring, substring index - 문자열을 쪼개고 싶거나, 일부만 보여주고 싶을 때 사용한다. 

코드 스니펫 안에 학습했던 코드와 그에 대한 주석을 달아 놓았다.

 

-- subquery : 원하는 데이터를 더 쉽게 뽑는 방법
-- 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미한다.

-- kakao pay로 결제한 유저들 정보 보기 (기본)
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 맛보기!
-- where절에 사용해서 내가 원하는 내용이 담겨 있는 쿼리를 직접 입력해서 출력해줄 수도 있다.

SELECT user_id, name, email from users
where user_id IN (
	SELECT user_id from orders o
	WHERE payment_method = 'kakaopay'
	);

-- Subquery 제대로 사용해보자!
-- Select절에 있는 Subquery, 셀렉트 안에 들어가는 서브쿼리
-- 기존 테이블에 함께 보고 싶은 통계 데이터를 손쉽게 붙이는 것에 사용됨.	
-- select에다가 subquery가 들어가면 셀렉이 될때마다 하나하나 다 서브쿼리가 실행되게 된다.
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	;
	
 -- from에 사용되는 Subquery (가장 많이 사용되는 유형)
 -- 내가 만든 셀렉트와 이미 존재하는 테이블을 join하고 싶을 때 사용한다.
 -- 포인트가 높은 유저들과 좋아요를 받은 평균 갯수의 상관관계
select pu.user_id, pu.point, a.avg_likes from point_users pu  
inner join (
	select c.user_id, ROUND(avg(c.likes),1) as avg_likes from checkins c 
	group by c.user_id;
) a 
on pu.user_id = a.user_id
ORDER BY pu.point desc;

-- 연습) Where 절에 들어가는 Subquery 연습해보기
-- 전체 유저의 포인트 평균보다 큰 유저들의 데이터 추출하기.
-- where 문 서브쿼리 넣는 방식, 내가 원하는 컬림을 출력하는 쿼리를 만들고, 실제 where 쿼리의 괄호 안에 집어 넣기.
SELECT * FROM point_users pu
where point > (SELECT round(AVG(point),0) from point_users)
order by pu.point desc;

-- 연습2) 이씨 성을 가진 유저 포인트의 평균보다 큰 유저들의 데이터 추출하기
-- 서브쿼리 내에 조인도 가능하다!
SELECT * FROM point_users pu
where point > 
	(SELECT round(AVG(point),0) from point_users p
		inner join users u
		on p.user_id = u.user_id
		where u.name like "%이%"		
)
order by pu.point desc;

-- 서브쿼리 안에 서브쿼리도 가능하다.
SELECT * FROM point_users pu
where point > (
	SELECT avg(point) from point_users pu
	WHERE user_id in (
		SELECT user_id from users u 
		WHERE name = '이**'
		)
	)
	
-- SELECT 절에 들어가는 Subquery 연습해보기.
-- 연습) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select c.checkin_id, 
	   c.course_id, 
	   c.user_id,
	   c.likes, 
	   (SELECT ROUND(avg(likes),1)
	   	FROM checkins c2
	   	WHERE c2.user_id = c.user_id
	   	) as avg_like_pt
   FROM checkins c
   
-- checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select c.checkin_id,
	   c2.title,
	   c.user_id,
	   c.likes,
	   (SELECT ROUND(avg(likes),1)
	    FROM checkins c3
   		inner join courses c4
   		on c3.course_id = c4.course_id
   		where c2.title = c4.title
   		) as title_likes_avg
   FROM checkins c
   inner join courses c2
   on c.course_id = c2.course_id
   
-- 다른 답 (더 짧게 써보기)
select c.checkin_id, 
	   c2.title, 
	   c.user_id,
	   c.likes, 
	   (SELECT ROUND(avg(likes),1)
	   	FROM checkins c2
	   	WHERE c2.course_id = c.course_id
	   	) as avg_like_pt
   FROM checkins c
   inner join courses c2
   on c.course_id = c2.course_id
   
-- from 절에 들어가는 Subquery 연습해보기
-- courses_id별 유저의 체크인 개수를 구해보기!
select c.course_id, count(DISTINCT(c.user_id)) FROM checkins c
GROUP by c.course_id

-- course_id별 전체 인원을 구해보기
SELECT course_id, count(*) from orders o
group by o.course_id

-- course_id별 체크인 개수에 전체 인원을 붙이기
-- group by를 각 서브쿼리별로 따로 묶어줬기 때문에 from 내에서 조인을 밑에 처럼 붙여줘야한다.
select c.title,
	   b.cnt_checkins, 
	   a.cnt_total,
	   ROUND((b.cnt_checkins/a.cnt_total),1) as ratio -- 비율로 나타내기.
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
inner join courses c
on a.course_id = c.course_id; -- 한번 더 innerjoin, 원하는 컬럼을 뽑아오기 위해서.

-- 깔끔한 정리를 위한 with 절 넣어보기
-- with절을 만들고 거기다가 from 안에 들어갈 서브쿼리를 집어넣어서 하나의 함수처럼 사용할 수 있다.
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

-- 실전에서 유용한 SQL 써보기! (문자열)
-- substring_index : 문자열 나눠주는 쿼리
SELECT user_id, email, SUBSTRING_INDEX(email,'@', -1)  FROM users u;

-- substring : 문자열 일부만 출력해주는 쿼리
-- 이런식으로 묶은 다음에 주문 건수가 얼마나 되는지도 확인해볼 수 있다.
select SUBSTRING(created_at,1,10) as date, count(*) FROM orders o
group by date

-- 실전에서 유용한 SQL 써보기 2! (case)
-- 구간 별로 표기를 다르게 하고 싶을때.
SELECT USER_ID, pu.point,
	   (case when pu.point > 10000 then '잘 하고 있어요!'
	   else '조금만 더 파이팅!' END) as msg
	   from point_users pu;

-- case문을 통한 통계 내보기. 
-- 각 케이스들을 그룹바이하여 몇개씩 있는지 보기.
select a.lv, count(*) from (
	SELECT 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
) a
group by a.lv;

-- with문 써서 코드 좀 더 줄여보기
with table1 as (
	SELECT 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(*) from table1 a
group by a.lv

-- Quiz 초급
-- 평균 이상 포인트를 가지고 있으면 잘하고 있어요! 낮으면 열심히 합시다! 표기하기
select pu.point_user_id, pu.point,
	   (CASE when pu.point > (SELECT avg(point) from point_users) then '잘 하고 있어요'
	   ELSE '열심히 합시다'
	   END) as msg
FROM point_users pu;

-- 이메일 도메인별 유저의 수 세어보기
select SUBSTRING_INDEX(email, '@', -1) as domain, count(*) as cnt FROM users
group by domain;

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

-- 화이팅이 포함된 오늘의 다짐만 뽑아보기
select * from checkins
where comment like '%화이팅%'

-- Quiz 중급
-- 수강 등록정보 (enrolleds_id)별 전체 강의 수와 들은 강의의 수 출력해보기
-- 일단 전체 강의 수 쿼리
select enrolled_id, count(*) FROM enrolleds_detail ed
group by enrolled_id;

-- 들은 강의의 수 쿼리
select enrolled_id, count(*) from enrolleds_detail ed
where done = 1
group by enrolled_id;

-- 이 2개를 합쳐!!!
SELECT a.enrolled_id, a.tot_cnt, b.done_cnt FROM
(select enrolled_id, count(*) as tot_cnt FROM enrolleds_detail ed
group by enrolled_id) a 
inner join
(select enrolled_id, count(*) as done_cnt from enrolleds_detail ed
where done = 1
group by enrolled_id) b
on a.enrolled_id = b.enrolled_id;

-- 진도율도 넣어보기
SELECT a.enrolled_id, b.done_cnt, a.tot_cnt,
	   round((b.done_cnt/a.tot_cnt),2) as ratio		
FROM
(select enrolled_id, count(*) as tot_cnt FROM enrolleds_detail ed
group by enrolled_id) a 
inner join
(select enrolled_id, count(*) as done_cnt from enrolleds_detail ed
where done = 1
group by enrolled_id) b
on a.enrolled_id = b.enrolled_id;

-- with 절도 써보기
with table1 as
	(SELECT enrolled_id, count(*) as tot_cnt FROM enrolleds_detail ed
	group by enrolled_id),
	 table2 AS
	(select enrolled_id, count(*) as done_cnt from enrolleds_detail ed
	 where done = 1
 	 group by enrolled_id)
SELECT a.enrolled_id, 
	   b.done_cnt,
	   a.tot_cnt,
	   round((b.done_cnt/a.tot_cnt),2) as ratio		
FROM table1 a 
inner join table2 b
on a.enrolled_id = b.enrolled_id;

-- 더 간단하게 만들 수 있지 않을까??
-- done은 0, 1로 구성되어 있기 때문에 그냥 sum 써서 나타낼 수도 있다.
select enrolled_id,
	   sum(done) as done_cnt,
	   count(*) as tot_cnt,
	   round(sum(done)/count(*),2) as ratio
from enrolleds_detail ed
group by enrolled_id