동영상 재생 사이트 관련 DB 설계 및 쿼리문 작성을 진행하면서 조회수를 불러오는데 문제가 생겼다.

동영상 조회수는 컬럼으로 넣어도 되지만, 알고리즘에 사용할 데이터로 활용하기 위해서는 어떤 유저가 어떤 영상을 시청했는지를 저장하는게 좋을 거 같아서 따로 테이블을 분리했다.

그 과정에서 다음과 같이 쿼리문을 짰었다.

SELECT
    U.userId            AS 'channel PK',
    profilePicUrl       AS 'profile picture',
    userName            AS 'channel name',
    thumbnailUrl        AS 'video thumbnail',
    title               AS 'video title',
    videoUrl            AS 'video url',
    CASE
            when TIMESTAMPDIFF(SECOND ,V.createdAt,CURRENT_TIMESTAMP) < 60
            then concat(TIMESTAMPDIFF(SECOND ,V.createdAt,CURRENT_TIMESTAMP), '초 전')
            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(HOUR,V.createdAt,CURRENT_TIMESTAMP) < 48
            then '하루 전'
            when TIMESTAMPDIFF(DAY,V.createdAt,CURRENT_TIMESTAMP) < 30
            then concat(TIMESTAMPDIFF(DAY ,V.createdAt,CURRENT_TIMESTAMP), '일 전')
            when TIMESTAMPDIFF(MONTH ,V.createdAt,CURRENT_TIMESTAMP) < 12
            then concat(TIMESTAMPDIFF(MONTH ,V.createdAt,CURRENT_TIMESTAMP), '달 전')
            else concat(TIMESTAMPDIFF(YEAR,V.createdAt,CURRENT_TIMESTAMP), '년 전')
        end AS 'uploaded At',
    (SELECT
      CASE
        when views<1000
        then concat(views,'회')
        when views<10000
        then concat(round(views/1000,1),'천 회')
        when views<100000000
        then concat(round(views/10000,1),'만 회')
        else concat(round(views/100000000,1),'억 회')
     END
    FROM (
        SELECT COUNT(*) AS views FROM Views
        WHERE Views.videoId = V.videoId
         ) Count)
    as 'view count',
    playTime    AS 'user playtime',
    videoLength AS 'Video Length'
FROM
    (Videos V inner join Users U on V.userId = U.userId)
WHERE status = 'ACTIVE' and isShorts = 'N'
;

여기서 SELECT문 안의

(SELECT
      CASE
        when views<1000
        then concat(views,'회')
        when views<10000
        then concat(round(views/1000,1),'천 회')
        when views<100000000
        then concat(round(views/10000,1),'만 회')
        else concat(round(views/100000000,1),'억 회')
     END
    FROM (
        SELECT COUNT(*) AS views FROM Views
        WHERE Views.videoId = V.videoId
         ) Count)
    as 'view count'

이 조회수를 담당하는 부분이었다. 하지만, 이렇게 작성한다면 가독성이 많이 떨어진다고 생각했다. 그래서 변수를 사용하면 어떨까라는 생각을 하게되었다.

MySQL의 변수 사용

MySQL에서는 변수명 앞에 @을 붙인다.
거기에 쿼리문 안이 아니면 SET이라는 명령어를 사용한다.

SET @var = 3;
SET @Variable :=5;

과 같은 형식으로 변수를 세팅해서 사용하고, 실제 쿼리문에서도 해당 변수들을 사용하여, 변경 사항이 생겼을 때 SET 부분의 변수 값을 변경하는 방식으로 사용할 수 있다.

쿼리문 안에서의 변수 사용

그렇다면 맨 위에 있는 쿼리문을 SET을 사용하고 싶다.
이때, 쿼리문 안에서 사용하려면 어떻게 해야할까?

SELECT
    U.userId            AS 'channel PK',
    profilePicUrl       AS 'profile picture',
    userName            AS 'channel name',
    thumbnailUrl        AS 'video thumbnail',
    title               AS 'video title',
    videoUrl            AS 'video url',
    CASE
            when TIMESTAMPDIFF(SECOND ,V.createdAt,CURRENT_TIMESTAMP) < 60
            then concat(TIMESTAMPDIFF(SECOND ,V.createdAt,CURRENT_TIMESTAMP), '초 전')
            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(HOUR,V.createdAt,CURRENT_TIMESTAMP) < 48
            then '하루 전'
            when TIMESTAMPDIFF(DAY,V.createdAt,CURRENT_TIMESTAMP) < 30
            then concat(TIMESTAMPDIFF(DAY ,V.createdAt,CURRENT_TIMESTAMP), '일 전')
            when TIMESTAMPDIFF(MONTH ,V.createdAt,CURRENT_TIMESTAMP) < 12
            then concat(TIMESTAMPDIFF(MONTH ,V.createdAt,CURRENT_TIMESTAMP), '달 전')
            else concat(TIMESTAMPDIFF(YEAR,V.createdAt,CURRENT_TIMESTAMP), '년 전')
        end AS 'uploaded At',
   @views := (select count(*) from Views where Views.videoId = V.videoId) as '@views',
        case when @views < 1000
            then concat(@views,'회')
            when @views<10000
            then concat(round(@views/1000,1),'천 회')
            when @views < 100000000
            then concat(round(@views/10000,1),'만 회')
            else concat(round(@views/100000000,1),'억 회') end as 'view counts'
    playTime    AS 'user playtime',
    videoLength AS 'Video Length'
FROM
    (Videos V inner join Users U on V.userId = U.userId)
WHERE status = 'ACTIVE' and isShorts = 'N'
;

가 된다. 즉,

@views := (select count(*) from Views where Views.videoId = V.videoId) as '@views',
        case when @views < 1000
            then concat(@views,'회')
            when @views<10000
            then concat(round(@views/1000,1),'천 회')
            when @views < 100000000
            then concat(round(@views/10000,1),'만 회')
            else concat(round(@views/100000000,1),'억 회') end as 'video views'

위와 같이 코드가 바뀌어 조금 더 가독성이 올라간 것을 알 수 있다.