style24.doc/산출물/4.구축/통계/회원통계.sql

-- 회원통계 > 가입통계
SELECT
    DATE_FORMAT(DAY,'%Y-%m-%d')                       AS DAY
    , MAX(ALL_CNT) AS ALL_CNT
    , MAX(REAL_CNT) AS REAL_CNT
    , MAX(DORM_CNT) AS DORM_CNT
    , MAX(VIP_CNT) AS VIP_CNT
    , MAX(GOLD_CNT) AS GOLD_CNT
    , MAX(SILVER_CNT) AS SILVER_CNT
    , MAX(BRONZE_CNT) AS BRONZE_CNT
    , MAX(WELCOME_CNT) AS WELCOME_CNT
    , SUM(JOIN_CNT) AS JOIN_CNT
    , SUM(DR_JOIN_CNT) AS DR_JOIN_CNT
    , SUM(AF_JOIN_CNT) AS AF_JOIN_CNT
    , SUM(CHG_DORM_CNT) AS CHG_DORM_CNT
    , SUM(RM_DORM_CNT) AS RM_DORM_CNT
    , SUM(SECEED_CNT) AS SECEED_CNT
FROM TB_STAT_CUST
WHERE DAY >= DATE_FORMAT('20210601', '%Y%m%d')
    AND DAY <= DATE_FORMAT(CONCAT('20210930','235959'), '%Y%m%d%h%i%s')
    AND SEX_GB IN('G007_M','G007_F','G007_X') -- 성별
    AND AGE IN('10','20','30','40','X')
GROUP  BY DATE_FORMAT(DAY,'%Y-%m-%d')
ORDER BY DAY DESC

-- 회원통계 > 등급별주문현황
SELECT
    CASE WHEN A.CUST_GRADE = 'G110_10' THEN 1
        WHEN A.CUST_GRADE = 'G110_20' THEN 2
        WHEN A.CUST_GRADE = 'G110_30' THEN 3
        WHEN A.CUST_GRADE = 'G110_40' THEN 4
        WHEN A.CUST_GRADE = 'G110_50' THEN 5
    ELSE 6 END AS PRIORITY
    , FN_GET_CODE_NM('G110', A.CUST_GRADE) AS CUST_GRADE
    , SUM(A.ORD_AMT - A.CNCL_AMT - A.RTN_AMT) AS TOT_ORD_AMT -- 총매출액
    , SUM(A.ORD_AMT) AS ORD_ATM -- 매출액
    , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/반품액
    , SUM(A.ORD_CNT) AS ORD_CNT -- 주문수
    , SUM(A.CNCL_QTY + A.RTN_QTY) AS CNCL_QTY -- 취/반품갯수
    , ROUND(CEIL(AVG(A.ORD_CNT) * 10) / 10, 1) AS AVG_ORD_CNT -- 평균주문수
    , ROUND(CEIL(AVG(A.SELL_QTY) * 10) / 10, 1) AS AVG_SELL_QTY -- 평균주문상품개수
    , FLOOR(SUM(A.ORD_AMT - A.CNCL_AMT - A.RTN_AMT) / SUM(A.ORD_CNT)) AS CUST_PRICE -- 객단가
FROM TB_STAT_CUST_ORD A
WHERE A.DAY >= DATE_FORMAT('20210701', '%Y%m%d') -- 시작일변수
    AND A.DAY <= DATE_FORMAT(CONCAT('20210930', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
    -- AND A.FRONT_GB IN('P','M','A') -- 디바이스
    -- AND A.SEX_GB IN('G007_M','G007_F','G007_X') -- 성별
    -- AND A.AGE IN('10','20','30','40','X')
    -- AND BRAND_CD IN() -- 브랜드
    -- AND ITEMKIND_CD IN() -- 카테고리
GROUP BY A.CUST_GRADE
ORDER BY PRIORITY
;

-- 회원통계 > 연령별주문현황
SELECT
    CASE WHEN A.AGE != 'X' THEN CONCAT(A.AGE, '대') ELSE '50대이상/기타' END AS AGE
    , FN_GET_CODE_NM('G007', A.SEX_GB) AS SEX_GB
    , SUM(A.ORD_AMT - A.CNCL_AMT - A.RTN_AMT) AS TOT_ORD_AMT -- 총매출액
    , SUM(A.ORD_AMT) AS ORD_ATM -- 매출액
    , SUM(A.CNCL_AMT + A.RTN_AMT) AS CNCL_AMT -- 취/반품액
    , SUM(A.ORD_CNT) AS ORD_CNT -- 주문수
    , SUM(A.CNCL_QTY + A.RTN_QTY) AS CNCL_QTY -- 취/반품갯수
    , ROUND(CEIL(AVG(A.ORD_CNT) * 10) / 10, 1) AS AVG_ORD_CNT -- 평균주문수
    , ROUND(CEIL(AVG(A.SELL_QTY) * 10) / 10, 1) AS AVG_SELL_QTY -- 평균주문상품개수
    , FLOOR(SUM(A.ORD_AMT - A.CNCL_AMT - A.RTN_AMT) / SUM(A.ORD_CNT)) AS CUST_PRICE -- 객단가
FROM TB_STAT_CUST_ORD A
WHERE A.DAY >= DATE_FORMAT('20210701', '%Y%m%d') -- 시작일변수
    AND A.DAY <= DATE_FORMAT(CONCAT('20210701', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
    -- AND A.FRONT_GB IN('P','M','A') -- 디바이스
    -- AND A.CUST_GRADE IN('G110_10','G110_20','G110_30','G110_40','G110_50','G110') -- 등급
    -- AND BRAND_CD IN() -- 브랜드
    -- AND ITEMKIND_CD IN() -- 카테고리
GROUP BY A.AGE, A.SEX_GB
ORDER BY A.AGE, A.SEX_GB
;


-- 회원통계 > 구매자랭킹
WITH TAB AS (
        SELECT *
        FROM (
            SELECT A.ORD_NO
                , B.ORD_DTL_NO
                , B.ORD_QTY
                , B.CNCL_RTN_QTY
                , B.ORD_AMT
                , B.CNCL_RTN_AMT
                , B.CPN1_DC_AMT
                , A.PAY_DT
                , A.FRONT_GB
                , C.ITEMKIND_CD
                , C.BRAND_CD
                , E.CUST_NO
                , E.CUST_GRADE
                , RANK() OVER (PARTITION BY B.ORD_DTL_NO ORDER BY B.ORD_DTL_HST_SQ) AS RNK
            FROM TB_ORDER A,
                TB_ORDER_DETAIL_HST B,
                TB_GOODS C,
                TB_CUSTOMER E
            WHERE A.ORD_NO = B.ORD_NO
                AND B.GOODS_CD = C.GOODS_CD
                AND A.CUST_NO = E.CUST_NO
                AND A.PAY_DT >= DATE_FORMAT('20210920', '%Y%m%d')                         -- 시작일변수
                AND A.PAY_DT <= DATE_FORMAT(CONCAT('20210926', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
                AND E.CUST_NO > 0
                AND A.MALL_GB = 'G011_10'
                AND B.ORD_EXCH_GB = 'O'
                AND B.ORD_DTL_STAT = 'G013_20' -- 결제완료
                -- AND A.FRONT_GB IN('P','M','A') -- 디바이스
                -- AND FN_DEC_AES(E.SEX_GB) IN('G007_M','G007_F','G007_X') -- 성별
                /*AND CASE WHEN FN_DEC_AES(E.BIRTH_YMD) = '' OR FN_DEC_AES(E.BIRTH_YMD) IS NULL THEN 'X'
                       WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(E.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 10 AND 19 THEN '10'
                       WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(E.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 20 AND 29 THEN '20'
                       WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(E.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 30 AND 39 THEN '30'
                       WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(E.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 40 AND 49 THEN '40'
                       ELSE 'X' END  IN('10','20','30','40','X') -- 성별*/
                -- AND BRAND_CD IN() -- 브랜드
                -- AND ITEMKIND_CD IN() -- 카테고리
        ) A
        WHERE RNK = 1
    )
    -- 판매수
    , TAB_SELL_QTY AS (
        SELECT
            SUM(ORD_QTY) AS VAL
            , '1' AS GBN
            , CUST_NO
        FROM TAB
        GROUP BY CUST_NO
    )
    -- 매출
    , TAB_ORD AS (
        SELECT
            SUM(ORD_AMT + CPN1_DC_AMT + (SELECT SUM(OPT_ADD_PRICE * ITEM_QTY) FROM TB_ORDER_DETAIL_ITEM X WHERE X.ORD_DTL_NO = A.ORD_DTL_NO)) AS VAL
            , '2' AS GBN
            , CUST_NO
        FROM TAB A
        GROUP BY CUST_NO
    )
    -- 취소갯수
    , TAB_CNCL_QTY AS (
        SELECT SUM(CHG_QTY) AS VAL
            , '3' AS GBN
            , C.CUST_NO
        FROM TB_ORDER_CHANGE_DETAIL A, TB_ORDER_DETAIL B, TB_ORDER C, TB_GOODS E, TB_CUSTOMER G
        WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
            AND B.ORD_NO = C.ORD_NO
            AND B.GOODS_CD = E.GOODS_CD
            AND C.CUST_NO = G.CUST_NO
            AND CHG_STAT = 'G685_18' -- 결제후취소
            AND COMPLETE_DT >= DATE_FORMAT('20210920', '%Y%m%d') -- 시작일변수
            AND COMPLETE_DT <= DATE_FORMAT(CONCAT('20210926', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND MALL_GB = 'G011_10'
            AND G.CUST_NO > 0
            -- AND C.FRONT_GB IN('P','M','A') -- 디바이스
            -- AND FN_DEC_AES(G.SEX_GB) IN('G007_M','G007_F','G007_X') -- 성별
            /*AND CASE WHEN FN_DEC_AES(E.BIRTH_YMD) = '' OR FN_DEC_AES(G.BIRTH_YMD) IS NULL THEN 'X'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 10 AND 19 THEN '10'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 20 AND 29 THEN '20'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 30 AND 39 THEN '30'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 40 AND 49 THEN '40'
                   ELSE 'X' END  IN('10','20','30','40','X') -- 성별*/
            -- AND BRAND_CD IN() -- 브랜드
            -- AND ITEMKIND_CD IN() -- 카테고리
        GROUP BY C.CUST_NO
    )
    -- 반품갯수
    , TAB_RTN_QTY AS (
        SELECT SUM(CHG_QTY) AS VAL
            , '4' AS GBN
            , C.CUST_NO
        FROM TB_ORDER_CHANGE_DETAIL A, TB_ORDER_DETAIL B, TB_ORDER C, TB_GOODS E, TB_CUSTOMER G
        WHERE A.ORD_DTL_NO = B.ORD_DTL_NO
            AND B.ORD_NO = C.ORD_NO
            AND B.GOODS_CD = E.GOODS_CD
            AND C.CUST_NO = G.CUST_NO
            AND CHG_STAT = 'G685_60' -- 반품완료
            AND COMPLETE_DT >= DATE_FORMAT('20210920', '%Y%m%d') -- 시작일변수
            AND COMPLETE_DT <= DATE_FORMAT(CONCAT('20210926', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND MALL_GB = 'G011_10'
            AND G.CUST_NO > 0
            -- AND C.FRONT_GB IN('P','M','A') -- 디바이스
            -- AND FN_DEC_AES(G.SEX_GB) IN('G007_M','G007_F','G007_X') -- 성별
            /*AND CASE WHEN FN_DEC_AES(E.BIRTH_YMD) = '' OR FN_DEC_AES(G.BIRTH_YMD) IS NULL THEN 'X'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 10 AND 19 THEN '10'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 20 AND 29 THEN '20'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 30 AND 39 THEN '30'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 40 AND 49 THEN '40'
                   ELSE 'X' END  IN('10','20','30','40','X') -- 성별*/
            -- AND BRAND_CD IN() -- 브랜드
            -- AND ITEMKIND_CD IN() -- 카테고리
        GROUP BY C.CUST_NO
    )
    -- 취소액
    , TAB_CNCL_AMT AS (
        SELECT 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)) * B.CHG_QTY) AS VAL
            , '5' AS GBN
            , D.CUST_NO
        FROM TB_ORDER_CHANGE_DETAIL B, TB_ORDER_DETAIL C, TB_ORDER D, TB_GOODS E, TB_CUSTOMER G
        WHERE B.ORD_DTL_NO = C.ORD_DTL_NO
            AND C.ORD_NO = D.ORD_NO
            AND C.GOODS_CD = E.GOODS_CD
            AND D.CUST_NO = G.CUST_NO
            AND CHG_STAT = 'G685_18' -- 결제후취소
            AND B.COMPLETE_DT >= DATE_FORMAT('20210920', '%Y%m%d') -- 시작일변수
            AND B.COMPLETE_DT <= DATE_FORMAT(CONCAT('20210926', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND MALL_GB = 'G011_10'
            AND G.CUST_NO > 0
            -- AND D.FRONT_GB IN('P','M','A') -- 디바이스
            -- AND FN_DEC_AES(G.SEX_GB) IN('G007_M','G007_F','G007_X') -- 성별
            /*AND CASE WHEN FN_DEC_AES(E.BIRTH_YMD) = '' OR FN_DEC_AES(G.BIRTH_YMD) IS NULL THEN 'X'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 10 AND 19 THEN '10'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 20 AND 29 THEN '20'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 30 AND 39 THEN '30'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 40 AND 49 THEN '40'
                   ELSE 'X' END  IN('10','20','30','40','X') -- 성별*/
            -- AND BRAND_CD IN() -- 브랜드
            -- AND ITEMKIND_CD IN() -- 카테고리
        GROUP BY D.CUST_NO
    )
    -- 반품액
    , TAB_RTN_AMT AS (
        SELECT 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)) * B.CHG_QTY) AS VAL
            , '6' AS GBN
            , D.CUST_NO
        FROM TB_ORDER_CHANGE_DETAIL B, TB_ORDER_DETAIL C, TB_ORDER D, TB_GOODS E, TB_CUSTOMER G
        WHERE B.ORD_DTL_NO = C.ORD_DTL_NO
            AND C.ORD_NO = D.ORD_NO
            AND C.GOODS_CD = E.GOODS_CD
            AND D.CUST_NO = G.CUST_NO
            AND CHG_STAT = 'G685_60' -- 반품완료
            AND B.COMPLETE_DT >= DATE_FORMAT('20210920', '%Y%m%d') -- 시작일변수
            AND B.COMPLETE_DT <= DATE_FORMAT(CONCAT('20210926', '235959'), '%Y%m%d%H%i%s') -- 종료일변수
            AND MALL_GB = 'G011_10'
            AND G.CUST_NO > 0
            -- AND D.FRONT_GB IN('P','M','A') -- 디바이스
            -- AND FN_DEC_AES(G.SEX_GB) IN('G007_M','G007_F','G007_X') -- 성별
            /*AND CASE WHEN FN_DEC_AES(E.BIRTH_YMD) = '' OR FN_DEC_AES(G.BIRTH_YMD) IS NULL THEN 'X'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 10 AND 19 THEN '10'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 20 AND 29 THEN '20'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 30 AND 39 THEN '30'
                   WHEN (DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(CONCAT(SUBSTRING(FN_DEC_AES(G.BIRTH_YMD), 1, 4), '0101'), '%Y') + 1) BETWEEN 40 AND 49 THEN '40'
                   ELSE 'X' END  IN('10','20','30','40','X') -- 성별*/
            -- AND BRAND_CD IN() -- 브랜드
            -- AND ITEMKIND_CD IN() -- 카테고리
        GROUP BY D.CUST_NO
    )
   -- 주문수
   , TAB_ORD_CNT AS (
        SELECT
            SUM(VAL) AS VAL
             , '7' AS GBN
             , CUST_NO
        FROM (
                 SELECT
                     1 AS VAL
                      , ORD_NO
                      , CUST_NO
                 FROM TAB
                 GROUP BY ORD_NO, CUST_NO
             ) A
        GROUP BY CUST_NO
    )
    SELECT
        CONCAT(SUBSTRING(CUST_ID, 1, 3), '****', '(', SUBSTRING(FN_DEC_AES(CUST_NM), 1, 1), '**', ')') AS CUST_ID -- 아이디
        , FN_GET_CODE_NM('G110', CUST_GRADE) AS CUST_GRADE -- 등급
        , SUM(ORD_AMT - CNCL_AMT - RTN_AMT) AS TOT_ORD_AMT -- 매출액
        , SUM(ORD_CNT) AS ORD_CNT -- 주문수
        , SUM(SELL_QTY - CNCL_QTY - RTN_QTY) AS SELL_QTY -- 판매수
        , FLOOR(SUM(ORD_AMT - CNCL_AMT - RTN_AMT) / SUM(ORD_CNT)) AS CUST_PRICE -- 객단가
        , IFNULL((SELECT SUM(RM_PNT_AMT) FROM TB_CUST_POINT CP WHERE CP.CUST_NO = X.CUST_NO AND EXP_CMP_DT IS NULL), 0) AS CUST_POINT -- 회원포인트
    FROM (
        SELECT
            CUST_NO
            , SUM(CASE WHEN GBN = '1' THEN VAL ELSE 0 END) AS SELL_QTY -- 판매수
            , SUM(CASE WHEN GBN = '2' THEN VAL ELSE 0 END) AS ORD_AMT -- 매출액(판매금액)
            , SUM(CASE WHEN GBN = '3' THEN VAL ELSE 0 END) AS CNCL_QTY -- 취소수량
            , SUM(CASE WHEN GBN = '4' THEN VAL ELSE 0 END) AS RTN_QTY -- 반품수량
            , SUM(CASE WHEN GBN = '5' THEN VAL ELSE 0 END) AS CNCL_AMT -- 취소금액
            , SUM(CASE WHEN GBN = '6' THEN VAL ELSE 0 END) AS RTN_AMT -- 반품금액
            , SUM(CASE WHEN GBN = '7' THEN VAL ELSE 0 END) AS ORD_CNT -- 주문수
        FROM (
            SELECT * FROM TAB_SELL_QTY
            UNION ALL
            SELECT * FROM TAB_ORD
            UNION ALL
            SELECT * FROM TAB_CNCL_QTY
            UNION ALL
            SELECT * FROM TAB_RTN_QTY
            UNION ALL
            SELECT * FROM TAB_CNCL_AMT
            UNION ALL
            SELECT * FROM TAB_RTN_AMT
            UNION ALL
            SELECT * FROM TAB_ORD_CNT
        ) A
        GROUP BY CUST_NO
    ) X, TB_CUSTOMER Y
    WHERE X.CUST_NO = Y.CUST_NO
    GROUP BY X.CUST_NO
    ORDER BY TOT_ORD_AMT DESC
    LIMIT 50
;





-- 회원통계 > 회원활동현황 > 미로그인현황
SELECT
    X.*
    , ROUND(CNT_5 / TOT_CNT * 100) AS PER_CNT_5
    , ROUND(CNT_4 / TOT_CNT * 100) AS PER_CNT_4
    , ROUND(CNT_3 / TOT_CNT * 100) AS PER_CNT_3
    , ROUND(CNT_2 / TOT_CNT * 100) AS PER_CNT_2
    , ROUND(CNT_1 / TOT_CNT * 100) AS PER_CNT_1
FROM (
    SELECT
        SUM(CASE WHEN GB = 5 THEN CNT ELSE 0 END) AS CNT_5
        , SUM(CASE WHEN GB = 4 THEN CNT ELSE 0 END) AS CNT_4
        , SUM(CASE WHEN GB = 3 THEN CNT ELSE 0 END) AS CNT_3
        , SUM(CASE WHEN GB = 2 THEN CNT ELSE 0 END) AS CNT_2
        , SUM(CASE WHEN GB = 1 THEN CNT ELSE 0 END) AS CNT_1
        , SUM(CNT) AS TOT_CNT
    FROM (
        -- 1년
        SELECT COUNT(1) AS CNT
            , 5 AS GB -- 1년 ~
        FROM TB_CUSTOMER
        WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 YEAR ), '%Y%m%d%H%i%s') -- now() 수정 : 조회시점의 시간까지만. ex) 2019년9월25일15시 조회 -> 20210925150000
        -- 12개월
        UNION ALL
        SELECT COUNT(1) AS CNT
            , 4 AS GB -- 9개월~12개월
        FROM TB_CUSTOMER
        WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 YEAR ), '%Y%m%d%H%i%s')
            AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -9 MONTH ), '%Y%m%d%H%i%s')
        -- 9개월
        UNION ALL
        SELECT COUNT(1) AS CNT
            , 3 AS GB -- 6개월~9개월
        FROM TB_CUSTOMER
        WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -9 MONTH ), '%Y%m%d%H%i%s')
            AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
        -- 6개월
        UNION ALL
        SELECT COUNT(1) AS CNT
            , 2 AS GB -- 3개월~6개월
        FROM TB_CUSTOMER
        WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
            AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
        -- 3개월
        UNION ALL
        SELECT COUNT(1) AS CNT
            , 1 AS GB -- ~3개월
        FROM TB_CUSTOMER
        WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
            AND LOGIN_LDT < DATE_FORMAT(NOW(), '%Y%m%d%H%i%s')
    ) Z
) X
;

-- 회원통계 > 회원활동현황 > 미로그인현황 > 엑셀다운로드
SELECT
    CUST_ID
    , FN_DEC_AES(CUST_NM) AS CUST_NM
    , TIMESTAMPDIFF(MONTH, LOGIN_LDT, NOW()) AS MONTHS
    , FN_DEC_AES(CELL_PHNNO) AS CELL_PHNNO
    , FN_DEC_AES(EMAIL) AS EMAIL
    , SUBSTRING(FN_DEC_AES(BIRTH_YMD), 1, 6) AS BIRTH_YM
FROM TB_CUSTOMER
WHERE CUST_NO > 0
    AND CUST_STAT = 'G104_10'
    AND LOGIN_LDT < DATE_FORMAT(STR_TO_DATE(DATE_FORMAT(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), '%Y%m%d%H'), '%Y%m%d%H'), '%Y%m%d%H%i%s') -- now() 수정 : 조회시점의 시간까지만. ex) 2019년9월25일15시 조회 -> 20210925150000
;

-- 회원통계 > 회원활동현황 > 마케팅수신동의현황
-- SMS
SELECT
    X.*
    , ROUND(CNT_4 / TOT_CNT * 100) AS PER_CNT_4
    , ROUND(CNT_3 / TOT_CNT * 100) AS PER_CNT_3
    , ROUND(CNT_2 / TOT_CNT * 100) AS PER_CNT_2
    , ROUND(CNT_1 / TOT_CNT * 100) AS PER_CNT_1
FROM (
     SELECT SUM(CASE WHEN GB = 4 THEN CNT ELSE 0 END) AS CNT_4
          , SUM(CASE WHEN GB = 3 THEN CNT ELSE 0 END) AS CNT_3
          , SUM(CASE WHEN GB = 2 THEN CNT ELSE 0 END) AS CNT_2
          , SUM(CASE WHEN GB = 1 THEN CNT ELSE 0 END) AS CNT_1
          , SUM(CNT)                                  AS TOT_CNT
     FROM (
          SELECT COUNT(1) AS CNT
               , 4        AS GB
          FROM TB_CUSTOMER
          WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND SMS_AGREE_YN = 'Y'
          -- EMAIL
          UNION ALL
          SELECT COUNT(1) AS CNT
               , 3        AS GB
          FROM TB_CUSTOMER
          WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND EMAIL_AGREE_YN = 'Y'
          -- 푸쉬
          UNION ALL
          SELECT COUNT(1) AS CNT
               , 2        AS GB
          FROM TB_CUSTOMER
          WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND APP_AGREE_YN = 'Y'
          -- 미동의
          UNION ALL
          SELECT COUNT(1) AS CNT
               , 1        AS GB
          FROM TB_CUSTOMER
          WHERE CUST_NO > 0
            AND CUST_STAT = 'G104_10'
            AND SMS_AGREE_YN = 'N'
            AND EMAIL_AGREE_YN = 'N'
            AND APP_AGREE_YN = 'N'
      ) Z
 ) X
;

-- 회원통계 > 회원활동현황 > 휴면전환 잔여일별현황
SELECT
    SUM(CASE WHEN GB = 4 THEN CNT ELSE 0 END) AS CNT_4 -- 12개월
    , SUM(CASE WHEN GB = 3 THEN CNT ELSE 0 END) AS CNT_3 -- 9개월
    , SUM(CASE WHEN GB = 2 THEN CNT ELSE 0 END) AS CNT_2 -- 6개월
    , SUM(CASE WHEN GB = 1 THEN CNT ELSE 0 END) AS CNT_1 -- 9개월
FROM (
    -- 12개월
    SELECT COUNT(1) AS CNT
        , 4 AS GB -- 12개월
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT > DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 DAY ), '%Y%m%d%H%i%s') -- now() 수정 : 조회시점의 시간까지만. ex) 2019년9월25일15시 조회 -> 20210925150000
    -- 9개월
    UNION ALL
    SELECT COUNT(1) AS CNT
        , 3 AS GB -- 9개월
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 DAY ), '%Y%m%d%H%i%s')
    -- 6개월
    UNION ALL
    SELECT COUNT(1) AS CNT
        , 2 AS GB -- 6개월
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
    -- 3개월
    UNION ALL
    SELECT COUNT(1) AS CNT
        , 1 AS GB -- ~3개월
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -9 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
) Z
;


-- 회원통계 > 회원활동현황 > 휴면전환 잔여일별현황 > 엑셀다운로드
SELECT
    *
FROM (
    -- 12개월
    SELECT '12개월' AS GB -- 12개월
        , CUST_ID
        , FN_DEC_AES(CUST_NM) AS CUST_NM
        , FN_DEC_AES(CELL_PHNNO) AS CELL_PHNNO
        , FN_DEC_AES(EMAIL) AS EMAIL
        , SUBSTRING(FN_DEC_AES(BIRTH_YMD), 1, 6) AS BIRTH_YM
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT > DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 DAY ), '%Y%m%d%H%i%s') -- now() 수정 : 조회시점의 시간까지만. ex) 2019년9월25일15시 조회 -> 20210925150000
    -- 9개월
    UNION ALL
    SELECT '9개월' AS GB -- 9개월
        , CUST_ID
        , FN_DEC_AES(CUST_NM) AS CUST_NM
        , FN_DEC_AES(CELL_PHNNO) AS CELL_PHNNO
        , FN_DEC_AES(EMAIL) AS EMAIL
        , SUBSTRING(FN_DEC_AES(BIRTH_YMD), 1, 6) AS BIRTH_YM
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -1 DAY ), '%Y%m%d%H%i%s')
    -- 6개월
    UNION ALL
    SELECT '6개월' AS GB -- 6개월
        , CUST_ID
        , FN_DEC_AES(CUST_NM) AS CUST_NM
        , FN_DEC_AES(CELL_PHNNO) AS CELL_PHNNO
        , FN_DEC_AES(EMAIL) AS EMAIL
        , SUBSTRING(FN_DEC_AES(BIRTH_YMD), 1, 6) AS BIRTH_YM
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -3 MONTH ), '%Y%m%d%H%i%s')
    -- 3개월
    UNION ALL
    SELECT '3개월' AS GB -- ~3개월
        , CUST_ID
        , FN_DEC_AES(CUST_NM) AS CUST_NM
        , FN_DEC_AES(CELL_PHNNO) AS CELL_PHNNO
        , FN_DEC_AES(EMAIL) AS EMAIL
        , SUBSTRING(FN_DEC_AES(BIRTH_YMD), 1, 6) AS BIRTH_YM
    FROM TB_CUSTOMER
    WHERE CUST_NO > 0
        AND CUST_STAT = 'G104_10'
        AND LOGIN_LDT >= DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -9 MONTH ), '%Y%m%d%H%i%s')
        AND LOGIN_LDT < DATE_FORMAT(DATE_ADD(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL -6 MONTH ), '%Y%m%d%H%i%s')
) Z
;