[SQL 입문] Week4

4주차

노랑이 = 배운 것 

핑크 = 내가 모르거나 틀린 것

 

1. subquery 원하는 데이터를 더 쉽게 

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

쿼리안에 쿼리라는 의미

하위 쿼리의 결과를 상위 쿼리에서 사용하면 SQL이 간단해진다

서브쿼리를 사용하지 않아도 원하는 데이터를 얻어 낼 수 있지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 기능

 

(1) where에 들어가는 subquery

where는 조건문. 서브쿼리의 결과를 조건에 활용하는 방식으로 사용

where 필드명 in (subquery) 방식

 

e.g.

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

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'

 

다른 방법은 : 

select user_id, name, email from users u 
where user_id in (
    select user_id from orders 
    where payment_method = 'kakaopay'
)

 

(2) Select에 들어가는 서브쿼리

select는 결과를 출력해주는 부분. 기존 테이블에 함께 보고 싶은 통계 데이터를 손쉽게 붙이는 것에 사용

selct 필드면, 필드명, (subquery) from... 이렇게

 

오늘의다짐 데이터를 보고 싶은데, 오늘의다짐 좋아요 수가 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지 궁금할 때

평균 user_id = '4b8a10eb' 부터 구하면서 시작

 

select c.checkin_id , 
       c.user_id , 
       c.likes,
       (
        select (avg(likes) from checkins c
        where user_id = c.user_id
       ) as avg_likes_user
  from checkins c 

 

(3) From 절에 들어가는 서브쿼리 (가장 많이 사용되는 유형!!)

내가 만든 select와 이미 있는 테이블을 join하고 싶을 때 사용~

 

유저별 좋아요 평균 구하기

열심히 활동한 사람이 포인트가 많을 텐데, 그 사람이 likes도 많이 받았을까? 

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 c 
group by user_id
) a on pu.user_id = a.user_id

 

---

연습

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

select * from point_users pu 
where point > (
select avg(point) from point_users pu 
)

 

2. 이씨 성을 가지 유저들의 평균 포인트 보다 큰 데이터

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

 

3. 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 

 

4. checkins 테이블에 과목명별 평균 likes 수 우측에 붙여 보기 

,내가 푼 방식> 문제 풀 때 조인 쓰라고 했었었네;; 

select c.checkin_id, 
   (select title from courses
    where course_id = c.course_id 
   ) as course_title,
   c.user_id, 
   c.likes, 
  (select round(avg(likes),1) from checkins
   where course_id = c.course_id 
) as course_avg
from checkins c 

 

<강사님이 푼 방식>

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 course_avg
from checkins c 
inner join courses c2 on c.course_id = c2.course_id 

 

c2.* = c2의 모든 것을 보여줘라

 

5. from 절 서브쿼리 연습

course_id 별로 checkin 갯수 구하기 

distinct로 유저를 세면 되겠네요

비율까지 구해보고

강의제목도 나타나게~