[분석실습] SQL 데이터 사칙연산 심화 (날짜변환, 연산, 고유값)

안녕하세요.

이번 포스팅에서는, SQL의 사칙연산 심화 구문들을 준비했습니다.

실무 데이터 분석을 하신다면 꼼꼼히 봐주세요

 

사칙연산 심화

 

 

거래데이터, 고객데이터를 이용해서 인당 평균 구매 수량과 금액을 구해보겠습니다

 

1. 인당 구매수량 

(상품코드 goodcd의 갯수를 세어서 계산. 한번의 거래row에 상품을 1개씩만 샀다고 가정)

select 
htrc.custid
, count(goodcd) --상품코드 갯수->상품갯수
from hds_transactions_rea_csv htrc 
join hds_customers_rea_csv hcrc on htrc.custid = hcrc.custid 
group by 1

고객별로 몇개의 상품을 샀는지 확인할 수 있습니다

 

*고객 수

아래처럼 고객 id를 세어보면 136명 입니다

하지만, 거래 테이블에는 동일한 고객이 여러번 구매했을 수 있죠.

- 단순 count는 동일한 고객을 여러번 카운트하기때문에 의미없는 결과

 

고유의 고객수를 세려면? 고유값을 세면 되겠죠?

distinct

 

2. 아이템 브랜드별로 총 고유 고객수, 총 상품판매량

select
brd_nm 
, count(goodcd) as good_cou
, count(distinct htrc.custid) as id_cou
from hds_transactions_rea_csv htrc 
join hds_customers_rea_csv hcrc on htrc.custid =hcrc.custid 
group by 1

브랜드별로  상품판매갯수와 고객수가 나타납니다

 

 

3. 인당 구매 수량이 높은 상품은?  총 판매수량/총 고객수

select
brd_nm 
, count(goodcd) as co_good
, count(distinct htrc.custid) as co_id 
, round(cast(count(goodcd) as numeric)/count(distinct htrc.custid), 2) as cou_per_cus
from hds_transactions_rea_csv htrc 
join hds_customers_rea_csv hcrc on htrc.custid =hcrc.custid 
group by 1
order by 4 desc

 

* round(cast(count(goodcd) as numeric)/count(distinct htrc.custid), 2) as cou_per_cus -> 총 판매수량/총 고객수 : 브랜드별 인당 구매수량 확인!

- cast ~ as numeric : 이전 포스팅에서와 마찬가지로, 너무 정수결과만 나오기 때문에 소수점을 나타내기위해 int-> numeric으로 변환해주었습니다

- round(~, 2) : 위 소수점 결과를 보기 쉽게 소수점 2째자리까지 나타내었습니다 

 

고객당 평균 구매 수량은 '아베다'라는 브랜드가 구매 수량이 가장 크군요

한 사람이 평균 4개의 구매를 했습니다

 

 

4. 인당 구매금액이 높은 상품은? : 총 구매액 / 총고객수

select
brd_nm 
, sum(tot_amt) as sum_tot_amt -- 총 구매액
, count(distinct htrc.custid) as co_id --갯수
, round(cast(sum(tot_amt) as numeric)/count(distinct htrc.custid), 1) as sold_per_cus
from hds_transactions_rea_csv htrc 
join hds_customers_rea_csv hcrc on htrc.custid =hcrc.custid 
group by 1
order by 4 desc

3번의 goodcd 제품코드로 제품 판매수량 확인 코드를 빼고

, sum(tot_amt) as sum_tot_amt -- 총 구매액 계산 코드를 넣었습니다

 

아르마니 브랜드가 고객1인당 가장 많은 구매액을 나타내네요

3번에서 1인당 구매수량이 가장 많았던 아베다는 3번째.

예상할 수 있듯이 명품 제품들이 상위권에 나타납니다

 

*여기서 left join을 하게되면?

고객id 데이터기준으로 left 조인을 해보겠습니다

위의 inner join의 결과는 custid로 join하고 나머지 컬럼들 모두 교집합이되는 결과들만 표기되었는데

 

아래의 left join의 결과는 key값인 custid값만 연결되면, 값이 없는 경우에도 null로써 표기가 되는 차이점이 있었죠

(이전 포스팅 참고)

https://s-s-o-story.tistory.com/28?category=977087 

[분석 실전] SQL JOIN 심화(2). join 종류와 구문 팁! inner join, left join~

그래서, 아래와 같이 결과가 달라집니다. 아르마니의 경우만 보더라도 co_id가 1->2로 늘어나면서

총 구매액이 늘어나고  값이 변하게됩니다

 

이전 포스팅에서도 언급했었지만

left join은,  key를  날려버리면 안되는 경우에 주로 사용합니다(고객정보 key 등)

* 두 테이블의 관계를 잘 모르는 경우에는 left join을 많이 사용합니다

 

 

5. 인당 구매금액이 높은 성연령

 

-먼저 join을 잠깐 해보겠습니다

아래 두개의 이미지를 보면 left, inner join의 row갯수가 다른데

성,연령이 거래데이터와 연결될 수 없다면 필요가 없으므로 그대로 inner조인으로 진행해줍니다

 

 

연령은 현재 birth 칼럼에 yyyy-mm-dd 00:00 문자 형식으로 되어있습니다

위 데이터를

1. 앞 4개의 숫자로 연도추출 substring

2. 연산을 위해 숫자변환 cast(~ as integer)

3. 현재의 연도추출 date_part

4. 현재 연도 - 출생연도 = 연령으로 변환

과 같이 연령을 구해보겠습니다

select 
sex
,birth
, substring(birth, 1,4)
--연도 추출
, cast(substring(birth, 1,4) as integer) as yyyy
--연산을 위해 연도 문자열->숫자 변환
, date_part('year', now()) as now_ye
--현재 연도 추출
, date_part('year', now()) - cast(substring(birth, 1,4) as integer) as ages
-- 현재연도 - 출생연도 = 연령으로 변환
from hds_customers_rea_csv hcrc 
join hds_transactions_rea_csv htrc on hcrc .custid =htrc.custid 
where birth not like '%nul%' 
--위 문자열을 없애지않으면 cast로 숫자로 변경이 안됩니다

마지막 where절에서

birth 칼럼의 '$nul'을 제외해 주어야 숫자로 변환가능합니다

위와같이 연령을 추출했습니다.

 

성, 연령별 인당 평균 구매액을 계산해보겠습니다

select 
sex
, date_part('year', now()) - cast(substring(birth, 1,4) as integer) as ages
, sum(htrc.tot_amt)
, count(distinct hcrc.custid)
, sum(htrc.tot_amt) / sum(distinct hcrc.custid) as avg_amt
from hds_customers_rea_csv hcrc 
join hds_transactions_rea_csv htrc on hcrc .custid =htrc.custid 
where birth not like '%nul%' 
group by 1,2
order by 5 desc

40대 여성이 가장 많은 구매를 나타냈지만, 모수가 너무 작네요. 1명이라니..
더미 데이터라서 정확치는 않지만, 모수 30이상이라면, 43세 여성이 인당 평균 구매금액이 높다고 볼 수 있어요

 

* 참고로 남성의 구매를 한번 볼까요?

-먼저 위 코드로 진행하면 값이 하나도 나오지 않습니다

left 조인으로 진행해볼게요

2건 밖에 없어서.. 의미가 없네요. 그리고 tot_amt 값 자체가 null입니다

 

앞서 말씀드렸다시피

이 경우 고객데이터와 거래데이터가 꼭 연결되어야 하기때문에 inner join으로 진행했습니다

위 경우 데이터가 여러개 있더라도 tot_amt 값이 없기때문에 사용할 수 없죠