Instagram DB 프로필 화면.md

프로필 화면

유저 프로필 정보

-- 블루뮤직의 피드 게시물 개수
select count(feedNo) as bllumusicFeedCnt from FeedTable where feedHost = 'bllumusic';

-- 블루뮤직의 팔로워 수
select count(followerNo) as bllumusicFollower from FollowerTable where followerHost = 'bllumusic';

-- 블루뮤직이 팔로우하는 유저 수
select count(followerNo) as bllumusicFollowing from FollowerTable where followerUserID = 'bllumusic';

-- 블루뮤직의 정보
select profileUserID,
       profileImageUrl, (select count(feedNo) as bllumusicFeedCnt from FeedTable where feedHost = 'bllumusic') as profileFeedCnt,
       (select count(followerNo) as bllumusicFollower from FollowerTable where followerHost = 'bllumusic') as profileFollowerCnt,
       (select count(followerNo) as bllumusicFollowing from FollowerTable where followerUserID = 'bllumusic') as profileFollowingCnt,
       profileName, profileGenre, profileContent, profileLink
from ProfileTable
where profileUserID = 'bllumusic';

하이라이트

-- 블루뮤직의 하이라이트
select hlCoverImageUrl as highlightCover, hlTitle as highlightTitle
from HighlightTable
where hlHost = 'bllumusic';

유저 피드(전체)

-- 블루뮤직의 전체 피드, 컨텐츠 이미지 or 동영상 구분
select *, 'Image' as Type
from FeedImageTable
where feedimageno in
      (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
  and feedImageUserID = 'bllumusic'
union all
select *, 'Video' as Type
from FeedVideoTable
where feedVideoNo in
      (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
  and feedvideoUserID = 'bllumusic';

-- 블루뮤직의 피드별 첫 번째 이미지 or 동영상
select feedImageFeedNo, feedImageUrl as firstUrlPerFeed, Type
from (select *, 'Image' as Type
      from FeedImageTable
      where feedimageno in
            (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
        and feedImageUserID = 'bllumusic'
      union all
      select *, 'Video' as Type
      from FeedVideoTable
      where feedVideoNo in
            (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
        and feedvideoUserID = 'bllumusic') as contentUrlPerFeed
where feedImageNo in (select min(feedImageNo)
                      from (select *
                            from FeedImageTable
                            where feedimageno in
                                  (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
                              and feedImageUserID = 'bllumusic'
                            union all
                            select *
                            from FeedVideoTable
                            where feedVideoNo in
                                  (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
                              and feedvideoUserID = 'bllumusic') as onlyOneUrlPerFeed
                      group by feedImageFeedNo);

-- 블루뮤직의 피드별 컨텐츠 수
select feedImageFeedNo, count(feedImageNo)
from (select *, 'Image' as Type
      from FeedImageTable
      where feedimageno in
            (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
        and feedImageUserID = 'bllumusic'
      union all
      select *, 'Video' as Type
      from FeedVideoTable
      where feedVideoNo in
            (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
        and feedvideoUserID = 'bllumusic') as bllumusicAllContentCnt
group by feedImageFeedNo;

-- 블루뮤직의 피드 전체 나열, 'Type'은 한 피드에 컨텐츠가 2개 이상일 경우('Multi')와 이미지('Image'), 동영상('Video')로 나눈다.
select contentUrlPerFeed.feedImageFeedNo,
       feedImageUrl                              as firstUrlPerFeed,
       if(contentCntPerFeed >= 2, 'Multi', Type) as Type
from (select *, 'Image' as Type
      from FeedImageTable
      where feedimageno in
            (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
        and feedImageUserID = 'bllumusic'
      union all
      select *, 'Video' as Type
      from FeedVideoTable
      where feedVideoNo in
            (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
        and feedvideoUserID = 'bllumusic') as contentUrlPerFeed
         inner join (select feedImageFeedNo, count(feedImageNo) as contentCntPerFeed
                     from (select *, 'Image' as Type
                           from FeedImageTable
                           where feedimageno in
                                 (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
                             and feedImageUserID = 'bllumusic'
                           union all
                           select *, 'Video' as Type
                           from FeedVideoTable
                           where feedVideoNo in
                                 (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
                             and feedvideoUserID = 'bllumusic') as bllumusicAllContentCnt
                     group by feedImageFeedNo) as bllumusicFeedCnt
                    on bllumusicFeedCnt.feedImageFeedNo = contentUrlPerFeed.feedImageFeedNo
where feedImageNo in (select min(feedImageNo)
                      from (select *
                            from FeedImageTable
                            where feedimageno in
                                  (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
                              and feedImageUserID = 'bllumusic'
                            union all
                            select *
                            from FeedVideoTable
                            where feedVideoNo in
                                  (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
                              and feedvideoUserID = 'bllumusic') as onlyOneUrlPerFeed
                      group by feedImageFeedNo)
order by contentUrlPerFeed.feedImageFeedNo desc;

유저 피드(동영상)

-- 블루 뮤직의 전체 피드 중 동영상 컨텐츠로만 이루어진 피드만 나열
select *
from (select contentUrlPerFeed.feedImageFeedNo,
             feedImageUrl                              as firstUrlPerFeed,
             if(contentCntPerFeed >= 2, 'Multi', Type) as Type
      from (select *, 'Image' as Type
            from FeedImageTable
            where feedimageno in
                  (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
              and feedImageUserID = 'bllumusic'
            union all
            select *, 'Video' as Type
            from FeedVideoTable
            where feedVideoNo in
                  (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
              and feedvideoUserID = 'bllumusic') as contentUrlPerFeed
               inner join (select feedImageFeedNo, count(feedImageNo) as contentCntPerFeed
                           from (select *, 'Image' as Type
                                 from FeedImageTable
                                 where feedimageno in
                                       (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
                                   and feedImageUserID = 'bllumusic'
                                 union all
                                 select *, 'Video' as Type
                                 from FeedVideoTable
                                 where feedVideoNo in
                                       (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
                                   and feedvideoUserID = 'bllumusic') as bllumusicAllContentCnt
                           group by feedImageFeedNo) as bllumusicFeedCnt
                          on bllumusicFeedCnt.feedImageFeedNo = contentUrlPerFeed.feedImageFeedNo
      where feedImageNo in (select min(feedImageNo)
                            from (select *
                                  from FeedImageTable
                                  where feedimageno in
                                        (select min(feedImageNo) from FeedImageTable group by feedImageFeedNo)
                                    and feedImageUserID = 'bllumusic'
                                  union all
                                  select *
                                  from FeedVideoTable
                                  where feedVideoNo in
                                        (select min(feedVideoNo) from FeedVideoTable group by feedvideoFeedNo)
                                    and feedvideoUserID = 'bllumusic') as onlyOneUrlPerFeed
                            group by feedImageFeedNo)) as grip
where Type = 'Video';