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

SET GROUP_CONCAT_MAX_LEN = 150000;

-- 상품 이미지 정보 생성 (30초)
DROP TABLE TMP_EP_GOODS_IMG;
CREATE TABLE TMP_EP_GOODS_IMG AS
SELECT G.GOODS_CD
     , MIN(CONCAT('https://image.istyle24.com/Upload/ProductImage/',GI.SYS_IMG_NM)) AS IMAGE_LINK
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 = 'Y'
-- AND    G.GOODS_CD = 'A91M-SH121A'
GROUP  BY G.GOODS_CD
;
SELECT COUNT(*) FROM TMP_EP_GOODS_IMG;

-- 상품 추가이미지 정보 생성 (15초 소요)
DROP TABLE TMP_EP_GOODS_ADD_IMG;
CREATE TABLE TMP_EP_GOODS_ADD_IMG AS
SELECT GOODS_CD
     , GROUP_CONCAT(CONCAT('https://image.istyle24.com/Upload/ProductImage/',SYS_IMG_NM) SEPARATOR '|') AS ADD_IMAGE_LINK
FROM   (SELECT DISTINCT
               GOODS_CD
             , SYS_IMG_NM
        FROM   TB_GOODS_IMG
        WHERE  DEFAULT_IMG_YN = 'N'
        GROUP  BY GOODS_CD, SYS_IMG_NM
        HAVING COUNT(*) <= 4
       ) Z
GROUP  BY GOODS_CD
;
SELECT COUNT(*) FROM TMP_EP_GOODS_ADD_IMG;

-- 카드이벤트 정보 생성
DROP TABLE TMP_EP_CARD_EVENT;
-- CREATE TABLE TMP_EP_CARD_EVENT AS
-- SELECT CPC.MIN_PAY_AMT
--      , GROUP_CONCAT(DISTINCT
--                     CONCAT(FN_GET_CODE_NM('G941',CPT.PRMT_TARGET_CD),'^',
--                            CPC.MIN_NO_ITRT,'~',CPC.MAX_NO_ITRT)
--                     SEPARATOR '|') AS CARD_EVENT
-- FROM   TB_CARD_PROMOTION CP
--      , TB_CARD_PROMOTION_CONDITION CPC
--      , TB_CARD_PROMOTION_TARGET CPT
-- 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'
-- GROUP  BY CPC.MIN_PAY_AMT
-- ;
CREATE TABLE TMP_EP_CARD_EVENT AS
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 TMP_EP_CARD_EVENT;

-- 예약판매 정보 생성
-- SELECT GOODS_CD
--      , '예약판매' AS RES_SELL
-- FROM   TB_GOODS_RES_SELL
-- WHERE  DELV_RES_DT >= NOW()
-- AND    USE_YN = 'Y'
-- ;

-- 쿠폰 정보 생성 (47ms)
DROP TABLE TMP_EP_COUPON;
CREATE TABLE TMP_EP_COUPON AS
SELECT GOODS_CD
     , CONCAT(IFNULL(NORMAL_CPN,''),'^',IFNULL(AFLINK_CPN,'')) AS COUPON
FROM   (
        SELECT CR.REF_VAL            AS GOODS_CD
             , CASE WHEN IFNULL((SELECT COUNT(*)
                                 FROM   TB_COUPON_AF_CHANNEL
                                 WHERE  CPN_ID = C.CPN_ID
                                ),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((SELECT COUNT(*)
                                 FROM   TB_COUPON_AF_CHANNEL
                                 WHERE  CPN_ID = C.CPN_ID
                                ),0) > 0 AND C.DC_WAY = 'G240_11' THEN
                        C.DC_PVAL
                    ELSE
                        NULL
               END                   AS AFLINK_CPN /*제휴쿠폰*/
             , CASE WHEN IFNULL((SELECT COUNT(*)
                                 FROM   TB_COUPON_AF_CHANNEL
                                 WHERE  CPN_ID = C.CPN_ID
                                ),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 /*일반쿠폰할인금액*/
             , CASE WHEN IFNULL((SELECT COUNT(*)
                                 FROM   TB_COUPON_AF_CHANNEL
                                 WHERE  CPN_ID = C.CPN_ID
                                ),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                   AS ACPN_DC_AMT /*제휴쿠폰할인금액*/
             , RANK() OVER(PARTITION BY CR.REF_VAL
                           ORDER BY (CASE WHEN IFNULL((SELECT COUNT(*)
                                                       FROM   TB_COUPON_AF_CHANNEL
                                                       WHERE  CPN_ID = C.CPN_ID
                                                      ),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((SELECT COUNT(*)
                                                       FROM   TB_COUPON_AF_CHANNEL
                                                       WHERE  CPN_ID = C.CPN_ID
                                                      ),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
        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  1 = 1
-- AND    GOODS_CD = 'M211BLB35P'
AND    RK = 1
;
SELECT COUNT(*) FROM TMP_EP_COUPON;

-- 무이자이벤트 정보 생성
DROP TABLE TMP_EP_INTEREST_FREE_EVENT;
CREATE TABLE TMP_EP_INTEREST_FREE_EVENT AS
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 TMP_EP_INTEREST_FREE_EVENT;

-- EP네이버 생성
DROP TABLE TMP_EP_NAVER;
CREATE TABLE TMP_EP_NAVER AS
SELECT G.GOODS_CD                                             AS ID
     , CONCAT('[',CASE WHEN B.DISP_NM_LANG = 'EN' THEN B.BRAND_ENM ELSE B.BRAND_KNM END,'] ',
              G.GOODS_NM,
              IFNULL(G.GOODS_NUM,''))                         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 /*정상가*/
     , G.GOODS_STAT
     , CONCAT('https://www.style24.com/goods/detail/form?goodsCd=',
              G.GOODS_CD)                                     AS LINK
     , NULL                                                   AS MOBILE_LINK  /*PC URL과 다를 경우. 동일함으로 NULL로 처리*/
     , GI.IMAGE_LINK
     , GAI.ADD_IMAGE_LINK /*이미지4개까지(|로 구분)*/
     , SUBSTRING_INDEX(I.ITEMKIND_NM,'>',1)                   AS CATEGORY_NAME1
     , 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
     , 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
     , 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
--      , NULL                                                   AS NAVER_PRODUCT_ID
     , FN_GET_CODE_NM('G073',G.GOODS_GB)                      AS CONDITION1
     , 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           /*브랜드명*/
--      , (SELECT NI_CONTENT
--         FROM   TB_GOODS_NOTI_INFO
--         WHERE  GOODS_CD = G.GOODS_CD
--         AND    NI_ITEM_CD = 'G005_005'
--         LIMIT 1)                                              AS MAKER            /*제조사*/
     , IFNULL(FN_GET_CODE_NM('G076',G.ORIGIN_CD),G.ORIGIN_CD) AS ORIGIN          /*원산지*/
     , CE.CARD_EVENT      /*카드행사. 카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가 형식*/
     , G.GOODS_TNM                                            AS EVENT_WORDS     /*상품셀링문구*/
     , TEC.COUPON
     , 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 PNT             /*포인트. 스타일포인트^포인트금액 형식으로 표기*/
     , CONCAT(IFNULL(G.GOODS_SNM,''),
              CASE WHEN LENGTH(IFNULL(G.GOODS_SNM1,'')) = 0 THEN
                       ''
                   ELSE
                       '|'
              END,
              IFNULL(G.GOODS_SNM1,'')
             )                                                AS SEARCH_TAG
     , CASE WHEN G.MIN_ORD_QTY > 0 THEN
                G.MIN_ORD_QTY
            ELSE
                ''
       END                                                    AS MINIMUM_PURCHASE_QUANTITY
     , IFNULL(GS.REVIEW_REG_CNT,0)                            AS REVIEW_REG_CNT
     , IFNULL(CASE WHEN DFP.DELV_FEE_CRITE = 'G078_20' /*무료*/ THEN
                       0
                   ELSE /*조건부무료 또는 유료*/
                       CASE WHEN DFP.MIN_ORD_AMT <= 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
     , 'I'                                                    AS CLASS /*전체색인:I, 부분색인:전체색인 데이터랑 비교해서 없으면 I, 있는데 변경되었으면 U, 품절: D)*/
     , 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
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 TMP_EP_GOODS_IMG GI ON G.GOODS_CD = GI.GOODS_CD
LEFT OUTER JOIN TMP_EP_GOODS_ADD_IMG GAI ON G.GOODS_CD = GAI.GOODS_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 TMP_EP_CARD_EVENT CE ON G.GOODS_CD = CE.GOODS_CD
LEFT OUTER JOIN TMP_EP_COUPON TEC ON G.GOODS_CD = TEC.GOODS_CD
LEFT OUTER JOIN TMP_EP_INTEREST_FREE_EVENT IFE ON G.GOODS_CD = IFE.GOODS_CD
WHERE  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' /*성인상품아닌넘만*/
;
SELECT COUNT(*) FROM TMP_EP_NAVER;

-- SELECT ID, COUNT(*) AS CNT
-- FROM   TMP_EP_NAVER Z
-- GROUP  BY ID
-- HAVING COUNT(*) > 1
-- ;
-- 
-- SELECT *
-- FROM   TMP_EP_NAVER
-- WHERE  ID IN (
-- 'A91M-SH121A',
-- 'F99U-TS971B',
-- 'F75A-MM92ZA',
-- 'B195Z5210P',
-- 'F75U-TS94ZA',
-- 'F71M-CP02ZA',
-- 'O174TS001P',
-- 'F75M-DP942B',
-- 'F71U-TS18ZA',
-- 'F65M-DP909A'
-- )
-- ORDER  BY ID
-- ;