부트캠프 개발일지(SQL 4주차)

부트캠프 개발일지 - SQL4주차(23.05.12)

 

• SubQuery : 쿼리 안에 들어가는 모든 쿼리문을 지칭(select/from/where절 모두 들어가기 가능), subquery내에 또 다른 subquery를 넣을 수 있음, 한 눈에 보기 좋게 잘 짜는게 핵심!, 실행 순서로는 가장 안의 subquery를 실행 후 결과를 가지고 점차 바깥의 쿼리문을 실행하는 순서

* subquery(where절) 예제) 

// 결제 수단이 'kakaopay'인 user_id, name, email 출력

SELECT user_id, name, email

   FROM users u

WHERE user_id in (     // user_id 중 ()에 해당되는 user_id

              select user_id FROM orders o

              where payment_method = 'kakaopay' 

)

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

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

 

SELECT * FROM point_users pu

where point > (

                         SELECT avg(point) FROM point_users pu

                         inner join users u

                         on pu.user_id = u.user_id

                         where name = '이**'

)

-----------------같은 결과(아래는 subquery 내 subquery 사용)----------------------------------------------

SELECT * FROM point_users pu

where point > (

                         SELECT avg(point) FROM point_users pu

                         where user_id in (

                                                      select user_id from users where name = '이**'

                          )

)

 

* subquery(select절) 예제) 

// '오늘의 다짐' 좋아요 수가 본인이 평소에 받았던 좋아요 수에 비해 높고 낮은지 비교 

SELECT c.checkin_id,

               c.course_id,

               c.user_id ,   //user_id가 나올 때마다 아래 서브 쿼리문을 실행 (ex)1.  4b8a 라는 user_id하나 들어왔다면 2. 그 다음 id가 fac9인 것이 들어왔다

               c.likes,

               (

                        SELECT avg(likes) FROM checkins c2

                        WHERE c2.user_id = c.user_id  //여기에 위에 c.user_id를 붙여주면서 해당 user_id당 like 개수의 평균값을 계산해 출력   => 1.  4b8a 아이디에 해당하는 평균like개수를 구함  2. fac9 아이디에 해당하는 평균like 개수 구함

                ) as avg_likes_user   // checkin_id에 대해서 해당 유저의 평균 like개수

FROM checkins c

* select절 예제2) 

// course_id별 평균 like 수 붙이기(과목명으로 출력)

SELECT c.checkin_id, c2.title, c.user_id, c.likes,

               (

                 select round(avg(likes), 1) FROM checkins

                 where course_id = c.course_id  // 첫 course id 값에 c.coure id의 첫 값이 대응 이것이 계속 반복(같은 값이 들어간다는 의미)

                ) as course_avg

    FROM checkins c

inner join courses c2 on c.course_id = c2.course_id  // c2.title을 출력하기 위한 inner join

 

* subquery(from절) : 가장 많이 사용!! 예제) 

// user_id별 포인트와 좋아요의 평균 수를 파악해보고 싶다.

SELECT pu.user_id, pu.point, a.avg_likes FROM point_users pu

inner join (    //subquery를 이용해 join!

                 SELECT user_id, round(avg(likes), 1) as avg_likes FROM checkins c

                 group by user_id

) a on pu.user_id = a.user_id

* from절 예제2) 

// course_id 별 유저의 체크인 수와 전체 인원 그리고 비율 나타내기(과목명으로 출력)

SELECT c2.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 c // 숫자를 셀 때 중복이 없어야 하므로 count 안에 DISTINCT 를 넣었음(카운트 밖에 DISTINCT를 넣는다면 오류가 나옴 -> 밖에 넣으면 distinct가 적용이 되질 않음)

                 group by course_id

) a

inner JOIN

(

                  SELECT course_id , count(*) as cnt_total FROM orders o

                  group by course_id

) b on a.course_id = b.course_id

inner join courses c2 on a.course_id = c2.course_id

=> from에 들어가는 테이블 먼저 설계 후 ()a, ()b 를 하나의 테이블이라 생각하고 전체 쿼리 작성하기

 

• with 절 : 쿼리문을 좀 더 깔끔하게 정리하기 위해 서브쿼리를 제일 상단에 따로 정의 

* with 절 예(from절 예제 2번을 with 절을 통해 정리)

with table1 as (

                         select course_id, count(DISTINCT(user_id)) as cnt_checkins FROM checkins c

                         group by course_id

), table2 as (

                         SELECT course_id , count(*) as cnt_total FROM orders o

                         group by course_id

)

SELECT c2.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 c2 on a.course_id = c2.course_id

 

• SUBSTRING_INDEX : 문자열을 쪼갤 때 사용(실제 업무에서는 문자열 데이터를 원하는 형태로 한 번 정리해야 하는 경우가 많음), 예제로 SUBSTRING_INDEX(email, '@', -1(뒤 숫자)) =>email을 '@'기준으로 쪼개서 뒤 숫자가 1이면 @앞 문자열 보여달라는 뜻이고 -1이면 뒷 부분 보여달라는 뜻
• SUBSTRING : 문자열 일부만 출력 시 사용, SUBSTRING(필드명, n, m) => 해당 필드의 n에 해당하는 문자부터 m길이의 문자열을 출력(이 때 주의!! 문자열의 시작은 1이고 만약 n에 0을 넣으면 아무것도 출력이 안됨 ex) 문자열이 somemen12라면 SUBSTRING('somemen12' 1, 3) 하면 som이 출력 즉, 시작숫자가 1이다(=s))

* SUBSTRING_INDEX 예제) 

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

SELECT domain, count(*) as cnt_domain from (

                 select SUBSTRING_INDEX(email, '@', -1) as domain FROM users u

) a

group by domain

---------------------------------------같은 결과----------------------------------------------------------

SELECT SUBSTRING_INDEX(email, '@', -1) as domain , count(*) as cnt_domain

   FROM users u

group by SUBSTRING_INDEX(email, '@', -1)

 

• CASE : 조건문이 필요할 때 사용, CASE WHEN 조건식 THEN 실행문장 ELSE 실행문장 END(WHEN 여러 개 사용 가능)

* CASE 예제) 

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

SELECT pu.point_user_id,

               pu.point,

               (case when point > (SELECT avg(point) from point_users) then '잘 하고 있어요!'

                         else '열심히 합시다!' end) as msg

FROM point_users pu

=>평균이상 포인트를 가지고 있으면 잘하고 있어요 낮으면 열심히 합시다 표시

 

* 응용 문제!!!

// 수강등록정보(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

---------------------------------------같은 결과----------------------------------------------------------

SELECT enrolled_id,

               sum(done) as done_cnt,

               count(*) as total_cnt,

               round(sum(done)/count(*), 2) as ratio

    FROM enrolleds_detail

group by enrolled_id

=> 두개의 결과 값은 동일 하나 아래 쿼리문은 done_cnt가 0인것도 출력되므로 결과가 49개가 더 출력된다. 따라서 결과를 완벽하게 똑같이 하고 싶다면

SELECT * from (

                            SELECT enrolled_id,

                                           sum(done) as done_cnt,

                                           count(*) as total_cnt,

                                           round(sum(done)/count(*), 2) as ratio

                               FROM enrolleds_detail

                            group by enrolled_id

) a

where ratio != 0

=> 요렇게 묶으면 된다. (굳이 서브쿼리 여러 개 사용해서 복잡하게 만드는 것보다 마지막 결과처럼 간단하게 만드는 게 더 유용함 - 가끔 멀리서 보면 더 나은 쿼리 작성할 수 있다!)

 

 

- 어떤 부분은 AS명으로 사용이 되는데 또 어떤 부분은 안되는 게 헷갈린다. 한 두 번 정도 다시 보면서 익숙해져야 겠다. 서브쿼리 개념도 두 번정도 반복하다 보니 익숙해졌다.