엑셀보다 쉬운 SQL - 4주차

---

SubQuery

쿼리 안의 쿼리

• 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해짐
• SubQuery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 표현 가능

EX] kakaopay로 결제한 유저들의 정보 보기

-- Inner Join을 사용한 방법
select u.user_id, u.name, u.email from users u
inner join order 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 user_id from orders
    where payment_method = 'kakaopay'
)

• Inner Join 방법: 테이블을 합친 뒤에 값을 필터링해 payment_method가 kakaopay인 값들을 남기는 방식
  - A라는 유저가 a를 구매한 것, A라는 유저가 b를 구매한 것 모두 테이블로 합쳐져 A유저의 데이터가 2번 들어감
• SubQuery 방법: 테이블을 합치기 전 값들을 필터링한 뒤 테이블로 합치는 방식
  - A라는 유저가 카카오페이로 결제한 경우(a,b)가 orders 테이블에 있어 A유저를 하나 테이블에 넣는다

→ 결과의 차이가 생김: 두 코드에 출력되는 값의 개수가 다름

Where절에 들어가는 SubQuery

SubQuery의 결과를 조건에 활용하는 방식

-- 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력하기
select * from users u
where u.user_id in (
	select o.user_id from orders o
    where o.payment_method = 'kakaopay'
);

• 쿼리 실행 순서: from → subquery → where 절 필터링 → 출력

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 * from 에서 데이터를 하나하나 출력
  → select 안의 subquery가 매 데이터 한줄마다 실행
  → 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요를 계산
  → 함께 출력

From절에 들어가는 SubQuery

내가 만든 select와 이미 있는 테이블을 붙이고 싶을 때

-- 해당 유저 별 포인트 (포인트와 like의 상관정도)
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
  → 이를 테이블처럼 여기고 외부의 select 실행

SubQuery 연습

Where절에 들어가는 SubQuery

1) 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출

select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2);

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

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

Select절에 들어가는 SubQuery

1) checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙이기

select c.checkin_id,
	   c.course_id,
       c.user_id,
       c.likes,
       (select avg(c2.likes) from checkins c2
       where c.course_id = c2.course_id) as course_avg
  from checkins c;

2) checkins 테이블에 과목명별 평균 likes 수 필드 우측에 붙이기

select c.checkin_id,
	   co.title,
       c.user_id,
       c.likes,
       (select avg(c2.likes) from checkins c2
       where c.course_id = c2.course_id) as course_avg
  from checkins c
 inner join courses co on c.course_id = co.course_id;

From절에 들어가는 SubQuery

1) course_id별 유저의 체크인 개수, course_id별 인원 합치기

select a.course_id, a.cnt_checkins, b.cnt_total
from ( 
	select course_id, count(distinct(user_id)) as cnt_chekins
	  from courses group by course_id
) a inner join ( 
    select course_id, count(*) as cnt_total
	  from courses group by course_id)
) b on a.course_id = b.course_id;

2) 퍼센트 나타내기

select a.course_id,
 	   a.cnt_checkins,
       b.cnt_total,
       (a.cnt_checkins / b.cnt_total) as ratio
from ( 
	select course_id, count(distinct(user_id)) as cnt_chekins
	  from courses group by course_id
) a inner join ( 
    select course_id, count(*) as cnt_total
	  from courses group by course_id)
) b on a.course_id = b.course_id;

3) 강의 제목도 나타내기

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_chekins
	  from courses group by course_id
) a inner join ( 
    select course_id, count(*) as cnt_total
	  from courses 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로 나타내기
with table1 as (
	select course_id, count(distinct(user_id)) as cnt_chekins
	  from courses group by course_id
), table2 as (
	select course_id, count(*) as cnt_total
	  from courses 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를 포함한 전체 쿼리를 실행하도록 하기

실전에서 유용한 SQL문법 - 문자열 데이터 다뤄보기

문자열 데이터 다뤄보기

• 문자열 쪼개보기(SUBSTRING_INDEX)

-- 이메일에서 아이디만 가져오기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users;
-- 이메일에서 이메일 도메인만 가져오기
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users;

• 문자열 일부만 출력하기(SUBSTRING)
  • SUBSTRING(문자열, 출력 하고싶은 첫 글자의 위치, 출력할 문자의 개수)

-- orders 테이블에서 날짜까지 출력하게 하기
select order_no, created_at, 
       SUBSTRING(created_at, 1, 10) as date
  from orders;
-- 일별로 몇 개씩 주문이 일어났는지 보기
select order_no, created_at,
	   SUBSTRING(created_at, 1, 10) as date,
       count(*) as cnt_date
  from orders
 group by date;

실전에서 유용한 SQL문법 - CASE

경우에 따라 원하는 값을 새 필드에 출력하기

-- 10000점보다 높으면 '잘하고있어', 평균보다 낮으면 '조금만더해보자'라고 표시
select pu.point_user_id, pu.point,
(case when pu.point > 10000 then '잘하고있어'
	 else '조금만더해보자' end ) as '구분'
  from point_users pu;

• 실전을 위한 트릭

-- subquery로 통계내보기
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;
-- 서브쿼리를 이용해 group by로 통계내기
select level, count(*) as cnt 
  from (
	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
) a
group by level;
-- with절과 함께 사용
with tabel1 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(*) as cnt
from table1 a
group by level;

SQL문법 복습

퀴즈1] 평균 이상 포인트를 가지고 있으면 '잘하고있어요' 낮으면 '열심히해보자' 표시하기

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;

퀴즈2] 이메일 도메인별 유저 수 세기

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

퀴즈3] '화이팅'이 포함된 오늘의 다짐만 출력해보기

select * from checkins c
where c.comment like "%화이팅%";

퀴즈4] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의 수 출력

select a.enrolled_id,
	   a.done_cnt,
       b.total_cnt
  from (
		select enrolled_id, count(*) as done_cnt
  		  from enrolleds_detail
		 where done = 1
         group by enrolled_id
) a inner join (
	select enrolled_id, count(*) as total_cnt
      from enrolleds_detail
      group by enrolled_id
) b on a.enrolled_id = b.enrolled_id;

-- with절로 정리
with table1 as (
	select enrolled_id, count(*) as done_cnt
  		  from enrolleds_detail
		 where done = 1
         group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as total_cnt
      from enrolleds_detail
      group by enrolled_id
)
select a.enrolled_id,
	   a.done_cnt,
       b.total_cnt
  from table1 a 
 inner join table2 b on a.enrolled_id = b.enrolled_id;

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

with table1 as (
	select enrolled_id, count(*) as done_cnt
  		  from enrolleds_detail
		 where done = 1
         group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as total_cnt
      from enrolleds_detail
      group by enrolled_id
)
select a.enrolled_id,
	   a.done_cnt,
       b.total_cnt,
       round((a.done_cnt/b.total_cnt),2) as ratio
  from table1 a 
 inner join table2 b on a.enrolled_id = b.enrolled_id;
 
-- 위 쿼리를 더 간단하게
select enrolled_id,
	   sum(done) as done_cnt,
       count(*) as total_cnt
  from enrolleds_detail
 group by enrolled_id;