4주차: subquery, with, substring_index, case when

Subquery - 쿼리 안의 쿼리라는 뜻. 하위 쿼리의 결과를 상위 쿼리에서 사용한다.

바로 예시로 들어감

• 카카오로 결제한 유저들의 정보를 보자

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' #저번시간까지 한 것

select * from users
where user_id in ('3b3eac9f') #괄호안에있는 것만 보겠다~

select user_id, name, email from users
where user_id in (
select user_id from orders
where payment_method = 'kakaopay'
) #변형후! 일단 users를 불러왔고, 거기에서 user id만 내보낼거야. 근데 그중에서도 orders에 있는 거에서의 user id 중 카카오페이로 결제한 것들에 관한 정보만 내보낼거야. 원래는 inner join을 썼겠지만 이렇게 한번에 포함시킬 수 있다는 말임. 가장 안에있는(괄호) 것이 먼저 실행되고, 바깥쪽이 가장 마지막에 실행된다. + 탭을 가지고 줄바꿈 해주기.

• select에 들어가는 subquery. from 이전에.
  예문: 오늘의 다짐 데이터에서, 개개인이 조아요 수가 이 평소에 받았던 좋아요 숫자에 비해 얼마나 많은지를 궁금

select avg(likes) from checkins
where user_id = '4b8a10e6' #4b8a10e6이라는 사람의 평소 조아요수

#하지만 우리는 특정 사람당 개별 좋아요수를 보이고 바로 옆에 새로운 변수를 만들고싶음. 그 변수명은 "평균 like"이고,, 각 사람들별 좋아요수 평균이 들어갈것임. ★★★★★★★★★이거 질문했던 부분임.
select c.checkin_id, #기원지 알파벳은 그냥 무조건쓰면좋은거같기도..
c.user_id,
c.likes,
(
select avg(likes) from checkins
where user_id = c.user_id # 이 줄이 가장 이해가 안되는데, 이 자체가 한 줄의 user id 갖는 데이터의 각각을 뽑아내는것임.
) as avg_likes_user
from checkins c #from 절에 들어가는 subquery,, 쪼인을 바로 하는 느낌
select user_id, point from point_users pu #이걸 아래랑 합칠래
group by user_id

select user_id, round(avg(likes),1) as avg_likes from checkins
group by user_id #id별 평균

#합쳐,, inner join을 패러프레이징하는느낌으로다가

select pu.user_id, pu.point, a.avg_likes from point_users pu
inner join (
select user_id, round(avg(likes),1) as avg_likes from checkins a
group by user_id
) a on pu.user_id = a.user_id

#퀴즈.

select * from point_users pu
where point > (
select avg(point) from point_users
) #실시간 반영이 되어야하니 고정된 실수를 쓰지 않는다고 함.

select * from point_users pu
where point > (
select avg(point) from point_users pu
inner join users u on pu.user_id = u.user_id
where u.name='이**'
)
혹은

select * from point_users pu
where point > (
select avg(point) from point_users pu
where user_id in(
select user_id from users where name='이**')

)

from 절에 들어가는 subquery 연습하기. ★★★★★★★★★★★★★★★

• step 1: 코스별 진짜 체크인한 아이디수

  select
  course_id,
  count(distinct(user_id)) as cnt_checkins
  from checkins #distinct는 유저의 중복을 피하기위함..그니까 하루에 여러번 출석하는 사람들을 ...제끼려는 것으로 이해해야할듯"? 그리고 count(distinct(user_id)) 이지만 distinct(count(user_id))가 아님! 조심하기!!
  group by course_id

• step 2: 코스별 결제한 아이디수

  select
  course_id,
  count(*) as cnt_total
  from orders
  group by course_id

• step 3: 스텝 1과 2를 묶기

select a.course_id,
b.cnt_checkins,
a.cnt_total,
(b.cnt_checkins/a.cnt_total)
from
(select course_id, count(*) as cnt_total from orders
group by course_id
) a #a는 위에 step 2
inner join (
select course_id, count(distinct(user_id)) as cnt_checkins
from checkins
group by course_id) b #b는 위에 step 1
on a.course_id = b.course_id

• step 4: 스텝 3의 가장 왼쪽에다가 전체 코스제목을 넣기.

  select c.title,
  a.course_id,
  b.cnt_checkins,
  a.cnt_total,
  (b.cnt_checkins/a.cnt_total)
  from
  (
  select course_id, count(*) as cnt_total from orders
  group by course_id
  ) a #a는 위에 step 2
  inner join (
  select course_id, count(distinct(user_id)) as cnt_checkins
  from checkins
  group by course_id) b #b는 위에 step 1
  on a.course_id = b.course_id
  inner join courses c on a.course_id = c.course_id #한번 더 inner jopni 해라

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 활용하기

• 엑셀에서 텍스트나누기 기능과 유사한..듯했으나
  또 다시 보니까 달랐네...!
  텍스트나누기보다는,, 특정 변수 안에있는 문자열에 기준을 부여해서 도려내고!
  새로운 변수를 통해 그 도려낸 항목을 넣게함.

select user_id, email,substring_index(email,'@',-1) from users #그러면 이메일주소의 @기준 뒷덩어리가 나온다. 만약 substring_index(email, '@', 1) 하면 @ 기준으로 앞이 나올 것이다.

select order_no, created_at, substring(created_at, 1,10) as date from orders #created_at시간대에서 왼쪽에서부터 10번째의 숫자 혹은 문자까지를 드러내게 함. 새로운 변수를 만드는 셈이ㄷ

select substring(created_at, 1,10) as date, count(*) from orders #날짜별 개수를 찾아본다면?
group by date

case when : 범위별로 새로운 문자열을 부여하는 변수를 만들기

select pu.user_id, point,
(case when pu.point > 10000 then '굿' when pu.point > 5000 then '쏘쏘'
else '더해라' end) as msg
from point_users pu

#바로직전꺼에서 더 나아가, msg를 그룹바이해보자. 이건 내가 해냈다 ㅠㅠ
with table1 as (select pu.user_id, point,
(case when pu.point > 10000 then '굿' when pu.point > 5000 then '쏘쏘'
else '더해라' end) as msg
from point_users pu )
select a.msg,count(*) from table1 a
group by a.msg

• 퀴즈1

  select pu.point_user_id, point, case when pu.point >= (select avg(pu1.point) from point_users pu1) then '잘 하고 있어요' else '열심히 합시다!' end as 'msg'
  from point_users pu

  #뭔가 나는 바로 point들의 평균을 case when 안에서 처리할 수 있을 줄 알았는데 그렇게 똑똑하지는 않나봄. 어쩔 수 없이 새롭게 subquery를 지정해가지고... ㅇ해야하ㅐ는듯.

• 퀴즈2

  select domain, count(*) as cnt from (
  select substring_index(email, '@',-1) as domain from users)
  a #마찬가지로 select를 해서 새롭게 테이블을 정의..한 후,,, 얘네들 자체의 개수를 한번 구해봐,라~
  group by domain

• 퀴즈 2 다르게 풀기

  select substring_index(email, '@',-1) as domain, count(*) from users
  group by domain

• 퀴즈 3 화이팅이 포함된 오늘의 다짐 출력 /복습용

  select comment from checkins
  where comment like '%화이팅%'

• 퀴즈 4 수강정보 별 전체 강의 수와 들은 강의 수 출력해보기

  with table1 as
  (select enrolled_id, count() as done_cnt from enrolleds_detail ed
  where done = 1
  group by enrolled_id), table2 as
  (select enrolled_id, count() as total_cnt from enrolleds_detail ed
  group by enrolled_id
  )
  select tb.enrolled_id, tb.done_cnt, t.total_cnt, round(tb.done_cnt/t.total_cnt,2) as ratio from table1 tb
  inner join table2 t on tb.enrolled_id = t.enrolled_id

#엄청 짧은 버전으로 다시..(허무... )
select enrolled_id,
sum(done) as cnt_done,
count(*) as cnt_total
from enrolleds_detail ed
group by enrolled_id