내배단 SQL 4주차 개발일지

01. 오늘 배울 것

1) Subquery: 원하는 데이터를 더 쉽게 얻어보기

Subquery란? 쿼리 안의 쿼리라는 의미

하위 쿼리의 결과를 상위 쿼리에서 사용하면, SQL 쿼리가 훨씬 간단해져요!

• 즉, Subquery를 사용하지 않아도 원하는 데이터를 얻어낼 수 있겠지만, 더 편하고 간단하게 원하는 데이터를 얻기 위해 사용되는 파워풀한 기능입니다.
• Subquery에 대한 이해도가 생기면, With구문을 이용해서 더 간단하게 만들어볼게요!

2) 실전에서 유용한 SQL 문법들

생각보다 실무에서의 데이터는 지저분하고 복잡합니다.

주어진 데이터를 원하는 유의미한 정보로 만들기 위해서는 이것저것 해야할 일이 많습니다.

문자열을 한 번에 정리한다든지, 조건에 따라 데이터를 구분한다든지요.

실무 속 날것의 데이터에서도 원하는 데이터를 뽑아낼 수 있는 유용한 기능을 배워봅니다!

 

 

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

기존

Subquery

Where 에 들어가는 Subquery

Where은 조건문이죠?

Subquery의 결과를 조건에 활용하는 방식으로 유용하게 사용합니다.

where 필드명 in (subquery) 이런 방식으로요!

 

카카오페이로 결제한 주문건 유저들만, 유저 테이블에서 출력해주고 싶을 때

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

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

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

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

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

 

Select 에 들어가는 Subquery

Select는 결과를 출력해주는 부분이죠?

기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용합니다.

select 필드명, 필드명, (subquery) from .. 이렇게요!

'오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금할 수 있겠죠?

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

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

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

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

(4) 함께 출력해준다!

 

From 에 들어가는 Subquery (가장 많이 사용되는 유형!)

From은 언제 사용하면 좋을까요?

내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용하면 딱이겠죠!

유저 별 좋아요 평균 -> 해당 유저 별 포인트를 보고 싶다면?

쿼리가 실행되는 순서를 이렇게 상상하면 편해요!

(1) 먼저 서브쿼리의 select가 실행되고,

(2) 이것을 테이블처럼 여기고 밖의 select가 실행!

 

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

Where 절에 들어가는 Subquery 연습해보기

전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

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

 

Select 절에 들어가는 Subquery 연습해보기

checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기

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

From 절에 들어가는 Subquery 연습해보기

course_id별 유저의 체크인 개수를 구해보기!

course_id별 인원을 구해보기!

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

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

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

 

04. with절 연습하기

with 절 사용

 

 

05. 실전에서 유용한 SQL 문법 (문자열, Case)

문자열 데이터 다뤄보기

이메일에서 아이디만 가져와보기 (@를 기준으로 텍스트를 쪼개고, 그 중 첫 번째 조각을 가져오라는 뜻!)

이메일에서 이메일 도메인만 가져와보기 (@를 기준으로 텍스트를 쪼개고, 그 중 마지막 조각을 가져오라는 뜻!)

 

문자열 일부만 출력하기

orders 테이블에서 날짜까지 출력하게 해보기 (SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지))

일별로 몇 개씩 주문이 일어났는지 살펴보기

CASE: 경우에 따라 원하는 값을 새 필드에 출력해보기

포인트 보유액에 따라 다르게 표시해주기 (10000점보다 높은 포인트를 가지고 있으면 '잘 하고 있어요!', 평균보다 낮으면 '조금 더 달려주세요!' 라고 표시)

구간별 MSG 표시

group by를 통한 구간별 카운트

with 절로 묶기

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

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

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

'화이팅'이 포함된 오늘의 다짐만 출력해보기

 

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

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

[힌트!] subquery 두 개를 만들어놓고, inner join!

살펴볼 테이블: enrolled_details

done_cnt는 들은 강의의 수(done=1),

total_cnt는 전체 강의의 수

with절로 정리 - 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기

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

그러나, 더 간단하게 만들 수 있지 않을까!