[Chapter 5] 실전 SQL - Query를 작성하는 방법

오늘의 주제는 데이터베이스 설계를 한 것을 바탕으로 직접 쿼리를 짜보는 시간 .. 😆

 

(SQL의 기본 문법과 join, subquery는 알고 있다는 가정 하에 작성했다)

 

---

ERD

 

위의 ERD를 바탕으로 요구 사항에 대해 어떻게 데이터를 줄지 고민해보자

💡 책이 받은 좋아요 개수를 보여준다.

 

book테이블에 likes라는 속성이 있었으면 아래와 같은 query를 사용하면 된다.

select likes from book;

 

하지만 book 테이블에 likes 칼럼이 없다면 아래와 같은 query가 필요하다

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

 

 

 

위의 요구 사항에서 추가로 아래와 같은 요구 사항이 생긴다면 어떻게 할까?

💡 책의 좋아요 갯수를 계산하는데, 내가 차단한 사용자의 좋아요는 집계를 하지 않는다. 

 

그럼 block(차단 테이블)을 만들고 아래와 같은 쿼리를 작성해면 된다

 

select count(*) from book_likes where book_id = {대상 책 아이디}
	and user_id not in (select target_id from block where owner_id = {내 아이디});
    
#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 = {내 아이디}
where bl.book_id = {대상 책 아이디};

#left join으로 변경
select count(*)
from book_like as bl
left joinblock as b on bl.user_id = b.target_id and b.owner_id = {내 아이디}
where bl.book_id = {대상 책 아이디} and b.target_id is null;

 

 

아래와 같은 요구 사항도 쿼리를 만들어보자

 

💡 UMC라는 이름을 가진 해시태그가 붙은 책을 찾기

 

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';

 

 

---

페이징 (Paging)

개인적으로 웹 사이트의 기본이라고 생각하는 Paging!

이것도 한번 쿼리를 생각해보자 🤔

 

목록을 조회할 때 ex) 책이 100만 권이 있을 때, 한번에 100만 개를 다 가져오니 쿼리를 그대로 사용하면 엄청난 렉이 발생할 것이다. 따라서 Database자테에서 끊어서 가져오는 것이 필요하고 이것을 paging이라고 한다

 

paging에도 2가지 형태가 존재한다

 

1. Offset based 페이징

우리가 기본적으로 자주 봤던 페이징일 것이다. 이렇데 직접 페이지 번호를 찾아내어 이동한다.

동작하는 쿼리는 아래와 같다.

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

 

이렇게 limit을 통해 한 페이지에서 보여줄 데이터의 개수를 정하고 offset으로 몇 개를 건너뛸지를 정한다.

 

그렇다면 페이지 n번에 대해 한번에 15개씩 보여준다고 하자

select * from book
order by created_at desc
limit 15 offset (n-1) * 15;

 

왜 n-1이냐면 보통 1페이지가 첫 페이지이기 때문이다.

페이지 n번에 대하여 한 페이지에 15개씩 보여준다는 뜻이다

 

offset paing의 단점

offset paging은 직접 여러 개의 데이터를 넘어가서 가져온다는 느낌이기에 페이지가 뒤로 갈수록 넘어가는 데이터가 많아져 성능 상의 이슈가 있고, 중복 현상이 발생할 수 있다. ex) 사용자가 1페이지에서 2페이지로 넘어가려는 찰나, 게시글 6개가 추가 된다면 2페이지에서 1페이지에서 봤던 내용이 또 보일 것이다.

 

Cursor based 페이징

offset의 단점을 보완할 수 있으며, 커서로 무언가를 가르켜 페이징을 하는 방법이다.

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

 

아래처럼 책 목록 조회 쿼리를 커서 페이징으로 할 수 있다

 

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

 

마지막으로 책 목록 조회시 좋아요 순으로 (좋아요 수가 같을 경우 최신순) 커서 페이징을 하면서 동시에 차단한 유저의 좋아요는 집계하지 않는 쿼리는?

 

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_id
order by likes.like_count desc, b.created_at desc
limit 15;

 

 

미션 🔥

1. 내가 진행중, 진행 완료한 미션 모아서 보는 쿼리(페이징 포함)

SELECT 
    mm.status AS Status,
    m.reward AS Reward,
    s.name AS StoreName,
    m.mission_spec AS MissionDescription,
    mm.created_at AS MissionStartDate,
    mm.updated_at AS LastUpdated
FROM 
    member_mission mm
JOIN 
    mission m ON mm.mission_id = m.id
JOIN 
    store s ON m.store_id = s.id
WHERE 
    mm.status IN ('진행중', '완료')
ORDER BY 
    mm.updated_at DESC
LIMIT 
    10 OFFSET 0;  -- 첫 페이지, 페이지 당 10개의 결과

 

2. 리뷰 작성하는 쿼리

INSERT INTO review (member_id, store_id, body, score, created_at, updated_at)
VALUES (1, 101, '너무 맛있어요.', 5, NOW(), NOW());

 

3. 홈 화면 쿼리 (현재 선택 된 지역에서 도전이 가능한 미션 목록, 페이징 포함)

SELECT 
    m.id AS MissionID,
    m.mission_spec AS MissionDescription,
    m.reward AS Reward,
    m.deadline AS Deadline,
    s.name AS StoreName,
    s.address AS StoreAddress
FROM 
    mission m
JOIN 
    store s ON m.store_id = s.id
WHERE 
    s.region_id = 5 AND          -- 예시로 '5' 사용
    m.deadline > NOW()           -- 현재 날짜 이후의 미션만 조회
ORDER BY 
    m.created_at DESC
LIMIT 
    10 OFFSET 0;                 -- 페이지 번호 1, 페이지 당 10개 항목으로 설정

 

4. 마이 페이지 화면 쿼리

SELECT 
    m.nickname AS Nickname,
    m.email AS Email,
    m.point AS Points
FROM 
    member m
WHERE 
    m.id = {사용자 번호};

 

 

reference

2023 최용욱(똘이) server workbook