[내일배움단] SQL 데이터 분석 강의 3주차 (feat. 스파르타코딩클럽)

이 강의의 이름은 "엑셀보다 쉬운 SQL"이라는 강의인데, 어떤 건 되게 쉽고 어떤 건 여전히 어렵게 느껴진다.

사실 알고보면 간단한 건데, 백지를 받아 들고 써보라고 하면 막막해지는 느낌이 글을 쓸 때보다 조금 더 강하게 든다. 

 

SQL 데이터분석 3주차

 

오늘 배운 것은 join, union 이었다. 

 

join

 

join은 테이블과 테이블을 붙이는 것이고, 거의 모든 쿼리에 들어간다고 해도 과언이 아닐만큼 실무에서 가장 많이 쓰는 것이라고 한다. 

 

굳이 나뉘어진 두 테이블을 join 할 거라면 애초에 왜 나눠둔 건지 궁금할 수도 있는데, 

강사님의 답변에 의하면 테이블은 기본적으로 한 목적에 맞는 것들만 모아두는 것이 가장 좋다고 한다. 

그래서 목적에 맞게 테이블이 여러개로 나뉘어있는 것이다.

 

그렇다면 무엇으로 이 테이블들을 연결할 수 있냐면 공통된 필드를 기준으로 연결하면 된다.

이를 key라고 부른다. 

 

엑셀에서 vloop과 동일하고 엑셀보다 더 쉽다고 하는데, 그래서 내가 엑셀을 못하는 것이구나 생각했다.. 

 

join은 inner join 과 left join이 있다. 

 

모든 값의 수가 일치하는 테이블을 붙일 때는 inner join을 쓴다. 한마디로 교집합이 있어서 순서 상관없이 매칭이 되는 필드를 가지고 있을 때 inner join을 쓰는 것이다. 

 

반대로 일부분의 값이 충족되지 않는 서로 다른 테이블을 붙일때는 큰 테이블에 작은 테이블을 붙이는 방식으로 순서가 존재한다. 

(이게 맞는건지 모르겠지만 내가 이해한 바로는 그렇다.) 

 

그러면 매칭이 이루어지지 않은 남는 항목들은  null이 가득 채우게 된다. 

 

 

inner join

실무에서도 주로 inner join을 많이 쓰며  순서에 상관없다 보니 초보자가 접근하기에도 쉽다고 한다.

 

inner join 쿼리문

 

다만 주의할 점이 있다면, 테이블을 지칭해주어야 한다. 

이 필드의 출처는 어느 테이블인지를 명확하게 밝혀줘야 오류가 적게 난다.

물론 적지 않아도 구동되긴 하지만 가끔 오류가 나니 처음부터 테이블을 지칭해서 써주도록 익히는 게 중요하다. 

 

붙일 테이블들을 꼼꼼히 확인하고 공통된 필드를 찾았다면 그 필드를 기준으로 inner join 해준 뒤, 내가 원하는 데이터 값을 추출해내는 연습을 하면 inner join 공부는 끝. 

 

inner join 테이블 약칭 on 테이블의약칭.필드값 = 테이블의 약칭. 필드 값의 형태이다. 

 

이렇게 join을 하고 나면 SQL 쿼리가 실행되는 순서는 from-join-select 순서이다. 

 

지난번에 배웠던 group by 와 count, round , where, like, as 등 join과 함께 적재적소에 섞어 쓰며

원하는 데이터 값만 도출이 되면 된다. 

 

만약 네이버 이메일을 사용하는 유저의 성씨별 주문건수를 세어보고 싶다면, 

 

일단은 특정 이메일을 사용한다고 하니 where를 써야겠다고 생각했다. 

like를 또 잊어버려서 한번 삽질을 해야 했지만, 

성씨별 주문건수니까 성씨별로 묶고, 결제방법을 세면 되지 않을까 생각했다. 

 

그래서 inner join으로 orders 테이블과  users 테이블을 합친 다음에, 

Orders 테이블에 있는 name 중 where 절을 활용해 네이버를 추출해내고, 

users 테이블에 있는 이름 필드를 묶어서, 보여달라고 했다. 

그리고 orders 테이블에 있는 결제방법을 세달라고 했다. 

 

 

 

네이버를 쓰는 유저들의 성씨별 주문건수

근데 필드 이름이 참 복잡하다. 

조금 정리를 해줄 필요가 있었다. 

해설에서도 간결하게 보여줬다. 

 

알리아스를 써서 낸 결과값

 

위의 쿼리가 실행되는 순서로는 from-join-where-groupby-select 순서이다. 

 

퀴즈는 총 다섯 개였다. 

 

첫 번째 , 결제 수단별 유저 포인트의 평균값 구해보기. 

문제를 보고 써야 할 구문들을 떠올렸다. 

point_users 테이블과 orders 테이블을 붙인다. 

point가 중심이 되니까 이것도 user_id를 중심으로 inner join 해서 살펴보면 되지 않을까

평균값은 avg를 쓰면 되고, 결제수단"별"은  group by를 쓰면 될 거 같다. 

 

첫번째 퀴즈 답

 

두 번째, 결제하고 시작하지 않은 유저들을 성씨별로 세어보기 

Enrolleds 테이블에 users 테이블을 붙이고, 

is_registered = 0인 사람들을 세고, order by 해서 내림차순으로 정렬해 보라고 한다. 

힌트에 답이 다 있었다 ㅋㅋㅋ 

두번째 퀴즈 답

 

나는 숫자에 작은따옴표를 붙여버렸다. 숫자에는 안 붙여도 되는 것을.. 

 

세 번째 퀴즈. 과목별로 시작하지 않은 유저들을 세어보기 

course에 enrolleds를 붙여서 is_registered = 0 인 사람의 숫자를 세면 되겠다. 

두 번째보다 더 쉽다며.. 

 

세번째 퀴즈 답

 

아직 끝나지 않았다. 퀴즈 2개가 더 남았음 

더 어려워졌다..! 

 

네 번째 퀴즈. 웹 개발, 앱 개발 종합반의 week별 체크인 수를 세어서 보기 좋게 정리해보기. 

courses에 checkins를 붙인다. 

 

Group by, order by에 콤마로 두 개씩 묶어보는 게 포인트.

 

이 정도힌트면 답을 다 준거 같은데… 

 

기본적인 오름차순 그대로 두고, 공통 필드인 코스 아이디로 묶어서 각각 그룹바이, 오더 바이 해줬다. 

체크 인수는 체크인 아이디 수를 세면 되는 거였겠지?

답안이랑 똑같기는 한데.. 해설을 봤더니 똑같았다 ^___^

 

네번째 퀴즈 답

 

다섯 번째 퀴즈. 네 번째 퀴즈에서 8월 1일 이후에 구매한 고객들만 발라내어 보세요! 

 

Courses에 checkins를 붙이고 checkins에 orders 한번 더 붙이기. 

Orders 테이블에 inner join 한번 더 걸고  where 절로 마무리 

 

이렇게 힌트를 줬지만 

개인적으로 여기서 관건은 where를 쓸 때, 8월 1일 이후에 구매했다는 것을 지정해줘야 하는데..

모르겠다 ㅠㅠ 

범위를 정해줄 것이 아니라 그냥 2020-08-01보다 크거나 같다로 수식을 넣어주니 됐다. 

등호와 부등호의 쓰임을 잘 생각해서 범위지정을 해준다는 것을 잊어버리지 말자… 

 

다섯번째 퀴즈 답

 

강사님이 한 가지 짚고 넘어가야 한다며 

SQL은 같은 결과에 도달할 수 있는 방법이 여러 가지라고 말씀하셨다.

나 역시도 그것을 체감하면서 수업을 따라가고 있는 중이다. 

강사님의 쿼리가 꼭 정답만은 아니라고 하시네 ㅋㅋ 

 

 그 누구도 한 번에 쭉 써서 결괏값을 내는 사람은 없다. 

모로 가도데이터만 나오면 된다는 생각을 하면 된다고 한다 

 

Left join 

 

어디에다가 뭐를 붙일 건지 순서가 매우 중요하다.

 

한쪽에는 있는데 한쪽에는 없는 것을 가지고 통계를 내고 싶을 때 쓴다. 

 

만약 point_users와 users 붙이고 싶은데, 모든 유저가 point를 가지고 있진 않을 것이기 때문에 

null이 뜬다.

 

이때 null만 추출하거나 빼는 걸 알아야 한다. 

 

NULL은 대문자로 쓴다. 

 

NULL로 뽑은 강의 시작을 아직 하지 않은 사람들의 수

 

만약 강의 시작을 한 사람들을 추출하고 싶다면, NULL 앞에 not 만 붙여주면 된다 

 

is not NULL

 

 

퀴즈! 

 

7월 10일 ~ 7월 19일에 가입한 고객 중,

포인트를 가진 고객의 숫자, 그리고 전체 숫자, 그리고 비율이 보고 싶어요!! 

 

count는 NULL을 세지 않는다.

Alias(별칭)도 잘 붙여야 한다

비율은 소수점 둘째 자리에서 반올림 

 

 

우선 포인트 고객 숫자는 point_users 테이블에 있을 거 같은데, 포인트를 가지고 있지 않은 고객의 정보까지 합쳐야 하므로 더 많은 인원이 있는 (더 큰 테이블) users에 point_users를 붙여서 

Is NULL 하면 되지 않을까? 

전체 숫자는 users에 있는 유저 수. 

포인트를 가진 고객의 숫자는 point_users의 유저수 

 

서로 간의 비율을 체크하는데 필요해서 Left join 한 듯..

 

비율을 엑셀처럼 : 를 쓰면 되는 건가?

 

일단 해설 없이 혼자 해보고, 해설을 봐야지 

 

SQL은 이런 여유가 있네.. 웹 개발과는 다르게 분량이 적어서 긍가..

 

 

Where로 범위도 지정해주어야 한다. 

이때 별칭을 지정해주지 않으면 오류가 나니 유의할 것. 

 

Users 테이블에 있는 created_at에서 between and 구문을 써서 추출해내고 

나머지를 카운트하면 될 듯..

 

카운트가 안된다…

다시 잘 생각해보면, 기간 구간대로 범위를 좁혀서 고객을 추출했으면 그다음 그 전체 숫자를 세는 것이 맞고, 

전체 숫자는 원래 users의 숫자가 아닌 건가?

그 비율을 over()로 추출해서 전부 별칭을 붙이면 된다고 생각했는데…

 

생각보다 잘 안풀린다안 풀린다.

 

중간과정

여기까지는 무리 없이 했는데, 비율을 계산해서 새로운 칸을 만드는 걸 모르겠는 거다. 

 

as ratio가 들어가는 것만 확신할 수 있었다.

분명히 어려운 걸 냈을 리가 없기 때문에, 다시 생각을 가다듬고  

그냥 엑셀처럼 / 하면 되지 않을까 했는데 됐다. ^____^

첨엔 아무리 해도 오류가 났는데, 줄을 맞춰서 보니 괜찮은 듯하다. 

다섯번째 퀴즈 최종 답

 

Union 

필드가 다 똑같다는 가정하에 select를 두 번 할게 아니라 연결해서 보고 싶을 때 쓴다. 

 

union의 예

Union 상태일 때는 order 가 안 먹어서 이렇게 나오는데, 

유니온만 봤을 때는 쉽다. 

 

전체를 괄호로 묶어서 그냥 중간에 union all이라고 하면 된다. 

유니온은 너무 쉬워서 외워지지 않을까 겁난다. 

 

 

마지막으로 숙제. 

 

enrolled_id별 수강 완료(done=1)한 강의 개수를 세어보고, 완료한 강의 수가 많은 순서대로 정렬해보기.

user_id도 같이 출력되어야 한다. 

 

힌트는 enrolleds, enrolleds_detail 이 두 테이블을 조인하고,  enrolled_id 필드를 조인한다. 

힌트에서 모든 것을 다 알려준 셈.. 

 

3주차 숙제의 답

나는 이렇게 나왔다. 

이건 아까보다 고민을 덜했고 쉽게 풀었다. 

해설이랑 쪼오끔 다르지만 결괏값이 같게 나와서 맞는 것으로.. 

 

이게 맞나? 하면서 답을 찾아가는 재미가 있는 SQL인 거 같다. 

 

4주 차까지 열심히 달려보겠다며..