엑셀보다 쉬운 SQL - With절과 유용한 문법들 -

With절

with 테이블명 as(서브쿼리)

from절에 들어가는 서브쿼리를 보기 좋고 간편하게 쓸 수 있는 방법 (반드시 제일 윗단에 작성)

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( //서브쿼리가 table1이라는 임시 테이블로 생성된 것이다.
	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

실전에서 유용한 SQL문법

문자열 쪼개기 - SUBSTRING_INDEX 문법 사용

이메일 주소에서 @을 기준으로 앞뒤 문자열 가져오기

select user_id, email, SUBSTRING_INDEX(email,'@',1) //@을 기준으로 나눴을 때, 첫번째
select user_id, email, SUBSTRING_INDEX(email,'@',-1) //마지막(도메인)

 

문자열 일부만 출력

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

 

경우에 따라 원하는 값을 새 필드에 출력하기 CASE WHEN 조건식 then 동작 ELSE  END

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

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

서브쿼리를 이용해 임의의 테이블로 만들고 group by로 통계를 낼 수 있다. (실전용)

select a.lv, count(*) 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 lv
	from point_users pu
) a
group by a.lv

with 활용

with table1 as (
	select pu.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 a.lv, count(*) as cnt from table1 a
group by a.lv

문제

1. 평균 이상 포인트를 가지고 있으면 '잘하고 있어요' / 낮으면 '열심히 합시다!' 표시

CASE안에서 서브쿼리로 평균 계산

select pu.user_id, 
	   pu.point,
	   (CASE when pu.point > (select avg(point) from point_users pu)
	    then '잘 하고 있어요'
	    else '열심히 합시다' end
	   ) as a
from point_users pu

2. 이메일 도메인별 유저의 수 세어보기

//내가 푼 방식
select SUBSTRING_INDEX(email,'@',-1) as domain, count(*) as user_cnt 
from users
group by domain

//서브 쿼리를 from절에 테이블처럼 사용
select domain, count(*) as cnt from (
	select SUBSTRING_INDEX(email,'@',-1) as domain from users
) a 
group by domain

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

SELECT comment from checkins c
where comment LIKE '%화이팅%'

4. 수강등록정보(enrolled_id) 별 전체 강의 수와 들은 강의의 수 출력

done_cnt - 들은 강의의 수(done=1),

total_cnt - 전체 강의의 수

select a.enrolled_id, a.done_cnt, b.total_cnt from (
	select enrolled_id,count(*) as done_cnt from enrolleds_detail
	where done = 1
	group by enrolled_id 
) a
inner join (
	select enrolled_id,count(*) as total_cnt from enrolleds_detail
	group by enrolled_id 
) b on a.enrolled_id = b.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 
from table1 a
inner join table2 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

더 간단하게 만들기 sum() 활용

select enrolled_id,
       sum(done) as done_cnt,
       count(*) as total_cnt,
       round(sum(done)/count(*),2) as ratio
from enrolleds_detail ed
group by enrolled_id