(주)카카오 구매상세내역 데이터 확인

구매상세내역 데이터 누락 여부 확인

일자별

• 집계함수를 제외한 select문의 모든 열은 group by 절에 있어야 한다.
• order by는 항상 문장 마지막 위치

select date,
    count(distinct shop_id) as shop_cnt
from report.sale_report
where date between '20220131' and '20220206'
group by date
order by date

[결과]
date	shop_cnt	
20220131	12	
20220201	11	
20220202	12	
20220203	12	
20220204	12	
20220205	12	
20220206	12

몰별

select shop_id , count(distinct date) as date_cnt
from report.sale_report
where date between '20220131' and '20220206'
group by shop_id
order by shop_id

[결과]
shop_id	date_cnt	
11st	    7	
auction	    7 	
boriboris	7	
g9today	    7	
gmarket	    7	
halfclub	7	
hmall	    7	
lotteimall	7	
namu-internet	7	
nseshop	    7	
ssg14	    7	
w-shopping	6	

특정 몰별 일자별

select date,
    sum(purchase_price) as purchase_price
from report.sale_report
where date between '20220101' and '20220131'
and shop_id ='auction'
group by date
order by date;

[결과]
date	purchase_price	
20220101	386213130	
20220102	51976051	
20220103	69914618	
20220104	344679754	
20220105	574393585	
20220106	507204152	
20220107	481653212	
20220108	392503830	
20220109	58011698	
20220110	60382338	
20220111	305457327	
20220112	591278331	
20220113	501820819	
20220114	392852623	
20220115	369088796	
20220116	319101819	
20220117	80816920	
20220118	207134435	
20220119	614433771	
20220120	517824344	
20220121	494013877	
20220122	406805846	
20220123	301327671	
20220124	61474550	
20220125	218490641	
20220126	521382826	
20220127	478859274	
20220128	388192756	
20220129	395336211	
20220130	301002131	
20220131	30330954