꿈꾸는 개발자, DBA 커뮤니티 구루비

MSSQL 연관 태그 질문드립니다.

by 튼실맨 [SQL Query] [2023.10.25 18:24:26]

23.png (23,387Bytes)

sdgdfg.png (313,540Bytes)

안녕하세요 이번에 연관동영상 관련해서 리스트 추출을 해야 하는데 너무 어려워 질문드립니다.ㅣ

일단 동영상 등록을 할 때 태그를 입력을 합니다. 개수는 무제한이구요

동영상 상세 페이지를 들어가면 오른쪽에 태그랑 관련 있는 연관 동영상이 5개가 나와야 합니다.

요구사항은

태그가 동영상 상세페이지를 들어갔는데 예를 들어 7개가 있다 가정한다면

A,B,C,D,E,F,G 이렇게라 생각하면 뒤에서 부터 검색을 합니다

G가 포함된 동영상을 검색하는데 5개미만이다 그러면

F 검색 얘도 미만이다

E 검색 얘가 5개가 이상이다 그러면 가장 최신에 올린 영상 5개를 추출해야 합니다.

머리로는 이해했는데 쿼리로 작성하려니 어려움이 많습니다

고수분들 도움 좀 주시면 감사하겠습니다 ㅜㅠ

by 마농 [2023.10.26 10:29:23]

SELECT TOP 5 *
  FROM (          SELECT TOP 5 * FROM t WHERE tag = 'G' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'F' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'E' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'D' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'C' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'B' ORDER BY dt DESC
        UNION ALL SELECT TOP 5 * FROM t WHERE tag = 'A' ORDER BY dt DESC
        ) a
 ORDER BY dt DESC
;

by 튼실맨 [2023.10.26 16:41:15]

WITH CATEGORY_MGR AS
(
SELECT
A.CATEGORY_SEQ
, A.CATEGORY_NAME
, A.UP_CATEGORY_SEQ
, ISNULL((SELECT B.CATEGORY_NAME
FROM TGMCMS_CATEGORY B
WHERE B.CATEGORY_SEQ = A.UP_CATEGORY_SEQ), 'ROOT') upCategoryName
, A.CATEGORY_LEVEL
, CONVERT(VARCHAR,CATEGORY_NAME) AS FULL_CATEGORY
, A.CATEGORY_USE
, A.CATEGORY_EXPOSE
, 1 lvl
FROM
TGMCMS_CATEGORY A
WHERE
A.UP_CATEGORY_SEQ = #{rootUpCategoryCode}
UNION ALL
SELECT
C.CATEGORY_SEQ
, C.CATEGORY_NAME
, C.UP_CATEGORY_SEQ
, ISNULL((SELECT D.CATEGORY_NAME
FROM TGMCMS_CATEGORY D
WHERE D.CATEGORY_SEQ = C.UP_CATEGORY_SEQ), 'ROOT') upCategoryName
, C.CATEGORY_LEVEL
, CONVERT(VARCHAR,CONCAT(CM.FULL_CATEGORY, ',' , C.CATEGORY_NAME)) AS FULL_CATEGORY
, C.CATEGORY_USE
, C.CATEGORY_EXPOSE
, lvl + 1 lvl
FROM
TGMCMS_CATEGORY C
INNER JOIN CATEGORY_MGR CM
ON C.UP_CATEGORY_SEQ = CM.CATEGORY_SEQ
)
SELECT
FULL_CATEGORY,
A.CONTENTS_ID,
A.CONTENTS_TITLE,
A.VIEW_CNT,
A.CORPORATE,
A.AFFILIATION1,
A.AFFILIATION2,
A.POSITION,
A.INDIVIDUAL,
(SELECT N.USER_NAME FROM TGMCMS_USER N WHERE N.USER_ID = A.REG_USER_ID) AS USER_NAME,
DATEDIFF(DAY, A.REG_DATE, GETDATE()) AS FEW_DAYS_AGO,
CONVERT(VARCHAR, A.REG_DATE, 23) AS REG_DATE,
ISNULL(RANDOM_IMG_INFO, STUFF(
REPR_IMG_ENCODING_FILE_PATH,
1,
CHARINDEX('/upload', REPR_IMG_ENCODING_FILE_PATH) + LEN('/upload') - 1,
''
) + REPR_IMG_ENCODING_FILE_NAME) AS CONTENTS_THUMBNAIL,
A.UPLOAD_TYPE,
A.CATEGORY_CD AS CATEGORY_SEQ,
A.EXTERNAL_URL,
TAG.TAG_ID,
TAG.TAG_NAME,
ST.STATUS,
(SELECT COUNT(P.CONTENTS_ID) FROM TGMCMS_USER_PICK P WHERE P.CONTENTS_ID = A.CONTENTS_ID) AS LIKE_CNT
<if test='contentsCate == "popularWeek" or contentsCate == "popularMonth"'>
,RV.LASTWEEK_VIEW
</if>
<if test='contentsCate == "latestview"'>
, C.RECORD_SEQ,
C.USER_ID,
C.VIEW_DATE
</if>
FROM TGMCMS_CONTENTS A
LEFT OUTER JOIN
(
SELECT
CONTENTS_ID,
STRING_AGG(B.TAG_ID, ', ') WITHIN GROUP (ORDER BY A.TAG_CONTENT_SEQ) AS TAG_ID,
STRING_AGG(B.TAG_NAME, ', ') WITHIN GROUP (ORDER BY A.TAG_CONTENT_SEQ) AS TAG_NAME
FROM TGMCMS_TAG_CONTENT A
INNER JOIN TGMCMS_TAG B
ON A.TAG_ID = B.TAG_ID
GROUP BY CONTENTS_ID
) TAG ON A.CONTENTS_ID = TAG.CONTENTS_ID
<include refid="contents_status_join" />
INNER JOIN
( SELECT * FROM
(
SELECT * FROM CATEGORY_MGR
) AS TEMP ) AS CATEGORY
ON A.CATEGORY_CD = CATEGORY.CATEGORY_SEQ
<if test="contentsCate == 'popularWeek' or contentsCate == 'popularMonth'">
LEFT OUTER JOIN
(
SELECT COUNT(B.CONTENTS_ID) AS LASTWEEK_VIEW,
B.CONTENTS_ID
FROM TGMCMS_CONTENTS AS A
LEFT OUTER JOIN TGMCMS_USER_RECORD_VIEW AS B ON A.CONTENTS_ID = B.CONTENTS_ID
WHERE 1=1
AND A.DEL_YN = 'N'
AND MEDIA_USE = 'Y'
<choose>
<when test="contentsCate == 'popularWeek'">
AND B.REG_DATE >= (SELECT CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 1 OR (DATEPART(WEEKDAY, GETDATE())= 2 AND FORMAT(GETDATE(),'HH') < '09') THEN DATEADD(WK,DATEDIFF(WK,0,DATEADD(WEEK,-2,GETDATE())),0)+'09:00:00.000' ELSE DATEADD(WK,DATEDIFF(WK,0,DATEADD(WEEK,-1,GETDATE())),0)+'09:00:00.000' END)
AND B.REG_DATE < (SELECT CASE WHEN DATEPART(WEEKDAY, GETDATE()) = 1 OR (DATEPART(WEEKDAY, GETDATE())= 2 AND FORMAT(GETDATE(),'HH') < '09') THEN DATEADD(WK,DATEDIFF(WK,0,DATEADD(WEEK,-1,GETDATE())),0)+'09:00:00.000' ELSE DATEADD(WK,DATEDIFF(WK,0,DATEADD(WEEK,-0,GETDATE())),0)+'09:00:00.000' END)
</when>
<when test="contentsCate == 'popularMonth'">
AND B.REG_DATE >= DATEADD(MONTH, DATEDIFF(MM,0,GETDATE())-1,0)
AND B.REG_DATE <= DATEADD(MS,-3,DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()), 0))
</when>
</choose>
GROUP BY B.CONTENTS_ID
) AS RV ON A.CONTENTS_ID = RV.CONTENTS_ID
</if>
<if test='contentsCate == "latestview"'>
RIGHT OUTER JOIN
( SELECT A.RECORD_SEQ
, B.USER_ID
, B.CONTENTS_ID
, B.VIEW_DATE
FROM TGMCMS_USER_RECORD_VIEW AS A
INNER JOIN (SELECT USER_ID
, CONTENTS_ID
, MAX(REG_DATE) AS VIEW_DATE
FROM TGMCMS_USER_RECORD_VIEW
WHERE USER_ID = #{userId}
GROUP BY USER_ID, CONTENTS_ID) AS B ON A.REG_DATE = B.VIEW_DATE
ORDER BY VIEW_DATE DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
) AS C ON A.CONTENTS_ID = C.CONTENTS_ID
</if>
WHERE A.MEDIA_USE = 'Y'
AND A.DEL_YN = 'N'
AND CATEGORY.CATEGORY_EXPOSE = 'Y'
<if test='contentsCate == "main"'>
AND MAJOR_CONTENTS_YN = 'Y'
</if>
<if test='contentsCate == "latest"'>
AND FORMAT(A.REG_DATE,'yyyy-MM') >= FORMAT(DATEADD(MONTH,-2,GETDATE()),'yyyy-MM')
AND A.REG_DATE <= CONCAT(EOMONTH(GETDATE()),' 23:59:59')
</if>
<if test='contentsCate == "popularWeek" or contentsCate == "popularMonth"'>
AND RV.LASTWEEK_VIEW IS NOT NULL
</if>

<choose>
<when test='contentsCate == "popularWeek" or contentsCate == "popularMonth"'>
ORDER BY RV.LASTWEEK_VIEW DESC, A.VIEW_CNT DESC, CONVERT(VARCHAR, A.REG_DATE, 120) DESC
</when>

<when test="contentsCate == 'latestview'">
ORDER BY CONVERT(VARCHAR,C.VIEW_DATE,120) DESC
</when>

<otherwise>
ORDER BY CONVERT(VARCHAR, A.REG_DATE, 120) DESC
</otherwise>
</choose>
<choose>
<when test='contentsCate == "latest" or contentsCate == "latestview"'>
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
</when>
<otherwise>
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
</otherwise>
</choose>

이게 지금 저희가 사용 중인 동영상 리스트를 불러오는 쿼리입니다.

제 생각은

SELECT

value

FROM tgmcms_contents

CROSS APPLY STRING_SPLIT(SEARCH_TAG,',')

WHERE CONTENTS_ID = ${동영상 번호};

이 쿼리로 태그들을 불러와서 하나씩 검색해서 리스트로 출력해야 할 것 같은데....어떻게 방법이 없을까요 선생님

by 튼실맨 [2023.10.26 16:44:16]

추가로 태그들을 전부 검색했는데 5개가 안 나올 시 최신 영상 5개만 추출하게 해야 할 것 같습니다.

by 마농 [2023.10.26 17:32:56]

SELECT TOP 5 *
  FROM (SELECT TOP 5
             , a.tag_name
             , d.contents_id
             , d.reg_date
          FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) rn
                     , value tag_name
                  FROM tgmcms_contents
                 CROSS APPLY STRING_SPLIT(search_tag,',')
                 WHERE contents_id = ${동영상 번호}
                ) a
         INNER JOIN tgmcms_tag b
            ON a.tag_name = b.tag_name
         INNER JOIN tgmcms_tag_content c
            ON b.tag_id = c.tag_id
         INNER JOIN tgmcms_contents d
            ON c.contents_id = d.contents_id
         ORDER BY a.rn DESC, d.reg_date DESC
         UNION ALL
        SELECT TOP 5
             , null tag_name
             , contents_id
             , reg_date
          FROM tgmcms_contents
         WHERE contents_id != ${동영상 번호}
         ORDER BY reg_date DESC
        ) a
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변