[스파르타코딩]4주차 SQL

Subquery

하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것을 의미합니다.

===============================================

users 와 orders 의 inner join으로!

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'

< -- 이걸 직관적으로 서브쿼리로 변경을 하면 -- >

1. 우선 kakaopay로 결제한 user_id를 모두 구해보기 → `K` 라고 합시다.

select user_id 
from orders
where payment_method = 'kakaopay'

2. 그 후에, user_id가 `K` 에 있는 유저들만 골라보기

→ 이게 바로 서브쿼리!

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'
)
===============================================

Where 에 들어가는 Subquery

Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.

where 필드명 in (subquery) 이런 방식으로요!

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

[연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

SELECT p.point_user_id, p.created_at , p.updated_at , p.user_id , p.point
FROM point_users p
WHERE point > (
SELECT AVG(point) 
FROM point_users pu 
)

====================================================

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

SELECT p.point_user_id, p.created_at , p.updated_at , p.user_id , p.point
FROM point_users p
WHERE point > (

SELECT AVG(point) 
FROM point_users pu inner join users u 
on pu.user_id = u.user_id
WHERE u.name='이**' 
)

====================================================

[연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

SELECT c.checkin_id , 
c.course_id , 
c.user_id , 
c.likes,
(
select AVG(likes) 
from checkins
WHERE course_id = c.course_id 
)as course_avg
FROM checkins c 


====================================================

[연습] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

SELECT c.checkin_id , c2.title, c.user_id , c.likes,
(
select AVG(likes) 
from checkins
WHERE course_id = c.course_id 
)as course_avg
FROM checkins c 
inner join courses c2
on c.course_id = c2.course_id 

====================================================

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

SELECT c2.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(DISTINCT(user_id)) as cnt_total
FROM checkins c
group by course_id 
) b on a.course_id = b.course_id
INNER JOIN courses c2 on a.course_id = c2.course_id

====================================================

with 절로 더 깔끔하게 쿼리문을 정리하기

<이랬던 코드가>

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
group by course_id
) a
inner join
(
select course_id, count(*) as cnt_total from orders
group by course_id 
) b on a.course_id = b.course_id
inner join courses c on a.course_id = c.course_id


<이렇게 바뀝니다.>

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

====================================================

문자열 데이터 다뤄보기

실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많습니다.

[예제]
이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!

SELECT user_id , email, SUBSTRING_INDEX(email,'@',1) 
-- @ 기준으로 앞 뒤 지정하여 출력가능 1이면아이디, -1이면 도메인
FROM users u 

====================================================

[예제]
orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!

SELECT order_no , created_at, SUBSTRING(created_at,1,10) as date 
FROM orders o 


[예제]
일별로 몇 개씩 주문이 일어났는지 살펴보기

SELECT order_no , 
created_at, 
SUBSTRING(created_at,1,10) as date,
COUNT(*)
FROM orders
group by date
====================================================

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

특정 조건에 따라, 데이터를 구분해서 정리해주고 싶을 때가 있겠죠? 
이런 경우에 CASE 라는 문법이 사용됩니다.

[예제]

10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!',
 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?

SELECT user_id , point,
(case when point > 10000 then '잘 하고 있어요!'
else '조금만 더 파이팅!' end) as msg
FROM point_users pu 


Subquery를 이용하면 이런 통계도 낼 수 있어요!

SELECT a.lv, COUNT(*) as cnt 
FROM (
SELECT user_id , point,
(case when point > 10000 then '1만 이상'
  when 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 , point,
(case when point > 10000 then '1만 이상'
  when 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

======================================================

 [퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

SELECT point_user_id , point,
(case when point > 
(SELECT AVG(point) FROM point_users pu ) then '잘하고 있어요'
 else '열심히 합시다.' end) as msg
FROM point_users pu

[퀴즈] 이메일 도메인별 유저의 수 세어보기

SELECT DOMAIN, COUNT(*) as cnt 
FROM (
SELECT SUBSTRING_INDEX(email,'@',-1) as domain
from users
) a
group by DOMAIN 

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

SELECT *
FROM checkins c 
WHERE comment LIKE '%화이팅%'


===========================================


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

with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
group by enrolled_id
)


select a.enrolled_id, a.cnt_done, b.cnt_total 
from lecture_done a
inner join lecture_total b 
on a.enrolled_id = b.enrolled_id

 [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

with lecture_done as (
select enrolled_id, count(*) as cnt_done from enrolleds_detail ed 
where done = 1
group by enrolled_id
), lecture_total as (
select enrolled_id, count(*) as cnt_total from enrolleds_detail ed 
group by enrolled_id
)


select a.enrolled_id, a.cnt_done, b.cnt_total,
   round(a.cnt_done/b.cnt_total,2) 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 ed
group by enrolled_id

[ oh my god.... ]