엑셀로 시트/피벗 나누어서 수식 한번으로 안되는 데이터를 가공하듯,
임시 테이블을 통해서 데이터 가공을 용이하게 함.
--1단계. 오퍼가 적용된 MBR_ID, 주문번호, 오퍼ID 추출
IF OBJECT_ID('TEMPDB.DBO.#TMP_OFFER') IS NOT NULL DROP TABLE #TMP_OFFER
CREATE TABLE #TMP_OFFER
WITH (DISTRIBUTION=HASH(ORORD_NO), CLUSTERED INDEX ([ORORD_NO] ASC))
AS
SELECT OFFER_ID, P.MBR_ID
, ORORD_NO
FROM SCOM_DW..PROM_APL_DTLC P
WHERE P.OFFER_ID IN ('1101719853',
'1101719855',
'1101719852',
'1101719846',
'1101719863',
'1101719851'
) -- 조회하고자 하는 오퍼ID 작성
GROUP BY OFFER_ID, P.MBR_ID, ORORD_NO
-- 2단계. 주문테이블 편집
IF OBJECT_ID('TEMPDB..#ORD_ACCUM') IS NOT NULL DROP TABLE TEMPDB..#ORD_ACCUM
CREATE TABLE TEMPDB..#ORD_ACCUM
WITH (LOCATION = USER_DB, DISTRIBUTION = HASH(MBR_ID), CLUSTERED INDEX (MBR_ID))
AS
SELECT
DATEFROMPARTS(YEAR(A.ORORD_DT), MONTH(A.ORORD_DT), 1) AS ORORD_MM
, A.ORORD_DT
, A.MBR_ID
, A.ORORD_NO
, CASE WHEN A.BIZ_DIV_CD IN ('20', '30', '80') THEN 'GRO' ELSE 'LIFE' END AS BIZ_GRP
, B.MBRSP_ORD_YN
, ISNULL(B.PNTIME_MBRSP_JOIN_SITE_CD, 199) AS MBRSP_JOIN_SITE_CD
, SUM(A.ORD_AMT) AS ORD_AMT -- 주문금액
, SUM(A.VAT_EXCL_RLORD_AMT) AS VAT_EXCL_RLORD_AMT -- 주문금액(세제외)
, SUM(A.RLORD_AMT) AS RLORD_AMT -- 실주문금액
, SUM(A.ORD_QTY) AS ORD_QTY
FROM SCOM_USERSET..CUST_PLAN_ORD_ACCUM A
LEFT JOIN SCOM_DW..ORD_MBR B ON A.ORORD_NO = B.ORORD_NO
WHERE A.ORD_QTY > 0
AND A.ORORD_DT BETWEEN '2023-03-01' AND '2023-03-12'
GROUP BY DATEFROMPARTS(YEAR(A.ORORD_DT), MONTH(A.ORORD_DT), 1)
, A.ORORD_DT
, A.MBR_ID
, A.ORORD_NO
, CASE WHEN A.BIZ_DIV_CD IN ('20', '30', '80') THEN 'GRO' ELSE 'LIFE' END
, B.MBRSP_ORD_YN
, B.PNTIME_MBRSP_JOIN_SITE_CD
-- 3단계. 주문테이블 편집2
IF OBJECT_ID('TEMPDB..#BIZ_GRP_NEW_CUST') IS NOT NULL DROP TABLE #BIZ_GRP_NEW_CUST
CREATE TABLE #BIZ_GRP_NEW_CUST
WITH (LOCATION = USER_DB, DISTRIBUTION = HASH(MBR_ID), CLUSTERED INDEX (MBR_ID))
AS
SELECT
ORORD_MM
, MBR_ID
, BIZ_GRP
, 'NEW' AS MBR_DIV_CD
FROM SCOM_USERSET..ORD_ITEM_BIZ_BF_ORD_DT_PSB
WHERE RN = 1
AND ORORD_DT BETWEEN '2023-03-01' AND '2023-03-12'
UNION ALL
SELECT
ORORD_MM
, MBR_ID
, BIZ_GRP
, 'WB' AS MBR_DIV_CD
FROM SCOM_USERSET..ORD_ITEM_BIZ_BF_ORD_DT_PSB
WHERE DATE_DIFF >= 365
AND ORORD_DT BETWEEN '2023-03-01' AND '2023-03-12'
--4단계. 주문테이블 편집2 _ #TMP_OFFER 조인
IF OBJECT_ID('TEMPDB..#ORD_ACCUM_2') IS NOT NULL DROP TABLE TEMPDB..#ORD_ACCUM_2
CREATE TABLE TEMPDB..#ORD_ACCUM_2
WITH (LOCATION = USER_DB, DISTRIBUTION = HASH(MBR_ID), CLUSTERED INDEX (MBR_ID))
AS
SELECT
A.*
, C.MBR_GRD_CD
, C.MBR_DIV_CD
, E.SSG_MBRSP_CONT
, E.GMK_MBRSP_CONT
, CASE WHEN D.MBR_ID IS NOT NULL AND D.BIZ_GRP = 'GRO' AND D.MBR_DIV_CD = 'NEW' THEN '01.NEW_GRO'
WHEN D.MBR_ID IS NOT NULL AND D.BIZ_GRP = 'LIFE' AND D.MBR_DIV_CD = 'NEW' THEN '02.NEW_LIFE'
WHEN D.MBR_ID IS NOT NULL AND D.BIZ_GRP = 'GRO' AND D.MBR_DIV_CD = 'WB' THEN '03.WB_GRO'
WHEN D.MBR_ID IS NOT NULL AND D.BIZ_GRP = 'LIFE' AND D.MBR_DIV_CD = 'WB' THEN '04.WB_LIFE'
ELSE '05.NOR' END AS CTG_DIV_CD
FROM TEMPDB..#ORD_ACCUM A
LEFT JOIN SCOM_USERSET..CUST_PLAN_MBR_INFO C ON A.MBR_ID = C.MBR_ID AND A.ORORD_MM = C.DT
LEFT JOIN TEMPDB..#BIZ_GRP_NEW_CUST D ON A.MBR_ID = D.MBR_ID AND A.ORORD_MM = D.ORORD_MM AND A.BIZ_GRP = D.BIZ_GRP
LEFT JOIN SCOM_USERSET..MBRSP_MONTHLY_PSB E ON A.MBR_ID = E.MBR_ID AND A.ORORD_MM = E.DT
WHERE A.ORORD_DT BETWEEN '2023-03-01' AND '2023-03-12'
AND C.MBR_STAT_CD = '10'
AND C.MBR_TYPE_CD IN ('10','25','40','50','55','60','70','80','98','99')
AND A.ORORD_NO IN (SELECT DISTINCT ORORD_NO FROM #TMP_OFFER)
SELECT TOP 10 * FROM TEMPDB..#ORD_ACCUM_2
--5. 쿠폰발급수용 테이블 생성
IF OBJECT_ID('TEMPDB..#ORD_ACCUM_5') IS NOT NULL DROP TABLE TEMPDB..#ORD_ACCUM_5
CREATE TABLE TEMPDB..#ORD_ACCUM_5
WITH (LOCATION = USER_DB, DISTRIBUTION = HASH(ORORD_DT), CLUSTERED INDEX (ORORD_DT))
AS
SELECT
'2023-03-01' AS ORORD_DT
, ORORD_MM
, BIZ_GRP
, CASE WHEN MBR_DIV_CD = 'NEW' THEN '01.NEW'
WHEN CTG_DIV_CD = '01.NEW_GRO' THEN '02.NEW_GRO'
WHEN CTG_DIV_CD = '02.NEW_LIFE' THEN '03.NEW_LIFE'
WHEN MBR_DIV_CD = 'WB' THEN '04.WB'
WHEN CTG_DIV_CD = '03.WB_GRO' THEN '05.WB_GRO'
WHEN CTG_DIV_CD = '04.WB_LIFE' THEN '06.WB_LIFE'
WHEN SSG_MBRSP_CONT > 0 AND MBR_GRD_CD = '40' THEN '07.SSG_PAY_GOLD'
WHEN SSG_MBRSP_CONT > 0 AND MBR_GRD_CD IN ('30', '20', '10', '199')THEN '08.SSG_PAY_FRIENDS'
WHEN SSG_MBRSP_CONT > 0 AND MBR_GRD_CD = '50' THEN '09.SSG_NOPAY'
WHEN GMK_MBRSP_CONT > 0 AND MBR_GRD_CD = '50' THEN '10.GMK_VIP'
WHEN GMK_MBRSP_CONT > 0 AND MBR_GRD_CD = '40' THEN '10.GMK_GOLD'
WHEN GMK_MBRSP_CONT > 0 AND MBR_GRD_CD IN ('30', '20', '10', '199')THEN '10.GMK_FRIENDS'
WHEN MBR_GRD_CD = '50' THEN '11.VIP'
WHEN MBR_GRD_CD = '40' THEN '12.GOLD'
ELSE '13.NOR'
END AS MBR_DIV
, MBR_ID
FROM TEMPDB..#ORD_ACCUM_2
SELECT TOP 10 * FROM TEMPDB..#ORD_ACCUM_5
--6. 오퍼 쿠폰 발급수 구하기
SELECT ISSUE_DT , OFFER_ID
, COUNT(DISTINCT CPN_ISSUE_NO) AS N'쿠폰발급건수'
, COUNT(DISTINCT M.MBR_ID) AS N'쿠폰발급회원수'
, MBR_DIV
FROM SCOM_ODS..MBR_CPN M
LEFT JOIN TEMPDB..#ORD_ACCUM_5 O ON M.MBR_ID=O.MBR_ID
WHERE 1=1
AND OFFER_ID IN ('1101719853',
'1101719855',
'1101719852',
'1101719846',
'1101719863',
'1101719851') -- 쿠폰번호
AND REGPE_ID <> 'CPN_GIFT' -- 선물받은 쿠폰 제외
--AND ISSUE_DT BETWEEN '20210401' AND '20210430'
AND CPN_RSN_CD = '99' -- 환불 재발급 제외 (최초 발급 쿠폰 기준)
GROUP BY ISSUE_DT , OFFER_ID, MBR_DIV
--7.오퍼실적 구하기
SELECT
T.OFFER_ID
, MBR_DIV
, SUM(CONVERT(BIGINT,ROUND(A.ORD_AMT / VATRT,0)) - CONVERT(BIGINT,ROUND(A.DC_AMT / VATRT,0)) + A.VAT_EXCL_ORD_CST) AS N'실주문금액_세제외'
, COUNT(DISTINCT CASE WHEN A.ORD_QTY > 0 AND A.ORD_ITEM_DIV_CD = '011' AND A.ORD_APL_TGT_CD IN ('1', '2') THEN A.ORORD_NO ELSE NULL END)
-COUNT(DISTINCT CASE WHEN ORD_WHL_CNCL_YN = 'Y' AND A.ORD_ITEM_DIV_CD IN ('021','031') AND A.ORD_APL_TGT_CD IN ('1', '2') THEN A.ORORD_NO ELSE NULL END) AS N'주문건수'
, COUNT(DISTINCT CASE WHEN A.ORD_QTY > 0 AND A.ORD_ITEM_DIV_CD = '011' AND ORD_INVLD_YN = 'N' AND A.ORD_APL_TGT_CD IN ('1', '2') THEN A.MBR_ID ELSE NULL END) AS N'주문회원수'
, COUNT(DISTINCT CASE WHEN A.ORD_QTY > 0 AND A.ORD_ITEM_DIV_CD = '011' AND A.ORD_APL_TGT_CD IN ('1', '2') THEN A.MBR_ID ELSE NULL END) AS N'원주문회원수'
, COUNT(DISTINCT CASE WHEN A.ORD_QTY > 0 AND A.ORD_ITEM_DIV_CD = '011' AND A.ORD_APL_TGT_CD IN ('1','2') THEN A.ORORD_NO ELSE NULL END) AS N'원주문건수'
, SUM(VAT_EXCL_MRG_AMT) AS N'마진금액_세제외'
, SUM(ITEM_PRFT_AMT) AS N'상품이익금액_세제외'
, SUM(CONVERT(BIGINT,ROUND(A.DC_AMT / VATRT,0))) AS N'할인금액_세제외'
, SUM(CONVERT(BIGINT, ROUND(A.OWNCO_BDN_MD_DC_AMT /A.VATRT, 0))) AS N'MD자사할인금액_세제외'
, SUM(CONVERT(BIGINT, ROUND(A.OWNCO_BDN_MKTG_DC_AMT /A.VATRT, 0))) AS N'MKT자사할인금액_세제외'
FROM SCOM_DW..ORD_ITEM A
JOIN SCOM_DW..DW_NACTRLT_DIV ACT ON A.ACTRLT_DIV_CD = ACT.ACTRLT_DIV_CD
JOIN #TMP_OFFER T ON A.ORORD_NO = T.ORORD_NO
JOIN #ORD_ACCUM_5 O ON O.MBR_ID = A.MBR_ID
WHERE 1=1
--AND A.ORD_DT BETWEEN '20210101' AND '20211231' -- 주문일자 기준의 실적을 확인하시는 경우, 이 조건을 추가하시면 됩니다.
AND A.ORD_APL_TGT_CD IN ('1','2','A','B')
AND A.ITEM_ID NOT IN ('Z!','Z~')
AND LEFT(A.CONTR_LRNK_SPL_VEN_ID, 1) <> 'T'
AND ACT.MD_ACTRLT_INCL_YN = 'Y' -- T몰/글로벌 실적 제외
AND A.ORORD_NO IN (SELECT DISTINCT ORORD_NO FROM #TMP_OFFER)
GROUP BY T.OFFER_ID, MBR_DIV
공부하는 것들을 적는 블로그.