2. 실전 SQL

해시태그를 통한 책의 검색

• 서브쿼리(혹은 조인)을 통해서 관계를 따라가며 찾음
  • 예시에선 hash_tag → book_hash_tag → book 순으로 따라감

페이징(paging) : 데이터베이스 자체에서 끊어서 가져오는 것

• Offset based 페이징 : 직접 페이지 번호를 찾아 이동하는 페이징
  • 단점 : 페이지가 뒤로 갈수록 넘어가는 데이터가 많아져 성능상 이슈 | 내가 1페이지에서 5개를 보고서 다음 페이지로 넘어갔는데, 그새 10개 글이 추가됨 → 글이 밀려남(중복)

-- limit: 한 페이지에서 보여줄 데이터 개수
-- offset: 몇 개를 건너뛸지
-- 페이지 x번에 대해 한 페이지에 y개를 보여준다면?
select * 
from book
order by likes desc
limit y offset(x - 1) * y;
-- 보통 첫 페이지가 1페이지이기에 (x-1)

• Cursor based 페이징 : offset based 단점 보완 | 커서로 무언가(마지막으로 조회한 콘텐츠)를 가리켜 페이징 하는 방법 => 마지막으로 조회한 대상 그 다음부터 가져오는 것
  • 참고) 인기순 정렬 같이 같은 값이 있을 수 있는 경우, 정렬 기준이 더 있어야 함!

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

 

 

스터디 때 정리한 내용

• LPAD(문자열, 자릿수, 채울거) : 문자열을 자릿수로 변환 → 공백을 채울거로 나머지 채움
• CONCAT : 두 값을 합침(더하기X) => 0022 + 0123 = 00220123

SELECT b.*,
       CONCAT(LPAD(likes.like_count, 10, '0'), LPAD(b.id, 10, '0')) AS cursor_value
FROM book AS b
JOIN (-- book_likes 1. 테이블에서 id로 그룹화 후 좋아요 값을 집계(like_count)
			SELECT book_id, COUNT(*) AS like_count
      FROM book_likes
      GROUP BY book_id) AS likes ON b.id = likes.book_id -- 2. 결론적으로 book 테이블에 like_count 추가
    -- 좋아요 수를 길이 10짜리 문자열로 변환 + 앞을 0으로 채움
		-- 두 값을 합쳐서 정렬 기준이 되는 “cursor값”을 문자열로 만듦
		-- 그후 그 결과보다(cursor_value) 값보다 작은 값을 가져오겠다.
WHERE CONCAT(LPAD(likes.like_count, 10, '0'), LPAD(b.id, 10, '0')) < 
      (SELECT CONCAT(LPAD(like_count_sub.like_count, 10, '0'), LPAD(like_count_sub.book_id, 10, '0'))
       FROM (SELECT book_id, COUNT(*) AS like_count -- 1. 좋아요 카운트 쿼리 (별칭 like_count_sub)
             FROM book_likes
             GROUP BY book_id) AS like_count_sub
       WHERE like_count_sub.book_id = 3) # 여기에 cursor_value 값이 들어가면 됨. 3 말고 ?로 바꾸면 됨
ORDER BY likes.like_count DESC, b.id DESC
LIMIT 15;

---

미션 기록

(다이어그램은 워크북에 있는 걸 참고해서...)

 

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

• • 사용되는 항목 : 미션정보(mission.id), 가게이름(store.name), 미션포인트(mission.reward), 미션조건(mission.mission_spec), 미션진행상태(member_mission.status)
  • 미션 특성상 내가 미션을 추가해야지 새로운 미션이 추가되기 때문에(실시간으로 추가X) Offset based 방법 채택

-- 미션포인트가 높은 순으로 10개씩 페이지를 나눔
select * from mission ...
order by mission.reward desc
limit 10 offset 0;

select mission.id, store.name, mission.reward, mission.mission_spec, member_mission.status
from member
join member_mission on member.id = member.mission.member_id
join mission on mission.id = member_missino.mission_id
join store on mission.store_id = store.id
where member.id = {:member.id}
	and member_mission.status = '진행완료' -- 혹은 '진행중'
order by mission.reward desc
limit 10 offset 0;

 

2. 리뷰 작성하는 쿼리(사진의 경우는 일단 배제)

• 사용되는 항목 : 유저계정(member_id), 가게이름(store_id), 리뷰내용(body), 별점(score), 생성시간(created_at, 현재 시간을 자동 생성하기에 now() 사용)

insert into review (member_id, store_id, body, score, created_at)
values (:member.id, :store.id, {리뷰내용}, {별점}, now());

 

3. 홈화면 쿼리

• 가져와야 할 항목 : 미션명(mission.id), 가게이름(store.name), 가게주소(store.address), 미션포인트(mission.reward), 미션조건(mission.mission_spec), 기한일(mission.deadline)

select mission.id, mission.reward, mission.mission_spec, mission.deadline,
			store.name as store_name, store.address as store_address
from mission
join store on mission.store_id = store.id
join region on store.region_id = region.id
where region.name = :region_name
	and mission.deadline > NOW() -- 기한이 남은 미션만 보여줌
	and mission.id not in (
		select mission_id
		from member_mission
		where memeber_id = :member.id
		)
order by mission.deadline asc -- 기한이 얼마 남지 않은 순으로 정렬
limit 10 offset 0;

 

4. 마이 페이지 화면 쿼리

• 가져와야 할 항목 : 아이디(id), 닉네임(name), 이메일(email), 휴대폰번호(phone_number)

select id, name, email, phone_number
from member
where id = :member.id;

---

2주차 후기 : 커서 기반이 이해할 것 같으면서도 잘 모르겠다... 좀 더 찾아보면서 공부를 해야겠다!