엑셀보다 쉬운, SQL

엑셀보다 쉬운, SQL 4주차

Won's log 2023. 5. 2. 11:08

 

오늘은 SQL강의 마지막 주차인

4주차 강의를 들었다

 

4주차에는 쿼리문을 복잡하게 쓸 수 있는

Subquery,

subquery를 간결하게 보이게 하는 with 부문

현실 데이터를 문자열로 가공하는 법

case when 조건문에

대해서 배웠다

 

Subquery는

큰 쿼리문 안에 들어가는 쿼리문

순서:

가장 안에 있는 서브쿼리 -> 결과 -> 

그 다음 쿼리 -> 결과

 

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'

Subquery로 쓰면...
select user_id , name, email from users u 
where user_id in (
	select user_id from orders o 
	where payment_method = 'kakaopay'

두 쿼리 모두 동일한 결과를 추출한다
 

 

Where 절 + Subquery

select user_id , name, email from users u 
where user_id in (
	select user_id from orders o 
	where payment_method = 'kakaopay'
 

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

*평균이 바뀔 수 있으므로 썹쿼리 사용

SELECT * from point_users pu 
where point > (select round(avg(point),-1) from point_users pu
)
 

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

SELECT * from point_users pu 
where point >  ( select AVG(pu.point) from point_users pu
inner join users u
on pu.user_id = u.user_id 
where u.name ='이**'
)

다른 답변
SELECT * from point_users pu 
where point >  (select AVG(pu.point) from point_users pu
where User_id in (select user_id from users where name = '이**')
)
 

 

Select + Subquery

select 안에 subquery가 들어간다

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

오답
select avg(likes) from checkins c
where course_id = '5f0ae408765dae0006002817'

정답
SELECT c.checkin_id , 
		c.course_id , 
		c.user_id , 
		c.likes,
		(
		select avg(likes) from checkins
		where course_id = c.course_id 
		) as course_avg
		from checkins c 

Subquery는 응용해서 사용할 때 요긴한 쿼리인 것 같음
 

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

오답
SELECT c.checkin_id , 
		c.course_id , 
		(select c1.title from courses c1 
inner join checkins c
on c1.course_id = c.course_id
),
		c.user_id , 
		c.likes,
		(
		select avg(likes) from checkins
		where course_id = c.course_id 
		) as course_avg
		from checkins c 

정답
SELECT c.checkin_id , 
		c.course_id , 
		c1.title,
		c.user_id , 
		c.likes,
		(
		select round(avg(likes),1) from checkins
		where course_id = c.course_id 
		) as course_avg
		from checkins c 
inner join courses c1
on c1.course_id = c.course_id

inner join은 뒤에 붙여야 하는구나!
 

 

From + Subquery

가장 많이 사용되는 쿼리

SELECT pu.user_id, pu.point, a.avg_likes from point_users pu 
inner 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
 

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

오답
SELECT DISTINCT(*() from checkins
group by course_id

정답
SELECT course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id

distinct는 중복제거!
 

course_id별 인원을 구해보기!

SELECT course_id, count(*) as cnt_total from orders o 
group by course_id
 

course_id별 checkin개수에 전체 인원을 붙이기

오답
SELECT c.course_id, count(distinct(user_id)) as cnt_checkins from checkins c
group by c.course_id
inner join orders o
on o.course_id = c.course_id 

정답
SELECT  a.course_id, a.cnt_checkins, b.cnt_total 
		from (
				SELECT course_id, count(distinct(user_id)) as cnt_checkins from checkins c
				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

from query를 자유자재로 구사하려면
쿼리와 테이블의 정보를 명확히 알고 익숙해져야 한다
 

퍼센트, 강의제목을 나타내기

SELECT  c.title, a.course_id, 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
				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 c 
		on c.course_id = a.course_id
 
 

 

 

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

문자열 SUBSTRING_INDEX 문법

문자열 쪼개기

select user_id, email, SUBSTRING_INDEX(email, '@', 1) from users

select user_id, email, SUBSTRING_INDEX(email, '@', -1) from users

문자열에서 일부만 출력하기

SELECT order_no, created_at, SUBSTRING(created_at, 1, 10) as date from orders

// 1 시작포인트, 10번째 문자 가져오기

 

조건문 CASE문

경우에 따라 원하는 값을 필드에 출력

SELECT pu.user_id, pu.point, 
	   (case when pu.point > 10000 then '잘 하고 있어요!'
	   else '조금만 더 파이팅!' end) as msg
from point_users pu
 
  • Subquery를 사용하면 통계를 낼 수 있다!
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(*) as cnt from table1 a
group by a.lv
 

[연습문제]

 

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

오답
select pu.point_user_id , pu.point ,
	(case when point>avg(point) then '잘 하고 있어요'
			 else '열심히 합시다!' end) as msg
from point_users pu 

정답
select pu.user_id , pu.point ,
	(case when pu.point> (select avg(pu.point) from point_users pu)
	  		 then '잘 하고 있어요'
			 else '열심히 합시다!' end) as msg
from point_users pu 

비교대상(평균)을 Subquery로 사용한 쿼리문
 

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

오답
select email from users
group by SUBSTRING_INDEX((email,'@', 1)

정답
SELECT domain, count(*) as cnt from (
		SELECT SUBSTRING_INDEX(email,'@', -1) as domain from users 
) a
group by domain 

subquery와 substring_index를 응용해서 사용한 쿼리문
 

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

SELECT * from checkins c 
WHERE comment like '%화이팅%'
 

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

내용을 입력하세요.
 

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

오답
SELECT ed.enrolled_id, count(done=1) from enrolleds_detail ed 
group by ed.enrolled_id 
select ed.enrolled_id, count(*) from enrolleds_detail ed 
group by ed.enrolled_id

정답
ith table1 as (
		SELECT enrolled_id , count(*) as done_cnt from enrolleds_detail ed 
		where done = 1
		group by enrolled_id), 
		table2 as (
		SELECT enrolled_id , count(*) as total_cnt from enrolleds_detail ed 
		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 쿼리문 2개 -> inner join -> with
 

[4주차 느낀점]

써브쿼리는 사용할 수 있는 방법이 무궁무진하기 때문에

쿼리와 테이블간의 관계성을 논리적으로 이해하고

익숙해져야 겠다

직접 공부하고

무조건 반복하는 것이 답이다!

 

[SQL 완강 느낀점]

드디어 4주차를 완강했다

느낀 장점과 단점을 나누어서

설명하려 한다

 

<장점>

1. 짧은 시간 컴팩트하게 이해가능

(강의당 1시간 안팎)

2. 이론 먼저 배우고 예시 반복으로

집중해서 sql을 어떻게 사용해야하는지 익숙해짐

3. 온라인 강의

언제 어디서든 수강 가능

4.평생 소장

80%이상 수강 시, 평생 소장

재복습 가능

5.강의 노트 제공

PDF, Notion으로 제공

포인트가 잘 담겨있음

6.국비 지원

완강/80% 완강하면 환급되는 걸로 알고 있음

 

<단점>

큰 불편함 없었음

 

주변 추천 의사 있음 ㅇㅅㅇ