Oracle) Column 합치기 : ||, CONCAT() / Row 합치기 : LISTAGG()

• Column 합치기 https://gent.tistory.com/256
• Row 합치기 https://subbak2.com/26

--SELECT ft.movieCd ||'|'|| ft.movieNm  AS repMovie
/* -------------------------------------------------------------------------------------------- */
SELECT LISTAGG(ft.movieCd ||'|'|| ft.movieNm, ',') WITHIN GROUP (ORDER BY ROWNUM) AS repMovieList
    /* LISTAGG : ROWNUM으로 정렬한 것 인지. ft에서 최종 정렬한대로 출력하기 위함 */
/* -------------------------------------------------------------------------------------------- */
FROM (
    SELECT ROWNUM, iv4.* 
    FROM (
        SELECT iv3.peopleNm, iv3.updateDate, iv3.movieCd, iv3.movieNm
            ,iv3.commaCnt - REGEXP_COUNT(iv3.actorDetail, ',', iv3.peopleIdx) +1 AS actorOrder
            ,iv3.cntStarRating, iv3.avgStarRating
            ,iv3.actorDetail
        FROM (
            SELECT iv2.peopleNm, iv2.updateDate
                , m.movieCd, m.movieNm, m.actorDetail
                , REGEXP_INSTR(m.actorDetail, iv2.peopleNm) AS peopleIdx
                , REGEXP_COUNT(m.actorDetail, ',') AS commaCnt  
                , COUNT(s.starRating)  AS cntStarRating
                , ROUND(AVG(s.starRating) , 1) AS avgStarRating
            FROM (
                SELECT iv1.peopleCd, iv1.peopleNm, iv1.updateDate, REGEXP_SUBSTR(iv1.actorFilmos, '[^,]+', 1, level) actorFilmo
                FROM (
                    SELECT p.peopleCd, p.peopleNm, p.actorFilmos, p.updateDate 
                    FROM tbl_people p 
                    WHERE p.peopleCd= #{peopleCd}
                ) iv1
                CONNECT BY REGEXP_SUBSTR(iv1.actorFilmos, '[^,]+', 1, level) IS NOT NULL
                /* CONNECT BY는 WHERE 이전에 실행되므로 FROM절이 단일행이 아니면 전체 FULL SCAN하여 연삭속도 극히느려짐 */
            ) iv2
            INNER JOIN tbl_movie m
            ON (iv2.actorFilmo = m.movieCd)
            LEFT OUTER JOIN tbl_starRating s
            ON (m.movieCd = s.movieCd)
            GROUP BY iv2.peopleNm, iv2.updateDate, m.movieCd, m.movieNm, m.actorDetail
        ) iv3
        WHERE iv3.peopleIdx != 0 /* INSTR()은 찾고자 하는 문자열 없으면 0 반환 */
        ORDER BY actorOrder ASC /* 비중 기준 정렬 */
    ) iv4
    WHERE ROWNUM BETWEEN 1 AND 10 /* 비중 높은 출연작 10개 먼저 선정 */
    ORDER BY iv4.cntStarRating DESC, iv4.avgStarRating DESC, iv4.updateDate ASC /* 이후, 정렬 */
) ft
WHERE ROWNUM BETWEEN 1 AND 2

• 참고) LISTAGG()에서 ROWNUM을 기준으로 ORDER BY 했다는 사실 유념할 것.
  -> 서브쿼리에서 ORDER BY했던 정렬 순서대로 row 합치기 위함