🔥 TIL - Day 38 - 1

📌group by (~ 별로 조회한다)

특정 필드별로 개수를 조회한다.

select 필드, count(*) from 테이블
group by 필드

ex) group by + where
select 필드, count(*) from 테이블
where 조건필드 = 조건
group by 필드

여러 집계쿼리와 합께 사용한다.

• ~별로 믂어서 개수를 조회한다. count
• ~별로 묶어서 특정 필드의 합계를 조회한다. sum
• ~별로 묶어서 특정 필드의 최소값을 조회한다. min
• ~별로 묶어서 특정 필드의 최대값을 조회한다. max
• ~별로 묶어서 특정 필드의 평균값을 조회한다. avg

📌 order by

• 정렬을 위해 사용
• 쿼리의 가장 마지막에 위치한다.

ex) 필드로 오름차순 정렬  (기본값= 오름차순 asc)
select * from 테이블
order by 필드

ex) 필드별로 묶었을 때 개수를 내림차순 정렬한다.
select 필드, count(*) from 테이블
group by 필드
order by count(*) desc

📌 Join

left join

• A와 B 두 테이블을 join할 때 A와 B의 교집합 부분과 join의 기준이 되는 왼쪽 테이블 A의 모든 데이터를 가져오는 join 방식이다. (왼쪽테이블(A) + 교집합)
• from 절에 오는 테이블이 기준이 되는 테이블 (왼쪽 테이블)이 된다.
• 만약 교집합이 없는 A 테이블의 경우 B에 해당하는 칼럼들을 null로 처리한다.

select * from A_table a
left join B_table b
on a.user_id = b.user_id

inner join (default)

• 교 집 합

select * from A_table a
[inner] join B_table b 
on a.user_id = b.user_id

ex)
orders테이블과 users테이블을 이용해서 네이버 이메일을 사용하는 유저의 성씨별 주문건수 조회 (user_id를 fk로 가진다.)

select u.name, count(*) as count from orders o 
join users u 
on o.user_id = u.user_id
where u.email like '%naver.com'
group by u.name;

ex)
결제수단 별 user의 point 평균값 조회하기 (소수점 3번째까지 표시)
(orders와 point_users 테이블 이용 fk: user_id)

select o.payment_method, round(avg(pu.point),3) from orders o
join point_users pu 
on o.user_id = pu.user_id
group by o.payment_method

ex)
course의 이름 별로 week 별 체크인 수 조회
courses 테이블과 checkins 테이블 이용 (fk: course_id)

[ 2개 필드로 그룹화 및 정렬 ]

select c1.title, c2.week, count(*) as cnt 
from courses c1
join checkins c2
on c1.course_id = c2.course_id
group by c1.title, c2.week
order by c1.title, c2.week

위 쿼리에서 8월 1일 이후 주문한 고객의 강의만 조회 추가
총 3개 테이블을 join (course, checkins, orders)

select c1.title, c2.week, count(*) as cnt 
from courses c1
join checkins c2 on c1.course_id = c2.course_id
join orders o on c2.user_id = o.user_id
where o.created_at >= '2021-08-01'
group by c1.title, c2.week
order by c1.title, c2.week

📌 in 쿼리

• where절의 필드가 in 에 작성한 값인 행만 조회한다.
• 비교적 좋은 성능을 갖는 편 (or로 조건을 열거하는 것보다는 좋은 성능을 보인다.)

select * from 테이블
where 필드 in (값1, 값2, 값3)

📌 Subquery

• select절, from절, where절 등에 사용가능하다.
• join, in 등의 대상으로도 사용가능하다.

select절 Subquery

• 매 행마다 select 절의 서브쿼리가 실행된다.

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

Subquery를 join의 대상으로 사용하기

• Subquery의 결과를 마치 한 개 테이블인 것처럼 join의 대상으로 사용할 수 있다.

[ user_id별 좋아요를 받은 평균을 조회하는 쿼리 ]
[ 이 쿼리의 결과를 join의 대상으로 사용할 수 있다. ]
select user_id, avg(likes) as avg_likes from checkins
group by user_id

select pu.user_id, pu.point, u.avg_likes 
from point_users pu
join (
	select user_id, avg(likes) as avg_likes from checkins
	group by user_id
) as u
on pu.user_id = u.user_id

where절의 조건으로 Subquery 사용하기

[ 전체 point의 평균보다 큰 point를 가진 user만 조회 ]
select * from point_users pu
where pu.point > (
	select avg(point) from point_users
)


[ 위 조건에서 이씨 성을 가진 유저라는 조건을 추가 ]
[ join으로 해결 ]
select * from point_users pu
where pu.point > (
	select avg(point) from point_users pu
	join users u 
	on pu.user_id = u.user_id
	where u.name like '이%'
) 

[ 위 문제를 in쿼리를 이용 ]
[ 서브쿼리 안에 또 서브쿼리가 있는 형태 ]
select * from point_users pu
where pu.point > (
	select avg(point) from point_users
	where user_id in (
		select user_id from users where name like '이%'
	)
) 

in 쿼리를 이용한 Subquery

• in 안쪽의 select 쿼리 결과를 in 쿼리의 조건으로 사용한다.

select * from 테이블1
where 필드 in (
	select 필드 from 테이블2
	where 조건필드 = 조건
)

with절 사용

• 서브쿼리를 깔끔하게 사용하기 위함
• 앞서 사용해본 from 절에서 Subquery를 사용하는 방법 중 하나인 Subquery를 join의 대상으로 사용하는 방법을 with를 이용해서 같은 결과를 만들어낼 수 있다.

select *
from (
	select 필드 from 테이블1
	...
) a
join 
(
	select 필드 from 테이블2
	...
) b on a.id = b.id
join
(
	select 필드 from 테이블3
	...
) c on a.id = c.id

위와 같이 Subquery의 결과를 마치 테이블처럼 사용하는 것을 with를 통해 가능하다.
서브쿼리를 테이블처럼 사용하기 위해 with를 이용해서 임시로 이름을 부여하는 느낌?

with tableA as (
	select 필드 from 테이블1
	...
), tableB as (
	select 필드 from 테이블2
	...
), tableC as (
	select 필드 from 테이블3
	...
)

select * 
from tableA a
join tableB b on a.id = b.id
join tableC c on a.id = c.id

[ 깔____끔 ]

오늘의 뽀인트는 group by, join, subquery

오늘의 SQL은 여기까지