[항해99_엑셀보다 쉬운 SQL] 4주차 강의 TIL

지난주차까지는 가뿐했다. 이번주차 강의는 서브쿼리가 등장해서 머리를 꽤나 아프게 했다.

서브쿼리(Sub query) :

쿼리문 안에서 서브로 들어가는 쿼리문. where절, select절, from절에 들어가서 테이블, 필드명을 대신해서 사용할 수 있게한다. (order by 사용 불가)

 

서브쿼리를 사용할 때 가장 중요한건 어떻게 그룹화하고 어떻게 정보를 찾을 것인가이다. 나같은 경우 최대한 문제의 문장을 영어 끊어 읽으려고 노력한다. 

예를 들면 '전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기' 라면 전체유저의 포인트의 평균/ (보다) 큰 유저들의 데이터 추출하기 이렇게... 간단하고 당연하지만 저렇게 하면 일의 순서가 약간은 정리되는 느낌이 든다...난 약간 정신없고 성격이 급해서 차근차근 하지 않으면 다 섞여버려서 되는것도 안된다... 솔직히 이걸 서브쿼리로 작성해야겠다라는 생각이 들게하는건 SQL을 많이 풀어봐야 느는 감각이다... 아니면 천재던가;

 

 

Where절 subquery : where 필드명 in (subquery) _ 일반 서브 쿼리

서브쿼리의 결과를 조건으로 활용한다. (like 변수와 상수)

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

  방법1 전체 유저의 포인트 평균을 구하는 쿼리 작성

SELECT round(avg(point)) as user_avg from point_users pu

  방법2 평균보다 큰 유저를 검색하는 쿼리 작성

SELECT * from point_users pu
where pu.point > (
				  SELECT round(avg(point)) as user_avg 
				    FROM point_users pu
				 )

 

 

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

   방법1 이씨 성을 가진 유저의 포인트 평균을 구하는 쿼리 작성

SELECT round(avg(point)) as user_avg from point_users pu
inner join users u on pu.user_id = u.user_id
WHERE u.name like '이%'

   

  방법2 평균보다 큰 유저들의 데이터 추출하는 쿼리 작성

SELECT * from point_users pu
where pu.point > (
				  SELECT round(avg(point)) as user_avg from point_users pu
		      inner join users u on pu.user_id = u.user_id
                   WHERE u.name like '이%'
				 )

 

 

---

select절 subquery: select 필드명, 필드명, suquery from ~  _스칼라 서브쿼리

필드명처럼 사용이 가능. 기존 테이블과 함께 보고 싶은 통계 데이터를 손쉽게 붙이는데에 사용(like 컬럼)

select 절에 서브쿼리가 들어가면, 무조건 하나의 레코드만 리턴. 두 개 이상 리턴 불가.

 

문제1 checkins 테이블에 couse_id별 평균 likes수 필드 우측에 붙여보기

   방법1 course_id별 평균 likes수를 구하는 쿼리 작성

SELECT c.course_id, avg(likes) as course_avg from checkins c
group by c.course_id

 

   방법2-1 checkins 테이블에 left join 이용하여 붙인다.

SELECT c1.checkin_id, c1.course_id, c1.user_id, c1.likes, c2.course_avg from checkins c1 
left join (
		   SELECT c.course_id, round(avg(likes),1) as course_avg 
		     from checkins c
		 group by c.course_id
          ) c2
on c1.course_id = c2.course_id

 

   방법2-2 checkins 테이블 구하는 쿼리에 select 문에다가 그냥 방법1에서 만든 쿼리를 넣어도 된다. -> 더 심플!

SELECT checkin_id, course_id, user_id, likes, (
SELECT c.course_id, avg(likes) as course_avg 
from checkins c
group by c.course_id
)
from checkins

 

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

   방법1 과목명별 평균 likes구하는 쿼리 작성

select round(avg(likes),1) as course_avg
from checkins c 
where c.course_id = c1.course_id

 

  방법2 checkins 테이블에 붙이기

     말은 간단하지만, 여기서 주목할 점은 

     1. select절에 서브쿼리문이 들어가면서 서브쿼리문 안에 있던 as가 괄호밖으로 빠져나왔다는 점이고, 

     2. 서브쿼리문에서 where절 내부couse_id와 외부 course_id를 동일하다면~ 이라는 조건을 달았다는 점이다. 

select c1.checkin_id,
	   c3.title,
       c1.user_id,
       c1.likes,
       (
       	select round(avg(likes),1) 
       	  from checkins c 
       	 where c.course_id = c1.course_id
       ) as course_avg 
from checkins c1
inner join courses c3
on c1.course_id =c3.course_id

 

---

from절 subquery _ 인라인뷰 => 무조건 AS 별칭 사용

내가 만든  서브쿼리문과 서브쿼리문 혹은 다른 테이블과 조인하고 싶을 때 주로 사용. 서브쿼리를 이용해서 원래 있었던 테이블마냥 사용이 가능. (like 테이블)

 

문제1 course_id별 유저의 체크인 개수 구하기

   방법

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

 

문제2 course_id별 인원 구하기

   방법

select course_id,  count(DISTINCT user_id) as cnt_total from orders o 
group by course_id

 

문제3 course_id별 check in 개수에 전체 인원을 붙이기

   방법

select c1.course_id, c1.cnt_checkins, o1.cnt_total, round((c1.cnt_checkins/o1.cnt_total),2) as ratio
  from (select c.course_id,  count(DISTINCT c.user_id) as cnt_checkins from checkins c
        group by c.course_id) as c1
inner join 
       (select o.course_id, count(DISTINCT o.user_id) as cnt_total from orders o 
        group by o.course_id) as o1 
on c1.course_id = o1.course_id

 

( + ) 문제4 비율과 강의 제목도 넣기  

   방법

select c2.title, c1.course_id, c1.cnt_checkins, o1.cnt_total, round((c1.cnt_checkins/o1.cnt_total),2) as ratio
  from (select c.course_id,  count(DISTINCT c.user_id) as cnt_checkins from checkins c
        group by c.course_id) as c1
inner join 
       (select o.course_id, count(DISTINCT o.user_id) as cnt_total from orders o 
        group by o.course_id) as o1 
on c1.course_id = o1.course_id
inner join courses c2 on c1.course_id = c2.course_id

 

( + ) 위에서 from절에서는 무조건 alias를 사용해야한다고 언급했는데 안그럼 아래와 같은 오류가 뜬다. 

SQL Error [1248] [42000]: Every derived table must have its own alias

 

 

 

---

with : 서브쿼리의 가독성 높이기!

with table1 as ( 서브쿼리 ), table2 as ( 서브쿼리 ), ...

코드의 맨 윗단에 작성하기.

select c2.title, c1.course_id, c1.cnt_checkins, o1.cnt_total, round((c1.cnt_checkins/o1.cnt_total),2) as ratio
  from (select c.course_id,  count(DISTINCT c.user_id) as cnt_checkins from checkins c
        group by c.course_id) as c1
inner join 
       (select o.course_id, count(DISTINCT o.user_id) as cnt_total from orders o 
        group by o.course_id) as o1 
on c1.course_id = o1.course_id
inner join courses c2 on c1.course_id = c2.course_id

위와 같은 코드를 아래와 같이 별칭 주듯 가독성을 높일 수 있다.

 

with table1 as (select c.course_id, count(DISTINCT c.user_id) as cnt_checkins from checkins c group by c.course_id 
), table2 as (select o.course_id, count(DISTINCT o.user_id) as cnt_total from orders o group by o.course_id)
select c1.course_id, c1.cnt_checkins, o1.cnt_total, round((c1.cnt_checkins/o1.cnt_total),2) as ratio
  from table1 as c1
inner join 
       table2 as o1 
on c1.course_id = o1.course_id

 

 

 

---

 

문자 쪼개기 substring_index ( 컬럼명, 쪼갤 기준(문자, 숫자), 몇번째 자리까지 보여줄까?인덱스)

문자 일부 출력 substring ( 컬럼명, 시작 인덱스, 몇번째 자리까지 보여줄까? 인덱스)

 

여기서는 인덱스가 1부터 시작한다. 

맨 마지막 문자를 보고싶으면 -1을 하면 된다.

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

 

 

---

 

조건문 case when A then B else C end : A조건이 true면 B실행, false면 C실행.

 

SELECT p.point_user_id, p.point,
case when p.point > (select round(avg(pu.point)) as avg_point from point_users pu) then '잘 하고 있어요'
	 else '열심히 합시다' end as msg
from point_users p