데이터 처리 심화 : SQL 테이블 검색(4주차)

1. Subquery 사용방법

=> users와 orders table을 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'
- Subquery
일단 결재 수단 조건의 사용자를 검색한 후, 그 결과의 사용자들을 다시 검색하는 쿼리문을 작성합니다.
select user_id from orders
where payment_method = 'kakaopay'

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'
)

2. 자주 쓰이는 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) from
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 (가장 많이 사용되는 유형!)
select user_id, round(avg(likes),1) as avg_like from checkins
group by user_id

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가 실행됩니다.

3. with절로 table 세팅 간소화하기

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

- inner join 안쪽이 너무 헷갈리므로, 이때 쓰는 것이 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

4. 문자열 쪼개보기

=> 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져옵니다.

select user_id, email, SUBSTRING_INDEX(email, '@', 1) as email_id from users
select user_id, email, SUBSTRING_INDEX(email, '@', -1) as domain from users

5. 문자열 일부만 출력하기

=> created_at을 날짜까지만 출력하고 일자별로 몇 개씩 주문이 일어났는지 살펴봅니다.

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

6. CASE When 조건문 사용하기

=> 경우에 따라 원하는 값을 새 필드에 출력해 보기

select pu.point_user_id, pu.point,
case
when pu.point > 10000 then '잘하고 있어요!'
else '조금 더 달려주세요!'
END as '구분'
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