결과집합의 레코드갯수를 줄여주는 기능을 함
적용전 : (주문상태, 금액) 별로 세세하게 그룹핑됨
적용후 : (주문상태, 금액구간) 별로 큼직큼직하게 그룹핑
SELECT
T.ORD_ST,
T.ORD_AMT,
COUNT(*) ORD_CNT
FROM T_ORD T
GROUP BY
T.ORD_ST,
T.ORD_AMT
ORDER BY 1,2 DESC;
ORD_ST (기준) ORD_AMT (기준) ORD_CNT
-------------------------------------------
COMP 7700 1
COMP 7500 1
COMP 7300 1
COMP 7200 2
COMP 7100 2
COMP 7000 2
COMP 6900 2
COMP 6820 1
COMP 6800 3
COMP 6700 4
COMP 6600 3
COMP 6530 1
COMP 6500 3
COMP 6400 2
COMP 6300 3
COMP 6200 4
COMP 6100 4
COMP 6000 4
COMP 5900 4
COMP 5800 5
COMP 5700 6
COMP 5600 5
COMP 5500 5
COMP 5460 1
COMP 5400 4
COMP 5300 5
COMP 5200 6
COMP 5100 6
………………….
………………….
………………….
COMP 300 78
COMP 200 66
COMP 100 77
SELECT
T.ORD_ST,
CASE WHEN T.ORD_AMT >= 5000 THEN 'High Order'
WHEN T.ORD_AMT >= 3000 THEN 'Middle Order'
ELSE 'Low Order'
END ORD_AMT_TP,
COUNT(*) ORD_CNT
FROM T_ORD T
GROUP BY
T.ORD_ST,
CASE WHEN T.ORD_AMT >= 5000 THEN 'High Order'
WHEN T.ORD_AMT >= 3000 THEN 'Middle Order'
ELSE 'Low Order'
END
ORDER BY 1,2 DESC
;
**ORD_ST** (기준) **ORD_AMT_TP ORD_CNT
------------- ---------- -------**
COMP **High Order 90**
COMP Low Order 2255
COMP Middle Order 397
기본쿼리 : 월 단위 총집계
개선1 : 월 단위 분석용컬럼집계
개선2 : 월 ‘구간’ 단위 분석용컬럼집계
--월 단위 총집계
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
, COUNT(*) CNT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM')
;
YYYYMM (기준) CNT
------------ ----------
201701 218
201702 178
201703 167
201704 194
201705 251
201706 243
201707 252
201708 251
201709 243
201710 251
201711 243
201712 251
-- 월단위 집계 & 열 세분화
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
,SUM(CASE WHEN T1.PAY_TP = 'BANK' THEN 1 END) BANK_PAY_CNT
,SUM(CASE WHEN T1.PAY_TP = 'CARD' THEN 1 END) CARD_PAY_CNT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM')
;
**ORD_YM** (기준) **CNT** **ORD_YM** (기준) **BANK_PAY_CNT CARD_PAY_CNT**
------------ ---------- ------------ ------------ ------------
201701 **218** 201701 **73 145**
201702 178 201702 59 119
201703 167 201703 57 110
201704 194 201704 63 131
201705 251 **->** 201705 84 167
201706 243 201706 81 162
201707 252 201707 84 168
201708 251 201708 84 167
201709 243 201709 81 162
201710 251 201710 84 167
201711 243 201711 81 162
201712 251 201712 84 167
--월 ‘구간’ 단위 분석용컬럼집계
SELECT
case when TO_CHAR(T1.ORD_DT,'YYYYMM') == '201701' THEN '20171'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201704' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201707' THEN '2017_Q2'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201707' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201710' THEN '2017_Q3'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201710' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201801' THEN '2017_Q4'
ELSE 'ELSE'
END SEASON
,SUM(CASE WHEN T1.PAY_TP = 'BANK' THEN 1 END) BANK_PAY_CNT
,SUM(CASE WHEN T1.PAY_TP = 'CARD' THEN 1 END) CARD_PAY_CNT
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY
case when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201701' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201704' THEN '2017_Q1'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201704' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201707' THEN '2017_Q2'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201707' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201710' THEN '2017_Q3'
when TO_CHAR(T1.ORD_DT,'YYYYMM') >= '201710' AND TO_CHAR(T1.ORD_DT,'YYYYMM') < '201801' THEN '2017_Q4'
ELSE 'ELSE'
END
ORDER BY 1
;
**SEASON** (기준) **BANK_PAY_CNT CARD_PAY_CNT**
-------------- ------------ ------------
2017_Q1 189 374
2017_Q2 228 460
2017_Q3 249 497
2017_Q4 249 496
(주문건수는 월별로 컬럼늘리기)
**[앞에서 살펴본 결과집합]**
-- 일자별 결제유형단위 집계
-- 위아래로 길게 데이터를 보여줌
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM
,SUM(CASE WHEN T1.PAY_TP = 'BANK' THEN 1 END) BANK
,SUM(CASE WHEN T1.PAY_TP = 'CARD' THEN 1 END) CARD
FROM T_ORD T1
WHERE T1.ORD_ST = 'COMP'
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM')
;
ORD_YM BANK CARD
------------ ---------- ----------
201701 73 145
201702 59 119
201703 57 110
201704 63 131
201705 84 167
201706 81 162
201707 84 168
201708 84 167
201709 81 162
201710 84 167
201711 81 162
201712 84 167