220312. 엑셀보다 쉬운 SQL 4주차 - Subquery, with절, 유용한 SQL문법(문자열, Case)

 

Subquery 

하나의 SQL 쿼리 안에 또다른 SQL쿼리가 있는 것. 

 

 

kakaopay로 결제한 유저들의 정보 보기

• 3주차에서 배운대로 'inner join'으로 데이터 가져오기

 

• subquery 를 이용해서 데이터 가져오기

 

 

---

 

 

자주 쓰이는 Subquery 유형

• Where 에 들어가는 Subquery  : where 필드명 in (subquery)

(1) from 실행: users 데이터를 가져와줌

(2) Subquery 실행: 해당되는 user_id의 명단을 뽑아줌

(3) where .. in 절에서 subquery의 결과에 해당되는 'user_id의 명단' 조건으로 필터링 해줌

(4) 조건에 맞는 결과 출력

 

• Select 에 들어가는 Subquery : select 필드명, 필드명, (subquery) from ..

key인 user_id가 나올때마다 subquery가 실행되는 거!

(1) 밖의 select * from 문에서 데이터를 한줄한줄 출력하는 과정에서

(2) select 안의 subquery가 매 데이터 한줄마다 실행되는데

(3) 그 데이터 한 줄의 user_id를 갖는 데이터의 평균 좋아요 값을 subquery에서 계산해서

(4) 함께 출력해준다!

 

• From 에 들어가는 Subquery (가장 많이 사용되는 유형!) : 내가 만든 Select와 이미 있는 테이블을 Join할 때 사용

내가 만든 select문을 마치 원래 있던 테이블인마냥 갖다붙임!

 

---

 

Subquery 연습해보기

• Where 절에 들어가는 Subquery 연습해보기
  • [연습] 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

실시간으로 바뀌는 데이터(평균값)와 비교해야하니까 subquery를 이용하는 것

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

inner join으로 풀어보기

subquery로 풀어보기

 

• Select 절에 들어가는 Subquery 연습해보기
  • [연습] checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
  

• [연습] checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기

inner join으로 붙은거 잘 됐는지 확인하려면 c3.*

 

• From 절에 들어가는 Subquery 연습해보기
  • [준비1] course_id별 유저의 체크인 개수를 구해보기!

체크인을 한번이라도 한 유저! -> 중복이 없어야하므로 distinct 사용

 

• [준비2] course_id별 인원을 구해보기!

 

• [진짜 하고 싶은 것] course_id별 like 개수에 전체 인원을 붙이기

 

• [한 걸음 더] 퍼센트를 나타내기

 

• [반 걸음 더] 앗, 강의 제목도 나타나면 좋겠네요!

 

from에 들어갈 테이블들을 먼저 설계!!

 

 

with절

from안에 있는 subquery를 쓸 때, with절을 이용해서 쿼리문을 깔끔하게 정리할 수 있음

 

 

---

 

 

실전에서 유용한 SQL 문법 (문자열)

실제 업무에서 문자열 데이터를 원하는 형태로 쪼개고, 변형시켜서 사용할 일이 더 많음

 

substring_index 문자열 쪼개보기

@기준으로 쪼개서 그 중 첫번째 조각인 email ID만 추출

@기준으로 쪼개서 email 도메인만 추출

 

 

substring 문자열 일부만 출력하기

created_at 필드의 1번째 문자열부터 10번째 문자열까지만 추출

이를 활용하여 데이터 가공한 예제

 

 

---

 

실전에서 유용한 SQL 문법 (Case)

특정한 조건에 따라 데이터를 구분해서 원하는 값을 새 필드에 출력하기

포인트 보유액에 따라 다르게 표시해주기

구분을 더 나눠주고

방금 짠 쿼리문을 subquery로 만들어서 group by로 통계를 낼 수 있음

with절을 활용해서 깔끔하게 정리까지!

 

---

 

SQL 문법 복습. 또 복습! (초급)

• [퀴즈] 평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!

 

• [퀴즈] 이메일 도메인별 유저의 수 세어보기

내 답

subquery 이용한 답

 

• [퀴즈] '화이팅'이 포함된 오늘의 다짐만 출력해보기

 

---

 

SQL 문법 복습. 또 복습! (중급)

• [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

내 답 subquery 활용

with절 활용하는 또다른 답

 

• [퀴즈] 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기

 

• [함께] 그러나, 더 간단하게 만들 수 있지 않을까!

done이 1 or 0 이기 때문에... 맙소사..! 간단히 가능한 문제였어!