엑셀보다 쉬운, SQL - 4일차(1)

2023-05-04

2:52PM - 4:47 PM

subquery , with

SubQuery: 큰 쿼리문 안에 들어가는 쿼리문을 의미함.

(1) where 안에 서브쿼리 활용

select user_id, name, email from users u

where user_id in (

    select user_id from orders o

    where o.payment_method = 'kakaopay'

)

 

select user_id from orders o

where o.payment_method = 'kakaopay'

위 쿼리에서 괄호 안에 코드가 서브쿼리임.

 

 

(2) select 안에 서브쿼리 활용.

//유저 아이디 별로 좋아요의 평균값을 구하기

/*

select avg(likes) from checkins c

where user_id = '4b8a10e6'

*/

select c.checkin_id ,

           c.user_id ,

           c.likes,

           (

           select avg(likes) from checkins c2

           where user_id = c.user_id

           ) as avg_likes_user

from checkins c

 

 

 

(3) from 안에 서브쿼리 활용

//from안에 서브쿼리 활용

//user별 포인트와 좋아요 평균 조회하기

 

//아이디별 포인트

select user_id, point from point_users pu

group by user_id

 

//아이디별 좋아요 평균 (소수점 첫째자리까지 표스)

select user_id, round(avg(likes),1) as avg_likes from checkins c

group by user_id

 

//user별 포인트와 좋아요 평균 조회하기

select a.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

예제 4개

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

select * from point_users pu2

where point > (

           select round(avg(point),1) from point_users pu

)

 

 

//2. 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

select * from point_users pu2

where point > (

           select round(avg(point),1) from users u

           inner join point_users pu

           on u.user_id = pu.user_id

           where u.name like '이%'

)

//서브쿼리 안에 서브쿼리 들어갈 수 있음

select * from point_users pu2

where point > (

           select avg(point) from point_users pu

                     where user_id in (

                     select * user_id from users u where name like '이%'

           )

)

 

 

 

//3. checkins테이블에 course_id별 평균 likes수 필드 추륵에 붙여보기

select checkin_id,

           course_id,

           user_id,

l           ikes,

           (

           select round(avg(likes),1) from checkins c2

           where c.course_id = c2.course_id

           ) as course_avg

from checkins c

 

select * from checkins c2

inner join (

           select course_id, round(avg(likes),1) as course_avg from checkins c

           group by course_id

) a on c2.course_id = a.course_id

 

 

 

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

select checkin_id,

           c3.title,

           user_id,

           likes,

           (

           select round(avg(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

 

 

조금 어려운 예제 

//course_id별 유저의 체크인 갯수 구하기

select course_id, count(distinct(user_id)) as cnt_checkins from checkins c

group by course_id

 

//course_id별 인원 구하기

select course_id, count(*) as cnt_total from orders o

group by course_id

 

 

//course_id별 like갯수 전체 인원을 붙이기

//퍼센트 나타내기

//course_id별이 아닌 title로 필드 변경하기

select a.course_id, c2.title, cnt_checkins, cnt_total, (cnt_checkins/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(*) as cnt_total from orders o

group by course_id

) b on a.course_id = b.course_id

inner join courses c2

on c2.course_id = a.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