style24.doc/산출물/4.구축/데이터처리/네이버EP생성.sql

SET GROUP_CONCAT_MAX_LEN = 150000;

-- 상품 이미지 정보 생성 (30초)
TRUNCATE TABLE TB_EP_GOODS_IMG;
INSERT INTO TB_EP_GOODS_IMG (
       GOODS_CD
     , IMAGE_LINK
)
SELECT G.GOODS_CD
     , CONCAT('https://image.istyle24.com/Upload/ProductImage/',GI.SYS_IMG_NM) AS IMAGE_LINK
FROM   TB_GOODS G
     , (
        SELECT GOODS_CD
             , COLOR_CD
             , MIN(SYS_IMG_NM) AS SYS_IMG_NM
        FROM   TB_GOODS_IMG
        WHERE  DEFAULT_IMG_YN = 'Y'
        GROUP BY GOODS_CD, COLOR_CD
       ) GI
WHERE  G.GOODS_CD = GI.GOODS_CD
AND    IF(G.SELF_GOODS_YN = 'N','XX',G.MAIN_COLOR_CD) = GI.COLOR_CD
;

SELECT COUNT(*) FROM TB_EP_GOODS_IMG;

-- 상품 추가이미지 정보 생성 (15초 소요)
TRUNCATE TABLE TB_EP_GOODS_ADD_IMG;
INSERT INTO TB_EP_GOODS_ADD_IMG (
       GOODS_CD
     , ADD_IMAGE_LINK
)
SELECT GOODS_CD
     , GROUP_CONCAT(CONCAT('https://image.istyle24.com/Upload/ProductImage/',SYS_IMG_NM) SEPARATOR '|') AS ADD_IMAGE_LINK
FROM   (SELECT GI.GOODS_CD
             , GI.SYS_IMG_NM
             , RANK() OVER(PARTITION BY GI.GOODS_CD
                           ORDER BY GI.DISP_ORD, GI.SYS_IMG_NM) AS RK
        FROM   TB_GOODS G
             , TB_GOODS_IMG GI
        WHERE  G.GOODS_CD = GI.GOODS_CD
        AND    IF(G.SELF_GOODS_YN = 'N','XX',G.MAIN_COLOR_CD) = GI.COLOR_CD
        AND    GI.DEFAULT_IMG_YN = 'N'
       ) Z
WHERE  RK <= 4
GROUP  BY GOODS_CD
;

SELECT COUNT(*) FROM TB_EP_GOODS_ADD_IMG;

-- 카드이벤트 정보 생성
TRUNCATE TABLE TB_EP_CARD_EVENT;
INSERT INTO TB_EP_CARD_EVENT (
       GOODS_CD
     , CARD_EVENT
)
SELECT G.GOODS_CD
     , GROUP_CONCAT(DISTINCT
                    CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                           (G.CURR_PRICE
                            -
                            CASE WHEN CPC.DC_WAY = 'G240_10' /*정액할인*/ THEN CPC.DC_VAL
                                 ELSE /*정율할인*/ CAST(G.CURR_PRICE * CPC.DC_VAL / 100 AS SIGNED INTEGER)
                            END))
                    SEPARATOR '|') AS CARD_EVENT
FROM   TB_CARD_PROMOTION CP
     , TB_CARD_PROMOTION_CONDITION CPC
     , TB_CARD_PROMOTION_TARGET CPT
     , (
        SELECT G.GOODS_CD
             , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS CURR_PRICE
        FROM   TB_GOODS G
             , TB_GOODS_BENEFIT_PRICE GBP
        WHERE  G.GOODS_CD = GBP.GOODS_CD
       ) G
WHERE  CP.CARD_PRMT_SQ = CPC.CARD_PRMT_SQ
AND    CPC.CARD_PRMT_CDT_SQ = CPT.CARD_PRMT_CDT_SQ
AND    CP.PRMT_GB = 'A' /*카드할인*/
AND    CP.DC_GB = '1' /*즉시할인*/
AND    CP.PRMT_STD <= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
AND    CP.PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
AND    CP.DISP_YN = 'Y'
AND    CPC.MIN_PAY_AMT < G.CURR_PRICE
GROUP  BY G.GOODS_CD
;

SELECT COUNT(*) FROM TB_EP_CARD_EVENT;

-- 쿠폰 정보 생성 (47ms)
TRUNCATE TABLE TB_EP_COUPON;
INSERT INTO TB_EP_COUPON (
       GOODS_CD
     , COUPON
)
SELECT GOODS_CD
     , CONCAT(IFNULL(NORMAL_CPN,''),IF(AFLINK_CPN11 IS NULL,'',CONCAT('^',AFLINK_CPN11)),IF(AFLINK_CPN10 IS NULL,'',CONCAT('^',AFLINK_CPN10))) AS COUPON
FROM   (
        SELECT CR.REF_VAL            AS GOODS_CD
             , CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN
                        CONCAT(C.DC_PVAL,CASE WHEN C.DC_WAY = 'G240_10' THEN '원' ELSE '%' END)
                    ELSE
                        NULL
               END                   AS NORMAL_CPN /*일반쿠폰*/
             , CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_10' THEN
                        C.DC_PVAL
                    ELSE
                        NULL
               END                   AS AFLINK_CPN10 /*정액제휴쿠폰*/
             , CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_11' THEN
                        C.DC_PVAL
                    ELSE
                        NULL
               END                   AS AFLINK_CPN11 /*정율제휴쿠폰*/
             , CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN
                        CASE WHEN C.DC_WAY = 'G240_10' THEN C.DC_PVAL
                             ELSE IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100
                        END
                    ELSE
                        0
               END                   AS NCPN_DC_AMT /*일반쿠폰할인금액*/
             , RANK() OVER(PARTITION BY CR.REF_VAL
                           ORDER BY (CASE WHEN IFNULL(CAC.CNT,0) = 0 THEN
                                              CASE WHEN C.DC_WAY = 'G240_10' THEN C.DC_PVAL
                                                   ELSE IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100
                                              END
                                          ELSE
                                              0
                                     END) DESC,
                                    (CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_10' THEN
                                              C.DC_PVAL
                                          ELSE
                                              0
                                     END) DESC,
                                    (CASE WHEN IFNULL(CAC.CNT,0) > 0 AND C.DC_WAY = 'G240_11' THEN
                                              IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * C.DC_PVAL / 100
                                          ELSE
                                              0
                                     END) DESC
                          )          AS RK
        FROM   TB_COUPON C
        INNER JOIN TB_COUPON_REFVAL CR ON C.CPN_ID = CR.CPN_ID
        INNER JOIN TB_GOODS G ON CR.REF_VAL = G.GOODS_CD
        LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
        LEFT OUTER JOIN (
                         SELECT CPN_ID
                              , COUNT(*) AS CNT
                         FROM   TB_COUPON_AF_CHANNEL
                         WHERE  AF_LINK_CD IN (SELECT AF_LINK_CD
                                               FROM   TB_AF_LINK
                                               WHERE  AF_CHANNEL = 'G053_02' /*네이버*/
                                               AND    USE_YN = 'Y'
                                              )
                         GROUP  BY CPN_ID
                        ) CAC ON C.CPN_ID = CAC.CPN_ID
        WHERE  C.SITE_CD = 'G000_10'
        AND    C.CPN_STAT = 'G232_11' /*진행쿠폰*/
        AND    C.CPN_TYPE IN ('G230_11','G230_20') /*상품쿠폰,주문서쿠폰*/
        AND    (
                (C.PD_GB = 'D' AND NOW() < DATE_ADD(NOW(), INTERVAL C.AVAIL_DAYS DAY))
                OR
                (C.PD_GB = 'P' AND NOW() BETWEEN C.AVAIL_STDT AND C.AVAIL_EDDT)
               )
        AND    C.APPLY_SCOPE = 'I' /*개별쿠폰*/
        AND    CR.CPN_TYPE IN ('G230_11','G230_20') /*상품쿠폰,주문서쿠폰*/
        AND    CR.CPN_TARGET = 'G260_10' /*상품쿠폰*/
        AND    CR.DEL_YN = 'N'
        AND    NOT EXISTS (SELECT 1
                           FROM   TB_COUPON_REFVAL
                           WHERE  CPN_ID = CR.CPN_ID
                           AND    CPN_TARGET = 'G260_14' /*제외상품*/
                           AND    REF_VAL = CR.REF_VAL
                           AND    DEL_YN = 'N'
                          )
       ) Z
WHERE  RK = 1
AND    (
        NORMAL_CPN IS NOT NULL
        OR
        AFLINK_CPN10 IS NOT NULL
        OR
        AFLINK_CPN11 IS NOT NULL
       )
;

SELECT COUNT(*) FROM TB_EP_COUPON;

-- 무이자이벤트 정보 생성
TRUNCATE TABLE TB_EP_INTEREST_FREE_EVENT;
INSERT INTO TB_EP_INTEREST_FREE_EVENT (
       GOODS_CD
     , INTEREST_FREE_EVENT
)
SELECT G.GOODS_CD
     , GROUP_CONCAT(DISTINCT
                    CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
                           CPC.MIN_NO_ITRT,'~',CPC.MAX_NO_ITRT)
                    SEPARATOR '|') AS INTEREST_FREE_EVENT /*무이자이벤트정보*/
FROM   TB_CARD_PROMOTION CP
     , TB_CARD_PROMOTION_CONDITION CPC
     , TB_CARD_PROMOTION_TARGET CPT
     , (
        SELECT G.GOODS_CD
             , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS CURR_PRICE
        FROM   TB_GOODS G
             , TB_GOODS_BENEFIT_PRICE GBP
        WHERE  G.GOODS_CD = GBP.GOODS_CD
       ) G
WHERE  CP.CARD_PRMT_SQ = CPC.CARD_PRMT_SQ
AND    CPC.CARD_PRMT_CDT_SQ = CPT.CARD_PRMT_CDT_SQ
AND    CP.PRMT_GB = 'B' /*무이자할부*/
AND    CP.PRMT_STD <= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
AND    CP.PRMT_EDD >= DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
AND    CP.DISP_YN = 'Y'
AND    CPC.MIN_PAY_AMT < G.CURR_PRICE
GROUP  BY G.GOODS_CD
;
SELECT COUNT(*) FROM TB_EP_INTEREST_FREE_EVENT;

-- 네이버 전체E 생성
TRUNCATE TABLE TB_EP_NAVER;
INSERT INTO TB_EP_NAVER (
       GB
     , ID
     , TITLE
     , PRICE_PC
     , PRICE_MOBILE
     , NORMAL_PRICE
     , LINK
     , MOBILE_LINK
     , IMAGE_LINK
     , ADD_IMAGE_LINK
     , CATEGORY_NAME1
     , CATEGORY_NAME2
     , CATEGORY_NAME3
     , CATEGORY_NAME4
     , NAVER_CATEGORY
     , GOODS_GB_NM
     , IMPORT_FLAG
     , PARALLEL_IMPORT
     , ORDER_MADE
     , PRODUCT_FLAG
     , ADULT
     , MANUFACTURE_DEFINE_NUMBER
     , BRAND
     , MAKER
     , ORIGIN
     , CARD_EVENT
     , EVENT_WORDS
     , COUPON
     , PARTNER_COUPON_DOWNLOAD
     , INTEREST_FREE_EVENT
     , POINT
     , SEARCH_TAG
     , MINIMUM_PURCHASE_QUANTITY
     , REVIEW_COUNT
     , SHIPPING
     , AGE_GROUP
     , GENDER
     , GOODS_STAT
     , SELF_MALL_YN
     , SELL_STDT
     , SELL_EDDT
     , UPD_GB
     , UPDATE_TIME
)
SELECT 'LAST'                                                 AS GB                       /*구분(PREV:이전, CURR:현재)*/
     , G.GOODS_CD                                             AS ID                       /*상품ID*/
     , SUBSTRING(CONCAT('[',CASE WHEN B.DISP_NM_LANG = 'EN' THEN B.BRAND_ENM ELSE B.BRAND_KNM END,'] ',
                        G.GOODS_NM,
                        IFNULL(G.GOODS_NUM,'')),1,100)        AS TITLE                    /*상품명*/
     , IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS PRICE_PC                 /*즉시할인이적용된가격*/
     , IFNULL(GBP.MO_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY AS PRICE_MOBILE             /*즉시할인이적용된가격*/
     , G.LIST_PRICE                                           AS NORMAL_PRICE             /*정상가*/
     , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD)                      AS LINK                     /*PC상세URL*/
     , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',G.GOODS_CD)                      AS MOBILE_LINK              /*모바일상세URL*/
     , GI.IMAGE_LINK                                                                      /*이미지URL*/
     , GAI.ADD_IMAGE_LINK                                                                 /*추가이미지URL(4개까지. |로 구분)*/
     , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1)                   AS CATEGORY_NAME1           /*카테고리명1*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1)
       END                                                    AS CATEGORY_NAME2           /*카테고리명2*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',2),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1)
       END                                                    AS CATEGORY_NAME3           /*카테고리명3*/
     , CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',3),'>',-1) = SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1) THEN
                NULL
            ELSE
                SUBSTRING_INDEX(SUBSTRING_INDEX(I.ITEMKIND_NM,'>',4),'>',-1)
       END                                                    AS CATEGORY_NAME4            /*카테고리명4*/
     , EC.EP_CATE_CD                                          AS NAVER_CATEGORY            /*네이버카테고리*/
     , CC1.CD_NM                                              AS GOODS_GB_NM               /*상품구분명*/
     , G.FOREIGN_BUY_YN                                       AS IMPORT_FLAG               /*해외구매대행여부*/
     , G.PARALLEL_IMPORT_YN                                   AS PARALLEL_IMPORT           /*병행수입여부*/
     , G.ORDER_MADE_YN                                        AS ORDER_MADE                /*주문제작상품여부*/
     , IF(GRS.GOODS_CD IS NULL,NULL,'예약판매')               AS PRODUCT_FLAG              /*예약판매*/
     , G.ADULT_YN                                             AS ADULT                     /*성인여부*/
     , G.GOODS_NUM                                            AS MANUFACTURE_DEFINE_NUMBER /*제품코드*/
     , IF(B.DISP_NM_LANG = 'EN',B.BRAND_ENM,B.BRAND_KNM)      AS BRAND                     /*브랜드명*/
     , SUBSTRING(GNI.NI_CONTENT,1,50)                         AS MAKER                     /*제조사*/
     , CC2.CD_NM                                              AS ORIGIN                    /*원산지*/
     , CE.CARD_EVENT                                                                       /*카드행사*/
     , G.GOODS_TNM                                            AS EVENT_WORDS               /*상품셀링문구*/
     , TEC.COUPON                                                                          /*쿠폰*/
     , IF(TEC.COUPON IS NULL,NULL,'Y')                        AS PARTNER_COUPON_DOWNLOAD   /*쿠폰다운로드여부*/
     , IFE.INTEREST_FREE_EVENT                                                             /*무이자행사*/
     , CONCAT('스타일포인트','^',
              CAST(IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY * G.PNT_PRATE AS SIGNED INTEGER)
             )                                                AS POINT                     /*포인트*/
     , SUBSTRING(CONCAT(IFNULL(G.GOODS_SNM,''),
                        CASE WHEN LENGTH(IFNULL(G.GOODS_SNM1,'')) = 0 THEN
                                 ''
                             ELSE
                                 '|'
                        END,
                        IFNULL(G.GOODS_SNM1,'')
                       ),1,100)                               AS SEARCH_TAG                /*검색태그*/
     , CASE WHEN G.MIN_ORD_QTY > 0 THEN
                G.MIN_ORD_QTY
            ELSE
                NULL
       END                                                    AS MINIMUM_PURCHASE_QUANTITY /*최소구매금액*/
     , IFNULL(GS.REVIEW_REG_CNT,0)                            AS REVIEW_COUNT              /*상품평수*/
     , IFNULL(CASE WHEN DFP.DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
                       0
                   ELSE /*조건부무료 또는 유료*/
                       CASE WHEN DFP.MIN_ORD_AMT <![CDATA[<=]]> IFNULL(GBP.PC_CURR_PRICE,G.CURR_PRICE) * G.MIN_ORD_QTY THEN
                                0
                            ELSE
                                DFP.DELV_FEE
                       END
              END,0)                                          AS SHIPPING                  /*배송료*/
     , CASE WHEN SUBSTRING(I.ITEMKIND_CD,1,2) = '07' THEN '유아'
            WHEN SUBSTRING(I.ITEMKIND_CD,1,2) IN ('08','09','10') THEN '아동'
            WHEN SUBSTRING(I.ITEMKIND_CD,1,2) IN ('11','12','13') THEN '청소년'
            ELSE '성인'
       END                                                    AS AGE_GROUP                 /*나이*/
     , CASE WHEN G.SEX_GB = 'G007_F' THEN '여성'
            WHEN G.SEX_GB = 'G007_M' THEN '남성'
            ELSE '남여공용'
       END                                                    AS GENDER                    /*성별*/
     , G.GOODS_STAT
     , G.SELF_MALL_YN
     , G.SELL_STDT
     , G.SELL_EDDT
     , 'I'                                    AS UPD_GB
     , DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S') AS UPDATE_TIME /*변경일시*/
FROM   TB_GOODS G
INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
INNER JOIN TB_ITEMKIND I ON G.ITEMKIND_CD = I.ITEMKIND_CD
INNER JOIN TB_DELV_FEE_POLICY DFP ON G.DELV_FEE_CD = DFP.DELV_FEE_CD 
                                 AND G.SUPPLY_COMP_CD = DFP.SUPPLY_COMP_CD
INNER JOIN TB_EP_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
LEFT OUTER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD
LEFT OUTER JOIN TB_EP_GOODS_ADD_IMG GAI ON G.GOODS_CD = GAI.GOODS_CD
LEFT OUTER JOIN (
                 SELECT ITEMKIND_CD
                      , MIN(EP_CATE_CD) AS EP_CATE_CD
                 FROM   TB_EP_CATE
                 WHERE  EP_GB = '10' /*네이버*/
                 AND    USE_YN = 'Y'
                 GROUP  BY ITEMKIND_CD
                ) EC ON G.ITEMKIND_CD = EC.ITEMKIND_CD
LEFT OUTER JOIN TB_GOODS_RES_SELL GRS ON G.GOODS_CD = GRS.GOODS_CD
                                     AND GRS.DELV_RES_DT >= NOW()
                                     AND GRS.USE_YN = 'Y'
LEFT OUTER JOIN TB_EP_CARD_EVENT CE ON G.GOODS_CD = CE.GOODS_CD
LEFT OUTER JOIN TB_EP_COUPON TEC ON G.GOODS_CD = TEC.GOODS_CD
LEFT OUTER JOIN TB_EP_INTEREST_FREE_EVENT IFE ON G.GOODS_CD = IFE.GOODS_CD
LEFT OUTER JOIN TB_GOODS_NOTI_INFO GNI ON G.GOODS_CD = GNI.GOODS_CD
                                      AND GNI.NI_ITEM_CD = 'G005_005' /*제조사*/
LEFT OUTER JOIN TB_COMMON_CODE CC1 ON G.GOODS_GB = CC1.CD
                                 AND CC1.CD_GB = 'G073'
LEFT OUTER JOIN TB_COMMON_CODE CC2 ON G.ORIGIN_CD = CC2.CD
                                  AND CC2.CD_GB = 'G076'
WHERE  1 = 1
AND    G.GOODS_STAT = 'G008_90' /*승인완료상품*/
AND    G.SELF_MALL_YN = 'Y' /*몰노출상품*/
AND    G.SELL_STDT <= NOW() /*유효한 판매기간*/
AND    G.SELL_EDDT >= NOW() /*유효한 판매기간*/
AND    G.ADULT_YN = 'N' /*성인상품아닌넘만*/
AND    NOT EXISTS (SELECT 1
                   FROM   TB_GOODS_EP_SKIP
                   WHERE  GOODS_CD = G.GOODS_CD
                   AND    NOW() BETWEEN APPLY_STDT AND APPLY_EDDT
                  )
;

SELECT COUNT(*) FROM TB_EP_NAVER;