SQL 4주차 단어/문법 정리

 

 

 

Subquery

: '쿼리 안의 쿼리'

 

+) 하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 간단해진다.

    = Subquery를 사용하지 않아도 결과값을 얻어낼 수 있지만,

       더욱 편하고 간단하게 데이터를 얻을 수 있는 기능이다.

 

 

 

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

+) 앞서 배웠던 inner join 방식

 

 

Subquery

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

+) orders 테이블의 결제수단이 kakaopay인 user_id 필드만 출력하는 쿼리문을

    users 테이블의 user_id 필드에 in을 적용해 inner join 없이도 kakaopay로 결제한 유저들의 정보를 조회할 수 있다.

 

    orders 테이블의 쿼리문1 속의 users 테이블의 쿼리문2 = Subquery, 쿼리 속의 쿼리.

 

 

---

- Subquery 사용해보기 -

 

 

 

Subquery

- Where

: Subquery의 결과를 조건에 활용하는 방식으로 사용

 

select * from 테이블명 Alias
where Alias.필드명 in (select Alias2.필드명 from 테이블명2 Alias2
		      where 조건문)

+) where .. in절은 Subquery의 결과에 해당되는 필드의 조건으로 필터링 해준다.

 

 

 

 - Gmail을 사용하는 유저들의 포인트를 조회하기 -

 

SELECT pu.user_id, pu.point from point_users pu
where pu.user_id in (SELECT u.user_id from users u
		     where u.email like '%gmail.com')

 

 

---

 

 

Subquery

- Select

: 기존 테이블에 보고싶은 통계 데이터를 붙이는 데 사용한다.

 

select 필드명, 필드명, (Subquery) from 테이블명

 

 

 

 - 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
where c.user_id = '4b8a10e6'

+) 첫 번째 쿼리문의 select .. from 문이 실행될 때 필드 데이터를 출력하는 과정에서

    Subquery도 순차대로 계산되어 subquery의 결과값을 첫 번째 쿼리문에서 함께 출력해준다. 

 

 

 

> 결과값

 

 

---

 

 

Subquery

- From

: Subquery와 기존 테이블을 join 할 때 사용한다

 

select Alias.필드명, Alias2.필드명 from 테이블명 Alias
inner join (Subquery) Alias2
on Alias.Key = Alias2.Key

+) 첫 번째 쿼리문에서 출력할 Subquery의 필드나 join의 key로 사용할 필드는

    Subquery의 출력값으로 먼저 나와야 오류가 발생하지 않는다.

 

    = 이미 Subquery의 select .. from문으로 출력된 값을 필드로 이룬 테이블을 만들어 낸 것이기 때문에

 

 

 

 - 유저별 좋아요의 평균과 포인트를 조회하기 -

 

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

 

 

 

---

- 이 외 유용한 문법 -

 

 

쿼리문의 정리

- With

: 테이블을 선언한다. Alias로 Subquery를 붙여준다.

 

+) From에 사용하는 Subquery가 길어질수록 join 안쪽이 헷갈리기 때문에

    1) With로 임의의 테이블명을 지은 뒤

    2) Alias로 Subquery를 붙여 테이블로 만들어 둔 후

    3) 쿼리문에서 임의의 테이블명에 Alias를 붙이는 방법으로

        쿼리의 길이를 줄여준다.

 

with 임의테이블명 as (
   Subquery
), 임의테이블명2 as (
   Subquery2
)
select * from 임의테이블명 Alias
inner join 임의테이블명2 Alias2
on Alias.key = Alias2.key

 

 

 - 강의별 like 개수와 전체 인원, 비율을 구하기 -

 

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

 

 

---

 

문자열 정리 - 쪼개기

- SUBSTRING_INDEX

: 문자열을 특정 조건 기준으로 쪼개 출력할 범위를 정해준다

 

select SUBSTRING_INDEX(필드명, '기준', 1) from 테이블명

+) 괄호 속 들어가는 3번째 조건은 문자열을 쪼갠 '기준' 을 기준으로 어떤 문자열을 출력할 지 정한다.

    앞 문자열은 1 / 뒷 문자열은 -1 이다.

 

 

 - 이메일에서 아이디만 가져오기 -

 

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

 

 

---

 

문자열 정리 - 일부 출력

- SUBSTRING

: 문자열을 특정 조건 기준으로 출력할 범위를 정해준다

 

select substring(필드명, 첫 글자의 위치, 출력할 글자의 수) from 테이블명

+) 괄호 속에 있는 '첫 글자의 위치'는 출력할 글자가 기존 데이터 문자열에 몇 번째에 위치해 있는지 적으면 된다.

    '출력할 글자의 수'는 첫 글자의 위치와 상관없이 몇 글자를 출력할 것인지 적어주면 된다.

 

 

 

 - Orders 테이블에서 created_at의 날짜까지만 출력하기 -

 

select order_no, created_at, substring(created_at,1,10) as date from orders

 

 

---

 

새 필드에 조건별로 출력하기

- Case   // when, then, else, END

: 문자열을 특정 조건 기준으로 출력할 범위를 정해준다.

 

select 필드명
case
when 조건 then '멘트'
else '멘트2'
END as Alias
from 테이블명

 

 

 

 - 포인트 보유액에 따라 다르게 표시해주기 -

 

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

 

 

 

---

- 복습하기 -

 

 

 

 [ Subquery / Where ]

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

 

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

 

 

 

 [ Subquery / Where ]

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

 

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

 

 

 

 [ Subquery / Select ]

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

 

select checkin_id, 
   course_id, 
   user_id, 
   likes, 
   round((select avg(c2.likes) from checkins c2
   where c.course_id = c2.course_id),1) as cnt
from checkins c

 

 

 

 [ Subquery / Select ]

 - 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;

 

 

 

 [ Subquery / Select ]

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

 

select pu.point_user_id, pu.point,
case 
when pu.point >= 10000 then '1만 이상'
when pu.point >= 5000 then '5천 이상'
else '5천 미만'
END as level
from point_users pu

 

 

 

 [ Subquery / With ]

 - 윗 문제에 Subquery와 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 level
	from point_users pu
)
select level, count(*) as cnt from table1
group by level

 

 

 

 [ Subquery / With ]

 - 윗 문제에 Subquery와 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 level
	from point_users pu
)
select level, count(*) as cnt from table1
group by level