[Database/MySQL] Youtube 유튜브 한방쿼리 작성

MySQL + Node.js로 Youtube를 따라 만들며 공부했을 때 직접 설계했던 ERD이다.

💡 영상 조회

select v.videoName,
       v.videoPlus,
       u.userName,
       up.userProfile,
#        (select count(vv.videoViewIdx) as viewNum
#         from videoview vv left join video v on vv.videoIdx = v.videoIdx
#         group by v.videoIdx) as viewNum,     # 조회수
       ifnull(vieww.viewNum, 0) as viewNum,
       (select count(subUserIdx)
           from subscription s
           group by s.userIdx
           having s.userIdx = ?) as subNum,     # 구독자수
       ifnull(likee.LikeNum, 0) as likeNum,    # 좋아요수
       ifnull(likee.UnlikeNum, 0) as unlikeNum
from video v left join user u on v.userIdx = u.userIdx
             left join userprofile up on u.userIdx = up.userIdx
             left join (select v.videoIdx,
                               count(vv.videoViewIdx) as viewNum
                        from videoview vv left join video v on vv.videoIdx = v.videoIdx
                        group by v.videoIdx) vieww
                 on vieww.videoIdx = v.videoIdx
             left join subscription s on s.subUserIdx = u.userIdx
             left join videocomment vc on vc.videoIdx = v.videoIdx
             left join (select vc.videoCommentIdx,
                               -- count(case when vc.commentClass = '1' then 1 end) as recommentNum,
                               count(case when vcl.commentLikeStatus = 'L' then 1 end) as likeNum,  # 여기 좋아요수 맞음
                               count(case when vcl.commentLikeStatus = 'U' then 1 end) as UnlikeNum
                        from videocommentlike vcl left join videocomment vc on vcl.videoCommentIdx = vc.videoCommentIdx
                        group by videoCommentIdx, vc.commentClass, vcl.commentLikeStatus) as likee
                   on vc.videoCommentIdx = likee.videoCommentIdx
group by v.videoIdx
having v.videoIdx = ?;

• select절에서 서브쿼리 subNum을 작성하여 구독자수를 가져옴
• from절에서 서브쿼리 vieww를 작성하여 join시킴
• 마찬가지로 from절에서 서브쿼리 likee를 작성하여 join시킴
  • from절에서 서브쿼리를 사용하면 뷰처럼 결과가 동적으로 생성된 테이블로 사용할 수 있다고 한다. 따라서 vieww.videoIdx처럼 칼럼에 자유롭게 접근할 수 있어 테이블처럼 사용가능하다!
  • 따라서 from절에서 작성한 서브쿼리에 이름을 주어 본 쿼리의 select문에서도 활용할 수 있다.
• ifnull() → 비어있을 수 있는 데이터 처리

 

💡 영상 댓글 조회

select ifnull(recomm.recommentNum, 0) as recommentNum,  -- 대댓글 개수
       ifnull(likee.LikeNum, 0) as commentLikeNum,
       ifnull(likee.UnlikeNum, 0) as commentUnLikeNum,
       u.userName,  -- 댓글 단사람 정보
       up.userProfile,
       classs.commentContent, -- 댓글내용
       # count(case when vc.commentClass = '1' and vc.videoIdx = v.videoIdx then 1 end) as recommentNum, -- 대댓글 개수
       case when timestampdiff(minute, v.createdAt, current_timestamp) < 60
                then concat(timestampdiff(minute, v.createdAt, current_timestamp), '분 전')
            when timestampdiff(hour, v.createdAt, current_timestamp) < 24
                then concat(timestampdiff(hour, v.createdAt, current_timestamp), '시간 전')
            when timestampdiff(day, v.createdAt, current_timestamp) < 7
                then concat(timestampdiff(day, v.createdAt, current_timestamp), '일 전')
            when timestampdiff(week, v.createdAt, current_timestamp) < 4
                then concat(timestampdiff(week, v.createdAt, current_timestamp), '주 전')
            when timestampdiff(month, v.createdAt, current_timestamp) < 12
                then concat(timestampdiff(month, v.createdAt, current_timestamp), '달 전')
            when timestampdiff(year, v.createdAt, current_timestamp) < 20
                then concat(timestampdiff(year, v.createdAt, current_timestamp), '년 전')
            else v.createdAt end
from video v -- left join user u on v.userIdx = u.userIdx
             -- -- left join videocomment vc on v.videoIdx = vc.videoIdx
             left join (select vc.videocommentIdx,
                               vc.commentcontent,
                               vc.videoIdx,
                               u.userIdx
                        from videocomment vc left join user u on vc.userIdx = u.userIdx
                        where vc.commentClass = '0') as classs   # 대댓글 삭제 추가
                    on v.videoIdx = classs.videoIdx
             -- left join userprofile up on u.userIdx = up.userIdx
             left join (select vc.videoCommentIdx,
                               -- count(case when vc.commentClass = '1' then 1 end) as recommentNum,
                               count(case when vcl.commentLikeStatus = 'L' then 1 end) as likeNum,  # 좋아요수
                               count(case when vcl.commentLikeStatus = 'U' then 1 end) as UnlikeNum
                        from videocommentlike vcl left join videocomment vc on vcl.videoCommentIdx = vc.videoCommentIdx
                        group by videoCommentIdx, vc.commentClass, vcl.commentLikeStatus) as likee
                   on classs.videoCommentIdx = likee.videoCommentIdx
             left join (select commentGroup,
                               count(case when vc.commentClass = '1' then 1 end) as recommentNum
                        from videocomment vc
                        group by vc.commentGroup) as recomm   # 댓글별 대댓글 개수 추가
                    on recomm.commentGroup = classs.videoCommentIdx
             left join user u on classs.userIdx = u.userIdx
             left join userprofile up on u.userIdx = up.userIdx
where v.videoIdx = ?;

유튜버와 댓글 작성자를 구분하기, 댓글과 대댓글 구분하여 출력하기, 댓글의 좋아요수 반영하기 등 어려웠던 부분이 많았던 쿼리이다.

• videoComment 테이블과 조인할 때 서브쿼리 classs를 통해 대댓글을 제외하고 불러오기
• commentLikeStatus 테이블과 조인할 때 작성한 서브쿼리 likee에서 좋아요수, 싫어요수 불러오기
• 데이터베이스를 설계할 때 댓글의 commentClass는 0, 대댓글의 commentClass는 1로 설정해주었던게 많은 도움이 되었다.

 

💡 구독 -> 유튜버 프로필 클릭 화면 조회

select u.userName,
       up.userProfile,
       ifnull(vieww.viewNum, 0) as viewNum,
       v.videoName,
       v.videoLength,
      case when timestampdiff(minute, v.createdAt, current_timestamp) < 60
                then concat(timestampdiff(minute, v.createdAt, current_timestamp), '분 전')
            when timestampdiff(hour, v.createdAt, current_timestamp) < 24
                then concat(timestampdiff(hour, v.createdAt, current_timestamp), '시간 전')
            when timestampdiff(day, v.createdAt, current_timestamp) < 7
                then concat(timestampdiff(day, v.createdAt, current_timestamp), '일 전')
            when timestampdiff(week, v.createdAt, current_timestamp) < 4
                then concat(timestampdiff(week, v.createdAt, current_timestamp), '주 전')
            when timestampdiff(month, v.createdAt, current_timestamp) < 12
                then concat(timestampdiff(month, v.createdAt, current_timestamp), '달 전')
            when timestampdiff(year, v.createdAt, current_timestamp) < 20
                then concat(timestampdiff(year, v.createdAt, current_timestamp), '년 전')
            else v.createdAt end
from user u left join video v on u.userIdx = v.userIdx
            left join userprofile up on u.userIdx = up.userIdx
            left join  (select v.videoIdx,
                               count(vv.videoViewIdx) as viewNum
                        from videoview vv left join video v on vv.videoIdx = v.videoIdx
                        group by v.videoIdx) as vieww
                on v.videoIdx = vieww.videoIdx
group by u.userIdx, v.videoIdx
having u.userIdx = ?;

• from절에서 서브쿼리 vieww를 작성하고, 서브쿼리 안에서 조회수를 count하여 viewNum으로 저장 → select절에서 vieww.viewNum으로 영상 조회수를 접근할 수 있도록함

 

💡 실시간 스트리밍 댓글 조회

select u.userName,
       up.userprofile,
       sc.streamCommentContent,
       ifnull(sc.commentAmount, 0) as commentAmount,
       ifnull(sc.ifSuperChat, 0) as ifsuperChat,
       date_format(current_timestamp(), '%h:%i %p')
from stream st left join streamcomment sc on st.streamIdx = sc.streamIdx
               left join user u on sc.userIdx = u.userIdx
               left join userprofile up on u.userIdx = up.userIdx
where st.streamIdx = 1;

 

경소톤 쿼리를 짜는데 머리가 안돌아가서 여름에 작성했던 유튜브 쿼리들을 복습했다. 하나의 쿼리에 여러개의 서브쿼리를 작성하는것과 having, group by, where을 각각 다른 상황에 적용하는게 어려웠던 기억이 난다. 보기만해도 뿌듯한 퀴리들😊