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

################################################################################
#EP상품이미지
################################################################################
DROP TABLE IF EXISTS TB_EP_GOODS_IMG RESTRICT;
CREATE TABLE TB_EP_GOODS_IMG (
  GOODS_CD    VARCHAR(20)   NOT NULL COMMENT '상품코드(상품)',
  IMAGE_LINK  VARCHAR(200)  COMMENT '이미지링크'
)
COMMENT 'EP상품이미지. EP상품이미지 생성에 의한 발생';

################################################################################
#EP상품추가이미지
################################################################################
DROP TABLE IF EXISTS TB_EP_GOODS_ADD_IMG RESTRICT;
CREATE TABLE TB_EP_GOODS_ADD_IMG (
  GOODS_CD        VARCHAR(20)    NOT NULL COMMENT '상품코드(상품)',
  ADD_IMAGE_LINK  VARCHAR(1000)  COMMENT '추가이미지링크(|로 구분. 최대4개)'
)
COMMENT 'EP상품추가이미지. EP상품추가이미지 생성에 의한 발생';

################################################################################
#EP카드행사
################################################################################
DROP TABLE IF EXISTS TB_EP_CARD_EVENT RESTRICT;
CREATE TABLE TB_EP_CARD_EVENT (
  GOODS_CD    VARCHAR(20)    NOT NULL COMMENT '상품코드(상품)',
  CARD_EVENT  VARCHAR(100)   COMMENT '카드행사'
)
COMMENT 'EP카드행사. EP카드행사 생성에 의한 발생';

################################################################################
#EP쿠폰
################################################################################
DROP TABLE IF EXISTS TB_EP_COUPON RESTRICT;
CREATE TABLE TB_EP_COUPON (
  GOODS_CD    VARCHAR(20)   NOT NULL COMMENT '상품코드(상품)',
  COUPON      VARCHAR(100)  COMMENT '할인쿠폰정보'
)
COMMENT 'EP쿠폰. EP쿠폰 생성에 의한 발생';

################################################################################
#EP무이자행사
################################################################################
DROP TABLE IF EXISTS TB_EP_INTEREST_FREE_EVENT RESTRICT;
CREATE TABLE TB_EP_INTEREST_FREE_EVENT (
  GOODS_CD             VARCHAR(20)   NOT NULL COMMENT '상품코드(상품)',
  INTEREST_FREE_EVENT  VARCHAR(100)  COMMENT '무이자행사'
)
COMMENT 'EP무이자행사. EP무이자행사 생성에 의한 발생';

################################################################################
#EP네이버
################################################################################
DROP TABLE IF EXISTS TB_EP_NAVER RESTRICT;
CREATE TABLE TB_EP_NAVER (
  GB                         VARCHAR(4)     NOT NULL COMMENT '구분(PREV:이전, CURR:현재)',
  ID                         VARCHAR(20)    NOT NULL COMMENT '상품코드(상품)',
  TITLE                      VARCHAR(100)   COMMENT '상품명([브랜드명]+상품명+상품코드 형식)',
  PRICE_PC                   INT UNSIGNED   COMMENT 'PC가격',
  PRICE_MOBILE               INT UNSIGNED   COMMENT '모바일가격',
  NORMAL_PRICE               INT UNSIGNED   COMMENT '정상가',
  LINK                       VARCHAR(200)   COMMENT 'PC상품상세URL',
  MOBILE_LINK                VARCHAR(200)   COMMENT '모바일상품상세URL',
  IMAGE_LINK                 VARCHAR(200)   COMMENT '이미지링크',
  ADD_IMAGE_LINK             VARCHAR(1000)  COMMENT '추가이미지링크(|로 구분. 최대4개)',
  CATEGORY_NAME1             VARCHAR(50)    COMMENT '카테고리명1',
  CATEGORY_NAME2             VARCHAR(50)    COMMENT '카테고리명2',
  CATEGORY_NAME3             VARCHAR(50)    COMMENT '카테고리명3',
  CATEGORY_NAME4             VARCHAR(50)    COMMENT '카테고리명4',
  NAVER_CATEGORY             VARCHAR(20)    COMMENT '네이버카테고리(EP카테고리)',
  GOODS_GB_NM                VARCHAR(10)    COMMENT '상품구분명(공통코드G073)',
  IMPORT_FLAG                VARCHAR(1)     COMMENT '해외구매대행여부',
  PARALLEL_IMPORT            VARCHAR(1)     COMMENT '병행수입여부',
  ORDER_MADE                 VARCHAR(1)     COMMENT '주문제작여부',
  PRODUCT_FLAG               VARCHAR(10)    COMMENT '예약판매',
  ADULT                      VARCHAR(1)     COMMENT '미성년자구매불가상품여부',
  MANUFACTURE_DEFINE_NUMBER  VARCHAR(20)    COMMENT '제품코드(품번)',
  BRAND                      VARCHAR(50)    COMMENT '브랜드명',
  MAKER                      VARCHAR(50)    COMMENT '제조사',
  ORIGIN                     VARCHAR(50)    COMMENT '원산지',
  CARD_EVENT                 VARCHAR(100)   COMMENT '카드행사(카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가|카드명^카드적용가... 형식)',
  EVENT_WORDS                VARCHAR(100)   COMMENT '상품타이틀명(세일링문구)',
  COUPON                     VARCHAR(100)   COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)',
  PARTNER_COUPON_DOWNLOAD    VARCHAR(1)     COMMENT '쿠폰다운로드필요여부',
  INTEREST_FREE_EVENT        VARCHAR(100)   COMMENT '무이자행사(카드명^개월수-개월수|카드명^개월수-개월수... 형식)',
  POINT                      VARCHAR(50)    COMMENT '포인트정보(스타일포인트^포인트액 형식)',
  SEARCH_TAG                 VARCHAR(100)   COMMENT '검색태그',
  MINIMUM_PURCHASE_QUANTITY  VARCHAR(10)    COMMENT '최소구매수량',
  REVIEW_COUNT               INT UNSIGNED   COMMENT '상품평수',
  SHIPPING                   INT UNSIGNED   COMMENT '배송료',
  AGE_GROUP                  VARCHAR(10)    COMMENT '나이(베이비->유아, 키즈여아/키즈남아/키즈공용->아동, 주니어남아/주니어여아/주니어공용->청소년, 성인)',
  GENDER                     VARCHAR(10)    COMMENT '성별(남성/여성/남여공용)',
  GOODS_STAT                 VARCHAR(20)    COMMENT '상품상태(공통코드G008)',
  SELF_MALL_YN               CHAR(1)        COMMENT '자사몰노출여부',
  SELL_STDT                  DATETIME       COMMENT '판매시작일시',
  SELL_EDDT                  DATETIME       COMMENT '판매종료일시',
  ADULT_YN                   CHAR(1)        COMMENT '성인용품여부',
  UPD_GB                     VARCHAR(1)     COMMENT '업데이트구분(I:신규상품, U:기존상품중업데이트된상품 또는 품절되었다가다시서비스되는상품, D:품절상품)',
  UPDATE_TIME                VARCHAR(20)    COMMENT '업데이트시간(YYYY-MM-DD HH:MM:SS 형식)'
)
COMMENT 'EP네이버. EP네이버 생성에 의한 발생';

CREATE UNIQUE INDEX IX_EP_NAVER_01
  ON TB_EP_NAVER (
    GB, ID
  );

################################################################################
#EP다음
################################################################################
DROP TABLE IF EXISTS TB_EP_DAUM RESTRICT;
CREATE TABLE TB_EP_DAUM (
  GB            VARCHAR(4)     NOT NULL COMMENT '구분(PREV:이전, CURR:현재)',
  MAPID         VARCHAR(20)    NOT NULL COMMENT '상품코드(상품)',
  LPRICE        INT UNSIGNED   COMMENT '정상가',
  PRICE         INT UNSIGNED   COMMENT 'PC가격',
  MPRIC         INT UNSIGNED   COMMENT '모바일가격',
  PNAME         VARCHAR(100)   COMMENT '상품명([상품구분명]+[브랜드명]+상품명+상품코드 형식)',
  PGURL         VARCHAR(200)   COMMENT 'PC상품상세URL',
  IGURL         VARCHAR(200)   COMMENT '이미지링크',
  UPIMG         VARCHAR(1)     DEFAULT 'N' COMMENT '전체EP생성 이후 이미지변경되었을때전송(Y/N)',
  CATE1         VARCHAR(50)    COMMENT '카테고리명1',
  CAID1         VARCHAR(10)    COMMENT '카테고리ID1',
  CATE2         VARCHAR(50)    COMMENT '카테고리명2',
  CAID2         VARCHAR(10)    COMMENT '카테고리ID2',
  CATE3         VARCHAR(50)    COMMENT '카테고리명3',
  CAID3         VARCHAR(10)    COMMENT '카테고리ID3',
  CATE4         VARCHAR(50)    COMMENT '카테고리명4',
  CAID4         VARCHAR(10)    COMMENT '카테고리ID4',
  MODEL         VARCHAR(20)    COMMENT '모델(품번)',
  BRAND         VARCHAR(50)    COMMENT '브랜드명',
  MAKER         VARCHAR(50)    COMMENT '제조사',
  COUPON        VARCHAR(100)   COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)',
  MCOUPON       VARCHAR(100)   COMMENT '할인쿠폰정보(일반쿠폰^정율제휴쿠폰^정액제휴쿠폰 형식. 일반쿠폰은 최대할인기준 1개)',
  PCARD         VARCHAR(100)   COMMENT '무이자행사(카드명^개월수-개월수|카드명^개월수-개월수... 형식)',
  POINT         VARCHAR(10)    COMMENT '포인트율',
  DELIV         INT UNSIGNED   COMMENT '배송료',
  DELIVTERM     INT UNSIGNED   COMMENT '배송기간',
  RATING        VARCHAR(20)    COMMENT '상품평점수(4.5/5 형식)',
  REVCT         INT UNSIGNED   COMMENT '상품평수',
  EVENT         VARCHAR(100)   COMMENT '상품타이틀명(세일링문구)',
  CARDDN        VARCHAR(100)   COMMENT '카드행사(카드즉시할인 등록된 기준으로 적용. 카드명^카드적용가|카드명^카드적용가... 형식)',
  CARDP         INT UNSIGNED   COMMENT '카드적용가(최대1개)',
  ADULT         VARCHAR(1)     COMMENT '성인상품여부',
  PUBDATE       VARCHAR(8)     COMMENT '상품등록일',
  GOODS_STAT    VARCHAR(20)    COMMENT '상품상태(공통코드G008)',
  SELF_MALL_YN  CHAR(1)        COMMENT '자사몰노출여부',
  SELL_STDT     DATETIME       COMMENT '판매시작일시',
  SELL_EDDT     DATETIME       COMMENT '판매종료일시',
  UPD_GB        VARCHAR(1)     COMMENT '업데이트구분(I:신규상품, U:기존상품중업데이트된상품 또는 품절되었다가다시서비스되는상품, D:품절상품)',
  UTIME         VARCHAR(20)    COMMENT '업데이트시간(YYYYMMDDHHMMSS 형식)'
)
COMMENT 'EP다음. EP다음 생성에 의한 발생';

CREATE UNIQUE INDEX IX_EP_DAUM_01
  ON TB_EP_DAUM (
    GB, ID
  );

-- SET GROUP_CONCAT_MAX_LEN = 102400;

/* TsbGoodsEp.truncateEpGoodsImage */
TRUNCATE TABLE TB_EP_GOODS_IMG;
	
/* TsbGoodsEp.createEpGoodsImage *(32초) */
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
     , 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'
;
SELECT COUNT(*) FROM TB_EP_GOODS_IMG;

/* TsbGoodsEp.truncateEpGoodsAddImage */
TRUNCATE TABLE TB_EP_GOODS_ADD_IMG;

/* TsbGoodsEp.createEpGoodsAddImage (24초) */
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;

/* TsbGoodsEp.truncateEpCardEvent */
TRUNCATE TABLE TB_EP_CARD_EVENT;

/* TsbGoodsEp.createEpCardEvent (1초) */
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;

/* TsbGoodsEp.truncateEpCoupon */
TRUNCATE TABLE TB_EP_COUPON;

/* TsbGoodsEp.createEpCoupon (1초) */
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;

/* TsbGoodsEp.truncateEpInterestFreeEvent */
TRUNCATE TABLE TB_EP_INTEREST_FREE_EVENT;

/* TsbGoodsEp.createEpInterestFreeEvent (1초) */
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;

/* TsbGoodsEp.deleteEpNaver */
DELETE FROM TB_EP_NAVER WHERE GB = 'PREV';
 
/* TsbGoodsEp.createPreviousEpNaver */
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
     , UPD_GB
     , UPDATE_TIME
)
SELECT 'PREV' AS 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
     , UPD_GB
     , UPDATE_TIME
FROM   TB_EP_NAVER
WHERE  GB = 'LAST'
;
SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'PREV';

/* TsbGoodsEp.deleteEpNaver */
DELETE FROM TB_EP_NAVER WHERE GB = 'CURR';

/* TsbGoodsEp.createEpNaver (2분 27초) */
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
     , UPD_GB
     , UPDATE_TIME
)
SELECT 'CURR'                                                 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 PNT                       /*포인트*/
     , 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 <= 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 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
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_IMG GI ON G.GOODS_CD = GI.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    G.UPD_DT >= DATE_ADD(NOW(), INTERVAL -2 HOUR) /*요약EP생성시사용*/
;
SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'CURR';

DELETE FROM TB_EP_NAVER WHERE GB = 'LAST';

-- 최종 네이버 EP 생성 (55초)
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
     , 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
     , DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S') AS UPDATE_TIME /*변경일시*/
FROM   (
        /*신규 목록*/
        SELECT 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
             , 'I'                         AS UPD_GB /*변경구분*/
        FROM   TB_EP_NAVER X
        WHERE  GB = 'CURR' /*현재데이터*/
        /*요약EP*/
#         AND    NOT EXISTS (SELECT 1
#                            FROM   TB_EP_NAVER
#                            WHERE  GB = 'PREV' /*이전백업데이터*/
#                            AND    ID = X.ID
#                           )
#         UNION ALL
#         -- 변경 목록
#         SELECT X.ID
#              , X.TITLE
#              , X.PRICE_PC
#              , X.PRICE_MOBILE
#              , X.NORMAL_PRICE
#              , X.LINK
#              , X.MOBILE_LINK
#              , X.IMAGE_LINK
#              , X.ADD_IMAGE_LINK
#              , X.CATEGORY_NAME1
#              , X.CATEGORY_NAME2
#              , X.CATEGORY_NAME3
#              , X.CATEGORY_NAME4
#              , X.NAVER_CATEGORY
#              , X.GOODS_GB_NM
#              , X.IMPORT_FLAG
#              , X.PARALLEL_IMPORT
#              , X.ORDER_MADE
#              , X.PRODUCT_FLAG
#              , X.ADULT
#              , X.MANUFACTURE_DEFINE_NUMBER
#              , X.BRAND
#              , X.MAKER
#              , X.ORIGIN
#              , X.CARD_EVENT
#              , X.EVENT_WORDS
#              , X.COUPON
#              , X.PARTNER_COUPON_DOWNLOAD
#              , X.INTEREST_FREE_EVENT
#              , X.POINT
#              , X.SEARCH_TAG
#              , X.MINIMUM_PURCHASE_QUANTITY
#              , X.REVIEW_COUNT
#              , X.SHIPPING
#              , X.AGE_GROUP
#              , X.GENDER
#              , X.GOODS_STAT
#              , X.SELF_MALL_YN
#              , X.SELL_STDT
#              , X.SELL_EDDT
#              , 'U'                         AS UPD_GB /*변경구분*/
#         FROM   TB_EP_NAVER X
#              , TB_EP_NAVER Y
#         WHERE  X.ID = Y.ID
#         AND    X.GB = 'CURR' /*현재데이터*/
#         AND    Y.GB = 'PREV' /*이전백업데이터*/
#         AND    (
#                 X.PRICE_PC != X.PRICE_PC OR
#                 X.PRICE_MOBILE != X.PRICE_MOBILE OR
#                 X.NORMAL_PRICE != X.NORMAL_PRICE OR
#                 X.LINK != X.LINK OR
#                 X.MOBILE_LINK != X.MOBILE_LINK OR
#                 X.IMAGE_LINK != X.IMAGE_LINK OR
#                 X.ADD_IMAGE_LINK != X.ADD_IMAGE_LINK OR
#                 X.CATEGORY_NAME1 != X.CATEGORY_NAME1 OR
#                 X.CATEGORY_NAME2 != X.CATEGORY_NAME2 OR
#                 X.CATEGORY_NAME3 != X.CATEGORY_NAME3 OR
#                 X.CATEGORY_NAME4 != X.CATEGORY_NAME4 OR
#                 X.NAVER_CATEGORY != X.NAVER_CATEGORY OR
#                 X.GOODS_GB_NM != X.GOODS_GB_NM OR
#                 X.IMPORT_FLAG != X.IMPORT_FLAG OR
#                 X.PARALLEL_IMPORT != X.PARALLEL_IMPORT OR
#                 X.ORDER_MADE != X.ORDER_MADE OR
#                 X.PRODUCT_FLAG != X.PRODUCT_FLAG OR
#                 X.ADULT != X.ADULT OR
#                 X.MANUFACTURE_DEFINE_NUMBER != X.MANUFACTURE_DEFINE_NUMBER OR
#                 X.BRAND != X.BRAND OR
#                 X.MAKER != X.MAKER OR
#                 X.ORIGIN != X.ORIGIN OR
#                 X.CARD_EVENT != X.CARD_EVENT OR
#                 X.EVENT_WORDS != X.EVENT_WORDS OR
#                 X.COUPON != X.COUPON OR
#                 X.PARTNER_COUPON_DOWNLOAD != X.PARTNER_COUPON_DOWNLOAD OR
#                 X.INTEREST_FREE_EVENT != X.INTEREST_FREE_EVENT OR
#                 X.POINT != X.POINT OR
#                 X.SEARCH_TAG != X.SEARCH_TAG OR
#                 X.MINIMUM_PURCHASE_QUANTITY != X.MINIMUM_PURCHASE_QUANTITY OR
#                 X.REVIEW_COUNT != X.REVIEW_COUNT OR
#                 X.SHIPPING != X.SHIPPING OR
#                 X.AGE_GROUP != X.AGE_GROUP OR
#                 X.GENDER != X.GENDER
#                )
#         UNION ALL
#         -- 삭제된 목록
#         SELECT X.ID
#              , X.TITLE
#              , X.PRICE_PC
#              , X.PRICE_MOBILE
#              , X.NORMAL_PRICE
#              , X.LINK
#              , X.MOBILE_LINK
#              , X.IMAGE_LINK
#              , X.ADD_IMAGE_LINK
#              , X.CATEGORY_NAME1
#              , X.CATEGORY_NAME2
#              , X.CATEGORY_NAME3
#              , X.CATEGORY_NAME4
#              , X.NAVER_CATEGORY
#              , X.GOODS_GB_NM
#              , X.IMPORT_FLAG
#              , X.PARALLEL_IMPORT
#              , X.ORDER_MADE
#              , X.PRODUCT_FLAG
#              , X.ADULT
#              , X.MANUFACTURE_DEFINE_NUMBER
#              , X.BRAND
#              , X.MAKER
#              , X.ORIGIN
#              , X.CARD_EVENT
#              , X.EVENT_WORDS
#              , X.COUPON
#              , X.PARTNER_COUPON_DOWNLOAD
#              , X.INTEREST_FREE_EVENT
#              , X.POINT
#              , X.SEARCH_TAG
#              , X.MINIMUM_PURCHASE_QUANTITY
#              , X.REVIEW_COUNT
#              , X.SHIPPING
#              , X.AGE_GROUP
#              , X.GENDER
#              , X.GOODS_STAT
#              , X.SELF_MALL_YN
#              , X.SELL_STDT
#              , X.SELL_EDDT
#              , 'D'                         AS UPD_GB /*변경구분*/
#         FROM   TB_EP_NAVER X
#              , TB_EP_NAVER Y
#         WHERE  X.ID = Y.ID
#         AND    X.GB = 'CURR' /*현재데이터*/
#         AND    Y.GB = 'PREV' /*이전백업데이터*/
#         AND    (
#                 (Y.GOODS_STAT != 'G008_70' AND X.GOODS_STAT = 'G008_70')
#                 OR
#                 (Y.SELF_MALL_YN = 'Y' AND X.SELF_MALL_YN = 'N')
#                 OR
#                 (Y.SELL_EDDT != X.SELL_EDDT AND Y.SELL_EDDT < NOW())
#                )
       ) X
;
SELECT COUNT(*) FROM TB_EP_NAVER WHERE GB = 'LAST';

/* TsbGoodsEp.getNaverAllEpList */
SELECT 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
     , UPD_GB
     , UPDATE_TIME
FROM   TB_EP_NAVER
WHERE  GB = 'LAST'
;