[당근마켓/데이터베이스] 당근마켓 한방쿼리 feat. SQL

 

 

당근마켓의 어플의 페이지별로 sql쿼리를 짜보자!

 

 

---

한방쿼리

SQL쿼리 연습을 위해 당근마켓의 각 페이지에 필요한 모든 데이터를 가져오는 한방쿼리를 짜보려고한다. 저번에 만들었던 당근마켓 ERD를 바탕으로 데이터베이스를 만들고 각 페이지 별로 한방쿼리를 짜보자.

 

당근마켓 클론의 전체적인 진행 상황이 궁금하시다면 이 글에서 확인 하실 수 있습니다!

 

 

 

---

홈 메인 페이지

select a1Name                                   as myLocation,
       P.productIdx                             as productIdx,
       title,
       photo,
       sellerNickName,
       price, -- format(price, N'#,0')
       A1.name                                  as productLocation,
       case when P.createdAt <> P.updatedAt then '끌올'
           else ''
       end                                      as isUpdated,
       case when timestampdiff(second , P.updatedAt, current_timestamp) <60
           then concat(timestampdiff(second, P.updatedAt, current_timestamp),' 초 전')
           when timestampdiff(minute , P.updatedAt, current_timestamp) <60
           then concat(timestampdiff(minute, P.updatedAt, current_timestamp),' 분 전')
           when timestampdiff(hour , P.updatedAt, current_timestamp) <24
           then concat(timestampdiff(hour, P.updatedAt, current_timestamp),' 시간 전')
           else concat(datediff( current_timestamp, P.updatedAt),' 일 전')
       end                                      as uploadTime,
       chatCount,
       likeCount
from (
         select productIdx,
                title,
                text,
                price,
                sellerIdx,
                locationIdx,
                category,
                createdAt,
                updatedAt
         from Product
         where status = 's'
     ) P
    join Address1 A1 on P.locationIdx = A1.address1Idx
    join (
        select userIdx,
               address,
               address2Idx,
               subAddress,
               A1.name        as a1Name
        from (
                 select userIdx, address, subAddress
                 from User
                 where userIdx = 1
             ) U
             join Address1 A1 on U.address = A1.address1Idx
    ) U on A1.address2Idx = U.address2Idx
    join (
        select userIdx      as sellerIdx,
               userNickName as sellerNickName
        from User
    ) S on P.sellerIdx = S.sellerIdx
    left join (
    select productIdx,
           photo
    from ProductPhoto PP
        join Photo P on PP.photoIdx = P.photoIdx
        where P.status = 'n'
        group by productIdx
        having min(P.photoIdx)
    ) Ph on P.productIdx = Ph.productIdx
    left join (
    select productIdx,
           count(chatRoomIdx)   as chatCount
    from ProductChatRoom CR
        group by productIdx
    ) CR on CR.productIdx = P.productIdx
    left join (
        select productIdx,
               count(userIdx) as likeCount
        from LikeProduct
        where status = 'l'
        group by productIdx
    ) LP on LP.productIdx = P.productIdx
order by P.updatedAt DESC
;

 

- 설정

userIdx가 1인 사용자의 address2지역 내에서 올라온 글들을 반영 했다. 주소를 Address1부터 Address4까지 나눠 따로 저장한 이유가 이것이었다. 범위를 넓혀가며 검색이 가능하다. 따라서 내 지역은 중계1동 이지만 같은 노원구 안에 있는 상계8동에서 올린 상품을 조회 할 수 있다. 검색 지역을 확대고 싶다면 Address2 테이블을 join 해 address3Idx를 사용하면된다. 실제 당근마켓에서는 구나 시같은 행정구역단위로 범위를 규정하지 않는다. 마다 GIS를 이용해 행정구역이 아닌 실제 지리적 정보를 이용할 것이다.

join Address1 A1 on U.address = A1.address1Idx

당근마켓은 지역을 최대 2개로 설정 할 수 있는데 두번째 지역으로 바꾸게 되면 위 코드에서 U.address를 U.subAddress로 바꾸면 된다.

상품에는 여러장의 사진을 올릴 수 있다. 하지만 첫 화면에서 보여지는 사진은 첫 사진이다. 

수정을 하면 '끌올'이 표시되고 수정시간이 표시된다.

 

- 코드 설명

from (
         select productIdx,
                title,
                text,
                price,
                sellerIdx,
                locationIdx,
                category,
                createdAt,
                updatedAt
         from Product
         where status = 's'
     ) P
    join Address1 A1 on P.locationIdx = A1.address1Idx

Product테이블에서 판매중인 상품(status = 's')을 가져와 Address1과 join한다. 이유는 Product의 Address2Idx를 얻기 위함인데, "서울시 노원구 중계1동"으로 예를 들면 Address1, Address2, Address3은 각각 "중계1동", "노원구", "서울시" 이다. 내가 사는 지역은 중계1동이지만 보여질 상품은 보다 상위 주소인 노원구에서 올라온 상품이기때문에 내 Address2(노원구)내에 있는 상품을 조회하기 위해 상품의 Address2Idx가 필요하다.

    join (
        select userIdx,
               address,
               address2Idx,
               subAddress,
               A1.name        as a1Name
        from (
                 select userIdx, address, subAddress
                 from User
                 where userIdx = 1
             ) U
             join Address1 A1 on U.address = A1.address1Idx
    ) U on A1.address2Idx = U.address2Idx

이제 위 결과에 내 Address2를 join해 내 Address2내에서 올라온 현재 판매중인 상품을 얻는다. 여기서 쿼리가 더러워졌다. 현재 사용자(userIdx=1)의 address2를 얻는 서브쿼리는 User 테이블에 Address1을 join 하고 where로 해당 유저(userIdx=1)를 걸러냈다. 여기까지가 현재 사용자(userIdx=1)가 살고 있는 Address2에 올라온 상품중 현재 판매중인 상품이다. 이제 이 상품의 첫번째 사진, 채팅방 수, 좋아요 수 를 가져오면된다. 각각의 테이블을 join하면 쉽게 얻어 올 수 있다. 

    join (
        select userIdx      as sellerIdx,
               userNickName as sellerNickName
        from User
    ) S on P.sellerIdx = S.sellerIdx

상품에 판매자의 닉네임을 붙이는 쿼리다.  User테이블의 정보가 여러번 붙다보면 컬럼이 중복되고 어떤 userNickName이 판매자인지, 어떤 updatedAt이 Product의 컬럼인지 헷갈려서 원하는 컬럼만 뽑고 이름도 새로 붙여줬다. 이런 작업이 오히려 코드를 난해하게 만들고 실행시간을 더 잡아먹는 것이 아닌가 싶다. 하지만 코드실행시간에 대해 정확하게 알지 못하기에 최대한 데이터를 적게 조작하는 방향으로 쿼리를 짰다.

left join (
    select productIdx,
           photo
    from ProductPhoto PP
        join Photo P on PP.photoIdx = P.photoIdx
        where P.status = 'n'
        group by productIdx
        having min(P.photoIdx)
    ) Ph on P.productIdx = Ph.productIdx
    left join (
    select productIdx,
           count(chatRoomIdx)   as chatCount
    from ProductChatRoom CR
        group by productIdx
    ) CR on CR.productIdx = P.productIdx
    left join (
        select productIdx,
               count(userIdx) as likeCount
        from LikeProduct
        where status = 'l'
        group by productIdx
    ) LP on LP.productIdx = P.productIdx
order by P.updatedAt DESC

 

사진, 채팅, 좋아요에 관한 부분이다. 여기서부터는 요소를 갖지 않는 상품이 있기때문에 left join을 했다. 만약 그냥 join(inner join)을 한다면 사진이 없거나 채팅과 좋아요를 받지 않은 상품은 표시되지 않는다. 여기서 Photo에 대한 설계가 잘못됐다고 느꼈다. 이 후회는 회고에서 다루겠다. 삭제되지 않고 잘 있는 사진을 보여주기위해 where절로 status를 걸렀다. 그냥 잘 있는 상태를 'n'이라고 했다. 또 각 상품마다 여러장의 사진이 있을 수 있는데 그중 첫번째 사진을 가져오기 위해 group by와 having을 썼다. photoIdx가 작을 수록 먼저 등록 한 것이기에 min(photoIdx)로 했지만 엄밀히 말하면 틀렸다. updateAt의 값으로 비교해야하는데 아직 group by와 having에 익숙하지 않아 일단 넘어갔다. 그 뒤는 채팅방과 좋아요 수를 계산해주는 쿼리이다. 

 

- 회고

사진에 대한 설계를 잘못 했다. 모든 사진을 Photo테이블에 저장하고 중간에 N:N을 위한 중간 테이블을 만든뒤 Product, Post, AdPost같은 테이블에 연결해주는 생각은 별로좋지 못한 생각같다. 내가 만든 ProductPhoto같은 Product테이블과 Photo테이블 N:N 관계를 맺어주는 테이블이다. 이런 중간 테이블을 만들지 말고 사진과 연결되는 테이블마다 각각의 사진을저장하는 테이블을 만들어 따로 관리하는게 좋아보인다. 사진을 가져오기 위해서는 무조건 ProductPhoto와 Photo 테이블을 join 해야하는데 너무 비효율 적이고 쿼리도 복잡해진다. 

 

각 쿼리의 실행시간을 몰라 최대한 데이터를 적게 조작하는 방향으로 쿼리를 짰다. 그러다보니 from절에도 select문이 들어가고 코드가 난잡해졌다. 사실 필요한 테이블을 모두 join하고 마지막에 where절로 걸러내는 작업을 하면 코드가 더 깔끔하겠지만 그러면 너무 쉽고 실행 시간과 자원을 고려하는 연습을 위해 위에서 말 했듯 데이터를 최대한 적게 조작하기위해 걸러내는 작업을 먼저 했다. 

 

전체 product에 sellerNickName을 붙인 뒤 해당 유저의 Address2내에서 올린 상품으로 거르는 방법보다 전체 product에서 해당 유저의 Address2내에서 올린 상품으로 거르고 난 뒤 sellerNickName을 붙여주는 방법이 더 효율적이라고 생각했다. 이 방법이 꼭 좋은 선택이 아닌것 같다. 그냥 전체를 join하고 나중에 거르는 방법이 더 빠를 수도 있고, 코드를 이해하기 쉽다. 이부분은 더 공부를한 뒤 작동원리를 이해하고 실행시간을 비교해보고 선택할 문제인것 같다. 

 

홈에는 중고거래 외에도 동네 홍보글도 반영된다. 같은 방식으로 AdPost 테이블에서 데이터를 받아와야 한다. 위 쿼리와 거의 비슷하고 테블과 컬럼 몇개만 바꾸면 되니 넘어간다.

 

---

 

동네 생활 메인 페이지

select a1Name           as myLocation,
       PC.name          as category,
       P.text           as content,
       U.userNickName   as writerNickName,
       A1.name          as writerLocation,
       case when timestampdiff(second , P.updatedAt, current_timestamp) <60
           then concat(timestampdiff(second, P.updatedAt, current_timestamp),' 초 전')
           when timestampdiff(minute , P.updatedAt, current_timestamp) <60
           then concat(timestampdiff(minute, P.updatedAt, current_timestamp),' 분 전')
           when timestampdiff(hour , P.updatedAt, current_timestamp) <24
           then concat(timestampdiff(hour, P.updatedAt, current_timestamp),' 시간 전')
           else concat(datediff(current_timestamp, P.updatedAt),' 일 전')
       end as uploadTime,
       photo
from PostCategories PC
left join Post P on PC.categoryIdx = P.category
join User U on P.writerIdx = U.userIdx
join Address1 A1 on A1.address1Idx = U.address
join (
    select userIdx,
           address,
           subAddress,
           A1.name          as a1Name,
           A1.address2Idx   as a2Idx
    from (
             select userIdx, address, subAddress
             from User
             where userIdx = 1
         ) U
             join Address1 A1 on U.address = A1.address1Idx
) Me on Me.a2Idx = A1.address2Idx
left join (
    select targetIdx, count(commentIdx) as commentCount
    from PostComment
    group by targetIdx
    ) C on P.postIdx = C.targetIdx
left join (
    select postIdx, photoIdx
    from PostPhoto
    where status ='n'
    ) PP on P.postIdx = PP.postIdx
left join Photo Ph on PP.photoIdx = Ph.photoIdx
where P.status ='n'
ORDER BY P.updatedAt DESC
;

 

- 설정

상단의 바에 모든 카테고리가 표시된다. 따라서 모든 카테고리를 가져오기 위해 from에서 PostCategories를 가져오고 left join으로 연결해주면 되지만 의미없는 null 값이 너무 많이 생기게 되어 카테고리는 제외 했다. 카테고리는 PostCategorus테이블을 따로 가져오면 된다. 

카테고리가 우리동네 질문일 경우 이모티콘 공감이 아닌 궁금해요로 바뀌게 된다. 이부분은 LikePost테이블의 status에서 값으로 해결 할 수 있을 것 같다.

동네생활 페이지에서 사진은 최대 3장을 보여주고 4장부터는 +숫자 로 표시된다. 이 부분은 실제 사진은 3장만 가져오고 전체 사진 수를 받아와 3 초과일 경우 3을 뺀 값을 +숫자로 표시하면 될것같다. 

- 코드 설명

위 홈메인 페이지와 구조가 거의 같다. 다른 점이라면 사진 수인데 위 코드에서는 사진을 모두 받아왔다..... 또 핑계아닌 한계.... 그루핑에 미숙하다. 꼭 수정하겠다.

 

- 회고 

홈 화면은 정말 쿼리를 짜고 수정하고 하는데 정말 많은 시간이 들었다. 홈 화면을 하고 나니 동네생활은 쉬웠다. 사실 어려운 부분은 나중에해야지 하고 넘어가서 쉬운 거이기도 하지만 머리속에 쿼리 구조가 있다 보니 연습을 위해 위를 복사 붙여넣기를 하지않고 처음부터 짰는데 굉장히 빠르게 짰다. updatedAt이 큰 순서대로 3장을 가져오는거에서 포기했다. 역시 group by와 having과 같은 그루핑에 익숙하지 않은 탓이다. 데이터를 그룹으로 묶고 그룹안에서 정렬, 선택 그룹을 정렬, 선택 하는 방법에 대해 공부 해야겠다.