[개발일지] [왕초보] 엑셀보다 쉬운, SQL 4주차 개발일지

1. 학습 요약

• Subquery를 이용하여 복잡한 문제를 해결할 수 있다.
• With 문을 이용하여 코드를 효율적으로 정리할 수 있다.
• Case when 문을 이용하여 조건문을 만들 수 있다.
• Quiz, 숙제 등을 통해 내용 점검하기

2. 노트 정리

1) Subquery 이해하기

• 쿼리문 안에 들어가는 또 다른 쿼리를 말하며 소괄호( )로 묶는다.
• 한번에 결합, 함수, 필요한 필드 추출 등을 해결할 수 있다.
• 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
  table1: ID, name, age, email, points
  table2: ID, method, likes

Where + (Subquery)

• method가 “A”인 유저의 ID, name, email만 추출하자.

select ID, name, email from table1 
 where ID in (
    select ID from table2
    where method = "A"
 )

Select + (Subquery)

• 한 행씩 나올때마다 쿼리가 실행된다.
• table2에 ID에 대한 likes의 평균인 average 열을 추가하자. 이때 평균은 소수점 한자리까지 나타내자.

select t2.ID, t2.method, 
       (
        select round(avg(likes),1) from table2
        where ID = t2.ID 
       ) as average
  from table2 t2

From + (Subquery)

• 가장 많이 사용된다.
• table1에 table2의 ID별 likes 평균 average를 결합하고, ID, name, points, average만 추출하자. 이때 평균은 소수점 한자리까지 나타내자.

select t1.ID, t1.points, t2.average from table1 t1
inner join (
    select ID, round(avg(likes),1) as average from table2
    group by ID
) t2 on t1.ID = t2.ID

2) With 이해하기

• 임시 테이블을 만드는 거라고 생각하면 된다. From + (Subquery)에 쓰기 유용하다.
• With문과 실행 쿼리를 한번에 블록을 씌운 후 실행해야 한다.
• 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
  table1: ID, course_ID, user_ID, course_title, email
  table2: ID, course_ID, user_ID, week, likes
• course별로 강의를 등록한 사람 중 강의를 들은 사람의 비율을 구하자.

with tbl1 as (
    select course_ID, course_title, count(*) as cnt from table1
    group by course_ID
), tbl2 as (
    select course_ID, count(distinct(user_id)) as tot_cnt from table2
    group by course_ID
)

select t1.course_ID, t1.course_title, t1.cnt, t2.tot_cnt, 
       round((cnt/tot_cnt)*100,2) as perc
  from tbl1 t1
inner join tbl2 t2 on t1.course_ID = t2.course_ID

3) 유용한 문법

SUBSTRING_INDEX, 구분값을 기준으로 문자열 쪼개기

• substring_index(필드명, 구분값, 왼쪽(1)/오른쪽(-1))
• 구분값을 기준으로 왼쪽과 오른쪽 문자를 추출할 수 있다.
• email에서 아이디를 추출하자.

  select user_id, substring(email,"@",1) as email_ID from table1
  

SUBSTRING, 문자열 추출하기

• substring(필드명, 처음자리, 끝자리)
• table1: order_no, course_id, user_id, created_at
• 날짜별로 강의 결제 건수를 계산하자.

  select order_no, 
       substring(created_at, 1, 10) as date,
       count(*)
  from table1 
  group by date
  

4) CASE WHEN 조건문 이해하기

• 쉽게 이해할 수 있도록 임의의 테이블, 필드명을 지정하자.
  table1: ID, name, age, email, points
  
• 포인트를 5천, 1만을 기준으로 그룹을 나누자.

select ID, points, 
       case when t1.point < 5000 then '5천 미만'
            when t1.point >= 10000 then '1만 이상'
       else '5천 이상' end as lvl
  from table1

4) 실습(Homework)

실습1. 수강등록정보별 전체 강의 수와 들은 강의 수, 진도율 출력하기
With문을 사용할 것.

with table1 as ( 
	select enrolled_id, count(*) as tot_cnt from enrolleds_detail 
	group by enrolled_id
), table2 as (
	select enrolled_id, count(*) as done_cnt from enrolleds_detail 
	where done = 1
	group by enrolled_id
)

select t1.enrolled_id, 
       t2.done_cnt, 
       t1.tot_cnt ,
       round((t2.done_cnt / t1.tot_cnt * 100),2) as progress
  from table1 t1 
 inner join table2 t2 on t1.enrolled_id = t2.enrolled_id

3. 느낀점

• 계속 따라하다보니 기계적으로 하고 있다는 느낌이 들었다.
  왜 이러한 결과를 내는게 궁금했을까?에 대해 고민하면서 어떤 인사이트를 얻어야할까에 대해서도 많이 고민해야겠다는 생각이 들었다.
• 쿼리가 복잡해지면서 별칭을 어디에 붙이고 어떻게 써야하는지 헷갈릴 때가 많았다. 이 부분을 주의해야겠다.
• 4주까지 배워본 결과 생각보다 엄청 어렵지는 않았던 것 같다. 좀 더 자신감을 가지고 이제는 혼자서 생각하고 쿼리를 짤 수 있도록 연습해야겠다. 너무 좋은 수업이었다.