[개발일지] SQL 입문_4주차-2

1. FROM 절에 Subquery 활용하기

⁕잊지말자! FROM은 테이블을 불러오고, SELECT는 필드를 정한다

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(distinct(user_id)) 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

 

2. WITH절의 활용

⁕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(distinct(user_id)) 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

 

3. substring_index: 문자열만 추출하기

  3-1) Email에서 아이디만 추출

SELECT user_id, email, substring_index(email,'@',1) FROM users u

 

  3-1) Email에서 도메인만 추출

SELECT user_id, email, substring_index(email,'@',-1) FROM users u

 

4. substring: 추출 범위 지정하기

⁕substring(필드,잘라낼 글자 순서,잘라낼 글자 기준으로 종료될 순서)

'yyyy-mm-dd'만 추출하고 싶으면

SELECT substring(created_at,1,10) as date, count(*) FROM orders
group by date

 

'hh:mm:ss'만 추출하고 싶으면

SELECT substring(created_at,12,8) as date, count(*) FROM orders
group by date

 

5. CASE(when, then, else, end)

⁕다양한 case를 연결할때는 when + then으로! 그 외 else 그리고 end!

⁕1번 with 활용 / 2번 with 미활용 비교해서 봐보자!

  5-1) 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(*) FROM table1 a
      group by a.lv

 

  5-2) with 미활용
      SELECT a.lv, count(*) 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