[스코클] 엑셀보다 쉬운 SQL - 4주차

take-away

- 문제는 항상 쪼개서 생각하기

- 점점 복잡해지고 어려워진다. 반복학습을 많이 해야겠다는 생각이 든다. 

- 복잡한 기능을 잘 쓰는 것도 중요하지만, 더 쉽고 간편한 query 를 짜는 것이 더 중요! 특정 조건의 카운팅을 위한 복잡한 subquery 보다 sum 을 활용할 수도..

 

1. 이번주 배울 것

- Subquery (쿼리 안의 쿼리) 하위 쿼리의 결과를 상위쿼리에서 사용하면 SQL쿼리가 훨씬 간단해짐

- with 구문

- 문자열을 활용해서 가공하기: 현실데이터는 지저분&복잡. - 

- case when (조건문)

 

2. subquery

2.0. subquery 활용 순서

1) 안에 들어가는 쿼리문을 먼저 작성
2) 결과 확인
3) 바깥의 쿼리문을 작성하고, 필요한 곳에 서브쿼리를 집어넣기

2.1. where 절에 들어가는 경우

- e.g., where 는 조건절이므로 subquery결과를 조건에 활용하는 방식으로 활용 가능

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

 

2.2. select 절에 들어가는 경우

- e.g., 유저별 평균 likes 숫자

select avg(likes) from checkins
where user_id = '4b8a10e6'

select c.checkin_id,
   c.user_id,
   c.likes,
   (
   select avg(likes) from checkins
   where user_id = c.user_id  
   ) as avg_likes_user
from checkins c 

*. user_id = c.user_id 이부분이 헷갈림.
1) 바깥쿼리문이 checkin_id, user_id, likes를 출력한 다음에 avg_likes_users를 출력.
2) where user_id = c.user_Id 이부분은 섭쿼리의 user_id가 쿼리의 출력한 user_id인 경우만 모아서 평균을 계산
3) 1~2의 반복

 

2.3. from 절에 들어가는 경우

- e.g., 내가 지금 만든 테이블을 마치 원래부터 있었던 table처럼 사용하는 경우

select user_id, round(avg(likes),1) as avg_likes from checkins
group by user_id 

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
group by user_id 
) a on pu.user_id = a.user_id

 

2.4. Subquery 연습해보기

- 엄청 복잡하고 어렵지만, 하나씩 차근차근 하면 됨. 

- 문제 전부 다시한번 풀어보기

 

2.5. with 절: subquery 를 하나의 임시 테이블로 받아서 쿼리문을 단순화시킬 수 있다. 

 

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

3.1. 문자열

- email에서 아이디만 가져오기: substring_index(a,'b',c) a는 칼럼, b는 기준문자열, c는 앞(+1) or 뒤(-1)

- email 에서 도메인 가져오기

- 날짜+시간 데이터에서 날짜만 뽑아내기: substring( a, b, c) a는 칼럼, b는 남기고싶은 시작점, b는 끝지점)

* 가끔 훨씬 쉬운방법으로 해결가능 e.g., where like 구문

3.2. case

- case는 일종의 가정법. (case when point > 10000 then '잘 하고 있어요!' else '조금만 더 파이팅!' end) as msg

-> select문으로 칼럼 추가할 때 유용

3.3. 각종 연습문제들..

 

questions

- 결국 두개의 테이블을 붙인다는 면에서 from subquery와 select subquery가 동일한데, 서로 바꿔서 써도 문제 없나? - 테스트 해 보기.