# 수업 목표
- Subquery(서브쿼리)의 사용 방법 배우기
- 실전에서 유용한 SQL 문법
- SQL을 사용하여 실전과 같은 데이터분석을 진행
# SubQuery란?
: 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것. subquery를 사용하지 않고도 원하는 데이터를 추출할 수 있지만, 서브쿼리를 이용하여 하위 쿼리의 결과를 상위 쿼리에서 사용하면 SQL 쿼리가 훨씬 간단해진다. 즉, 더 편하고 간단하게 사용하기 위한 기능!
* 주어진 데이터를 유의미한 정보로 만들기
- 문자열을 한 번에 정리하기
- 조건에 따라 데이터를 구분
# subquery 사용 방법
- EX 1 : kakaopay로 결제한 유저들의 정보 보기
- 기존 join을 사용한 방식
👉 테이블을 합친 뒤에 값들을 필터링하여 payment_method가 kakaopay인 값들을 남기는 방식 (선 연결 - 후 필터링)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를 사용한 방식
- 우선 kakaopay로 결제한 user_id를 모두 구해보기 → 이 값을
K라고 임의로 지칭.
select user_id from orders where payment_method = 'kakaopay'-
그 후에, 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' )👉 테이블을 합치기 전 값들을 필터링한 뒤 테이블로 합치는 방식 (선 필터링 - 후 연결)
- 기존 join을 사용한 방식
# 서브쿼리가 있는 코드와 없는 코드의 차이점
: 위에 작성한 것을 토대로 하면, 첫 번째 쿼리문은 A라는 유저가 a를 구매한 것, A라는 유저가 b를 구매한 것 모두 테이블로 합쳐져서(join되어) A 유저의 데이터가 2번 들어가지만,
두 번째 쿼리문에서는 A라는 유저가 카카오페이로 결제한 경우(a, b)가 orders 테이블에 있으므로 'A 유저를 하나의 테이블에 넣는다' 라는 순서의 차이가 생긴다.
따라서 이런 결과의 차이로 추출된 데이터는 비슷해보이지만 출력값의 개수가 다르게 나오게 된다!
# 자주 쓰이는 subquery 유형
-
where(조건문)
: 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
where 필드명 in (subquery)
ex) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때select * from users u where u.user_id in ( select o.user_id from orders o where o.payment_method = 'kakaopay' );< 쿼리 실행 순서 >
(1) from 실행: users 데이터를 가져와줌
(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
(4) 조건에 맞는 결과 출력 -
select(결과값)
: Subquery의 결과를 조건에 활용하는 방식으로 사용
select 필드명, 필드명, (subquery) from ..
ex) '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지
-
서브쿼리 작성
→ 평균 먼저 구하기 (예시:user_id='4b8a10e6')select avg(likes) from checkins c2 where c2.user_id = '4b8a10e6' -
상위 쿼리 작성
SELECT c.checkin_id, c.user_id, c.likes, (SELECT AVG(likes) from checkins WHERE user_id = c.user_id ) as avg_likes_users FROM checkins c< 쿼리 실행 순서 >
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!
from: 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용 (가장 많이 사용하는 유형!⭐⭐⭐)
ex) 유저 별 좋아요 평균 구하기
- 서브쿼리 작성
→checkins테이블을user_id로group byselect user_id, round(avg(likes),1) as avg_like from checkins group by user_id - 상위 쿼리 작성
→ 해당 유저 별 포인트를 구해서 포인트와 like의 상관정도를 알 수 있음SELECT pu.user_id, pu.`point`, a.avg_likes FROM point_users pu INNER JOIN ( SELECT c.user_id, ROUND(AVG(likes),1) as avg_likes FROM checkins c GROUP BY c.user_id ) a ON pu.user_id = a.user_id;< 쿼리 실행 순서 >
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!
# with절
: 더 깔끔하게 쿼리를 작성할 수 있다.
ex) 코스 제목별 like 개수, 전체, 비율을 구할 때 이렇게 서브쿼리가 계속 붙으면 inner join 안쪽이 헷갈리게 된다.
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절을 사용하면 결과는 같지만 훨씬 깔끔해보인다!
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# 문자열 정리하기
: 실전 업무에서는 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많다.
- 문자열 쪼개기
→SUBSTRING_INDEX을 사용해주면 된다.
ex) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오려면?
* 이메일에서 아이디만 가져오려면?
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users👉 @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
*이메일에서 이메일 도메인만 가져오려면?
select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users👉 @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
- 문자열 일부만 출력하기
→SUBSTRING을 사용해주면 된다.SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
ex 1 ) orders 테이블에서 created_at을 날짜까지만 출력하려면?
select order_no, created_at, substring(created_at,1,10) as date from ordersex 2 ) 일별로 몇 개씩 주문이 일어났는지,
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date# Case
: 경우에 따라 원하는 값을 새 필드에 출력.
ex) 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까?
select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘 하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
from point_users pu;# case와 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 a.level, COUNT(*) as cnt
FROM (
SELECT pu.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 a.level- with절을 사용하면?
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 level
FROM point_users pu )
SELECT a.level, COUNT(*) as cnt
FROM table1 a
group by a.level# 실습 (복습 후 수정 예정)
- EX 1 : 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
* hint : CASE 문법 사용, CASE 안에서 Subquery로 평균을 계산하여 비교해보기
SELECT point_user_id, `point`,
(
case when point >
(select avg(point) from point_users pu) then '잘하고 있어요!'
else '열심히 합시다!' end
) as msg
FROM point_users pu- EX 2 : 이메일 도메인별 유저의 수 세어보기
* hint : SUBSTRING_INDEX와 Group by를 잘 사용하면 끝!
SELECT domain, count(domain) as cnt_domain
FROM (select SUBSTRING_INDEX(u.email,'@',-1) as domain
from users u) a
GROUP BY domain- EX 3 : '화이팅'이 포함된 오늘의 다짐만 출력해보기
* hint : like를 어떻게 썼더라~
SELECT * FROM checkins c
WHERE c.comment LIKE '%화이팅%'- EX 4 : 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
* hint : 진도율 = (들은 강의의 수 / 전체 강의 수)
SELECT a.enrolled_id, cnt_done, cnt_tot,
ROUND((cnt_done/cnt_tot),2) as ratio
FROM
(SELECT enrolled_id, COUNT(*) as cnt_tot
FROM enrolleds_detail ed
GROUP BY enrolled_id) a
INNER JOIN
(SELECT enrolled_id, COUNT(*) as cnt_done
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 cnt_tot
FROM enrolleds_detail ed
GROUP BY enrolled_id
),
table2 as (
SELECT enrolled_id, COUNT(*) as cnt_done
FROM enrolleds_detail ed
WHERE done = '1'
GROUP BY enrolled_id
)
SELECT a.enrolled_id,
cnt_done,
cnt_tot,
ROUND((cnt_done/cnt_tot),2) as ratio
FROM table1 a
INNER JOIN table2 b
ON a.enrolled_id = b.enrolled_id;- EX 5 : 그러나, 더 간단하게 만들 수 있지 않을까!
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id해설: 수강한 강의는 데이터 값이 1(들은 강의)과 0(안 들은 강의)로 저장한다. 들은 강의는 done=1이고, 만약 들은 강의가 3개라고 하면(counting:3) 1+1+1이므로 합계는 3이 된다. 따라서 sum(done)을 쓴다면 done의 합계가 나올테니 count한 값과 같다. 이는 데이터가 1이기 때문에 이렇게 간단한 쿼리로 작성할 수 있던 것이다. 정석은 없지만 조금만 생각의 전환을 하면 복잡하지 않게 결과를 도출할 수 있다.
- EX 6 : 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
* hint : 위 구문의 서브쿼리 내에서 users와 inner join 하기
SELECT * FROM point_users pu
WHERE point > (
SELECT ROUND(AVG(`point`),2) as avg_pnt
FROM point_users pu
INNER JOIN users u
on pu.user_id = u.user_id
WHERE name LIKE '%이**'
);필요한 경우, Subquery 안에서 여러 테이블을 Join 할 수 있다.
- EX 7 : checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
* hint : Select 절에 들어가는 Subquery
SELECT c.checkin_id,
c2.title,
c.user_id,
c.likes,
(
SELECT ROUND(AVG(likes),1) FROM checkins
WHERE course_id = c.course_id
) as avg_course_id
FROM checkins c
inner join courses c2
on c.course_id = c2.course_id - EX 8 : course_id별 checkins개수와 전체 인원, 비율 구하기
* 작성 순서 :- course_id별 유저의 체크인 수를 구하고
- course_id별 인원을 구하고
- [진짜 구하려는 값] course_id별 checkins개수에 전체 인원을 붙인다
=> inner join 활용 - 비율을 구할 땐 checkins개수를 전체 개수로 나누고
- 보기 좋게 course_id 대신 제목을 붙이기 위해 title 필드로 변경.
※쿼리가 길어지므로 줄맞춤을 잘 해주고,
SELECT c.title,
a.cnt_checkins,
b.cnt_tot,
(a.cnt_checkins/b.cnt_tot) as ratio
FROM (
SELECT course_id, count(DISTINCT(user_id)) as cnt_checkins
FROM checkins c
GROUP BY c.course_id
) a
INNER JOIN
(
SELECT course_id, COUNT(*) as cnt_tot
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 - EX 9 : 위 구문을 with절로 보기 좋게 정리하기
with table1 as (
SELECT course_id, count(DISTINCT(user_id)) as cnt_checkins
FROM checkins c
GROUP BY c.course_id
),
table2 as (
SELECT course_id, COUNT(*) as cnt_tot
FROM orders o
GROUP BY course_id
)
SELECT c.title,
a.cnt_checkins,
b.cnt_tot,
(a.cnt_checkins/b.cnt_tot) 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 (subquery) 예제에서 where user.id = c.user.id인 이유
=> checkin 테이블이 서브쿼리와 전체쿼리에 두 번 적용되니, 상위 쿼리에는 별칭 c를 붙여 구분을 한다. 그리고 checkins c의 user.id를 서브쿼리 user.id와 연결되어 좋아요 평균을 구하고 select를 통해 마지막 필드로 추출하게 된다. -
from절에 사용한 서브쿼리는 -> 마치 원래 있던 테이블처럼 사용할 수 있게 된다.
내가 만들고자 하는 테이블을 각각 어떻게 생성하고 조인할지 등등 쪼개서 생각하는 것이 중요 -
서브쿼리와 메인쿼리에 같은 테이블을 사용할 때, alias 혼용하지 않도록 주의한다.
이 문제로 에러가 자주 났다. 항상 인지하고 쿼리를 작성할 것.
ex) checkins c (메인쿼리), checkins c1 (서브쿼리) -
쿼리가 길어질수록 헷갈리기 쉬우니 줄맞춤(tab)과 alias를 잘 활용해준다.
-
퀴즈2를 푸는데 이런 에러가 났다.
SQL Error [1248][42000]: Every derived table must have its own alias
처음 보는 에러였지만 문장 그대로, 파생된 모든 테이블에는 고유한 별칭이 있어야 한다는 것을 알았다. from절에 만든 서브쿼리에 별칭을 만들어주면 해결된다. -
like 쓸 때 @와 % 헷갈리지 않기. %사용해야 함!!
ex) 이메일 도메인만 추출할 때,like '%naver.com'를 자꾸'like @naver.com'라고 쓴다던가,, -
SQL Error [1241][21000]: Operand should contain 1 column(s)
=> 피연산자는 1개의 열을 포함해야 한다. -
아무래도 입문자다 보니 처음보는 에러가 많아서 쿼리를 짤 때 시간이 더 오래 걸린다. 에러 위주로 정리해서 포스팅하는 것도 좋을 것 같다.
-
위에 퀴즈5 해설에도 썼지만 가끔은 어려워 보이는 문제도 조금만 관점을 바꿔서 생각해보면 아주 간단하게 짤 수 있다. with절도 마찬가지로 처음 배울 땐 괜히 행도 길어지는 것 같고 더 복잡해보였는데 구조를 알고 나니 서브쿼리만 사용했을 때보다 훨씬 직관적이었다. 내가 아는 정보 내에서 최대한 효율적으로 쿼리를 짜는 습관을 들일 수 있도록 많은 실습이 필요할 것이다.!
