style24.doc/산출물/3.설계/03.인터페이스/검색엔진/검색_상품색인.sql

DROP TABLE TB_SEARCH_ENGINE;

CREATE TABLE TB_SEARCH_ENGINE AS
WITH TAB_GOODS AS (
	SELECT G.GOODS_CD
	     , G.GOODS_NM
	     , G.GOODS_TNM
	     , G.GOODS_GB
	     , G.FOREIGN_BUY_YN
	     , G.PARALLEL_IMPORT_YN
	     , G.ORDER_MADE_YN
	     , G.GOODS_SNM
	     , G.BRAND_CD
	     , G.ITEMKIND_CD
	     , G.FORMAL_GB
	     , G.LIST_PRICE
	     , G.CURR_PRICE
	     , G.SELF_GOODS_YN
	     , G.GOODS_STAT
	     , G.PNT_PRATE
	     , G.PRE_PPNT_USABLE_YN
	     , G.PNT_MRATE
	     , G.PRE_MPNT_USABLE_YN
	     , G.MIN_ORD_QTY
	     , G.MAX_ORD_QTY
	     , G.DAY_MAX_ORD_QTY
	     , G.SEX_GB
	     , G.MAIN_COLOR_CD
	     , G.SUPPLY_COMP_CD
	     , G.SELF_MALL_YN
	     , G.AGE_GRP_CD
	     , G.SEASON_CD 
	     , G.GOODS_TYPE
	     , G.REG_DT AS REG_DT 
	     , (CASE WHEN BG.DISP_NM_LANG = 'EN' THEN BG.BRAND_GROUP_ENM ELSE BG.BRAND_GROUP_KNM END) AS BRAND_GROUP_NM
         , BG.BRAND_GROUP_NO
         , E.DELV_FEE
         , E.MIN_ORD_AMT
         , GS.SELL_WEEK_QTY AS ORDER_CNT
         , GS.REVIEW_REG_CNT AS REVIEW_CNT
         , I.SIZE_GB
         , CASE WHEN I.SIZE_GB = 'T' THEN '상의'
                WHEN I.SIZE_GB = 'B' THEN '하의'
                WHEN I.SIZE_GB = 'S' THEN '신발'
               ELSE NULL
            END       AS SIZE_GB_NM
         , (CASE WHEN G.SELF_GOODS_YN ='Y' AND ifnull(QDS.GOODS_CD, 'Y') THEN 'Y' ELSE 'N' END ) AS QUIK_DELV_YN   
	FROM   TB_GOODS G
	INNER JOIN TB_BRAND B ON G.BRAND_CD = B.BRAND_CD
                         AND B.USE_YN = 'Y'
    INNER JOIN TB_BRAND_GROUP BG ON B.BRAND_GROUP_NO = BG.BRAND_GROUP_NO
                                 AND BG.USE_YN = 'Y' 
    INNER JOIN TB_DELV_FEE_POLICY E ON G.SUPPLY_COMP_CD = E.SUPPLY_COMP_CD
                                AND G.DELV_FEE_CD =  E.DELV_FEE_CD
    INNER JOIN TB_GOODS_SUMMARY GS ON G.GOODS_CD = GS.GOODS_CD          
    INNER JOIN TB_ITEMKIND I ON  G.ITEMKIND_CD = I.ITEMKIND_CD
    -- LEFT OUTER JOIN TB_ITEMKIND I ON  G.ITEMKIND_CD = I.ITEMKIND_CD
    LEFT OUTER JOIN TB_GOODS_SHOT_DELV_SKIP QDS ON G.GOODS_CD  = QDS.GOODS_CD                                      
	WHERE G.SELF_MALL_YN = 'Y' -- 자사몰 노출(기획전과 상품상세는 노출)
	-- AND G.GOODS_STAT = 'G008_90'
-- 	 AND G.GOODS_CD  IN ('14373685','14373686','14373687','14373688','14373690','14373697','14373699'  , '10770554','10770563','10770569','10770570' , 'AMM11QTS36')
)
-- SELECT * FROM TAB_GOODS ;  -- 25075
, TAB_OPT_SIZE AS (
    /*옵션 사이즈 목록 조회*/
    /*자사상품*/
    SELECT G.GOODS_CD
        --  , O.OPT_CD
         , O.OPT_CD1
         , C.COLOR_FILE /*RGB값*/
         , GROUP_CONCAT(CONCAT(O.OPT_CD2,'|',O.SOLDOUT_YN,'|',VS.CURR_STOCK_QTY)) AS SIZE_INFO
    FROM   TAB_GOODS G
         , TB_OPTION O
         , TB_COLOR C
         , VW_STOCK VS
    WHERE  G.GOODS_CD = O.GOODS_CD
    AND    O.OPT_CD1 = C.COLOR_CD
    AND    O.GOODS_CD = VS.GOODS_CD
    AND    O.OPT_CD = VS.OPT_CD
    AND    G.SELF_GOODS_YN = 'Y' /*자사상품*/
    AND    O.DISP_YN = 'Y'
    AND    C.USE_YN = 'Y'
    GROUP  BY G.GOODS_CD, O.OPT_CD1, C.COLOR_FILE
    UNION ALL
    /*입점상품*/
    SELECT G.GOODS_CD
         -- , O.OPT_CD
         , G.MAIN_COLOR_CD AS OPT_CD1
         , NULL            AS COLOR_FILE /*RGB값*/
         , GROUP_CONCAT(CONCAT(O.OPT_CD2,'|',O.SOLDOUT_YN,'|',VS.CURR_STOCK_QTY)) AS SIZE_INFO
    FROM   TAB_GOODS G
         , TB_OPTION O
         , VW_STOCK VS
    WHERE  G.GOODS_CD = O.GOODS_CD
    AND    O.GOODS_CD = VS.GOODS_CD
    AND    O.OPT_CD = VS.OPT_CD
    AND    G.SELF_GOODS_YN = 'N' /*입점상품*/
    AND    O.DISP_YN = 'Y'
    GROUP  BY G.GOODS_CD,G.MAIN_COLOR_CD
)
--  SELECT * FROM TAB_OPT_SIZE;
, TAB_OPT AS (
    /*옵션 목록*/
    SELECT OS.GOODS_CD
         , OS.OPT_CD1
         , GROUP_CONCAT(OS.SIZE_INFO ORDER BY OS.SIZE_INFO) AS SIZES
    FROM   TAB_OPT_SIZE OS
    GROUP  BY OS.GOODS_CD, OS.OPT_CD1
)
-- SELECT * FROM TAB_OPT;
, TAB_AD_KEYWORD AS (
	/* 상품별 키워드 목록*/
    SELECT AKG.GOODS_CD
          , GROUP_CONCAT(AK.AD_KEYWORD) AS  AD_KEYWORD
     FROM TB_AD_KEYWORD AK
     INNER JOIN TB_AD_KEYWORD_GOODS AKG ON AK.AD_KEYWORD_SQ  = AKG.AD_KEYWORD_SQ
     INNER JOIN TAB_GOODS G ON AKG.GOODS_CD = G.GOODS_CD
     WHERE NOW() BETWEEN  AK.AD_KEYWORD_STDT AND AK.AD_KEYWORD_EDDT
     AND AK.USE_YN  = 'Y'
     GROUP BY  AKG.GOODS_CD
)
, TAB_GOODS_VIDEO AS (
	SELECT MAX(GOODS_CD) AS GOODS_CD 
         , MAX(CASE WHEN  RNUM = 1 THEN  VIDEO_GB END) AS  VIDEO_GB_M
         , MAX(CASE WHEN  RNUM = 1 THEN  VIDEO_VAL END) AS  VIDEO_VAL_M
         , MAX(CASE WHEN  RNUM = 2 THEN  VIDEO_GB END) AS  VIDEO_GB_S
         , MAX(CASE WHEN  RNUM = 2 THEN  VIDEO_VAL END) AS  VIDEO_VAL_S
   FROM (
         SELECT A.DISPLOC_VAL AS GOODS_CD
              , B.VIDEO_GB 
              , B.VIDEO_VAL 
              , A.REG_DT 
              , RANK() OVER(PARTITION BY A.DISPLOC_VAL ORDER BY A.REG_DT ) RNUM 
         FROM TB_VIDEO_DISPLOC  A
         INNER JOIN  TB_VIDEO B ON A.VIDEO_SQ = B.VIDEO_SQ 
                                AND B.DISP_YN ='Y'
         INNER JOIN  TAB_GOODS G ON A.DISPLOC_VAL = G.GOODS_CD                      
         WHERE A.DISPLOC_GB ='G'
         AND A.DISP_YN = 'Y'
        ) Z
)
, TAB_CPN_ICON AS (
    /*상품별 쿠폰 아이콘 목록*/
    SELECT G.GOODS_CD
         , GBP.PC_CURR_PRICE
         , GBP.MO_CURR_PRICE
         , GBP.APP_CURR_PRICE
         -- , FN_IS_GOODS_COUPON('P',G.GOODS_CD,ifnull(GBP.PC_CURR_PRICE, G.CURR_PRICE, 'G100_10'))  AS CPN_PC_ICON  -- 속도 이슈  일반:G100_10, 임직원:G100_20
         -- , FN_IS_GOODS_COUPON('M',G.GOODS_CD,ifnull(GBP.PC_CURR_PRICE, G.CURR_PRICE, 'G100_10'))  AS CPN_MO_ICON
         -- , FN_IS_GOODS_COUPON('A',G.GOODS_CD,ifnull(GBP.PC_CURR_PRICE, G.CURR_PRICE, 'G100_10')) AS CPN_APP_ICON
          , FN_GET_FREEGIFT_GOODS_YN(G.GOODS_CD)                  AS FREEGIFT_ICON
    FROM   TAB_GOODS G
    LEFT OUTER JOIN TB_GOODS_BENEFIT_PRICE GBP ON G.GOODS_CD = GBP.GOODS_CD
                                                AND    GBP.CUST_GB = 'G100_00'
)
--  SELECT COUNT(*) FROM (
SELECT G.*
     , O.OPT_CD1 AS COLOR_CD
     , O.SIZES
     -- , CI.CPN_PC_ICON
     -- , CI.CPN_MO_ICON
     -- , CI.CPN_APP_ICON
     , '' AS CPN_PC_ICON
     , '' AS CPN_MO_ICON
     , '' AS CPN_APP_ICON
     , CI.FREEGIFT_ICON
     , AK.AD_KEYWORD
     , GV.VIDEO_GB_M
     , GV.VIDEO_VAL_M
     , GV.VIDEO_GB_S
     , GV.VIDEO_VAL_S
     , FN_GET_CODE_NM('G073',G.GOODS_GB) AS GOODS_GB_NM
     , FN_GET_CODE_NM('G007',G.SEX_GB) AS SEX_NM
     , FN_GET_CODE_NM('G023',G.AGE_GRP_CD) AGE_GRP_NM
     , FN_GET_CODE_NM('G006',G.SEASON_CD) SEASON_NM
FROM   TAB_GOODS G
INNER JOIN  TAB_CPN_ICON CI ON  G.GOODS_CD = CI.GOODS_CD
LEFT OUTER JOIN  TAB_OPT O ON G.GOODS_CD = O.GOODS_CD
LEFT OUTER JOIN TAB_AD_KEYWORD AK ON G.GOODS_CD = AK.GOODS_CD
LEFT OUTER JOIN TAB_GOODS_VIDEO GV ON G.GOODS_CD = GV.GOODS_CD
-- AND    G.SELF_GOODS_YN = 'Y'
-- AND    G.GOODS_CD = '14373685'
--  ) A
;