[SQL] 기초개념 - 4주차 (기초 완)

1. Subquery란?

- '쿼리안의 쿼리' 뜻으로 하위 쿼리의 결과를 상위 쿼리에 담아서 사용한다.

1) 간단 실습

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

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'  // 테이블을 합친 뒤에 값들을 필터링하여 payment_method가 kakaopay인 값들을 남기는 방식 ↓ 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 유형

- Subquery는 where, select, from 절에서 유용하게 사용될 수 있다.

1) Where 에 들어가는 Subquery 

: Where(조건문) 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) 조건에 맞는 결과 출력

2) Select 에 들어가는 Subquery

 : Select(결과를 출력해주는 부분)기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용한다. -> 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) 함께 출력

3) From 에 들어가는 Subquery (가장 많이 사용되는 유형)

: from은 언제 사용하면 좋을까요? 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용

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. Subquery 연습해보기 (where, select, from, inner join)

1) Where 절에 들어가는 Subquery

-전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출 (*참고: 평균 포인트는 5380점)

select * from point_users pu  where pu.point > (select avg(pu2.point) from point_users pu2);

-이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출 (*참고: 이씨 성을 가진 유저들의 평균 포인트는 7454점)

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

2) Select 절에 들어가는 Subquery

-checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

select checkin_id, course_id, user_id, likes,  (select avg(c2.likes) from checkins c2 where c.course_id = c2.course_id)  from checkins c;

-checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

select checkin_id, c3.title, user_id, likes,  (select round(avg(c2.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;

3) From 절에 들어가는 Subquery

-course_id별 유저의 체크인 개수를 구해보기 (단계별)

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

-course_id별 인원을 구해보기

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

-course_id별 checkin개수에 전체 인원을 붙이기

select a.course_id, b.cnt_checkins, a.cnt_total from ( select course_id, count(*) as cnt_total from orders group by course_id ) a inner join ( select course_id, count(distinct(user_id)) as cnt_checkins from checkins group by course_id ) b on a.course_id = b.course_id

-퍼센트 나타내기

select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from ( select course_id, count(*) as cnt_total from orders group by course_id ) a inner join ( select course_id, count(distinct(user_id)) as cnt_checkins from checkins group by course_id ) b on a.course_id = b.course_id

-강의 제목 나타내기

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

4. WITH절?

- with 절로 더 깔끔하게 쿼리문을 정리

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 ↓ (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

5. 실전에서 유용한 SQL 문법 (문자열, Case)

1) 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오기

// 이메일에서 아이디만  select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users // 이메일에서 도메인만 select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

2)orders 테이블에서 created_at을 날짜까지만 출력

-SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)

// orders 테이블에서 날짜까지 출력 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

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

-10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시

select pu.point_user_id, pu.point, case  when pu.point > 10000 then '잘 하고 있어요!' else '조금 더 달려주세요!' END as '구분' from point_users pu;

---

6. 퀴즈풀어보기

1) 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시 (CASE 문법 사용, CASE 안에서 Subquery로 평균을 계산하여 비교)

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

2) 이메일 도메인별 유저의 수 세기 (SUBSTRING_INDEX와 Group by를 잘 사용)

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

3) '화이팅'이 포함된 오늘의 다짐만 출력

select * from checkins c where c.comment like '%화이팅%'

4) 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력 (subquery 두 개를 만들어놓고, inner join! 살펴볼 테이블: enrolled_details done_cnt는 들은 강의의 수(done=1), total_cnt는 전체 강의의 수)

with lecture_done as ( select enrolled_id, count(*) as cnt_done from enrolleds_detail ed  where done = 1 group by enrolled_id ), lecture_total as ( select enrolled_id, count(*) as cnt_total from enrolleds_detail ed  group by enrolled_id ) select a.enrolled_id, a.cnt_done, b.cnt_total from lecture_done a inner join lecture_total b on a.enrolled_id = b.enrolled_id

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