-- 회원통계 > 가입통계 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 ;