[완료]/*contentExpand.distr.selectExposureNewScheduleList*/

Postgresql / PPAS/Query tuning

[완료]/contentExpand.distr.selectExposureNewScheduleList/

원샷원따봉 2022. 8. 29. 14:17

[튜닝 결과]

튜닝 전 : 2.7초
튜닝 후 : 0.3초

개선효과 : 88%

[튜닝 내용]

튜닝 후 sql 줄번호 209 ~ 216 참고
조건을 초기 필터 조건으로 추가하여 GROUP BY 대상을 줄임. 218663 건을 70건으로 줄임

* 원본 쿼리

SELECT /*contentExpand.distr.selectExposureNewScheduleList*/
         *
          FROM(
           SELECT
          AA.SCHEDULE_ID,
          AA.SCHEDULE_TYPE,
          AA.SCHEDULE_TITLE,
          AA.SCREEN_TYPE_LIST,
          CASE WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I'
                   WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE = 'N' THEN 'N'
                   WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE  <>  'N' THEN 'M'
                   WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U'
                   ELSE ''
              END SCREEN_TYPE,
          AA.SCREEN_TYPE_LIST_NAME,
          AA.CONTENT_CNT::integer,
          AA.STATUS,
          AA.AUTH_YN,
          AA.NEW_SCHEDULE_YN,
          AA.APPROVE_DATE,
          AA.INSERT_DATE,
          AA.INSERT_ID,
          AA.INSERT_NAME,
          AA.UPDATE_DATE,
          AA.START_DATE,
          AA.START_DAY,
          AA.END_DATE,
          AA.ALBUM_ID,
          AA.ISSUED_ALBUM_ID,
          CASE WHEN AA.STATUS ='P' THEN '1' ELSE '2' END STATUS_SB,
          AA.COMPLETE_Y,
          AA.COMPLETE_N,
          AA.CUESHEET_ITEM_TYPE,
          CASE WHEN AA.SCHEDULE_TYPE = 'N' THEN '일반' ELSE '재배포' END SCHEDULE_TYPE_NM,
          CASE WHEN AA.STATUS = 'E' THEN '공정에러'
               WHEN AA.STATUS = 'N' THEN '등록'
               WHEN AA.STATUS = 'R' THEN '스케줄실행중'
               WHEN AA.STATUS = 'C' THEN '대기'
               WHEN AA.STATUS = 'A' THEN '적용'
               WHEN AA.STATUS = 'S' THEN '보류'
               WHEN AA.STATUS = 'Y' THEN '완료'
               WHEN AA.STATUS = 'X' THEN '임시저장'
               WHEN AA.STATUS = 'F' THEN '실패(취소)'
               WHEN AA.STATUS = 'P' THEN '부분승인'
               ELSE '' END STATUS_NM,
              AA.EXCEPT_STATUS,
              AA.EXCEPT_CNT::integer
        FROM (
          SELECT
            A.SCHEDULE_ID,
            A.SCHEDULE_TYPE,
COALESCE(A.SCHEDULE_TITLE, '신규스케줄(컨텐츠명 존재하지 않음)') AS SCHEDULE_TITLE,
            A.SCREEN_TYPE_LIST,
            RTRIM(
CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 1, 1)  = '1' THEN  'I/'  ELSE '' END ||
   CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 2, 1)  = '1' THEN  'N/'  ELSE '' END ||
   CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 3, 1)  = '1' THEN  'U/'  ELSE '' END, '/'
                ) AS SCREEN_TYPE_LIST_NAME,
( SELECT COUNT(ZZ.ALBUM_ID)
                  FROM imcsuser.PT_LW_SCHE_DTL ZZ
                WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                ) AS CONTENT_CNT,
            CASE
     WHEN A.CHECK_STATE = 'E' THEN 'E'
     WHEN A.EXCEPT_YN = 'Y' THEN 'P'
    ELSE
        CASE WHEN A.STATUS = 'R'
            THEN
                CASE WHEN A.CHECK_STATE = 'Y'
                    THEN
                        CASE
                            WHEN A.AUTH_YN = 'N' THEN 'C'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                        END
                ELSE
                    CASE
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                    END
              END
        ELSE A.STATUS
        END
    END STATUS,
            CASE
     WHEN A.CHECK_STATE = 'E' THEN 'E'
    ELSE
        CASE WHEN A.STATUS = 'R'
            THEN
                CASE WHEN A.CHECK_STATE = 'Y'
                    THEN
                        CASE
                            WHEN A.AUTH_YN = 'N' THEN 'C'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                        END
                ELSE
                    CASE
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                    END
              END
        ELSE A.STATUS
        END
    END EXCEPT_STATUS,
            A.AUTH_YN,
            A.NEW_SCHEDULE_YN,
            A.APPROVE_DATE,
            A.INSERT_DATE,
            A.INSERT_ID,
            (SELECT USER_NAME FROM imcsuser.PT_LW_USER LU WHERE LU.USER_ID = A.INSERT_ID) INSERT_NAME,
            A.UPDATE_DATE,
            A.START_DATE,
            A.START_DAY,
            A.END_DATE,
            A.ALBUM_ID,
            A.ISSUED_ALBUM_ID,
            CASE WHEN A.SCREEN_TYPE = 'I'
                 THEN (
                  SELECT COUNT(ZZ.ALBUM_ID)
                    FROM imcsuser.PT_LW_SCHE_DTL ZZ
                  WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                     AND ZZ.AUTH_IPTV_YN IN ('Y', 'F')
                  )
                 ELSE (
                  SELECT COUNT(ZZ.ALBUM_ID)
                    FROM imcsuser.PT_LW_SCHE_DTL ZZ
                  WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                     AND ZZ.AUTH_NSC_YN IN ('Y', 'F')
                  )
            END COMPLETE_Y,
            CASE WHEN A.SCREEN_TYPE = 'I'
                 THEN (
                  SELECT COUNT(ZZ.ALBUM_ID)
                    FROM imcsuser.PT_LW_SCHE_DTL ZZ
                  WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                     AND COALESCE(ZZ.AUTH_IPTV_YN, 'N') = 'N'
                  )
                 ELSE (
                  SELECT COUNT(ZZ.ALBUM_ID)
                    FROM imcsuser.PT_LW_SCHE_DTL ZZ
                  WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                     AND COALESCE(ZZ.AUTH_NSC_YN, 'N') = 'N'
                  )
            END COMPLETE_N,
            A.CUESHEET_ITEM_TYPE,
            A.EXCEPT_CNT
          FROM (
            SELECT
        MST.SCHEDULE_ID AS SCHEDULE_ID,
        AST.SCREEN_TYPE AS SCREEN_TYPE,
        MAX(MST.SCHEDULE_TYPE) AS SCHEDULE_TYPE,
MAX(CASE WHEN MST.SERIES_YN  = 'Y' THEN  MST.SERIES_IMCS_NAME  ELSE DTL.CONTENTS_NAME END) AS SCHEDULE_TITLE,
   MAX(CASE WHEN AST.SCREEN_TYPE  = 'I' THEN  '10'  ELSE '01' END || CASE WHEN NULLIF(AST.UFLIX_FLAG,'')  IS NULL THEN  '0'  ELSE '1' END) AS SCREEN_TYPE_LIST,
        MAX(MST.GENRE_LARGE) AS GENRE_LARGE,
        MAX(MST.GENRE_MID) AS GENRE_MID,
        MAX(MST.GENRE_SMALL) AS GENRE_SMALL,
        MAX(MST.SERIES_ID_IMCS) AS SERIES_ID_IMCS,
        MAX(MST.SERIES_IMCS_NAME) AS SERIES_IMCS_NAME,
        MAX(MST.STATUS) AS STATUS,
        CASE WHEN AST.SCREEN_TYPE = 'I'
         THEN MIN(COALESCE(PRC.CHECK_IPTV_STATE, 'N'))
         ELSE MIN(COALESCE(PRC.CHECK_NSC_STATE, 'N'))
        END CHECK_STATE,
CASE WHEN AST.SCREEN_TYPE = 'I'
THEN MAX(COALESCE(CASE WHEN DTL.AUTH_IPTV_YN = 'Y' THEN '' WHEN DTL.AUTH_IPTV_YN  = 'F' THEN  ''  ELSE PRC.CHECK_IPTV_STATE END, 'N'))
               ELSE MAX(COALESCE(CASE WHEN DTL.AUTH_NSC_YN = 'Y' THEN  ''  WHEN DTL.AUTH_NSC_YN  ='F' THEN  ''  ELSE PRC.CHECK_NSC_STATE END, 'N'))
        END CHECK_STATE_MAX,
CASE WHEN AST.SCREEN_TYPE = 'I'
THEN MIN(CASE WHEN PRC.VIEW_FLAG  = 'I' THEN  'Y'  WHEN PRC.VIEW_FLAG  = 'Y' THEN  'Y'  ELSE 'N' END)
ELSE MIN(CASE WHEN PRC.VIEW_FLAG  = 'N' THEN  'Y'  WHEN PRC.VIEW_FLAG  = 'Y' THEN  'Y'  ELSE 'N' END)
        END VIEW_FLAG,
        CASE
            WHEN AST.SCREEN_TYPE = 'I'
            THEN MIN(COALESCE(DTL.AUTH_IPTV_YN, 'N'))
        ELSE MIN(COALESCE(DTL.AUTH_NSC_YN, 'N'))
        END AUTH_YN,
        MAX('Y') AS NEW_SCHEDULE_YN,
        MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS APPROVE_DATE,
        MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS START_DATE,
        MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYYMMDD')) AS START_DAY,
        MAX(TO_CHAR(TO_DATE(AST.SVC_END_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS END_DATE,
        MAX(TO_CHAR(TO_TIMESTAMP(MST.INSERT_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS INSERT_DATE,
        MAX(MST.INSERT_ID)  AS INSERT_ID,
        MAX(TO_CHAR(TO_TIMESTAMP(MST.UPDATE_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS UPDATE_DATE,
        MAX(MST.UPDATE_ID),
        MAX(DTL.ALBUM_ID) AS ALBUM_ID,
        MAX(DTL.ISSUED_ALBUM_ID) AS ISSUED_ALBUM_ID,
        MAX(AST.EXCEPT_YN) AS EXCEPT_YN,
        (SELECT COUNT(*) FROM imcsuser.PT_LW_SCHE_ASSET AA WHERE MST.SCHEDULE_ID = AA.SCHEDULE_ID AND AA.EXCEPT_YN = 'Y') AS EXCEPT_CNT,
COALESCE(MAX(DTL.CUESHEET_ITEM_TYPE),'N') AS CUESHEET_ITEM_TYPE
            FROM
imcsuser.PT_LW_SCHE_MST MST, imcsuser.PT_LW_SCHE_ASSET AST, imcsuser.PT_LW_SCHE_DTL DTL, imcsuser.PT_LW_SCHE_PROCESS_INFO PRC
            WHERE MST.SCHEDULE_ID = AST.SCHEDULE_ID
            AND MST.SCHEDULE_ID = DTL.SCHEDULE_ID
            AND MST.SCHEDULE_ID = PRC.SCHEDULE_ID
            AND DTL.SCHEDULE_ID = AST.SCHEDULE_ID
            AND DTL.SEQ_NO = AST.SEQ_NO
            AND DTL.SEQ_NO = PRC.SEQ_NO
            AND MST.STATUS != 'X'

            AND PRC.PROGRAMMED IN ('M','A','Y')


   AND AST.SVC_START_DATE BETWEEN '20200801' AND '20210801'






            GROUP BY MST.SCHEDULE_ID, AST.SCREEN_TYPE
          ) A

        ) AA

            WHERE AA.STATUS = UPPER('P')

    )BB
    ORDER BY BB.STATUS_SB, BB.INSERT_DATE DESC, BB.SCREEN_TYPE_LIST DESC

* 튜닝쿼리

SELECT /*contentExpand.distr.selectExposureNewScheduleList*/
    *
  FROM(
    SELECT
      AA.SCHEDULE_ID,
      AA.SCHEDULE_TYPE,
      AA.SCHEDULE_TITLE,
      AA.SCREEN_TYPE_LIST,
      CASE WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I'
           WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE = 'N' THEN 'N'
           WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE  <>  'N' THEN 'M'
           WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U'
           ELSE ''
      END SCREEN_TYPE,
      AA.SCREEN_TYPE_LIST_NAME,
      AA.CONTENT_CNT::integer,
      AA.STATUS,
      AA.AUTH_YN,
      AA.NEW_SCHEDULE_YN,
      AA.APPROVE_DATE,
      AA.INSERT_DATE,
      AA.INSERT_ID,
      AA.INSERT_NAME,
      AA.UPDATE_DATE,
      AA.START_DATE,
      AA.START_DAY,
      AA.END_DATE,
      AA.ALBUM_ID,
      AA.ISSUED_ALBUM_ID,
      CASE WHEN AA.STATUS ='P' THEN '1' ELSE '2' END STATUS_SB,
      AA.COMPLETE_Y,
      AA.COMPLETE_N,
      AA.CUESHEET_ITEM_TYPE,
      CASE WHEN AA.SCHEDULE_TYPE = 'N' THEN '일반' ELSE '재배포' END SCHEDULE_TYPE_NM,
      CASE WHEN AA.STATUS = 'E' THEN '공정에러'
           WHEN AA.STATUS = 'N' THEN '등록'
           WHEN AA.STATUS = 'R' THEN '스케줄실행중'
           WHEN AA.STATUS = 'C' THEN '대기'
           WHEN AA.STATUS = 'A' THEN '적용'
           WHEN AA.STATUS = 'S' THEN '보류'
           WHEN AA.STATUS = 'Y' THEN '완료'
           WHEN AA.STATUS = 'X' THEN '임시저장'
           WHEN AA.STATUS = 'F' THEN '실패(취소)'
           WHEN AA.STATUS = 'P' THEN '부분승인'
           ELSE '' END STATUS_NM,
      AA.EXCEPT_STATUS,
      AA.EXCEPT_CNT::integer
    FROM (
      SELECT
        A.SCHEDULE_ID,
        A.SCHEDULE_TYPE,
        COALESCE(A.SCHEDULE_TITLE, '신규스케줄(컨텐츠명 존재하지 않음)') AS SCHEDULE_TITLE,
        A.SCREEN_TYPE_LIST,
        RTRIM(
            CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 1, 1)  = '1' THEN  'I/'  ELSE '' END ||
            CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 2, 1)  = '1' THEN  'N/'  ELSE '' END ||
            CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 3, 1)  = '1' THEN  'U/'  ELSE '' END, '/'
            ) AS SCREEN_TYPE_LIST_NAME,
        ( SELECT COUNT(ZZ.ALBUM_ID)
              FROM imcsuser.PT_LW_SCHE_DTL ZZ
             WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
        ) AS CONTENT_CNT,
        CASE
            WHEN A.CHECK_STATE = 'E' THEN 'E'
            WHEN A.EXCEPT_YN = 'Y' THEN 'P'
        ELSE
            CASE WHEN A.STATUS = 'R'
                THEN
                CASE WHEN A.CHECK_STATE = 'Y'
                    THEN
                        CASE
                            WHEN A.AUTH_YN = 'N' THEN 'C'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                        END
                ELSE
                    CASE
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                    END
                END
            ELSE A.STATUS
            END
        END STATUS,
        CASE
            WHEN A.CHECK_STATE = 'E' THEN 'E'
        ELSE
            CASE WHEN A.STATUS = 'R'
                THEN
                CASE WHEN A.CHECK_STATE = 'Y'
                    THEN
                        CASE
                            WHEN A.AUTH_YN = 'N' THEN 'C'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                            WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                        END
                ELSE
                    CASE
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A'
                        WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y'
                        ELSE A.STATUS
                    END
                END
            ELSE A.STATUS
            END
        END EXCEPT_STATUS,
        A.AUTH_YN,
        A.NEW_SCHEDULE_YN,
        A.APPROVE_DATE,
        A.INSERT_DATE,
        A.INSERT_ID,
        (SELECT USER_NAME FROM imcsuser.PT_LW_USER LU WHERE LU.USER_ID = A.INSERT_ID) INSERT_NAME,
        A.UPDATE_DATE,
        A.START_DATE,
        A.START_DAY,
        A.END_DATE,
        A.ALBUM_ID,
        A.ISSUED_ALBUM_ID,
        CASE WHEN A.SCREEN_TYPE = 'I'
             THEN (
                    SELECT COUNT(ZZ.ALBUM_ID)
                      FROM imcsuser.PT_LW_SCHE_DTL ZZ
                     WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                       AND ZZ.AUTH_IPTV_YN IN ('Y', 'F')
                    )
             ELSE (
                    SELECT COUNT(ZZ.ALBUM_ID)
                      FROM imcsuser.PT_LW_SCHE_DTL ZZ
                     WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                       AND ZZ.AUTH_NSC_YN IN ('Y', 'F')
                    )
        END COMPLETE_Y,
        CASE WHEN A.SCREEN_TYPE = 'I'
             THEN (
                    SELECT COUNT(ZZ.ALBUM_ID)
                      FROM imcsuser.PT_LW_SCHE_DTL ZZ
                     WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                       AND COALESCE(ZZ.AUTH_IPTV_YN, 'N') = 'N'
                    )
             ELSE (
                    SELECT COUNT(ZZ.ALBUM_ID)
                      FROM imcsuser.PT_LW_SCHE_DTL ZZ
                     WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID
                       AND COALESCE(ZZ.AUTH_NSC_YN, 'N') = 'N'
                    )
        END COMPLETE_N,
        A.CUESHEET_ITEM_TYPE,
        A.EXCEPT_CNT
      FROM (
        SELECT
            MST.SCHEDULE_ID AS SCHEDULE_ID,
            AST.SCREEN_TYPE AS SCREEN_TYPE,
            MAX(MST.SCHEDULE_TYPE) AS SCHEDULE_TYPE,
            MAX(CASE WHEN MST.SERIES_YN  = 'Y' THEN  MST.SERIES_IMCS_NAME  ELSE DTL.CONTENTS_NAME END) AS SCHEDULE_TITLE,
            MAX(CASE WHEN AST.SCREEN_TYPE  = 'I' THEN  '10'  ELSE '01' END || CASE WHEN NULLIF(AST.UFLIX_FLAG,'')  IS NULL THEN  '0'  ELSE '1' END) AS SCREEN_TYPE_LIST,
            MAX(MST.GENRE_LARGE) AS GENRE_LARGE,
            MAX(MST.GENRE_MID) AS GENRE_MID,
            MAX(MST.GENRE_SMALL) AS GENRE_SMALL,
            MAX(MST.SERIES_ID_IMCS) AS SERIES_ID_IMCS,
            MAX(MST.SERIES_IMCS_NAME) AS SERIES_IMCS_NAME,
            MAX(MST.STATUS) AS STATUS,
            CASE WHEN AST.SCREEN_TYPE = 'I'
                THEN MIN(COALESCE(PRC.CHECK_IPTV_STATE, 'N'))
                ELSE MIN(COALESCE(PRC.CHECK_NSC_STATE, 'N'))
            END CHECK_STATE,
            CASE WHEN AST.SCREEN_TYPE = 'I'
                THEN MAX(COALESCE(CASE WHEN DTL.AUTH_IPTV_YN = 'Y' THEN '' WHEN DTL.AUTH_IPTV_YN  = 'F' THEN  ''  ELSE PRC.CHECK_IPTV_STATE END, 'N'))
                ELSE MAX(COALESCE(CASE WHEN DTL.AUTH_NSC_YN = 'Y' THEN  ''  WHEN DTL.AUTH_NSC_YN  ='F' THEN  ''  ELSE PRC.CHECK_NSC_STATE END, 'N'))
            END CHECK_STATE_MAX,
            CASE WHEN AST.SCREEN_TYPE = 'I'
                THEN MIN(CASE WHEN PRC.VIEW_FLAG  = 'I' THEN  'Y'  WHEN PRC.VIEW_FLAG  = 'Y' THEN  'Y'  ELSE 'N' END)
                ELSE MIN(CASE WHEN PRC.VIEW_FLAG  = 'N' THEN  'Y'  WHEN PRC.VIEW_FLAG  = 'Y' THEN  'Y'  ELSE 'N' END)
            END VIEW_FLAG,
            CASE
                WHEN AST.SCREEN_TYPE = 'I'
                THEN MIN(COALESCE(DTL.AUTH_IPTV_YN, 'N'))
            ELSE MIN(COALESCE(DTL.AUTH_NSC_YN, 'N'))
            END AUTH_YN,
            MAX('Y') AS NEW_SCHEDULE_YN,
            MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS APPROVE_DATE,
            MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS START_DATE,
            MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYYMMDD')) AS START_DAY,
            MAX(TO_CHAR(TO_DATE(AST.SVC_END_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS END_DATE,
            MAX(TO_CHAR(TO_TIMESTAMP(MST.INSERT_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS INSERT_DATE,
            MAX(MST.INSERT_ID)  AS INSERT_ID,
            MAX(TO_CHAR(TO_TIMESTAMP(MST.UPDATE_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS UPDATE_DATE,
            MAX(MST.UPDATE_ID),
            MAX(DTL.ALBUM_ID) AS ALBUM_ID,
            MAX(DTL.ISSUED_ALBUM_ID) AS ISSUED_ALBUM_ID,
            MAX(AST.EXCEPT_YN) AS EXCEPT_YN,
            (SELECT COUNT(*) FROM imcsuser.PT_LW_SCHE_ASSET AA WHERE MST.SCHEDULE_ID = AA.SCHEDULE_ID AND AA.EXCEPT_YN = 'Y') AS EXCEPT_CNT,
            COALESCE(MAX(DTL.CUESHEET_ITEM_TYPE),'N') AS CUESHEET_ITEM_TYPE
        FROM
            imcsuser.PT_LW_SCHE_MST MST, imcsuser.PT_LW_SCHE_ASSET AST, imcsuser.PT_LW_SCHE_DTL DTL, imcsuser.PT_LW_SCHE_PROCESS_INFO PRC
        WHERE MST.SCHEDULE_ID = AST.SCHEDULE_ID
        AND MST.SCHEDULE_ID = DTL.SCHEDULE_ID
        AND MST.SCHEDULE_ID = PRC.SCHEDULE_ID
        AND DTL.SCHEDULE_ID = AST.SCHEDULE_ID
        AND DTL.SEQ_NO = AST.SEQ_NO
        AND DTL.SEQ_NO = PRC.SEQ_NO
        AND MST.STATUS != 'X'
        AND PRC.PROGRAMMED IN ('M','A','Y')
        AND AST.SVC_START_DATE BETWEEN '20200801' AND '20210801'
        /* 마지막에 조건으로 추가되는 것을 초기 조건으로 변경 */
        AND AST.EXCEPT_YN = 'Y'
        GROUP BY MST.SCHEDULE_ID, AST.SCREEN_TYPE
      ) A
    ) AA
      /* 해당 조건을 초기 필터 조건으로 추가하여 GROUP BY 대상을 줄임. 218663 건을 70건으로 줄임.
        WHERE AA.STATUS = UPPER('P')
      */
)BB
ORDER BY BB.STATUS_SB, BB.INSERT_DATE DESC, BB.SCREEN_TYPE_LIST DESC
;

저작자표시 비영리 변경금지 (새창열림)