SQL 4주차

sss·2022년 2월 3일

select u.user_id, name, email from users u
inner join orders o
on u.user_id = o.user_id

이걸 서브쿼리로 가능

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

여기서 in(x)의 x는 user_id필드 안에 있는 데이터 -> 여기에 하나의 select를 통째로 넣음 → 서브쿼리

서브쿼리는 탭을 이용해 줄맞춰주는 게 중요 -> 안 그러면 어디까지가 서브쿼리인지 헷갈림

블록처리 한 다음 실행

서브쿼리 = 큰 쿼리 안에 있는 작은 쿼리

자주 쓰이는 서브쿼리 유형

where에 들어가는 subquery
where 필드명 in (subquery)

select에 들어가는 subquery
select 필드명, 필드명, (subquery) from …

ex) user id별로 평균 like
select c.checkin_id,
c.user_id,
c.likes,
(
select avg(likes) from checkins
where user_id = c.user_id
) as avg_likes_user
from checkins c

from에 들어가는 subquery
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
group by user_id
) a on pu.user_id = a.user_id

→ 마치 원래부터 있었던 테이블로 사용하는 것

쪼개서 생각하는 것이 좋음
ex) 목표로 생각하는 테이블이 있다 -> 이건 어떻게 만드는지 알겠고 요것도 알겠다
-> 둘 다 따로 만들어서 서브쿼리로 join하면 됨

전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출
select * from point_users pu
where pu.point >
(
select avg(pu.point) from point_users pu
)

굳이 서브쿼리로 넣는 이유? 평균값이 실시간으로 바뀌기 때문

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

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

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

쿼리가 길어질수록 가장 중요한 건 보기 좋게 작성하는 것이다

course_id별 평균 like수 붙이기

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

메인쿼리, 서브쿼리가 같은 필드를 다뤄도 엄연히 다른 필드기 때문에 구분해야함.
과목명별 평균 like수 붙이기
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,
c2.*
from checkins c
inner join courses c2 on c.course_id = c2.course_id

여기서 c2*는 c2가 잘 붙었나 확인작업
(콤마 빼먹지 않기)

포인트가 평균보다 큰 사람들의 데이터 추출
select * from point_users pu1
where point > (
select avg(pu2.point) from point_users pu2
)

이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출
select * from point_users pu1
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 pu.point > (
select avg(pu.point) from point_users pu
where user_id in (
select user_id from users where name = ‘이**’
)

in
user_id가 어디있는 거?

→ select * from users where name = ‘이**’

→ inner join 없이 사용 가능

From절에 들어가는 subquery연습
1. course_id별 유저의 체크인 개수 구하기
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

  1. course_id별 인원
    select course_id,
    count(user_id) as cnt_total
    from orders
    group by course_id

  2. course_id별 like개수에 전체 인원 붙이기
    select a.course_id, a.cnt_checkins, b.cnt_total
    from (
    select course_id,
    count(distinct(user_id)) as cnt_checkins
    from checkins
    group by course_id
    ) a
    inner join (
    select course_id,
    count(user_id) as cnt_total
    from orders
    group by course_id
    ) b
    on a.course_id = b.course_id

  3. 퍼센트를 나타내기
    select a.course_id,
    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(user_id) as cnt_total
    from orders
    group by course_id
    ) b
    on a.course_id = b.course_id

  4. 여기에 강의제목 붙이기
    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(user_id) 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
)

with table1 as
→ 일종의 alias처럼 사용 가능 → 임시 테이블 생성

***무조건 맨 윗줄에 쓴다

from 안에 있는 subquery를 쓸 때는 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

실전에서 유용한 문법 (문자열)
1. 문자열 데이터 다루기

  • 실제업무에서는 문자열 데이터를 원하는 형태로 한 번에 정리해야 하는 경우 많음
    1) 이메일에서 아이디만 가져와보기
    select user_id, email, SUBSTRING_INDEX(email,’@’,1) from users
    → @ 기준으로 앞만 (왼쪽)
    select user_id, email, SUBSTRING_INDEX(email,’@’,-1) from users
    → @ 기준으로 뒤만 (오른쪽)
    substring은 부분열, index는 색인(찾기)라는 뜻

  1. 문자열 일부만 출력하기
    select * from orders에서 시간 빼고 일자까지만 필요할 경우
    select order_no, created_at, SUBSTRING(created_at,1,10) from orders
    → 여기서 1, 10은 추출할 문자의 첫째 자리 수 / 추출할 문자 개수

  2. 일별로 몇 개씩 주문이 일어났는지 보기
    select order_no, SUBSTRING(created_at,1,10) as date, count(*) from orders
    group by date

실전에서 유용한 문법(case)
특정 조건에 따라 데이터를 구분해서 정리할 때 사용

point를 구간별로 표시하고 싶을 때?
select pu.user_id, pu.point,
(case when pu.point > 10000 then ‘잘 하고 있어요’
else ‘조금만 더 화이팅’ end) as msg
from point_users pu

포인트 보유액에 따라 표시하기
select a.lvl, 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 lvl
from point_users pu
) a
group by a.lvl

이렇게 하면 간단한 통계도 가능하다

이걸 with로 바꾸면
select pu.user_id, pu.point,
(case when pu.point > 10000 then ‘1만 이상’
when pu.point > 5000 then ‘5천 이상’
else ‘5천 미만’ end) as lvl
from point_users pu

평균 이상 포인트 -> 잘하고 있어요 / 미만 -> 열심히 합시다
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

이메일 도메인별 유저 수 카운트
select SUBSTRING_INDEX(email,’@’,-1) as domain,
count(*)
from users
group by domain
→ 오답

select domain, count(*) as cnt from (
select SUBSTRING_INDEX(email,’@’,-1) as domain from users
) a
group by domain
→ 정답
→ group by를 새로 지정한 domain으로 하려면 from을 바꿔줬어야함

‘화이팅’이 들어간 코멘트 찾기

select * from checkins
where comment like ‘%화이팅%’

수강등록정보(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 b.enrolled_id, a.done_cnt, b.total_cnt
from table2 b
inner join table1 a 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 b.enrolled_id, a.done_cnt, b.total_cnt, round((a.done_cnt/b.total_cnt),2) as ratio
from table2 b
inner join table1 a
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

****가끔 멀리서 보면 더 나은 쿼리를 만들 수 있다.

profile
sss
ㅇ
이전 포스트

SQL 3주차

다음 포스트

SQL 4주차

0개의 댓글