내일배움단 -개발일지 2주차 (2)

9일차

-오늘 계획 : SQL 1~3주 문제 복습 풀기(완료), SQl 공부

01.Subquery
: 하나의 SQL 쿼리 안에 또다른 SQL쿼리가 있는 것
where, select, from 절에서 유용하게 사용

Ex-1) 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'

inner join을 사용한 쿼리를 Subquery로 표현
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'
)
=>
우선 서브쿼리 안의 kakaopay로 결제한 user_id를 모두 구하고
그 후, user_id가 서브쿼리안에 속한 유저만 골라보도록 함

1-1) Where 필드명 in (subquery)
Subquery의 결과를 조건에 활용하는 방식으로 유용

포인트가 평균보다 많은 사람들의 데이터를 추출
select * from point_users pu 
where point > (
    SELECT avg(point) from point_users pu 
)
-> 굳이 평균값을 적지 않고 Subquery를 넣는 이유?
평균값이 변할 수 있기 때문 

1-2) Select 필드명, 필드명, (subquery) from .. .
=> Select는 결과를 출력해줌, 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용

Ex)
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) 함께 출력
// 직접 해보는 게 이해에 빠름 

** Select Subquery 복습 필수 문제!
문제)
checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
오답)
select checkin_id, course_id, user_id, likes, 
    (select avg(c.likes) from checkins c
    where c.course_id = course_id)
    from checkins 
해답)
select checkin_id, course_id, user_id, likes, 
    (select avg(c.likes) from checkins c
    where c.course_id = c2.course_id)
    from checkins c2

// subquery안의 course_id와 main query의 course_id를 join시켜야 하므로 alias를 꼭 붙여줘야 한다 ! alias를 쓰는 이유,, 일종의 변수 개념이기 때문에 ! + 구분 짓기 위해서 

10일차

1-3) From에 들어가는 Subquery (가장 많이 사용)
보통 내가 만든 select와 이미 있는 테이블을 join하고 싶을 때 사용

Ex) 
select c.title, 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
inner join courses c 
on a.course_id = c.course_id 

02.with 구문

• from안에 들어가는 subquery를 쓸 때 훨씬 간결하게 표현할 수 있다.

Ex) 
with table1 as (
	select course_id, count(*) as cnt_total from orders
	group by course_id
), table2 as (
	select course_id, count(distinct(user_id)) as cnt_checkins from checkins
	group by course_id
)

select c.title, 
	b.cnt_checkins, 
	a.cnt_total, 
	(b.cnt_checkins/a.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 

-> 이처럼 from과 inner join에 들어가는 subquery를 with구문으로 빼서 각각의 임시 테이블을 만들어주었다. *with구문을 사용할 때는 항상 윗단에다가 적도록 해야한다.

1. 문자열 데이터 가공실무에서는 문자열을 원하는 형태로 가공해야하는 경우가 많다! 그렇다면?

1-1) SUBSTRING_INDEX ; 문자열 쪼개기
문자열을 한 기준에서 기준의 앞 데이터나 뒤의 데이터만 쪼개는 것

예시 1) SUBSTRING_INDEX(email,'@',1) 
		@기준으로 문자열을 쪼개고, 첫 번째 조각을 가져온다 (아이디)
    2) SUBSTRING_INDEX(email,'@',-1) 
		@기준으로 문자열을 쪼개고, 마지막 조각을 가져온다 (도메인)

1-2) SUBSTRING ; 문자열 일부 출력
SUBSTRING(칼럼명, 출력시작위치, 마지막위치)

ex) 칼럼(created_at) [2023-02-16 20:29:04] 기준 
SELECT order_no, SUBSTRING(created_at,1,10) as date from orders
=> 일자 출력
	SELECT order_no, SUBSTRING(created_at,12,8) as date from orders
=> 시간 출력 

결과를 group by로 통계를 내도록 활용 가능

1. case when 조건문

SELECT pu.user_id, pu.point,
		(case when pu.point > 10000 then '잘 하고 있어요!'
		else '조금만 더 파이팅!' end) as msg
from point_users pu 
=> 유저의 포인트가 10000보다 많거나 적을 경우에 따른 메세지 조건 

+) subquery와 case when
SELECT a.lv, count(*) FROM (
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 
) a
group by a.lv
=> subquery로 조건문을 통계 낼 수 있다 
++) with절로 표현
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(*) FROM table1 a
group by a.lv

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

	답1) with 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 cnt from enrolleds_detail ed 
	group by enrolled_id
)

select a.enrolled_id, 
		a.done_cnt, 
		b.cnt,
		round((a.done_cnt/b.cnt),2) as ratio
	from table1 a 
	inner join table2 b on a.enrolled_id = b.enrolled_id

답2) select enrolled_id,
        sum(done) as cnt_done, 
        count(*) as totla_cnt, 
        round((sum(done)/count(*)),2) as ratio 
from enrolleds_detail ed
group by enrolled_id