실전 SQL - 어떤 Query를 작성해야 할까?

본 글은 UMC EWHA에서 활동하며 공부하는 워크북을 토대로 정리된 글입니다.

 

 

 

query 역시 데이터베이스 설계처럼 정해진 답이 없고,

join을 사용할 지 혹은 subquery를 사용할 지 선택하면 된다.

무리해서 한번에 큰 query를 보내기 보다는 2개로 쪼개서 보내는 것도 좋다.

 

1. 책이 받은 좋아요 개수를 보여주기

likes 칼럼 없이 집계

select count(*) from book_likes where book_id = {대상 책 아이디};

 

여기에 추가적으로 내가 차단한 사용자의 좋아요는 집계를 하지 않도록 설계

select count(*) from book_likes where book_id = 3 and 
	user_id not in (select target_id from block where owner_id = 2);

 

위의 커리를 inner join으로 구현

select count(*)
from book_like as bl
inner join block as b on bl.user_id = b.target_id and b.owner_id = 2
where bl.book_id = 3;

 

혹은 left join

select count(*)
from book_like as bl
left joinblock as b on bl.user_id = b.target_id and b.owner_id = 2
where bl.book_id = 3 and b.target_id is null;

 

+ join 연산 보다는 sub query가 더 가독성이 좋다.

 

2. 해시태그를 통한 책의 검색

쿼리를 작성할 때 N:M 관계로 인해 사이에 매핑 테이블이 추가된 경우는 쉬운 쿼리로 데이터를 가져오기가 힘들다.

 

select * from book where id in 
	(select book_id from book_hash_tag
    	where hash_tag_id = (select id from hash_tag where name = 'UMC'));

 

join 연산으로

select b.*
from book as b
inner join book_hash_tag as bht on b.id = bht.book_id
inner join hash_tag as ht on bht.hash_tag_id = ht.id
where ht.name = 'UMC';

 

 

책들의 목록을 최신 순으로 조회하는 쿼리

select * from book order by created_at desc;

 

좋아요 개수 순으로 목록 조회

book 자체에 likes 칼럼 없음 -> 단순 select로 조회 불가

select * from book as b
join (select count(*) as like_count from book_likes group by book_id)
	as likes on b.id = likes.book_id
order by likes.like_count desc;

 

 

3. 페이징

데이터베이스 자체에서 끊어서 가져오는 것이 필요하고 이를 paging 이라고 한다.

paging에는 2가지 종류가 있다.

 

1. Offset based 페이징

우리가 자주 접하는 페이징

직접 페이지 번호를 찾아내어 이동하는 페이징

paging 쿼리는 sql마다 다르며 아래 코드는 mysql 기반으로 한다.

 

select*
from book
order by likes desc
limit 10 offset 0;

 

limit을 통해 한 페이지에서 보여줄 데이터의 개수를 정하고

offset으로 몇 개를 건너뛸지를 정한다.

 

페이지 x번에 대하여 한 페이지에 y개를 보여준다면

select *
from book
order by likes desc
limit y offset (x-1)*y;

 

보통 1페이지가 첫 페이지 때문에 ( x-1 ) 이다.

 

페이지 n 번에 대해 한번에 15 개씩 보여준다.

+ 인기 순 정렬

select * from book as b
join (select count(*) as like_count from book_likes group by book_id)
	as likes on b.id = likes.book_id
order by likes.like_count desc
limit 15 offset (n-1) * 15;

 

Offset paging의 단점

: 직접 여러 개의 데이터를 넘어가서 가져오는 개념이라 페이지가 뒤로 갈수록 넘어가는 데이터가 많아져

  성능 상의 이슈가 있고, 결정적으로 아래와 같은 문제 상황이 발생할 수 있다.

 

사용자가 1페이지에서 2페이지로 넘어가려는 찰나, 게시글 6개가 추가 되었다.

-> 분명 2 페이지로 왔는데 1페이지에서 보았던 게 또 보임

이런 단점을 보완한 페이징 기법이,

 

2. Cursor based 페이징

여기서 커서는 마지막으로 조회한 컨텐츠이다.

= 마지막으로 조회한 대상 그 다음부터 가져오기

 

마지막으로 조회한 책의 좋아요가 20이라면

select * from book where book.likes <
	(select likes from book where id = 4)
order by likes desc
limit 15;

 

책 목록 조회 쿼리

select * from book where created_at <
	( select created_at from book where id = 3)
order by created_at desc
limit 15;

 

인기순

 

select * from book as b
join (select count(*) as like_count from book_likes group by book_id) as likes
	on b.id = likes.book_id
where likes.like_count < (select count(*) from book_likes where book_id = 3)
order by likes.like_count desc
limit 15;

 

위의 인기순 커서 페이징 쿼리는 제대로 작동하지 못한다.

왜냐하면 예를 들어, 좋아요가 0인 게시글이 400개이고, 마지막으로 조회한 책의 좋아요가 0개라면,

그리고 아직 뒤에 조회를 하지 않은 책이 있다면

다음 페이지에 책이 목록으로 조회가 되지 않는다.

 

따라서 인기순 정렬 같이 같은 값이 있을 수 있는 경우 정렬 기준이 하나 더 있어야 한다.

위의 코드에서 좋아요 수가 같을 경우 최신 순으로 정렬이 되도록

select * from book as b
join (select count(*) as like_count from book_likes group by book_id) as likes
	on b.id = likes.book_id
where likes.like_count < (select count(*) from book_likes where book_id = 3)
order by likes.like_count desc, b.cread_at desc
limit 15;

 

4. 책 목록 조회 시 좋아요 순으로 커서 페이징 + 차단한 유저의 좋아요는 집계 X

select b.*
from book as b
join (
	select bl.book_id, count(*) as like_count 
    from book_likes as bl
    where not exists (
    	select target_id
        from block as bc
        where bc.target_id = bl.user.id and bc.owner_id = 3
        )
     group by bl.book_id
 ) as likes on b.id = likes.book_iud
order by likes.like_count desc, b.created_at desc
limit 15;

 

 

---

 

Join 연산

• MySQL에서 join 연산은 두 개 이상의 테이블에서 데이터를 결합하는 데 사용된다.
• 주로 관계형 데이터베이스에서 테이블 간의 관계를 나타내기 위해 사용된다.
• 여러 테이블 간의 관계를 이해하고 데이터를 효율적으로 결합하여 필요한 정보를 추출하는 데 사용된다.
• Join 연산의 종류
  • INNER JOIN : 두 테이블 간의 일치하는 행만 반환한다. 즉, 조인 조건을 충족하는 행들만 선택된다.
  • LEFT JOIN : 왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환한다. 오른쪽 테이블에서 일치하는 행이 없는 경우에도 왼쪽 테이블의 행은 반환된다. 만약 오른쪽 테이블에서 일치하는 행이 없으면 오른쪽 테이블의 모든 열은 NULL 로 채워진다.
  • RIGHT JOIN : LEFT JOIN의 정반대로 작동한다. 즉 오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환한다.
  • FULL JOIN : 두 테이블 중 어느 한쪽에도 일치하지 않는 모든 행을 반환한다. 즉, 왼쪽 테이블과 오른쪽 테이블의 모든 행을 반환하고 일치하는 행이 없는 경우에는 NULL 로 채운다.
  • CROSS JOIN ( 상호 조인 ) : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시킨다. 상호 조인 결과의 전체 행 개수는 테이블의 각 행의 개수를 곱한 수이다.
  • SELF JOIN ( 자체 조인 ) : 자기 자신과 조인하므로 1개의 테이블을 사용한다.
• 일반적으로는 INNER JOIN이 가장 많이 사용되지만, 데이터의 특성과 요구사항에 따라 다른 조인 유형을 사용해야 할 수도 있다.