SQL 일지 - Subquery, 실전에서 유용한 SQL 문법

 

▷ 원하는 데이터를 더 쉽게: Subquery

• Subquery란? 쿼리 안의 쿼리라는 의미입니다.
• 예) kakaopay로 결제한 유저들의 정보 보기
  • 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'
    )
• 서브쿼리가 있는 코드와 없는 코드의 차이점
  • 첫 번째 쿼리문은 테이블을 합친 뒤에 값들을 필터링하여 payment_method가 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'
  • 두 번째 쿼리문은 테이블을 합치기 전 값들을 필터링한 뒤 테이블로 합치는 방식
  • 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'
    )
  • 즉, 첫 번째 쿼리문은 A라는 유저가 a를 구매한 것, A라는 유저가 b를 구매한 것 모두 테이블로 합쳐져서(join되어) A 유저의 데이터가 2번 들어가지만,두 번째 쿼리문에서는 A라는 유저가 카카오페이로 결제한 경우(a, b)가 orders 테이블에 있으므로 A 유저를 하나 테이블에 넣는다 라는 순서로 보시면 됩니다. 이런 차이에서 결과의 차이가 생겨 비슷해 보이는 두개의 코드의 출력되는 값의 갯수가 다르게 나오게 됩니다!

 

▷ 자주 쓰이는 Subquery 유형 알아보기

• Where 에 들어가는 Subquery
  • Where은 조건문이죠? Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다. 👉 where 필드명 in (subquery)
  • 예) 카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때
    • select * from users u
      where u.user_id in (select o.user_id from orders o 
                       where o.payment_method = 'kakaopay')
    • 쿼리가 실행되는 순서 
      1. from 실행: users 데이터를 가져와줌
      2. Subquery 실행: 해당되는 user_id의 명단을 뽑아줌
      3. where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌
      4. 조건에 맞는 결과 출력
• Select 에 들어가는 Subquery
  • Select는 결과를 출력해주는 부분이죠? 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용합니다. 👉 select 필드명, 필드명, (subquery) from ..
  • 예) '오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지
    • 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
    • 쿼리가 실행되는 순서
      1. 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서
      2. select 안의 subquery가 매 데이터 한줄마다 실행되는데
      3. 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서
      4. 함께 출력해준다!
• From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
  • 내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때
  • 예) 포인트와 like의 상관정도를 알아보기
    • 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
    • 쿼리가 실행되는 순서
      1. 먼저 서브쿼리의 select가 실행되고,
      2. 이것을 테이블처럼 여기고 밖의 select가 실행!

 

▷ Subquery 연습해보기 (where, select, from, inner join)

• Where 절에 들어가는 Subquery 연습해보기
  • [연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
    • select * from point_users pu 
      where pu.point > (select avg(pu2.point) from point_users pu2)
  • [오늘의 팁!] 위와 같이, 같은 테이블을 Subquery로 사용할 수도 있어요.
  • [연습] 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
    • 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 안에서 여러 테이블을 Join 할수도 있어요
• Select 절에 들어가는 Subquery 연습해보기
  • [연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
    • select checkin_id, course_id, user_id, likes, 
      (select avg(c2.likes) from checkins c2
      where c.course_id = c2.course_id) 
      from checkins c
  • [연습] 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
• From 절에 들어가는 Subquery 연습해보기
  • [준비1] course_id별 유저의 체크인 개수를 구해보기!
  • [준비2] course_id별 인원을 구해보기!
  • [진짜 하고 싶은 것] course_id별 체크인 개수에 전체 인원을 붙이기
    • select a.course_id, b.cnt_checkins, a.cnt_total from
      (
           select course_id, count(*) as cnt_total from orders
           group by course_id
      ) a
      inner join (
            select course_id, count(distinct(user_id)) as cnt_checkins from checkins
           group by course_id
      ) b
      on a.course_id = b.course_id
  • [한 걸음 더] 퍼센트를 나타내기
  • 전체 중 얼마나 like를 하는지 알아보기
    • select a.course_id, b.cnt_checkins, a.cnt_total, (b.cnt_checkins/a.cnt_total) as ratio from
      (
           select course_id, count(*) as cnt_total from orders
           group by course_id
      ) a
      inner join (
           select course_id, count(distinct(user_id)) as cnt_checkins from checkins
           group by course_id
      ) b
      on a.course_id = b.course_id
  • [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!
    • 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 절로 더 깔끔하게 쿼리문을 정리하기
  • 위의 코드와 같이 계속 서브쿼리가 붙으면, inner join 안쪽이 너무 헷갈리겠죠! → 그 때 쓰는 것이 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(*) 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 문법 (문자열, Case)

• 문자열 데이터 다뤄보기
  • 문자열 쪼개보기
    • 이메일 주소에서 @앞의 아이디만 가져오거나, @뒤의 이메일 도메인을 가져오고 싶어요!  👉 SUBSTRING_INDEX 라는 문법을 사용하면 됩니다.
    • 예) 이메일에서 아이디만 가져와보기
      • select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users
      • @를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!
    • 예) 이메일에서 이메일 도메인만 가져와보기
      • select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users
      • @를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!
  • 문자열 일부만 출력하기
    • orders 테이블에서 created_at을 날짜까지만 출력하게 해봅시다!  👉 SUBSTRING 이라는 문법을 사용하면 됩니다.
    • 예) orders 테이블에서 날짜까지 출력하게 해보기
      • select order_no, created_at, substring(created_at,1,10) as date from orders
      • SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지)
    • 예) 일별로 몇 개씩 주문이 일어났는지 살펴보기
      • select substring(created_at,1,10) as date, count(*) as cnt_date from orders
        group by date
• CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기
  • 10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시해 주려면 어떻게 해야할까요?
    • 포인트 보유액에 따라 다르게 표시해주기
    • select pu.point_user_id, pu.point,
      case 
      when pu.point > 10000 then '잘 하고 있어요!'
      else '조금 더 달려주세요!'
      END as '구분'
      from point_users pu
• CASE: 실전을 위한 트릭!
  1. 우선 몇 가지로 구분을 나누고
  2. 서브쿼리를 이용해서 group by로 통계를 낼 수 있습니다.
  3. with 절과 함께하면 금상첨화죠!
     1. 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
     2. select level, count(*) as cnt from (
           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
        ) a
        group by level
     3. 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