4.数据分析
4.数据分析
4.1 基于时间的用户行为分析
4.1.1 总访问量PV，总用户量UV
--总访问量PV，总用户量UV
create table res_pv_uv
comment "page views and unique visitor"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select pv, uv
from (
select count(*) as pv from user_behavior1 where behavior_type='pv'
) t1
join(
select count(distinct user_id) as uv from user_behavior1
) t2
on 1=1;
select * from res_pv_uv;
+-----------+---------+
| pv | uv |
+-----------+---------+
| 88596886 | 987984 |
+-----------+---------+思考以下select语句的优劣和正确性，和上面的语句谁能更好的发挥分区表的优势
select sum(pv) as pv, count(distinct uv) as uv from (
select count(*) as pv, distinct user_id as uv from user_behavior1 where behavior_type='pv'
union all
select 0 as pv, distinct user_id as uv from user_behavior1 where behavior_type='buy'
union all
select 0 as pv, distinct user_id as uv from user_behavior1 where behavior_type='cart'
union all
select 0 as pv, distinct user_id as uv from user_behavior1 where behavior_type='fav'
) t;
select sum(case when behavior_type = 'pv' then 1 else 0 end) as pv,
count(distinct user_id) as uv
from user_behavior1;4.1.2 日均访问量，日均用户量
--日均访问量，日均用户量
create table res_pv_uv_per_day
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select t1.`date` as `date`, pv, uv
from(
select `date`, count(*) as pv from user_behavior1 group by `date` order by `date`
) t1
join(
select `date`, count(distinct user_id) as uv from user_behavior1 group by `date` order by `date`
) t2
on t1.`date`=t2.`date`
order by `date`;
+--------------------------+------------------------+------------------------+
| res_pv_uv_per_day1.date | res_pv_uv_per_day1.pv | res_pv_uv_per_day1.uv |
+--------------------------+------------------------+------------------------+
| 2017-11-25 | 10511597 | 705571 |
| 2017-11-26 | 10571039 | 713522 |
| 2017-11-27 | 10013455 | 709207 |
| 2017-11-28 | 9884185 | 708339 |
| 2017-11-29 | 10319060 | 719356 |
| 2017-11-30 | 10541695 | 730809 |
| 2017-12-01 | 11171505 | 753166 |
| 2017-12-02 | 13940942 | 941709 |
| 2017-12-03 | 11961006 | 917531 |
+--------------------------+------------------------+------------------------+4.1.3 一天的活跃时段分布
-- 一天的活跃时段分布
create table res_behavior_among_day
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select t.hour as hour,
collect_list(map(t.behavior_type, t.count)) as tc
from(
SELECT hour(`timestamp`) AS hour,
behavior_type,
COUNT(*) AS count
FROM user_behavior1
GROUP BY behavior_type, hour(`timestamp`)
) t
group by hour
order by hour;
+---------+----------------------------------------------------+
| hour | tc |
+---------+----------------------------------------------------+
| 0 | [{"buy":64916},{"cart":192036},{"fav":103721},{"pv":3042342}] |
| 1 | [{"buy":96134},{"cart":229890},{"fav":127976},{"pv":3728498}] |
| 2 | [{"buy":127932},{"cart":266963},{"fav":147752},{"pv":4334810}] |
| 3 | [{"buy":122046},{"cart":260831},{"fav":145412},{"pv":4213518}] |
| 4 | [{"buy":118591},{"cart":255811},{"fav":140862},{"pv":4255794}] |
| 5 | [{"buy":123426},{"cart":279829},{"fav":150844},{"pv":4653933}] |
| 6 | [{"buy":122171},{"cart":277093},{"fav":148561},{"pv":4642054}] |
| 7 | [{"buy":122728},{"cart":284269},{"fav":151321},{"pv":4806704}] |
| 8 | [{"buy":116444},{"cart":279035},{"fav":148722},{"pv":4607743}] |
| 9 | [{"buy":101300},{"cart":255342},{"fav":137631},{"pv":4203395}] |
| 10 | [{"buy":95907},{"cart":253193},{"fav":133262},{"pv":4313516}] |
| 11 | [{"buy":115032},{"cart":314774},{"fav":161057},{"pv":5430878}] |
| 12 | [{"buy":133859},{"cart":393209},{"fav":191406},{"pv":6586331}] |
| 13 | [{"buy":145431},{"cart":465924},{"fav":219974},{"pv":7538382}] |
| 14 | [{"buy":138263},{"cart":486249},{"fav":232222},{"pv":7443069}] |
| 15 | [{"buy":100070},{"cart":395920},{"fav":195330},{"pv":5599901}] |
| 16 | [{"buy":52422},{"cart":164776},{"fav":94930},{"pv":2747149}] |
| 17 | [{"buy":20948},{"cart":76954},{"fav":46239},{"pv":1278813}] |
| 18 | [{"buy":10748},{"cart":41541},{"fav":25079},{"pv":692240}] |
| 19 | [{"buy":7212},{"cart":29333},{"fav":16791},{"pv":471981}] |
| 20 | [{"buy":6044},{"cart":25564},{"fav":13260},{"pv":403765}] |
| 21 | [{"buy":7351},{"cart":33462},{"fav":17158},{"pv":522063}] |
| 22 | [{"buy":16251},{"cart":73014},{"fav":36388},{"pv":1097628}] |
| 23 | [{"buy":33718},{"cart":131106},{"fav":66638},{"pv":1982379}] |
+---------+----------------------------------------------------+比较以下语句
select hour(`timestamp`) as hour,
sum(case when behavior_type = 'pv' then 1 else 0 end) as pv, --点击数
sum(case when behavior_type = 'fav' then 1 else 0 end) as fav, --收藏数
sum(case when behavior_type = 'cart' then 1 else 0 end) as cart, --加购物车数
sum(case when behavior_type = 'buy' then 1 else 0 end) as buy --购买数
from user_behavior1
group by hour(`timestamp`)
order by hour;4.1.4 一周用户的活跃分布
--一周用户的活跃分布
create table res_behavior_among_week
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select t1.weekday as weekday ,
collect_list(map(t1.behavior_type ,
(case when weekday=6 then ceil(t1.count/2)
when weekday=7 then ceil(t1.count/2)
else t1.count end)
)) as ct
from(
select pmod(datediff(t.d, '2017-11-25')+5, 7)+1 as weekday,
t.behavior_type as behavior_type ,
sum(t.count) as count
from (
select `date` as d,
behavior_type,
COUNT(*) as count
from user_behavior1
group by behavior_type , `date`
) t
group by pmod(datediff(t.d, '2017-11-25')+5, 7)+1, t.behavior_type
order by weekday
) t1
group by t1.weekday
order by weekday;
+----------+----------------------------------------------------+
| weekday | tc |
+----------+----------------------------------------------------+
| 1 | [{"buy":218401},{"cart":539212},{"fav":289413},{"pv":8966429}] |
| 2 | [{"buy":211754},{"cart":533807},{"fav":289431},{"pv":8849193}] |
| 3 | [{"buy":223077},{"cart":554747},{"fav":299588},{"pv":9241648}] |
| 4 | [{"buy":222235},{"cart":573032},{"fav":304428},{"pv":9442000}] |
| 5 | [{"buy":212849},{"cart":642251},{"fav":314121},{"pv":10002284}] |
| 6 | [{"buy":230424},{"cart":685302},{"fav":355318},{"pv":10955227}] |
| 7 | [{"buy":224891},{"cart":626233},{"fav":322460},{"pv":10092439}] |
+----------+----------------------------------------------------+比较以下语句
select pmod(datediff(`date`, '2017-11-25') + 5, 7)+1 as weekday,
sum(case when behavior_type = 'pv' then 1 else 0 end) as pv, --点击数
sum(case when behavior_type = 'fav' then 1 else 0 end) as fav, --收藏数
sum(case when behavior_type = 'cart' then 1 else 0 end) as cart, --加购物车数
sum(case when behavior_type = 'buy' then 1 else 0 end) as buy --购买数
from user_behavior1
group by pmod(datediff(`date`, '2017-11-25')+5, 7)+1
order by weekday;select t.weekday as weekday,
collect_list(map(t.behavior_type,
(case when weekday=6 then ceil(t.count/2)
when weekday=7 then ceil(t.count/2)
else t.count end)
)) as tc
from (
select pmod(datediff(`date`, '2017-11-25')+5, 7)+1 as weekday,
behavior_type,
COUNT(*) as count
from user_behavior1
group by behavior_type , pmod(datediff(`date`, '2017-11-25')+5, 7)+1
) t
group by weekday
order by weekday;4.2 用户行为转换率
--点击/(加购物车+收藏)/购买 , 各环节转化率
create table res_conversion_rate
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select pv, fav, cart, fav_cart, buy,
round(fav_cart/pv, 4) as pv2favcart,
round(buy/fav_cart, 4) as favcart2buy,
round(buy/pv, 4) as pv2buy
from(
select cast(gm['pv'] as int) as pv,
cast(gm['fav'] as int) as fav,
cast(gm['cart'] as int) as cart,
cast(gm['fav']+gm['cart'] as int) as fav_cart,
cast(gm['buy'] as int) as buy
from(
select collect(cast(behavior_type as string), cast(count as string)) as gm
from(
select behavior_type,
COUNT(*) as count
from user_behavior1
group by behavior_type
)t1
) t2
) t3;
+-----------+----------+----------+------------+----------+-------------+--------------+---------+
| pv | fav | cart | fav_cart | buy | pv2favcart | favcart2buy | pv2buy |
+-----------+----------+----------+------------+----------+-------------+--------------+---------+
| 88596886 | 2852536 | 5466118 | 8318654.0 | 1998944 | 0.0939 | 0.2403 | 0.0226 |
+-----------+----------+----------+------------+----------+-------------+--------------+---------+这里使用了Brickhouse UDF，用collect UDF便捷实现了TRANSPOSE
4.3 复购率
--每个用户的购物情况，加工到 user_behavior_count
create table res_doublebuy_rate
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select round(t2.count/uv,6) as rate
from(
select count(*) as count
from(
select count(*) as count
from user_behavior1
where behavior_type='buy'
group by user_id
) t1
where t1.count>1
) t2
join(
select uv from res_pv_uv
) t3;
+--------------------------+
| res_doublebuy_rate.rate |
+--------------------------+
| 0.446562 |
+--------------------------+4.4 基于 RFM 模型找出有价值的用户
RFM 模型是衡量客户价值和客户创利能力的重要工具和手段，其中由3个要素构成了数据分析最好的指标，分别是：
R-Recency（最近一次购买时间）
F-Frequency（消费频率）
M-Money（消费金额）
-- 创建一个临时表，记录每个用户的R-F排名
create temporary table temp_cte
as
select user_id,
datediff('2017-12-04', max(`date`)) as R,
dense_rank() over(order by datediff('2017-12-04', max(`date`))) as R_rank,
count(*) as F,
dense_rank() over(order by count(*) desc) as F_rank
from user_behavior1
where behavior_type = 'buy'
group by user_id;
+--------------+--------+-------------+--------+-------------+
| cte.user_id | cte.r | cte.r_rank | cte.f | cte.f_rank |
+--------------+--------+-------------+--------+-------------+
| 486458 | 1 | 1 | 262 | 1 |
| 866670 | 2 | 2 | 175 | 2 |
| 702034 | 1 | 1 | 159 | 3 |
| 107013 | 1 | 1 | 130 | 4 |
| 1014116 | 1 | 1 | 118 | 5 |
| 432739 | 1 | 1 | 112 | 6 |
| 500355 | 4 | 4 | 110 | 7 |
| 537150 | 1 | 1 | 109 | 8 |
| 1003412 | 1 | 1 | 100 | 9 |
| 919666 | 1 | 1 | 97 | 10 |
+--------------+--------+-------------+--------+-------------+-- R-F按照5-15个等级划分，因为本人认为value-F > value-R，然后计算每个用户的价值score
create table res_user_score
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select user_id, R, R_rank, R_score, F, F_rank, F_score, R_score + F_score AS score
from(
select *,
ntile(5) over(order by R_rank desc) as R_score,
ntile(15) over(order by F_rank desc) as F_score
from temp_cte
) t
order by score desc;
+-------------------------+-------------------+------------------------+-------------------------+-------------------+------------------------+-------------------------+-----------------------+
| res_user_score.user_id | res_user_score.r | res_user_score.r_rank | res_user_score.r_score | res_user_score.f | res_user_score.f_rank | res_user_score.f_score | res_user_score.score |
+-------------------------+-------------------+------------------------+-------------------------+-------------------+------------------------+-------------------------+-----------------------+
| 180128 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 180131 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 935417 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 925900 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 590239 | 1 | 1 | 5 | 9 | 79 | 15 | 20 |
| 307773 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 856339 | 1 | 1 | 5 | 14 | 74 | 15 | 20 |
| 178301 | 1 | 1 | 5 | 9 | 79 | 15 | 20 |
| 804543 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
| 327112 | 1 | 1 | 5 | 7 | 81 | 15 | 20 |
+-------------------------+-------------------+------------------------+-------------------------+-------------------+------------------------+-------------------------+-----------------------+-- 计算每层价值用户有多少人
create table res_user_score_count
comment "page views and unique visitor each day"
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
as
select score, count(*) as count
from res_user_score
group by score
order by score desc;
+-----------------------------+-----------------------------+
| res_user_score_count.score | res_user_score_count.count |
+-----------------------------+-----------------------------+
| 20 | 22104 |
| 19 | 22648 |
| 18 | 33199 |
| 17 | 31014 |
| 16 | 41697 |
| 15 | 32152 |
| 14 | 71802 |
| 13 | 34853 |
| 12 | 47973 |
| 11 | 20117 |
| 10 | 25696 |
| 9 | 36893 |
| 8 | 39444 |
| 7 | 63359 |
| 6 | 30289 |
| 5 | 29306 |
| 4 | 41855 |
| 3 | 36437 |
| 2 | 9532 |
+-----------------------------+-----------------------------+4.5 商品维度的分析
4.5.1 item_id商品
对于不同的behavior_type， 排名前50的商品，用pyhive分析后，转化表再插回。
# %%
from pyhive import hive
import pandas as pd
import numpy as np
from TCLIService.ttypes import TOperationState
from tqdm import tqdm
conn = hive.connect('127.0.0.1', port=10000, username='hive', database="sky")
cursor = conn.cursor()
# %%
def single_cmd(cmd:str):
pbar = tqdm(total=100)
cursor.execute(cmd, async_=True)
status = cursor.poll().operationState
while status in (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
progress = cursor.poll().progressUpdateResponse.progressedPercentage
pbar.update(int(progress * 100) - pbar.n)
status = cursor.poll().operationState
results = None
try:results = cursor.fetchall()
except Exception as e:
pass
pbar.close()
if results:
return pd.DataFrame(results, columns=[desc[0] for desc in cursor.description])
else:
return None
# %%
res = pd.DataFrame({})
for t in ['pv', 'cart', 'fav', 'buy']:
tmp = single_cmd('''select item_id,
count(*) as count
from user_behavior1
where behavior_type='{}'
group by item_id
order by count desc
limit 50
'''.format(t))
# print(tmp.head())
res[t] = [{k:v} for k,v in zip(tmp['item_id'], tmp['count'])]
# %%
single_cmd('''CREATE TABLE IF NOT EXISTS res_item_rank (
`rank` int ,
`pv` string ,
`cart` string ,
`fav` string ,
`buy` string )
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
''')
# %%
def multi_insert(table:str, values:list):
cmd = "INSERT INTO " + table + " VALUES "
for i in values:
cmd += '("{}", "{}", "{}", "{}", "{}")'.format(i[0], i[1], i[2], i[3], i[4]) + ", "
cmd = cmd[:-2]
print(cmd)
single_cmd(cmd)
# %%
res = res.astype(str)
res['rank'] = res.index
res['rank'] = res['rank'] + 1
res = res.reindex(columns=['rank', 'pv', 'cart', 'fav', 'buy'])
res = res.to_numpy()
print(res)
# %%
multi_insert("res_item_rank", res)
# %%
print(single_cmd('''select * from res_item_rank'''))
# %%
cursor.close()
conn.close()+---------------------+---------------------+---------------------+--------------------+--------------------+
| res_item_rank.rank | res_item_rank.pv | res_item_rank.cart | res_item_rank.fav | res_item_rank.buy |
+---------------------+---------------------+---------------------+--------------------+--------------------+
| 1 | {'812879': 29720} | {'3031354': 1730} | {'2279428': 971} | {'3122135': 1408} |
| 2 | {'3845720': 25290} | {'812879': 1514} | {'2331370': 866} | {'3031354': 942} |
| 3 | {'138964': 20927} | {'2331370': 1502} | {'812879': 861} | {'3964583': 671} |
| 4 | {'2331370': 19348} | {'2818406': 1403} | {'2818406': 815} | {'2560262': 658} |
| 5 | {'2032668': 19075} | {'2560262': 1258} | {'3330337': 709} | {'2964774': 614} |
| 6 | {'1535294': 17830} | {'138964': 1116} | {'3845720': 695} | {'740947': 553} |
| 7 | {'59883': 17313} | {'1535294': 1114} | {'1535294': 674} | {'1910706': 546} |
| 8 | {'4211339': 17235} | {'1583704': 1092} | {'138964': 634} | {'1116492': 512} |
| 9 | {'3371523': 17156} | {'2453685': 1042} | {'2453685': 632} | {'705557': 495} |
| 10 | {'2338453': 17044} | {'2279428': 981} | {'2364679': 609} | {'4443059': 490} 4.5.2 商品大类category_id
# %%
from pyhive import hive
import pandas as pd
import numpy as np
from TCLIService.ttypes import TOperationState
from tqdm import tqdm
conn = hive.connect('127.0.0.1', port=10000, username='hive', database="sky")
cursor = conn.cursor()
# %%
def single_cmd(cmd:str):
pbar = tqdm(total=100)
cursor.execute(cmd, async_=True)
status = cursor.poll().operationState
while status in (TOperationState.INITIALIZED_STATE, TOperationState.RUNNING_STATE):
progress = cursor.poll().progressUpdateResponse.progressedPercentage
pbar.update(int(progress * 100) - pbar.n)
status = cursor.poll().operationState
results = None
try:results = cursor.fetchall()
except Exception as e:
pass
pbar.close()
if results:
return pd.DataFrame(results, columns=[desc[0] for desc in cursor.description])
else:
return None
# %%
res = pd.DataFrame({})
for t in ['pv', 'cart', 'fav', 'buy']:
tmp = single_cmd('''select category_id,
count(*) as count
from user_behavior1
where behavior_type='{}'
group by category_id
order by count desc
limit 15
'''.format(t))
# print(tmp.head())
res[t] = [{k:v} for k,v in zip(tmp['category_id'], tmp['count'])]
# %%
single_cmd('''CREATE TABLE IF NOT EXISTS res_category_rank (
`rank` int ,
`pv` string ,
`cart` string ,
`fav` string ,
`buy` string )
row format delimited
fields terminated by ','
lines terminated by '\n'
STORED AS TEXTFILE
''')
# %%
def multi_insert(table:str, values:list):
cmd = "INSERT INTO " + table + " VALUES "
for i in values:
cmd += '("{}", "{}", "{}", "{}", "{}")'.format(i[0], i[1], i[2], i[3], i[4]) + ", "
cmd = cmd[:-2]
print(cmd)
single_cmd(cmd)
# %%
res = res.astype(str)
res['rank'] = res.index
res['rank'] = res['rank'] + 1
res = res.reindex(columns=['rank', 'pv', 'cart', 'fav', 'buy'])
res = res.to_numpy()
print(res)
# %%
multi_insert("res_category_rank", res)
# %%
print(single_cmd('''select * from res_category_rank'''))
# %%
cursor.close()
conn.close()
+-------------------------+-----------------------+-------------------------+------------------------+------------------------+
| res_category_rank.rank | res_category_rank.pv | res_category_rank.cart | res_category_rank.fav | res_category_rank.buy |
+-------------------------+-----------------------+-------------------------+------------------------+------------------------+
| 1 | {'4756105': 4426937} | {'4756105': 212652} | {'4756105': 137659} | {'1464116': 34248} |
| 2 | {'4145813': 3115400} | {'4145813': 172399} | {'4145813': 109352} | {'2735466': 33426} |
| 3 | {'2355072': 3110550} | {'982926': 151994} | {'982926': 88052} | {'2885642': 31619} |
| 4 | {'3607361': 2941480} | {'4801426': 120584} | {'2355072': 86088} | {'4145813': 31418} |
| 5 | {'982926': 2763686} | {'2355072': 120176} | {'3607361': 70514} | {'4756105': 28021} |
| 6 | {'2520377': 2003073} | {'3607361': 107732} | {'4801426': 67516} | {'4801426': 26258} |
| 7 | {'4801426': 1841977} | {'1320293': 93843} | {'2520377': 66565} | {'982926': 24570} |
| 8 | {'1320293': 1769460} | {'2735466': 92518} | {'2465336': 54162} | {'2640118': 18116} |
| 9 | {'2465336': 1484268} | {'2520377': 85070} | {'3002561': 53801} | {'4159072': 17917} |
| 10 | {'3002561': 1406307} | {'2465336': 83140} | {'1320293': 53117} | {'1320293': 16948} |
| 11 | {'2735466': 1101503} | {'3002561': 79667} | {'4181361': 42485} | {'3002561': 16330} |
| 12 | {'4181361': 990387} | {'2640118': 61789} | {'149192': 37840} | {'4357323': 15686} |
| 13 | {'149192': 978867} | {'149192': 61559} | {'2735466': 35193} | {'4789432': 15545} |
| 14 | {'1080785': 946692} | {'4217906': 60537} | {'4217906': 34854} | {'903809': 15285} |
| 15 | {'2885642': 944780} | {'1464116': 59858} | {'1080785': 31928} | {'4217906': 14310} |
+-------------------------+-----------------------+-------------------------+------------------------+------------------------+Last updated