[sql]스파르타코딩클럽 3~4주

또또·2024년 1월 26일

항해99 sql

목록 보기
2/6

3주차

01 여러 테이블을 연결해보자: Join 이란?

  1. join
    두개의 테이블을 연결하여 데이터를 조회하는 방식
    두 테이블의 공통된 정보(key)를 기준으로 조회

  2. join의 종류

  • inner join: 두 테이블의 합집합을 조회

    기본문법
select * from table a
inner join table b
on a.column  = b.column 
1

on이 기준

  • left join
    table a에는 값이 있지만 table b 에는 값이 없는 것
    table a 중심
select * from table a
left join table b
on a.column  = b.column

이 뒤 where 붙음

  • right join
    table b 에는 있지만 table a에 값이 있는것
select * from table a
right join table b
on a.column  = b.column

02 Join 본격 사용해보기

  1. orders 테이블에 users 테이블 연결해보기(inner join 사용)
SELECT *
from orders o 
inner join users u 
on o.user_id  = u.user_id

기준이 되는 같은 컬럼 사용
2. checkins 테이블에 users 테이블 연결해보기

SELECT *
from checkins c 
inner join users u 
on c.user_id = u.user_id ;
  1. enrolleds 테이블에 courses 테이블 연결해보기
SELECT *
from enrolleds e  
inner join courses c 
on e.course_id  = c.course_id

03 sql 쿼리가 실행되는 순서

  1. 테이블 a 데이터 전체를 가져옴
  2. inner join ~ on
    3.select 문

04 배웠던 문법을 Join과 함께 사용해보기

  1. checkins 테이블에 courses 테이블 연결해서 통계치 내보기
    오늘의 다짐' 정보에 과목 정보를 연결해 과목별 '오늘의 다짐' 갯수를 세어보자!
SELECT comment, count(comment) as count
from checkins c2
inner join courses c 
on c2.course_id  = c.course_id 
GROUP by c.title
  1. point_users 테이블에 users 테이블 연결해서 순서대로 정렬해보기
    유저의 포인트 정보가 담긴 테이블에 유저 정보를 연결해서, 많은 포인트를 얻은 순서대로 유저의 데이터를 뽑아보자!
SELECT*
from point_users pu 
inner join users u 
on pu.user_id =u.user_id 
order by pu.`point` desc
  1. orders 테이블에 users 테이블 연결해서 통계치 내보기
    주문 정보에 유저 정보를 연결해 네이버 이메일을 사용하는 유저 중, 성씨별 주문건수를 세어보자!
SELECT u.name, count(u.name)
from orders o 
inner join users u 
on o.user_id  =u.user_id 
where u.email like "%naver.com%"
GROUP by u.name
  • 쓰는 순서
  1. select from
    2.join ~on
    4.where
    5.group by or order by
    (6.limit)

05 이제는 실전! 본격 쿼리 작성해보기

1.Join 연습1
결제 수단 별 유저 포인트의 평균값 구해보기 point_users 에, orders 를 붙이기

SELECT o.payment_method, round(AVG(pu.point)) as avg_point 
from point_users pu 
inner join orders o  
on pu.user_id =o.user_id 
GROUP by o.payment_method

2.Join 연습2
결제하고 시작하지 않은 유저들을 성씨별로 세어보기 join 할 테이블: enrolleds 에, users 를 붙이기

SELECT name, count(*) as cnt_name
from enrolleds e 
inner join users u 
on e.user_id =u.user_id 
where is_registered = 0
GROUP by u.name
order by cnt_name desc

3.Join 연습3
과목 별로 시작하지 않은 유저들을 세어보기
join 할 테이블: courses에, enrolleds 를 붙이기

SELECT c.course_id ,title ,count(*) as cnt_notstart
from courses c 
inner join enrolleds e 
on e.course_id  =c.course_id 
where e. is_registered = 0
GROUP by c.title

06 이렇게 끝내면 아쉽죠? 한번 더 총복습!

  1. [퀴즈] Join 연습4
    웹개발, 앱개발 종합반의 week 별 체크인 수를 세어볼까요? 보기 좋게 정리해보기!
    join 할 테이블: courses에 checkins 를 붙이기
SELECT c.title, c2.week,COUNT(*) 
from courses c 
inner join checkins c2 
on c.course_id  = c2.course_id 
GROUP by c2.week ,c.title 
order by c.title ,c2.week

2.[퀴즈] Join 연습5
연습4번에서, 8월 1일 이후에 구매한 고객들만 발라내어 보세요!
join 할 테이블: courses에, checkins 를 붙이고+ checkins 에, orders 를 한번 더 붙이기!

SELECT c.title, c2.week,COUNT(*) 
from courses c 
inner join checkins c2 
on c.course_id  = c2.course_id 
inner join orders o 
on c2.user_id  = o.user_id 
where o.created_at >"2020-08-01"
GROUP by c2.week ,c.title 
order by c.title ,c2.week

07 Left Join 연습

1.7월10일 ~ 7월19일에 가입한 고객 중,
포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율을 보고 싶어요

select count(point_user_id) as pnt_user_cnt,
       count(*) as tot_user_cnt,
       round(count(point_user_id)/count(*),2) as ratio
  from users u
  left join point_users pu on u.user_id = pu.user_id
 where u.created_at between '2020-07-10' and '2020-07-20'

08 Union 배우기


두개의 쿼리 문을 견결해서 조회하고 싶을 때 union을 사용한다
(쿼리문1)
union all
(쿼리문2)

  • union과 union all의 차이점
    두개의 쿼리문을 연결하고 보여주는 것은 같지만
    union은 중복된 값을 제외하고 보여준다(중복된 값이 있으면 한번만 나온다)
    중복된 값을 제외해야되기 떄문에 연산속도가 느리다

union all 중복된값을 모두 보여준다(중복된값이 있으면 중복된 개수 전부 다 나온다)

09. 숙제

enrolled_id별 수강완료(done=1)한 강의 갯수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기. user_id도 같이 출력

SELECT e.enrolled_id ,user_id,count(e2.done) as max_count
from enrolleds e 
left join enrolleds_detail e2
on e.enrolled_id  =e2.enrolled_id 
where e2.done=1
GROUP by e.enrolled_id  
ORDER by max_count desc

10. 숙제

문제 유저 아이디 별 수강완료가 안된(done=0)된 갯수가 가장 많은 사람을 조회하기
조인할 테이블:endrolled_detail, endrolleds+ endrolleds, useres
조인할 필드 enrolled_id ,user_id

select u.user_id ,u.name ,e.done ,count(u.user_id) as cnt
from enrolleds_detail e
inner join enrolleds e2 
on e.enrolled_id  = e2.enrolled_id 
INNER join users u 
on e2.user_id  = u.user_id 
where e.done =0
group by u.user_id 
order by cnt desc

4주차

[수업 목표]

  1. Subquery(서브쿼리)의 사용 방법을 배워본다
  2. 실전에서 유용한 SQL 문법을 더 배워본다
  3. SQL을 사용하여 실전과 같은 데이터분석을 진행해본다

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

서브쿼리란 쿼리안의 쿼리라는 이름으로 하위 쿼리의 결과를 상위 쿼리에서 사용
서브쿼리를 사용하지 않아도 데이터는 얻을 수 있지만 사용시 더 편하고 간단하게 데이터를 얻을 수 있다

  1. 서브쿼리 사용방법 익히기
    하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는 것
    지금까지는 inner join을 사용했지만 where문안에 쿼리를 넣는것
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'
)

02. Subquery 특징

  • 알려지지 않은 기준을 이용한 검색에 유용
  • 메인쿼리 실행 전 한번만 실행
  • 여러번 사용가능

03. 자주 쓰이는 Subquery 유형

  1. where에 들어가는 서브쿼리
    Subquery의 결과를 조건에 활용하는 방식 where 필드명 in (subquery)
  2. Select 에 들어가는 Subquery
    Select는 결과를 출력해주는 부분 기존 테이블에 함께 보고싶은 통계 데이터를 손쉽게 붙이는 것에 사용
    select 필드명, 필드명, (subquery) from ..
    오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 평소에 받았던 좋아요 수에 비해 얼마나 높고 낮은지가 궁금
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) 함께 출력해준다!

  1. From 에 들어가는 Subquery (가장 많이 사용되는 유형!)
    내가 만든 Select와 이미 있는 테이블을 Join하고 싶을 때 사용
    서브쿼리의 결과를 기반으로 새로운 가상 테이블을 생성합니다. 이는 주로 서브쿼리의 결과를 활용하여 복잡한 데이터를 가공하거나 계산하는 데 사용
    (그러니까 내가 만든 테이블에 새롭게 부치는 거네? 이 서브쿼리를 하나의 그냥 쿼리라고 생각하고)
SELECT column1, column2
FROM (
    SELECT column1, MAX(column2) AS max_column2
    FROM table1
    GROUP BY column1
) AS subquery_result
WHERE column2 = max_column2;

컬럼1과 컬럼2 는 이 서브쿼리의 결과에서 반환된다
일반적으로 이렇게 서브쿼리의 값이 외부퀴리에서 사용할때 이름을 맞춰줘야한다 이름을 다르게 쓰고 싶으면 as 주기

  1. inner join에 들어가는 Subquery
select pu.user_id, a.avg_like, pu.point from point_users pu
inner join (
	select user_id, round(avg(likes),1) as avg_like from checkins
	group by user_id
) a on pu.user_id = a.user_id

04.Subquery 연습해보기

  1. Where 절에 들어가는 Subquery 연습해보기
  • 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu 
where `point` >(SELECT avg( `point`) from point_users pu2)
  • 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기
select * from point_users pu 
where pu.point >(SELECT avg(`point`) from point_users pu2
				inner join users u
				on pu.user_id = u.user_id
				where u.name = "이**"
				)

필요한 경우, Subquery 안에서 여러 테이블을 Join

  1. Select 절에 들어가는 Subquery 연습해보기
  • checkins 테이블에 course_id별 평균 likes수 필드 우측에 붙여보기
select checkin_id ,course_id,user_id, likes,
(select avg(likes) from checkins c2 
		where c.course_id = c2.course_id) as course_avg from checkins c
  • checkins 테이블에 과목명별 평균 likes수 필드 우측에 붙여보기
select checkin_id ,course_id,user_id, likes,
(select avg(likes) from checkins c2 
inner join courses c3 
		where c.course_id = c2.course_id) as course_avg from checkins c 
inner join courses c3 
on c.course_id = c3.course_id;
  1. From 절에 들어가는 Subquery 연습해보기
  • course_id별 유저의 체크인 개수를 구해보기!
select course_id, count(distinct(user_id)) as cnt_checkins from checkins
group by course_id
  • course_id별 인원을 구해보기!
select course_id, count(course_id) as cnt_checkins from checkins
group by course_id
  • course_id별 check in 개수에 전체 인원을 붙이기
select a.course_id, b.cnt_checkins, a.cnt_total 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

05. with절 연습하기

  1. with 절로 더 깔끔하게 쿼리문을 정리하기

쿼리를 작성하다보면 서브쿼리를 사용하는 경우가 많지만 그 경우가 많아질수록 가독성이 좋지 않다
또한 재사용이 불가능해 매번 같은 쿼리를 다시 작성해줘야 한다

이걸 해결하기 위해 with문 사용

  1. with문이란?

이름을 가진 SubQuery를 정의한 후 사용하는 구문.대부분 RDBS에서 사용가능
게층형 쿼리 구현가능

  1. 기본문법
WITH
    cte1 AS (
        SELECT column1, column2
        FROM table1
        WHERE condition1
    ),
    cte2 AS (
        SELECT column3, column4
        FROM table2
        WHERE condition2
    )

with문은 임시테이블을 만드는 문법
위의 문법은 서브쿼리를 사용해 CTE1이라는 테이블을 만들고 그 테이블을 이용해 외부쿼리를 작성할 수 있다

SELECT *
FROM cte1
JOIN cte2 ON cte1.column1 = cte2.column3;

2개이상의 임시테이블을 만들고 싶으면
위의 코드 처럼 두개를 연속으로 만들면 된다

그러니까 일단 서브쿼리로 푼 다음에 그 서브쿼리를 이용해서 가상의 테이블을 만들고 그 테이블 끼리 조인하기

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

  1. 문자열 데이터 다뤄보기
    실제 업무에서는, 문자열 데이터를 원하는 형태로 한번 정리하는 경우가 많음
  • 문자열 쪼개보기
    SUBSTRING_INDEX사용
    select 뒤 조회할 컬럼을 쪼갤때 사용 
select SUBSTRING_INDEX(col1,"기준이될문자","몇번째") from table
-- 맨 처음은 1, 맨 뒤는 -1
  • 문자열 일부만 출력하기
    SUBSTRING 사용
    select 뒤 컬럼의 일부만 조회할 때 사용
select SUBSTRING(문자열, 출력을 하고싶은 첫 글자의 위치, 몇개의 글자를 출력하고 싶은지) from table

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

1.CASE WHEN~ END: 경우에 따라 원하는 값을 새 필드에 출력해보기
case는 파이썬이나 자바스크립트에서 사용하는 switch문과 비슷
select 뒤 조건에 따라 다르게 출력하고 싶을때 사용

SELECT column1,
       CASE
           WHEN column2 > 0 THEN 'Positive'
           WHEN column2 < 0 THEN 'Negative'
           ELSE 'Zero'
       END AS result
FROM table_name;

08 SQL 문법 복습

1.평균 이상 포인트를 가지고 있으면 '잘 하고 있어요' / 낮으면 '열심히 합시다!' 표시하기!
(서브쿼리를 사용하지 않고 각 행의 개별적인 비교가 아니라 한개만 비교되서 맨위의 값 하나만 나온다)

select `point_user_id`, `point` ,
CASE when `point` >(select avg(point) from point_users pu )then "잘하고 있어요!" else "열심히 합시다" end as msg
from point_users pu
  1. 이메일 도메인별 유저의 수 세어보기
select SUBSTRING_INDEX(email,"@",-1) as domain, count(*)as cnt_domain 
from users u 
group by domain
  1. '화이팅'이 포함된 오늘의 다짐만 출력해보기
select * 
from checkins c 
where c.comment  like "%화이팅%"
  1. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수 출력해보기
select enrolled_id , count(*) as done_cnt, (select COUNT(*) 
											from enrolleds_detail eds 
											where ed.enrolled_id  = eds.enrolled_id 
											group by enrolled_id ) as totoal_cnt
from enrolleds_detail ed 
where done =1
group by enrolled_id

(여기서 중요한거
서브쿼리 쓸때 둘이 메인과 서브가 둘이 같은 테이블을 참조한다고 해도 둘이 같은 거라고
where문을 써줘야 한다)
서브쿼리에서 참조하는 외부 테이블의 특정 행을 가져올 필요가 있는 경우 WHERE 절을 사용하여 조건을 명시
SELECT 문의 FROM 절에 서브쿼리를 사용할 때, WHERE 절을 통해 메인 쿼리와 서브쿼리 간의 연결 조건을 설정

  1. 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의의 수, 그리고 진도율 출력해보기
    진도율 = (들은 강의의 수 / 전체 강의 수)
select enrolled_id ,
(select count(*) from enrolleds_detail ed  where e.enrolled_id  = ed.enrolled_id and done !=0) 
as done_cnt,
(select count(*) from enrolleds_detail ed  where e.enrolled_id  = ed.enrolled_id) as total,
round((select count(*) from enrolleds_detail ed  where e.enrolled_id  = ed.enrolled_id and done !=0) 
/
(select count(*) from enrolleds_detail ed  where e.enrolled_id  = ed.enrolled_id),2 )as ratio
from enrolleds e

근데 정답은 이거

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

그리고 이거 내가 한 방식대로 풀면 done_cnt가 0인게 나온다 그거 말고는 다 맞는거 같은데...

(추가)
일단 먼저 서브쿼리 2개로 만든 다음에 이 서브쿼리를 이용해 with 문을 만들었다

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)
from table1 a
inner join table2 b
on a.enrolled_id = b.enrolled_id
  • with에 있는 테이블 둘 다 group by 를 해줘야 하는거같다 안되니까 오류남
    테이블 만들어도 뒤에 조인이 없으면 안해도 되는데 두개 이상 만들어서 조인할꺼면
    조인의 기준이 되는 열들에 대해 GROUP BY가 필요

09 숙제

오늘의 다짐' 데이터를 보고 싶은데 '오늘의 다짐' 좋아요의 수가, 본인이 받은 최대 좋아요수에 비해 얼마나 높고 낮은지가 궁금

select c.checkin_id, c.user_id, c.likes, 
	(select max(likes) from checkins c2
	where c2.user_id = c.user_id) as avg_like_user
from checkins c;
profile
또또
내가 바라던 곳이야 흔들리지 않게 맘을 잡아
이전 포스트

[SQL]스파르타 코딩클럽1주차, 2주차

다음 포스트

sql having 절

0개의 댓글