style24.doc/산출물/4.구축/통계/결제통계.sql

/*G016_00	결제대기
G016_10	무통장입금전
G016_30	결제완료
G016_97	결제성공후DB실패
G016_98	결제전취소
G016_99	결제취소

G014	G014_10	실시간계좌이체
G014	G014_20	무통장입금
G014	G014_30	신용카드
G014	G014_40	포인트
G014	G014_50	쿠폰
G014	G014_60	휴대전화
G014	G014_70	상품권
G014	G014_80	네이버포인트
G014	G014_81	카카오포인트
G014	G014_82	PAYCO포인트
G014	G014_83	카카오머니
G014	G014_90	외부몰입금


G015	INICIS	이니시스
G015	ISTYLE	아이스타일
G015	KAKAO	카카오페이
G015	KCP	케이씨피
G015	NAVER	네이버페이
G015	NAVER_ORDER	네이버페이주문형
G015	NICE	나이스
G015	PAYCO	페이코*/

-- 결제수단통계 수집
INSERT INTO TB_STAT_PAY
SELECT
    DATE_FORMAT(PAY_DT, '%Y-%m-%d') AS TERM
    , PAY_MEANS -- 결제수단
    , PG_GB -- PG구분
    , COM_CARD_CD -- 카드코드
    , SUM(PAY_AMT) AS PAY_AMT -- 결제금액
    , NOW()
FROM TB_PAYMENT A USE INDEX (IX_PAYMENT_01)
WHERE PAY_DT >= DATE_FORMAT('20180101', '%Y%m%d')
    AND PAY_DT <= DATE_FORMAT(CONCAT('20210831', '235959'), '%Y%m%d%H%i%s')
    AND PAY_STAT IN ('G016_30', 'G016_99')
    AND PAY_MEANS IN ('G014_10','G014_20','G014_30','G014_60','G014_50')
GROUP BY TERM, PAY_MEANS, PG_GB, COM_CARD_CD
;

-- 결제통계 결제수단별통계 화변
WITH TAB AS (
    SELECT
        DATE_FORMAT(DAY, '%Y-%m-%d') AS TERM
        , PAY_MEANS -- 결제수단
        , PAY_AMT -- 결제금액
        , PG_GB -- PG구분
    FROM TB_STAT_PAY A
    WHERE DAY >= DATE_FORMAT('20200701', '%Y%m%d')
        AND DAY <= DATE_FORMAT(CONCAT('20210731', '235959'), '%Y%m%d%H%i%s')
        -- AND COM_CARD_CD IN () -- 카드사
        -- 간편결제체크시
        -- AND PG_GB IN('KAKAO','NAVER','PAYCO')
)
SELECT
    TERM
    , SUM(CD_AMT) AS CD_ATM -- 카드
    , SUM(AC_AMT) AS AC_AMT -- 실시간계좌이체
    , SUM(VA_AMT) AS VA_AMT -- 무통장
    , SUM(HP_AMT) AS HP_AMT -- 휴대폰
    , SUM(CP_AMT) AS CP_AMT -- PG쿠폰
    , SUM(NPAY_AMT) AS NPAY_AMT -- 네이버페이
    , SUM(PAYCO_AMT) AS PAYCO_AMT -- 페이코
    , SUM(KAKAO_AMT) AS KAKAO_AMT -- 카카오
FROM (
    SELECT
        TERM
        , CASE WHEN PAY_MEANS = 'G014_30' THEN PAY_AMT ELSE 0 END AS CD_AMT
        , CASE WHEN PAY_MEANS = 'G014_10' THEN PAY_AMT ELSE 0 END AS AC_AMT
        , CASE WHEN PAY_MEANS = 'G014_20' THEN PAY_AMT ELSE 0 END AS VA_AMT
        , CASE WHEN PAY_MEANS = 'G014_60' THEN PAY_AMT ELSE 0 END AS HP_AMT
        , CASE WHEN PAY_MEANS = 'G014_50' THEN PAY_AMT ELSE 0 END AS CP_AMT
        , CASE WHEN PAY_MEANS = 'NAVER' THEN PAY_AMT ELSE 0 END AS NPAY_AMT
        , CASE WHEN PAY_MEANS = 'PAYCO' THEN PAY_AMT ELSE 0 END AS PAYCO_AMT
        , CASE WHEN PAY_MEANS = 'KAKAO' THEN PAY_AMT ELSE 0 END AS KAKAO_AMT
    FROM (
         SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_30'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_10'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_20'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_60'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_50'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PG_GB AS PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PG_GB = 'NAVER'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PG_GB AS PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PG_GB = 'PAYCO'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PG_GB AS PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PG_GB = 'KAKAO'
        GROUP BY TERM
    ) Z
) Z
GROUP BY TERM
ORDER BY TERM
;


-- 실시간
/*
WITH TAB AS (
    SELECT
        DATE_FORMAT(PAY_DT, '%Y-%m-%d') AS TERM
        , PAY_MEANS -- 결제수단
        , PAY_AMT -- 결제금액
        , PG_GB -- PG구분
    FROM TB_PAYMENT A USE INDEX (IX_PAYMENT_01)
    WHERE PAY_DT >= DATE_FORMAT('20200701', '%Y%m%d')
        AND PAY_DT <= DATE_FORMAT(CONCAT('20210731', '235959'), '%Y%m%d%H%i%s')
        AND PAY_STAT IN ('G016_30', 'G016_99')
        -- AND PG_GB IS NOT NULL
        AND PAY_MEANS IN ('G014_10','G014_20','G014_30','G014_60','G014_50')
        -- AND COM_CARD_CD IN () -- 카드사
        -- 간편결제체크시
        -- AND PG_GB IN('KAKAO','NAVER','PAYCO')
)
SELECT
    TERM
    , SUM(CD_AMT) AS CD_ATM
    , SUM(AC_AMT) AS AC_AMT
    , SUM(VA_AMT) AS VA_AMT
    , SUM(HP_AMT) AS HP_AMT
    , SUM(CP_AMT) AS CP_AMT
FROM (
    SELECT
        TERM
        , CASE WHEN PAY_MEANS = 'G014_30' THEN PAY_AMT ELSE 0 END AS CD_AMT
        , CASE WHEN PAY_MEANS = 'G014_10' THEN PAY_AMT ELSE 0 END AS AC_AMT
        , CASE WHEN PAY_MEANS = 'G014_20' THEN PAY_AMT ELSE 0 END AS VA_AMT
        , CASE WHEN PAY_MEANS = 'G014_60' THEN PAY_AMT ELSE 0 END AS HP_AMT
        , CASE WHEN PAY_MEANS = 'G014_50' THEN PAY_AMT ELSE 0 END AS CP_AMT
    FROM (
         SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_30'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_10'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_20'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_60'
        GROUP BY TERM
        UNION ALL
        SELECT
            TERM
            , PAY_MEANS
            , SUM(PAY_AMT) AS PAY_AMT
        FROM TAB
        WHERE PAY_MEANS = 'G014_50'
        GROUP BY TERM
    ) Z
) Z
GROUP BY TERM
ORDER BY TERM
*/
;



-- 결제통계 -> 부결제수단통계 배치
INSERT INTO TB_STAT_SUB_PAY
WITH TAB_PNT AS (
    SELECT
        DATE_FORMAT(PNT_UPLOAD_DT, '%Y-%m-%d') AS TERM
        , ORD_NO
        , PNT_AMT
        , OCCUR_GB
        , CUST_PNT_SQ
    FROM TB_CUST_POINT_HST
    WHERE PNT_UPLOAD_DT >= DATE_FORMAT('20210601', '%Y%m%d')
        AND PNT_UPLOAD_DT <= DATE_FORMAT(CONCAT('20210831', '235959'), '%Y%m%d%H%i%s')
        AND PNT_UPLOAD_STAT = 'G070_30'
)
-- 포인트 적립액
, PNT_GIVE AS (
    SELECT
        TERM
        , SUM(PNT_AMT) AS VAL
        , OCCUR_GB
        , '1' AS GB
    FROM TAB_PNT
    WHERE 1=1
    GROUP BY TERM, OCCUR_GB
)
-- 포인트사용주문수
, PNT_ORD_CNT AS (
    SELECT
        TERM
        , SUM(CNT) AS VAL
        , OCCUR_GB
        , '2' AS GB
    FROM (
        SELECT
            B.TERM
            , 1 AS CNT
            , OCCUR_GB
        FROM TB_ORDER_DETAIL A, TAB_PNT B
        WHERE A.ORD_NO = B.ORD_NO
            AND A.ORD_QTY > A.CNCL_RTN_QTY
        GROUP BY B.TERM, A.ORD_NO, OCCUR_GB
    ) A
    GROUP BY TERM, OCCUR_GB
)
-- 포인트사용액
, PNT_USE AS (
    SELECT
        B.TERM
        , SUM(A.PNT_AMT * -1) AS VAL
        , B.OCCUR_GB
        , '3' AS GB
    FROM TB_CUST_POINT_HST A, TAB_PNT B
    WHERE A.ORD_NO = B.ORD_NO
        AND A.CUST_PNT_SQ = B.CUST_PNT_SQ
        AND A.OCCUR_GB IN ('G069_10', 'G069_11', 'G069_36', 'G069_40', 'G069_41', 'G069_42', 'G069_43')
    GROUP BY TERM, B.OCCUR_GB
)
, TAB_GIFT AS (
    SELECT
        DATE_FORMAT(REG_DT, '%Y-%m-%d') AS TERM
        , ORD_NO
        , GFCD_AMT
        , OCCUR_GB
        , CUST_GFCD_SQ
    FROM TB_CUST_GIFTCARD_HST
    WHERE REG_DT >= DATE_FORMAT('20210601', '%Y%m%d')
        AND REG_DT <= DATE_FORMAT(CONCAT('20210831', '235959'), '%Y%m%d%H%i%s')
)
-- 상품권등록매수
, GIFT_REG_QTY AS (
    SELECT
        TERM
        , COUNT(1) AS CNT
        , OCCUR_GB
        , '4' AS GB
    FROM TAB_GIFT
    WHERE OCCUR_GB = 'G074_11'
    GROUP BY TERM, OCCUR_GB
)
-- 상품권사용주문수
, GIFT_ORD_CNT AS (
    SELECT
        TERM
        , SUM(CNT) AS VAL
        , OCCUR_GB
        , '5' AS GB
    FROM (
        SELECT
            B.TERM
            , 1 AS CNT
            , OCCUR_GB
        FROM TB_ORDER_DETAIL A, TAB_GIFT B
        WHERE A.ORD_NO = B.ORD_NO
            AND A.ORD_QTY > A.CNCL_RTN_QTY
        GROUP BY B.TERM, A.ORD_NO, OCCUR_GB
    ) A
    GROUP BY TERM, OCCUR_GB
)
-- 상품권사용액
, GIFT_USE AS (
    SELECT
        B.TERM
        , SUM(A.GFCD_AMT * -1) AS VAL
        , B.OCCUR_GB
        , '6' AS GB
    FROM TB_CUST_GIFTCARD_HST A, TAB_GIFT B
    WHERE A.ORD_NO = B.ORD_NO
        AND A.CUST_GFCD_SQ = B.CUST_GFCD_SQ
        AND A.OCCUR_GB IN ('G074_12', 'G074_13', 'G074_18', 'G074_19')
    GROUP BY TERM, B.OCCUR_GB
)
SELECT
    DATE_FORMAT(TERM, '%Y-%m-%d') AS TERM
    , OCCUR_GB
    , SUM(CASE WHEN GB = '1' THEN VAL ELSE 0 END) AS PNT_GIVE
    , SUM(CASE WHEN GB = '2' THEN VAL ELSE 0 END) AS PNT_ORD_CNT
    , SUM(CASE WHEN GB = '3' THEN VAL ELSE 0 END) AS PNT_USE
    , SUM(CASE WHEN GB = '4' THEN VAL ELSE 0 END) AS GIFT_REG_CNT
    , SUM(CASE WHEN GB = '5' THEN VAL ELSE 0 END) AS GIFT_ORD_CNT
    , SUM(CASE WHEN GB = '6' THEN VAL ELSE 0 END) AS GIFT_USE
    , NOW()
FROM (
    SELECT *
    FROM PNT_GIVE
    UNION ALL
    SELECT *
    FROM PNT_USE
    UNION
    SELECT *
    FROM PNT_ORD_CNT
    UNION
    SELECT *
    FROM GIFT_REG_QTY
    UNION
    SELECT *
    FROM GIFT_ORD_CNT
    UNION
    SELECT *
    FROM GIFT_USE
) Z
GROUP BY TERM, OCCUR_GB
;


-- 결제통계 > 부결제수단통계화면
SELECT
    DATE_FORMAT(A.DAY, '%m%d') AS TERM
    , SUM(A.PNT_GIVE) AS PNT_GIVE -- 포인트적립액
    , SUM(A.PNT_ORD_CNT) AS PNT_ORD_CNT -- 포인트사용주문수
    , SUM(A.PNT_USE) AS PNT_USE -- 포인트사용액
    , SUM(A.GIFT_REG_CNT) AS GIFT_REG_CNT -- 상품권등록매수
    , SUM(A.GIFT_ORD_CNT) AS GIFT_ORD_CNT -- 상품권주문수
    , SUM(A.GIFT_USE) AS GIFT_USE -- 상품권사용액
FROM TB_STAT_SUB_PAY A
WHERE DAY >= DATE_FORMAT('20200701', '%Y%m%d')
    AND DAY <= DATE_FORMAT(CONCAT('20210731', '235959'), '%Y%m%d%H%i%s')
GROUP BY TERM
;

-- 결제통계 > 부결제수단통계화면 > 현잔액
SELECT
    SUM(RM_PNT_AMT) AS RM_PNT_AMT -- 포인트잔액
FROM TB_CUST_POINT A
WHERE A.EXP_CMP_DT IS NULL
;

SELECT
    SUM(RM_GFCD_AMT) AS RM_GFCD_AMT -- 상품권잔액
FROM TB_CUST_GIFTCARD A
WHERE DATE_FORMAT(USE_EXP_DATE, '%Y%m%d') < NOW()
;