[수업 목표]
- Subquery(서브쿼리)의 사용 방법을 배워본다
- 실전에서 유용한 SQL 문법을 더 배워본다
- SQL을 사용하여 실전과 같은 데이터분석을 진행해본다
Subquery: 원하는 데이터를 더 쉽게 얻어보기
Subquery란? 쿼리 안의 쿼리라는 의미입니다.
하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해져요!
ubquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!
생각보다 실무에서의 데이터는 지저분하고 복잡합니다.
주어진 데이터를 원하는 유의미한 정보로 만들기 위해서는 이것저것 해야할 일이 많습니다. 문자열을 한 번에 정리한다든지, 조건에 따라 데이터를 구분한다든지요. 실무 속 날것의 데이터에서도 원하는 데이터를 뽑아낼 수 있는 유용한 기능을 배워봅니다!
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'
u에 o 안에있는 pay=kakao인것을 가져오고 u.id,u.name,u.email 출력
이것을 이렇게 할 수도 있습니다. 조금 더 직관적이지 않나요?
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'
)
1.orders 안 payment~가 kakao인것을 user_id만 출력.
2.u.user 안 u.user_id 속 (1번)인것을 u.user_id,u.name, u.email 출력
Where 에 들어가는 Subquery
where 필드명 in (subquery) 이런 방식
예를 들면, 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때는 아래와 같이 표현할 수 있겠죠.
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는 결과를 출력해주는 부분이죠? 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용합니다.
select 필드명, 필드명, (subquery) from .. 이렇게요!
오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?
그럼, 평균을 먼저 구해봅시다! 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 c2
where c2.user_id = c.user_id) as avg_like_user
from checkins c;
쿼리가 실행되는 순서를 이렇게 상상하면 편해요!
(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데
(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
(4) 함께 출력해준다!
From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
From은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!
자, 우선 유저 별 좋아요 평균을 먼저 구해볼까요?
checkins 테이블을 user_id로 group by 하면 되겠죠?
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id
자, 이제 여기서 해당 유저 별 포인트를 보고 싶다면?
그러면, 포인트와 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
(1) 먼저 서브쿼리의 select가 실행되고,
(2) 이것을 테이블처럼 여기고 밖의 select가 실행!
Subquery 연습해보기 (where, select, from, inner join)
Where 절에 들어가는 Subquery 연습
전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu
where pu.point > (select avg(pu2.point) from point_users pu2)
이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기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 연습해보기
select checkin_id, course_id, user_id, likes,
(select avg(c2.likes) from checkins c2
where c.course_id = c2.course_id)
from checkins c
checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select checkin_id, c3.title, user_id, likes,
(select round(avg(c2.likes),1) from checkins c2
where c.course_id = c2.course_id) as course_avg
from checkins c
inner join courses c3
on c.course_id = c3.course_id;
From 절에 들어가는 Subquery 연습해보기
course_id별 유저의 체크인 개수를 구해보기!
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
course_id별 인원을 구해보기!
select course_id, count(*) as cnt_total from orders
group by course_id
course_id별 like 개수에 전체 인원을 붙이기
select a.course_id, b.cnt_checkins, a.cnt_total 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
퍼센트를 나타내기
(b.cnt_checkins/a.cnt_total) as ratio 를 select에 추가
강의제목 추가하기
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 절로 더 깔끔하게 쿼리문을 정리하기
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 문법 (문자열, Case)
실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리해야 하는 경우가 많습니다.
이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!
문자열 쪼개보기
UBSTRING_INDEX 라는 문법을 사용하면 됩니다. 바로 쿼리를 볼까요?
이메일에서 아이디만 가져와보기
select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
1을 -1로 바꾸면 마지막 조각을 가져오라는뜻
문자열 일부만 출력하기
orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!
SUBSTRING 이라는 문법을 사용
select order_no, created_at, substring(created_at,1,10) as date from orders
일별로 몇 개씩 주문이 일어났는지 살펴보기
select substring(created_at,1,10) as date, count(*) as cnt_date from orders
group by date
CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
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 lv
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 lv
from point_users pu
) a
group by lv
with 절과 함께하면 금상첨화죠!
with table1 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 lv
from point_users pu
)
select level, count(*) as cnt from table1
group by lv
SQL 문법 복습. 또 복습!
평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
힌트.CASE 문법 사용, CASE 안에서 Subquery로 평균을 계산하여 비교!
select pu.point_user_id, pu.point,
case
when pu.point > (select avg(pu2.point) from point_users pu2) then '잘 하고 있어요!'
else '열심히 합시다!'
end as 'msg'
from point_users pu
이메일 도메인별 유저의 수 세어보기
힌트. SUBSTRING_INDEX와 Group by를 잘 사용하면 끝!
select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a
group by domain
'화이팅'이 포함된 오늘의 다짐만 출력해보기
select * from checkins c
where c.comment like '%화이팅%'
like를 사용하면 보다 쉽게 원하는것을 얻을때도있음 %화이팅%처럼 양옆에 %를 붙여
화이팅이 들어간 모든 코멘를 가져올수있음 =오늘도화이팅,화이팅입니다~,화이팅
수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
힌트.subquery 두 개를 만들어놓고, inner join!
살펴볼 테이블: enrolled_details
done_cnt는 들은 강의의 수(done=1),
total_cnt는 전체 강의의 수
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 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 cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id
문법정리
select= db에서 데이터를 추출
from ~~ = 에서 가져온다 /from order = order테이블에서보다 크거나 작거나 크고 같은것,작고같은것
where ~~ = order테이블속 ~~ 부분에서 /from order where payment
같은것을 찾기
name = '이**' (=)를 쓰면같은것,(!=)같지 않은것을 가져온다
<,>,>=,<=
between = 가~하 사이에 있는 범위값
and = 간단히 말하자면 말 그대로 "그리고 " " and "" 구하고자하는것이 여러개일때
in = user_id in ~~ user_id 속 ~~인것만
like = name like '이' 이름이 이인것만
limit = 자료가 너무많을때 limit5을 넣어 5개의 자료만 추출
count() =group by name 처럼 이름으로 묶고 select 옆에 count()를 쓰면 갯수를 센다
distinct = 중복을 제외한 갯수
as(alias) = round(count(*),2) as cnt 행이름이 cnt로 변경
group by = group by name처럼 이름,이메일,같은 수업,타이틀로 묶게됨
order by = 오름차순 내림차순으로 정리가능 desc 내림차순 (order by count desc)
min = min(likes) 최소값
max 최대값
sum 더한값
avg 평균값
join = inner join ,left join
inner join 교집합 a와 b테이블의 동일한부분 추출
left join = ~~a lfet join b 처럼 a기준으로 a의 모든 데이터+a와 b 교집합부분 중복값추출
union = 조회한 다수의 SELECT 문을 하나로 합치고싶을때 유니온(UNION) 을 사용
sub query = 쿼리속 쿼리
예) select * from (
select name,email from users group user_id
)
with = with table1 as(
select name,email from users group user_id
),table2 as(
select *from orders
)
로 만든후
select *from table1 처럼 바로사용
사용시에는 with table1을 포함한 모든 것을 선택한후 ctrl+enter로 결과 추출
문자열 쪼개기
SUBSTRING_INDEX
select user_id, email, SUBSTRING_INDEX(email,'@',1)from users
@를 기준으로 앞 1 뒤는 -1 / 1을 썼으니 이메일의 아이디값을 가져옴
예)abc@naver.com = abc / -1을 썼으면 naver.com을 가져옴
SUBSTRING
select user_id, created_at, SUBSTRING(created_at,1,10) from orders
created_at 행에서
1번째 글자부터 10번째까지추출
예)2020-07-13-11:11:11 이면 2020의 2부터 13의 3까지 2020-07-13만 표기
CASE : 조건에 따라 값 을 표기
예)
select pu.point_user_id, pu.point,
( case when pu.point > 10000 then '잘하고 있어요'
else '조금만 화이팅' end ) as msg
from point_users pu
as가 붙었으니 행은 msg로 표기되며 point가 천보다 높으면 잘하고있어요!
그렇지않으면(else) 조금만 화이팅이 msg행에 표시된다.
스파르타 sql 마지막수업 끝
