style24.doc/산출물/4.구축/통계/프로모션통계.sql

-- 프로모션통계 -> 쿠폰사용통계
WITH TAB AS (
     SELECT
        A.ORD_NO
        , B.ORD_DTL_NO
        , B.ORD_QTY
        , B.CNCL_RTN_QTY
        , B.ORD_AMT
        , B.CURR_PRICE
        , B.CNCL_RTN_AMT
        , B.ORD_DTL_STAT
        , A.PAY_DT
        , C.GOODS_CD
        , CPN1_DC_AMT
        , CPN1_CPN_SQ
        , GOODS_CPN_DC_AMT
        , GOODS_CPN_SQ
        , CART_CPN_DC_AMT
        , CART_CPN_SQ
    FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D
    WHERE A.ORD_NO = B.ORD_NO
        AND B.GOODS_CD = C.GOODS_CD
        AND A.CUST_NO = D.CUST_NO
        AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
        AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
        AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
        AND B.ORD_QTY > B.CNCL_RTN_QTY
)
, TAB_CPN_STAT AS (
    SELECT CPN_TYPE, A.CPN_ID, A.CPN_NM
        , 100 - IFNULL((SELECT MAX(BURDEN_RATE) FROM TB_COUPON_BURDEN X WHERE X.CPN_ID = A.CPN_ID), 0) AS BURDEN
        , COUNT(1) AS USE_CNT
        , SUM((C.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = C.ORD_DTL_NO)) * (C.ORD_QTY - C.CNCL_RTN_QTY)) AS ORD_AMT
        , SUM(C.CPN1_DC_AMT) AS CPN_AMT
    FROM TB_COUPON A, TB_CUST_COUPON B, TAB C
    WHERE A.CPN_ID = B.CPN_ID
        AND B.CUST_CPN_SQ = C.CPN1_CPN_SQ
        AND A.CPN_TYPE = 'G230_10'-- 즉시할인쿠폰
        AND C.CPN1_DC_AMT > 0
    -- 쿠폰번호조건 (CPN_ID)
    GROUP BY CPN_TYPE, A.CPN_ID, A.CPN_NM
    UNION ALL
    SELECT CPN_TYPE, A.CPN_ID, A.CPN_NM
        , 100 - IFNULL((SELECT MAX(BURDEN_RATE) FROM TB_COUPON_BURDEN X WHERE X.CPN_ID = A.CPN_ID), 0) AS BURDEN
        , COUNT(1) AS USE_CNT
        , SUM((C.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = C.ORD_DTL_NO)) * (C.ORD_QTY - C.CNCL_RTN_QTY)) AS ORD_AMT
        , SUM(C.GOODS_CPN_DC_AMT) AS CPN_AMT
    FROM TB_COUPON A, TB_CUST_COUPON B, TAB C
    WHERE A.CPN_ID = B.CPN_ID
        AND B.CUST_CPN_SQ = C.GOODS_CPN_SQ
        AND A.CPN_TYPE = 'G230_11'-- 상품쿠푼
        AND C.GOODS_CPN_DC_AMT > 0
    -- 쿠폰번호조건 (CPN_ID)
    GROUP BY CPN_TYPE, A.CPN_ID, A.CPN_NM
    UNION ALL
    SELECT CPN_TYPE, A.CPN_ID, A.CPN_NM
        , 100 - IFNULL((SELECT MAX(BURDEN_RATE) FROM TB_COUPON_BURDEN X WHERE X.CPN_ID = A.CPN_ID), 0) AS BURDEN
        , COUNT(1) AS USE_CNT
        , SUM((C.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = C.ORD_DTL_NO)) * (C.ORD_QTY - C.CNCL_RTN_QTY)) AS ORD_AMT
        , SUM(C.CART_CPN_DC_AMT) AS CPN_AMT
    FROM TB_COUPON A, TB_CUST_COUPON B, TAB C
    WHERE A.CPN_ID = B.CPN_ID
        AND B.CUST_CPN_SQ = C.CART_CPN_SQ
        AND A.CPN_TYPE = 'G230_20'-- 장바구니쿠푼
        AND C.CART_CPN_DC_AMT > 0
    -- 쿠폰번호조건 (CPN_ID)
    GROUP BY CPN_TYPE, A.CPN_ID, A.CPN_NM
    UNION ALL
    SELECT CPN_TYPE, A.CPN_ID, A.CPN_NM
        , 100 - IFNULL((SELECT MAX(BURDEN_RATE) FROM TB_COUPON_BURDEN X WHERE X.CPN_ID = A.CPN_ID), 0) AS BURDEN
        , COUNT(1) AS USE_CNT
        , SUM((C.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = C.ORD_DTL_NO)) * (C.ORD_QTY - C.CNCL_RTN_QTY)) AS ORD_AMT
        , SUM(D.DELV_CPN_DC_AMT) AS CPN_AMT
    FROM TB_COUPON A, TB_CUST_COUPON B, TAB C, TB_DELIVERY_FEE D
    WHERE A.CPN_ID = B.CPN_ID
        AND B.CUST_CPN_SQ = C.CART_CPN_SQ
        AND C.ORD_NO = D.ORD_NO
        AND B.CUST_CPN_SQ = D.DELV_CPN_SQ
        AND A.CPN_TYPE = 'G230_30'-- 배송비쿠푼
        AND D.DELV_CPN_DC_AMT > 0
    -- 쿠폰번호조건 (CPN_ID)
    GROUP BY CPN_TYPE, A.CPN_ID, A.CPN_NM
)
SELECT CPN_TYPE -- 쿠폰타입
    , FN_GET_CODE_NM('G230', CPN_TYPE) AS CPN_TYPE_NM -- 쿠폰타입명
    , CPN_ID -- 쿠폰번호
    , CPN_NM -- 쿠폰명
    , BURDEN -- 자사분담율
    , DOWN_CNT
    , FLOOR(USE_CNT / DOWN_CNT * 100 * 10) / 10 AS USE_RATE -- 사용율
    , USE_CNT -- 사용수
    , ORD_AMT -- 주문금액
    , CPN_AMT -- 쿠폰비용
FROM (
    SELECT CPN_TYPE -- 쿠폰타입
        , FN_GET_CODE_NM('G230', CPN_TYPE) AS CPN_TYPE_NM -- 쿠폰타입명
        , CPN_ID -- 쿠폰번호
        , CPN_NM -- 쿠폰명
        , BURDEN -- 자사분담율
        , (SELECT COUNT(1) FROM TB_CUST_COUPON X WHERE X.CPN_ID = A.CPN_ID) AS DOWN_CNT
        , USE_CNT -- 사용수
        , ORD_AMT -- 주문금액
        , CPN_AMT -- 쿠폰비용
    FROM TAB_CPN_STAT A
    -- 쿠폰타입조건 (G230_10	즉시할인쿠폰, G230_11	상품쿠폰, G230_20	주문서쿠폰,  G230_30	배송비쿠폰
) A
;

-- 프로모션통계 -> 쿠폰사용통계 -> 상품현황
-- 즉시, 상품, 장바구니 쿠폰
SELECT Z.BRAND_CD
    , Z.BRAND_ENM
    , Z.GOODS_CD
    , Z.GOODS_NM
    , Z.ORD_AMT
    , Z.ORD_QTY
    , Z.CUST_CNT
    , X.CUST_CPN_CNT
    , FLOOR((X.CUST_CPN_CNT / Z.CUST_CNT * 100) / 10) * 10 AS ORD_RATE
FROM (
     SELECT
        A.BRAND_CD
        , B.BRAND_ENM
        , A.GOODS_CD
        , A.GOODS_NM
        , SUM(ORD_AMT) AS ORD_AMT
        , SUM(ORD_QTY) AS ORD_QTY
        , SUM(CUST_CNT) AS CUST_CNT
    FROM (
         SELECT
            C.BRAND_CD
            , C.GOODS_CD
            , C.GOODS_NM
            , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
            , SUM(B.ORD_QTY - B.CNCL_RTN_QTY) AS ORD_QTY
            , COUNT(1) OVER(PARTITION BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO) AS CUST_CNT
        FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D
        WHERE A.ORD_NO = B.ORD_NO
        AND B.GOODS_CD = C.GOODS_CD
        AND A.CUST_NO = D.CUST_NO
        AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
        AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
        AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
        AND B.ORD_QTY > B.CNCL_RTN_QTY
        GROUP BY C.BRAND_CD, C.GOODS_CD, C.GOODS_NM, A.CUST_NO
    ) A, TB_BRAND B
    WHERE A.BRAND_CD = B.BRAND_CD
    GROUP BY A.BRAND_CD, B.BRAND_ENM, A.GOODS_CD
) Z
, (
    SELECT
        X.BRAND_CD
        , X.BRAND_ENM
        , X.GOODS_CD
        , X.CUST_CPN_CNT
    FROM (
        SELECT
            A.BRAND_CD
            , B.BRAND_ENM
            , A.GOODS_CD
            , SUM(A.CUST_CPN_CNT) AS CUST_CPN_CNT
        FROM (
            SELECT
                C.BRAND_CD
                , C.GOODS_CD
                , COUNT(1) OVER(PARTITION BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO) AS CUST_CPN_CNT
            FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D
            WHERE A.ORD_NO = B.ORD_NO
            AND B.GOODS_CD = C.GOODS_CD
            AND A.CUST_NO = D.CUST_NO
            AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
            AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
            AND B.ORD_QTY > B.CNCL_RTN_QTY
            AND EXISTS(
                SELECT 1
                FROM TB_COUPON X, TB_CUST_COUPON Y
                WHERE X.CPN_ID = Y.CPN_ID
                    AND Y.CUST_CPN_SQ = B.GOODS_CPN_SQ -- B.CPN1_CPN_SQ, B.CART_CPN_SQ (쿠폰종류에따라 설정)
                    AND X.CPN_ID = 31045 -- 쿠폰번호변수
            )
            GROUP BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO
        ) A, TB_BRAND B
        WHERE A.BRAND_CD = B.BRAND_CD
        GROUP BY A.BRAND_CD, B.BRAND_ENM, A.GOODS_CD
    ) X
) X
WHERE Z.BRAND_CD = X.BRAND_CD
    AND Z.GOODS_CD = X.GOODS_CD
;

-- 배송비 쿠폰
SELECT Z.BRAND_CD
    , Z.BRAND_ENM
    , Z.GOODS_CD
    , Z.ORD_AMT
    , Z.ORD_QTY
    , Z.CUST_CNT
    , X.CUST_CPN_CNT
    , FLOOR((X.CUST_CPN_CNT / Z.CUST_CNT * 100) / 10) * 10 AS ORD_RATE
FROM (
     SELECT
        A.BRAND_CD
        , B.BRAND_ENM
        , A.GOODS_CD
        , SUM(ORD_AMT) AS ORD_AMT
        , SUM(ORD_QTY) AS ORD_QTY
        , SUM(CUST_CNT) AS CUST_CNT
    FROM (
         SELECT
            C.BRAND_CD
            , C.GOODS_CD
            , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
            , SUM(B.ORD_QTY - B.CNCL_RTN_QTY) AS ORD_QTY
            , COUNT(1) OVER(PARTITION BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO) AS CUST_CNT
        FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D
        WHERE A.ORD_NO = B.ORD_NO
        AND B.GOODS_CD = C.GOODS_CD
        AND A.CUST_NO = D.CUST_NO
        AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
        AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
        AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
        AND B.ORD_QTY > B.CNCL_RTN_QTY
        GROUP BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO
    ) A, TB_BRAND B
    WHERE A.BRAND_CD = B.BRAND_CD
    GROUP BY A.BRAND_CD, B.BRAND_ENM, A.GOODS_CD
) Z
, (
    SELECT
        X.BRAND_CD
        , X.BRAND_ENM
        , X.GOODS_CD
        , X.CUST_CPN_CNT
    FROM (
        SELECT
            A.BRAND_CD
            , B.BRAND_ENM
            , A.GOODS_CD
            , SUM(A.CUST_CPN_CNT) AS CUST_CPN_CNT
        FROM (
            SELECT
                C.BRAND_CD
                , C.GOODS_CD
                , COUNT(1) OVER(PARTITION BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO) AS CUST_CPN_CNT
            FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D, TB_DELIVERY_FEE E
            WHERE A.ORD_NO = B.ORD_NO
            AND B.GOODS_CD = C.GOODS_CD
            AND A.CUST_NO = D.CUST_NO
            AND A.ORD_NO = E.ORD_NO
            AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
            AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
            AND B.ORD_QTY > B.CNCL_RTN_QTY
            AND EXISTS(
                SELECT 1
                FROM TB_COUPON X, TB_CUST_COUPON Y
                WHERE X.CPN_ID = Y.CPN_ID
                    AND Y.CUST_CPN_SQ = E.DELV_CPN_SQ
                    AND X.CPN_ID = 31045 -- 쿠폰번호변수
            )
            AND E.DELV_CPN_DC_AMT > 0
            GROUP BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO
        ) A, TB_BRAND B
        WHERE A.BRAND_CD = B.BRAND_CD
        GROUP BY A.BRAND_CD, B.BRAND_ENM, A.GOODS_CD
    ) X
) X
WHERE Z.BRAND_CD = X.BRAND_CD
    AND Z.GOODS_CD = X.GOODS_CD
;

-- 프로모션통계 -> 다다익선통계
WITH TAB AS (
    SELECT A.ORD_NO
         , B.ORD_DTL_NO
         , B.ORD_QTY
         , B.CNCL_RTN_QTY
         , B.ORD_AMT
         , B.CURR_PRICE
         , B.CNCL_RTN_AMT
         , B.ORD_DTL_STAT
         , A.PAY_DT
         , C.GOODS_CD
         , B.TMTB1_SQ
         , B.TMTB1_DC_AMT
         , B.TMTB2_SQ
         , B.TMTB2_DC_AMT
    FROM TB_ORDER A,
         TB_ORDER_DETAIL B,
         TB_GOODS C,
         TB_CUSTOMER D
    WHERE A.ORD_NO = B.ORD_NO
      AND B.GOODS_CD = C.GOODS_CD
      AND A.CUST_NO = D.CUST_NO
      AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
      AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
      AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98')               -- 주문접수,입금대기,입금전취소 제외
      AND B.ORD_QTY > B.CNCL_RTN_QTY
      -- 프로모션유형 선택값 없을때
      AND (B.TMTB1_DC_AMT + B.TMTB2_DC_AMT) > 0
    -- 수량할인
    /*AND TMTB1_DC_AMT > 0
        -- 금액할인
        AND TMTB2_DC_AMT > 0
        -- 수량+금액 할인
        AND TMTB1_DC_AMT > 0
        AND TMTB2_DC_AMT > 0*/
    -- 프로모션 번호 (TMTB1_SQ OR TMTB2_SQ)
)
-- 수량할인
, TMTB1 AS (
    SELECT
        TMTB_SQ
        , TMTB_NM
        , FN_GET_CODE_NM('G810', APPLY_GB) AS APPLY_GB
        , SUM(ORD_CNT) AS ORD_CNT
        , SUM(ORD_AMT) AS ORD_AMT
        , SUM(DC_AMT) AS DC_AMT
    FROM
    (
        SELECT A.TMTB_SQ
            , A.TMTB_NM
            , APPLY_GB
            , COUNT(1) AS ORD_CNT
            , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
            , SUM(B.TMTB1_DC_AMT) AS DC_AMT
        FROM TB_TMTB A, TAB B
        WHERE A.TMTB_SQ = B.TMTB1_SQ
        GROUP BY A.TMTB_SQ, A.TMTB_NM, A.APPLY_GB, B.ORD_NO
    ) X
    GROUP BY TMTB_SQ, TMTB_NM, APPLY_GB
)
-- 금액할인
, TMTB2 AS (
    SELECT
        TMTB_SQ
        , TMTB_NM
        , FN_GET_CODE_NM('G810', APPLY_GB) AS APPLY_GB
        , SUM(ORD_CNT) AS ORD_CNT
        , SUM(ORD_AMT) AS ORD_AMT
        , SUM(DC_AMT) AS DC_AMT
    FROM
    (
        SELECT A.TMTB_SQ
            , A.TMTB_NM
            , APPLY_GB
            , COUNT(1) AS ORD_CNT
            , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
            , SUM(B.TMTB1_DC_AMT) AS DC_AMT
        FROM TB_TMTB A, TAB B
        WHERE A.TMTB_SQ = B.TMTB2_SQ
        GROUP BY A.TMTB_SQ, A.TMTB_NM, A.APPLY_GB, B.ORD_NO
    ) X
    GROUP BY TMTB_SQ, TMTB_NM, APPLY_GB
)
SELECT *
FROM TMTB1
UNION ALL
SELECT *
FROM TMTB2
;

-- 프로모션통계 -> 다다익선통계 -> 상품현황
SELECT
    A.BRAND_CD
    , B.BRAND_ENM
    , A.GOODS_CD
    , A.GOODS_NM
    , SUM(ORD_AMT) AS ORD_AMT
    , SUM(ORD_QTY) AS ORD_QTY
    , SUM(CUST_CNT) AS CUST_CNT
FROM (
     SELECT
        C.BRAND_CD
        , C.GOODS_CD
        , C.GOODS_NM
        , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
        , SUM(B.ORD_QTY - B.CNCL_RTN_QTY) AS ORD_QTY
        , COUNT(1) OVER(PARTITION BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO) AS CUST_CNT
    FROM TB_ORDER A, TB_ORDER_DETAIL B, TB_GOODS C, TB_CUSTOMER D
    WHERE A.ORD_NO = B.ORD_NO
    AND B.GOODS_CD = C.GOODS_CD
    AND A.CUST_NO = D.CUST_NO
    AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
    AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
    AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98') -- 주문접수,입금대기,입금전취소 제외
    AND B.ORD_QTY > B.CNCL_RTN_QTY
    -- 수량
    -- AND B.TMTB1_SQ = 14
    -- 금액
    AND B.TMTB2_SQ = 33
    GROUP BY C.BRAND_CD, C.GOODS_CD, A.CUST_NO
) A, TB_BRAND B
WHERE A.BRAND_CD = B.BRAND_CD
GROUP BY A.BRAND_CD, B.BRAND_ENM, A.GOODS_CD
;

-- 프로모션통계 -> 기획전통계
SELECT
    CASE WHEN DISTRIBUTION_GB = 'G065_20' THEN '입점' ELSE '위탁' END AS DISTRIBUTION_GB
    , BRAND_ENM
    , PLAN_SQ
    , (SELECT PLAN_NM FROM TB_PLAN P WHERE P.PLAN_SQ = X.PLAN_SQ) AS PLAN_NM
    , SUM(ORD_AMT) AS ORD_AMT
    , SUM(ORD_CNT) AS ORD_CNT
FROM (
    SELECT C.DISTRIBUTION_GB
         , F.BRAND_ENM
         , E.PLAN_SQ
         , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
         , COUNT(1) AS ORD_CNT
    FROM TB_ORDER A,
         TB_ORDER_DETAIL B,
         TB_GOODS C,
         TB_CUSTOMER D,
         TB_PLAN_DETAIL E,
         TB_BRAND F
    WHERE A.ORD_NO = B.ORD_NO
      AND B.GOODS_CD = C.GOODS_CD
      AND A.CUST_NO = D.CUST_NO
      AND B.PLAN_DTL_SQ = E.PLAN_DTL_SQ
      AND F.BRAND_CD = C.BRAND_CD
      AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
      AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
      AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98')               -- 주문접수,입금대기,입금전취소 제외
      AND B.ORD_QTY > B.CNCL_RTN_QTY
      -- 위탁
      -- AND C.DISTRIBUTION_GB IN ('G065_10', 'G065_11', 'G065_12')
      -- 입점
      -- AND C.DISTRIBUTION_GB = 'G065_20'
      -- AND E.PLAN_SQ = 기획전번호
    GROUP BY C.DISTRIBUTION_GB, F.BRAND_ENM, E.PLAN_SQ, A.ORD_NO
) X
GROUP BY DISTRIBUTION_GB, BRAND_ENM, PLAN_SQ
;

-- 프로모션통계 -> 기획전통계 -> 상품구매현황
WITH TAB AS (
    SELECT SUM(CUST_CNT) AS TOT_CUST_CNT
    FROM (
        SELECT COUNT(1) AS CUST_CNT
        FROM TB_ORDER A,
             TB_ORDER_DETAIL B,
             TB_GOODS C,
             TB_CUSTOMER D,
             TB_PLAN_DETAIL E,
             TB_BRAND F
        WHERE A.ORD_NO = B.ORD_NO
          AND B.GOODS_CD = C.GOODS_CD
          AND A.CUST_NO = D.CUST_NO
          AND B.PLAN_DTL_SQ = E.PLAN_DTL_SQ
          AND F.BRAND_CD = C.BRAND_CD
          AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
          AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
          AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98')               -- 주문접수,입금대기,입금전취소 제외
          AND B.ORD_QTY > B.CNCL_RTN_QTY
          -- AND E.PLAN_SQ = 기획전번호
        GROUP BY A.CUST_NO
    ) Z
)
SELECT
    CASE WHEN DISTRIBUTION_GB = 'G065_20' THEN '입점' ELSE '위탁' END AS DISTRIBUTION_GB
    , GOODS_CD
    , GOODS_NM
    , SUM(ORD_AMT) AS ORD_AMT
    , SUM(ORD_QTY) AS ORD_QTY
    , SUM(CUST_CNT) AS CUST_CNT
    , FLOOR((SUM(CUST_CNT) / (SELECT TOT_CUST_CNT FROM TAB) * 100) / 10) * 10 AS ORD_RATE
FROM (
     SELECT C.DISTRIBUTION_GB
         , C.GOODS_CD
         , C.GOODS_NM
         , SUM((B.CURR_PRICE + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = B.ORD_DTL_NO)) * (B.ORD_QTY - B.CNCL_RTN_QTY)) AS ORD_AMT
         , SUM(B.ORD_QTY - B.CNCL_RTN_QTY) AS ORD_QTY
         , COUNT(1) OVER(PARTITION BY C.DISTRIBUTION_GB, C.GOODS_CD, A.CUST_NO) AS CUST_CNT
    FROM TB_ORDER A,
         TB_ORDER_DETAIL B,
         TB_GOODS C,
         TB_CUSTOMER D,
         TB_PLAN_DETAIL E,
         TB_BRAND F
    WHERE A.ORD_NO = B.ORD_NO
      AND B.GOODS_CD = C.GOODS_CD
      AND A.CUST_NO = D.CUST_NO
      AND B.PLAN_DTL_SQ = E.PLAN_DTL_SQ
      AND F.BRAND_CD = C.BRAND_CD
      AND A.PAY_DT >= DATE_FORMAT('20210610', '%Y%m%d')                         -- 시작일변수
      AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210730', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
      AND B.ORD_DTL_STAT NOT IN ('G013_00', 'G013_10', 'G013_98')               -- 주문접수,입금대기,입금전취소 제외
      AND B.ORD_QTY > B.CNCL_RTN_QTY
      -- AND E.PLAN_SQ = 기획전번호
    GROUP BY C.DISTRIBUTION_GB, C.GOODS_CD, C.GOODS_NM, A.CUST_NO
) X
GROUP BY DISTRIBUTION_GB, GOODS_CD, GOODS_NM
;