style24.doc/산출물/4.구축/통계/주문통계_화면.sql

-- 주문통계 > 상품별주문

SELECT
    FN_GET_CODE_NM('G009',A.FORMAL_GB) AS FORMAL_GB -- 정상이월구분
    , A.GOODS_CD -- 상품코드
    , B.GOODS_NM -- 상품명
    , SUM(C.STOCK_QTY) AS STOCK_QTY -- 현재고수량
    , SUM(A.SELL_QTY) AS SELL_QTY -- 판매수량
    , SUM(A.SELF_AMT + A.CHANNEL_AMT + A.EXTMALL_AMT - A.GOODS_CPN_AMT - A.TMTB_AMT - A.CART_CPN_AMT) AS PAY_AMT -- 상품당결제액
    , SUM(A.GOODS_CPN_AMT) AS GOODS_CPN_AMT -- 즉시/주문상품쿠폰
    , SUM(A.TMTB_AMT) AS TMTB_AMT -- 다다익선
    , SUM(A.CART_CPN_AMT) AS CART_CPN_AMT -- 장바구니쿠폰
    , SUM(A.CNCL_QTY + A.RTN_QTY) AS CNCL_QTY -- 취/환불수량
    , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/환불액
    , SUM(A.SAVE_PNT_AMT) AS SAVE_PNT_AMT -- 지급포인트
FROM TB_STAT_ORD_DAY A, TB_GOODS B, TB_GOODS_STOCK C
WHERE A.GOODS_CD = B.GOODS_CD
    AND B.GOODS_CD = C.GOODS_CD
    AND A.DAY >= DATE_FORMAT('20210901', '%Y%m%d')
    AND A.DAY <= DATE_FORMAT('20210906', '%Y%m%d')
    -- AND A.FRONT_GB IN('P','M','A') -- 디바이스
    -- AND A.EXTMALL_ID IN ('') -- 제휴몰
    -- AND A.FORMAL_GB IN('G009_10','G009_20') -- 정상/이월구분
    -- AND A.DISTRIBUTION_GB IN ('WMS', 'SCM') -- 물류구분
    -- AND A.SUPPLY_COMP_CD IN () -- 공급처
    -- AND A.AF_LINK_CD IN('AF001') -- 채널
    -- AND BRAND_CD IN() -- 브랜드
    -- AND ITEMKIND_CD IN() -- 카테고리
GROUP BY A.FORMAL_GB, A.GOODS_CD, B.GOODS_NM, B.SELF_GOODS_YN
ORDER BY B.SELF_GOODS_YN DESC, A.FORMAL_GB, A.GOODS_CD
;

-- 주문통계 > 제휴몰주문
SELECT
    (SELECT EXTMALL_NM FROM TB_EXTMALL X WHERE X.EXTMALL_ID = A.EXTMALL_ID LIMIT 1) AS EXTMALL_NM
    , SUM(A.EXTMALL_AMT - A.CNCL_AMT - A.RTN_AMT) AS TOT_AMT -- 총매출
    , SUM(A.SELL_QTY) AS SELL_QTY -- 판매수량
    , SUM(A.EXTMALL_AMT) AS EXTMALL_AMT
    , SUM(A.CNCL_QTY + A.RTN_QTY) AS CNCL_QTY -- 취/반품수량
    , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/반품액
    , SUM(A.REAL_ORD_AMT)
    , (SELECT SELL_FEE_RATE FROM TB_EXTMALL X WHERE X.EXTMALL_ID = A.EXTMALL_ID LIMIT 1) AS SELL_FEE_RATE -- 수수료율
    , (SUM(A.REAL_ORD_AMT) * (1 - (SELECT SELL_FEE_RATE FROM TB_EXTMALL X WHERE X.EXTMALL_ID = A.EXTMALL_ID LIMIT 1) / 100)) AS EX_USAC_AMT
FROM TB_STAT_ORD_DAY A
WHERE A.DAY >= DATE_FORMAT('20210101', '%Y%m%d')
    AND A.DAY <= DATE_FORMAT('20210906', '%Y%m%d')
    AND A.EXTMALL_ID IS NOT NULL
    -- AND A.FORMAL_GB IN('G009_10','G009_20') -- 정상/이월구분
    -- AND A.SUPPLY_COMP_CD IN () -- 공급처
    -- AND A.AF_LINK_CD IN('AF001') -- 채널
    -- AND A.BRAND_CD IN() -- 브랜드
    -- AND A.ITEMKIND_CD IN() -- 카테고리
GROUP BY A.EXTMALL_ID
ORDER BY EXTMALL_NM
;

-- 주문통계 > 채녈별주문
SELECT
    AF_LINK_CD
    , (SELECT AF_LINK_NM FROM TB_AF_LINK X WHERE X.AF_LINK_CD = A.AF_LINK_CD) AS AF_LINK_NM
    , SUM(A.CHANNEL_AMT) AS CHANNEL_AMT
    , SUM(A.CHANNEL_AMT - A.CNCL_AMT - A.RTN_AMT) AS TOT_AMT -- 총매출
    , SUM(A.SELL_QTY) AS SELL_QTY -- 판매수량
    , SUM(A.CHANNEL_AMT) AS CHANNEL_AMT
    , SUM(A.CNCL_QTY + A.RTN_QTY) AS CNCL_QTY -- 취/반품수량
    , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/반품액
    , SUM(A.REAL_ORD_AMT)
    , (SELECT FEE_RATE FROM TB_AF_LINK X WHERE X.AF_LINK_CD = A.AF_LINK_CD) AS SELL_FEE_RATE
    , (SUM(A.REAL_ORD_AMT) * (1 - (SELECT FEE_RATE FROM TB_AF_LINK X WHERE X.AF_LINK_CD = A.AF_LINK_CD) / 100)) AS EX_USAC_AMT
FROM TB_STAT_ORD_DAY A
WHERE A.DAY >= DATE_FORMAT('20210101', '%Y%m%d')
    AND A.DAY <= DATE_FORMAT('20210906', '%Y%m%d')
    AND EXISTS(
          SELECT 1
          FROM TB_AF_LINK X
          WHERE X.AF_LINK_CD = A.AF_LINK_CD
            AND X.AF_CHANNEL != 'G053_01'
      )
    -- AND A.FRONT_GB IN('P','M') -- 디바이스
    -- AND A.FORMAL_GB IN('G009_10','G009_20') -- 정상/이월구분
    -- AND A.DISTRIBUTION_GB IN ('WMS', 'SCM') -- 물류구분
    -- AND A.SUPPLY_COMP_CD IN () -- 공급처
    -- AND A.ITEMKIND_CD IN() -- 카테고리
GROUP BY AF_LINK_CD
ORDER BY AF_LINK_NM
;



-- 주문통계 > 브랜드별주문
SELECT
    BRAND_CD -- 브랜드코드
    , BRAND_ENM -- 브랜드명
    , TOT_AMT -- 총매출
    , SELL_QTY -- 판매수량
    , SELFMALL_AMT -- 자사몰매출
    , EXTMALL_AMT-- 제휴몰매출
    , CNCL_AMT -- 취/반품액
    , TOT_AMT_10 -- 정상매출
    , TOT_AMT_20 -- 이월매출
    , ROUND( ((FLOOR(TOT_AMT_10 / TOT_AMT * 10000 / 10)) / 10), 1) AS AMT_RATE_10 -- 정상비
FROM (
    SELECT
        BRAND_CD
        , BRAND_ENM
        , SELF_YN
        , SUM(TOT_AMT) AS TOT_AMT
        , SUM(SELL_QTY) AS SELL_QTY
        , SUM(SELFMALL_AMT) AS SELFMALL_AMT
        , SUM(EXTMALL_AMT) AS EXTMALL_AMT
        , SUM(CNCL_AMT) AS CNCL_AMT
        , SUM(CASE WHEN FORMAL_GB = 'G009_10' THEN TOT_AMT ELSE 0 END) AS TOT_AMT_10 -- 정상매출
        , SUM(CASE WHEN FORMAL_GB = 'G009_20' THEN TOT_AMT ELSE 0 END) AS TOT_AMT_20 -- 이월매출
    FROM (
        SELECT
            A.BRAND_CD -- 브랜드코드
            , B.BRAND_ENM -- 브랜드명
            , B.SELF_YN -- 자사여부
            , A.FORMAL_GB -- 정상이월구분
            , SUM(A.SELF_AMT + A.CHANNEL_AMT + A.EXTMALL_AMT - A.CNCL_AMT - A.RTN_AMT) AS TOT_AMT -- 총매출
            , SUM(A.SELL_QTY) AS SELL_QTY -- 판매수량
            , SUM(A.SELF_AMT + A.CHANNEL_AMT) AS SELFMALL_AMT -- 자사몰매출
            , SUM(A.EXTMALL_AMT) AS EXTMALL_AMT -- 제휴몰매출
            , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/반품액
        FROM TB_STAT_ORD_DAY A, TB_BRAND B
        WHERE A.BRAND_CD = B.BRAND_CD
            AND A.DAY >= DATE_FORMAT('20210901', '%Y%m%d')
            AND A.DAY <= DATE_FORMAT('20210906', '%Y%m%d')
            -- AND A.FRONT_GB IN('P','M','A') -- 디바이스
            -- AND A.EXTMALL_ID IN ('') -- 제휴몰
            -- AND A.DISTRIBUTION_GB IN ('WMS', 'SCM') -- 물류구분
            -- AND A.SUPPLY_COMP_CD IN () -- 공급처
            -- AND A.AF_LINK_CD IN('AF001') -- 채널
            -- AND A.BRAND_CD IN() -- 브랜드
            -- AND A.ITEMKIND_CD IN() -- 카테고리
        GROUP BY A.BRAND_CD, B.BRAND_ENM, B.SELF_YN, A.FORMAL_GB
    ) X
    GROUP BY BRAND_CD, BRAND_ENM, SELF_YN
) Z
ORDER BY SELF_YN DESC, BRAND_ENM
;