HiveSQL實(shí)戰(zhàn) -- 電子商務(wù)消費(fèi)行為分析(附源碼和數(shù)據(jù))

• 「row_number() over(partition by transaction_id)」 窗口函數(shù) ：從1開始，按照順序，生成分組內(nèi)記錄的序列，row_number()的值不會(huì)存在重復(fù)，當(dāng)排序的值相同時(shí)，按照表中記錄的順序進(jìn)行排列  這里我們對分組的transaction_id
• if(t.ct=1,transaction_id,concat(t.transaction_id,'_',t.ct-1)) 如果滿足ct=1,就是transaction_id,否則進(jìn)行字符串拼接生成新的id

「Clean store_review table」

create table store_review 
as select transaction_id,store_id,nvl(review_score,ceil(rand()*5)) 
as review_score from ext_store_review

「NVL(E1, E2)的功能為：如果E1為NULL，則函數(shù)返回E2，否則返回E1本身。」我們可以看到表中的數(shù)據(jù)存在空值，通過NVL函數(shù)對數(shù)據(jù)進(jìn)行填充。

show tables

通過清洗后的近源表和明細(xì)表如上。

數(shù)據(jù)分析

Customer分析

• 找出顧客最常用的信用卡

select credit_type,count(credit_type) as peoplenum from customer_details
group by credit_type order by peoplenum desc limit 1

• 找出客戶資料中排名前五的職位名稱

select job,count(job) as jobnum from customer_details
group by job
order by jobnum desc
limit 5

• 在美國女性最常用的信用卡

select credit_type,count(credit_type) as femalenum from customer_details 
where gender='Female'
group by credit_type
order by femalenum desc
limit 1

• 按性別和國家進(jìn)行客戶統(tǒng)計(jì)

select count(*) as customernum,country,gender from customer_details
group by country,gender

Transaction分析

• 計(jì)算每月總收入

select partday,sum(price) as countMoney from transaction_details group by partday

• 計(jì)算每個(gè)季度的總收入「Create Quarter Macro 定義季度宏」，將時(shí)間按季度進(jìn)行劃分

create temporary macro 
calQuarter(dt string) 
concat(year(regexp_replace(dt,'/','-')),'年第',ceil(month(regexp_replace(dt,'/','-'))/3),'季度')

select calQuarter(buydate) as quarter,sum(price) as sale 
from transaction_details group by calQuarter(buydate)

• 按年計(jì)算總收入

create temporary macro calYear(dt string) year(regexp_replace(dt,'/','-'))

select calYear(buydate) as year,sum(price) as sale from transaction_details group by calYear(buydate)

• 按工作日計(jì)算總收入

create temporary macro calWeek(dt string) concat('星期',dayofweek(regexp_replace(dt,'/','-'))-1)

select concat('星期',dayofweek(regexp_replace(buydate,'/','-'))-1) as week,sum(price) as sale 
from transaction_details group by dayofweek(regexp_replace(buydate,'/','-'))

• 按時(shí)間段計(jì)算總收入(需要清理數(shù)據(jù))

select concat(regexp_extract(buytime,'[0-9]{1,2}',0),'時(shí)') as time,sum(price) as sale from transaction_details group by regexp_extract(buytime,'[0-9]{1,2}',0)

• 按時(shí)間段計(jì)算平均消費(fèi)「Time macro」

create temporary macro calTime(time string) if(split(time,' ')[1]='PM',regexp_extract(time,'[0-9]{1,2}',0)+12,
if(split(time,' ')[1]='AM',regexp_extract(time,'[0-9]{1,2}',0),split(time,':')[0]))

select calTime(buytime) as time,sum(price) as sale from transaction_details group by calTime(buytime) 

--define time bucket 
--early morning: (5:00, 8:00]
--morning: (8:00, 11:00]
--noon: (11:00, 13:00]
--afternoon: (13:00, 18:00]
--evening: (18:00, 22:00]
--night: (22:00, 5:00] --make it as else, since it is not liner increasing
--We also format the time. 1st format time to 19:23 like, then compare, then convert minites to hours
with
t1 as
(select calTime(buytime) as time,sum(price) as sale from transaction_details group by calTime(buytime) order by time),
t2 as
(select if(time>5 and time<=8,'early morning',if(time >8 and time<=11,'moring',if(time>11 and time <13,'noon',
if(time>13 and time <=18,'afternoon',if(time >18 and time <=22,'evening','night'))))) as sumtime,sale 
from t1)
select sumtime,sum(sale) from t2 
group by sumtime

• 按工作日計(jì)算平均消費(fèi)

select concat('星期',dayofweek(regexp_replace(buydate,'/','-'))-1) 
as week,avg(price) as sale from transaction_details 
where dayofweek(regexp_replace(buydate,'/','-'))-1 !=0 and dayofweek(regexp_replace(buydate,'/','-'))-1 !=6
group by dayofweek(regexp_replace(buydate,'/','-'))

• 計(jì)算年、月、日的交易總數(shù)

select buydate as month,count(*) as salenum from transaction_details group by buydate

• 找出交易量最大的10個(gè)客戶

select c.customer_id,c.first_name,c.last_name,count(c.customer_id) as custnum from customer_details c
inner join transaction_details t
on c.customer_id=t.customer_id
group by c.customer_id,c.first_name,c.last_name
order by custnum desc
limit 10

• 找出消費(fèi)最多的前10位顧客

select c.customer_id,c.first_name,c.last_name,sum(price) as sumprice from customer_details c
inner join transaction_details t
on c.customer_id=t.customer_id
group by c.customer_id,c.first_name,c.last_name
order by sumprice desc
limit 10

• 統(tǒng)計(jì)該期間交易數(shù)量最少的用戶

select c.customer_id,c.first_name,c.last_name,count(*) as custnum from customer_details c
inner join transaction_details t
on c.customer_id=t.customer_id
group by c.customer_id,c.first_name,c.last_name
order by custnum asc
limit 1

• 計(jì)算每個(gè)季度的獨(dú)立客戶總數(shù)

select calQuarter(buydate) as quarter,count(distinct customer_id) as uninum
from transaction_details
group by calQuarter(buydate)

• 計(jì)算每周的獨(dú)立客戶總數(shù)

select calWeek(buydate) as quarter,count(distinct customer_id) as uninum
from transaction_details
group by calWeek(buydate)

• 計(jì)算整個(gè)活動(dòng)客戶平均花費(fèi)的最大值

select sum(price)/count(*) as sale
from transaction_details
group by customer_id
order by sale desc
limit 1

• 統(tǒng)計(jì)每月花費(fèi)最多的客戶

with 
t1 as
(select customer_id,partday,count(distinct buydate) as visit from transaction_details group by partday,customer_id),
t2 as
(select customer_id,partday,visit,row_number() over(partition by partday order by visit desc) as visitnum from t1)
select * from t2 where visitnum=1 

• 統(tǒng)計(jì)每月訪問次數(shù)最多的客戶

with
t1 as
(select customer_id,partday,sum(price) as pay from transaction_details group by partday,customer_id),
t2 as
(select customer_id,partday,pay,row_number() over(partition by partday order by pay desc) as paynum from t1)
select * from t2 where paynum=1

• 按總價(jià)找出最受歡迎的5種產(chǎn)品

select product,sum(price) as sale from transaction_details 
group by product
order by sale desc
limit 5

• 根據(jù)購買頻率找出最暢銷的5種產(chǎn)品

select product,count(*) as num from transaction_details 
group by product
order by num desc
limit 5

• 根據(jù)客戶數(shù)量找出最受歡迎的5種產(chǎn)品

select product,count(distinct customer_id) as num from transaction_details
group by product
order by num desc
limit 5

• 驗(yàn)證前5個(gè)details

select * from transaction_details where product in ('Goat - Whole Cut')

Store分析

• 按客流量找出最受歡迎的商店

with 
t1 as (select store_id,count(*) as visit from transaction_details 
group by 
store_id order by visit desc limit 1)
select s.store_name,t.visit 
from t1 t 
inner join 
ext_store_details s 
on t.store_id=s.store_id

• 根據(jù)顧客消費(fèi)價(jià)格找出最受歡迎的商店

with 
t1 as (select store_id,sum(price) as sale from transaction_details 
group by 
store_id order by sale desc limit 1)
select s.store_name,t.sale 
from t1 t 
inner join 
ext_store_details s 
on t.store_id=s.store_id

• 根據(jù)顧客交易情況找出最受歡迎的商店

with
t1 as 
(select store_id,store_name from ext_store_details)
select t.store_id,store_name,count(distinct t.customer_id) as num
from transaction_details t
inner join t1 s
on s.store_id=t.store_id
group by t.store_id,store_name
order by num desc
limit 1

• 根據(jù)商店和唯一的顧客id獲取最受歡迎的產(chǎn)品

with
t1 as (select store_id,product,count(distinct customer_id) as num from transaction_details
group by store_id,product order by num desc limit 1)
select s.store_name,t.num,t.product 
from t1 t 
inner join 
ext_store_details s 
on t.store_id=s.store_id

• 獲取每個(gè)商店的員工與顧客比

with
t1 as (select store_id,count(distinct customer_id) as num from transaction_details
group by store_id )
select s.store_name,employee_number/num as vs from t1 t
inner join ext_store_details s 
on t.store_id=s.store_id

• 按年和月計(jì)算每家店的收入

select store_id,partday,sum(price) from transaction_details group by store_id,partday

• 按店鋪制作總收益餅圖

select store_id,sum(price) from transaction_details group by store_id

• 找出每個(gè)商店最繁忙的時(shí)間段

with
t1 as
(select store_id,count(customer_id) as peoplenum from transaction_details group by store_id,concat(regexp_extract(buytime,'[0-9]{1,2}',0),'時(shí)')),
t2 as
(select store_id,peoplenum,row_number() over(partition by store_id order by peoplenum desc) as peo from t1 )
select t.store_id,e.store_name,t.peoplenum from t2 t
inner join ext_store_details e
on e.store_id = t.store_id
where peo =1

• 找出每家店的忠實(shí)顧客

with
t1 as
(select customer_id,store_id,count(customer_id) as visit from transaction_details group by store_id,customer_id ),
t2 as
(select customer_id,store_id,visit,row_number() over(partition by store_id order by visit desc) as most from t1)
select r.customer_id,concat(first_name,last_name) as customer_name,r.store_id,store_name,r.visit from t2 r
inner join customer_details c
on c.customer_id=r.customer_id
inner join ext_store_details e
on e.store_id=r.store_id
where most=1

• 根據(jù)每位員工的最高收入找出明星商店

with
t1 as
(select store_id,sum(price) as sumprice from transaction_details group by store_id)
select t.store_id,s.store_name,sumprice/employee_number as avgprice  from t1 t
inner join ext_store_details s
on s.store_id=t.store_id
order by avgprice desc

Review分析

• 在ext_store_review中找出存在沖突的交易映射關(guān)系

select t.transaction_id,t.store_id from transaction_details t
inner join ext_store_review e
on e.transaction_id=t.transaction_id
where e.store_id!=t.store_id

• 了解客戶評價(jià)的覆蓋率

with
trans as (select store_id,count(transaction_id) as countSale from transaction_details group by store_id),
rev as (select store_id,count(distinct transaction_id) as review from store_review group by store_id)
select s.store_name,(r.review*100/t.countSale) as cover from  trans t 
inner join rev r 
on t.store_id=r.store_id 
inner join ext_store_details s
on t.store_id=s.store_id

• 根據(jù)評分了解客戶的分布情況

select store_id,review_score,count(review_score) as numview from ext_store_review  where review_score>0 group by review_score,store_id

• 根據(jù)交易了解客戶的分布情況

select store_id,count(transaction_id) as transactionnum from ext_store_review  group by store_id

• 客戶給出的最佳評價(jià)是否總是同一家門店

select store_id,customer_id,count(customer_id) as visit from transaction_details t
join ext_store_review e
on e.transaction_id = t.transaction_id
where e.review_score=5
group by t.store_id,t.customer_id