select
sum(user_id is null)
,sum(auction_id is null)
,sum(cat1 is null)
,sum(cat_id is null)
,sum(trade.property is null)
,sum(trade.buy_mount is null)
,sum(trade.day is null)
from trade;
select
buy_num
,count(user_id) as user_num
from
(
select
user_id
,count(user_id) as buy_num
from trade
group by user_id
)as tem
group by buy_num
order by user_num;
-- 分析按星期的銷量，使用者量
select dayname(day) as D,
sum(buy_num) as 銷量,
sum(user_num) as 活躍使用者量
from
(
select
day,
sum(buy_mount ) as buy_num,
count(distinct user_id) as user_num
from trade
where buy_mount<30
group by day
) as tem
group by D,dayofweek(day)
order by dayofweek(day) ;
-- 分析按月購買的情況
select
月份,
max(if(年份 = 2012,buy_num,0)) as 2012年,
max(if(年份 = 2013,buy_num,0)) as 2013年,
max(if(年份 = 2014,buy_num,0)) as 2014年,
max(if(年份 = 2015,buy_num,0)) as 2015年
from
(
select
month(day) as 月份,
year(day) as 年份,
sum(buy_mount ) as buy_num,
count(distinct user_id) as user_num
from trade
where buy_mount<30
group by year(day), month(day)
) as tem
group by 月份
order by 月份;
-- 尋找熱銷子類（銷量前十或使用者數量前十）
select
tem1.*,
使用者量排名, 使用者量
from
(select cat_id,
@j := @j + 1 as 銷量排名,
銷量
from (select @j := 0) as t,
(
select cat_id,
sum(buy_mount) as 銷量
from trade
where buy_mount < 30
group by cat_id
order by sum(buy_mount) desc) as n
) as tem1
join
(select cat_id,
@i := @i + 1 as 使用者量排名,
使用者量
from (select @i := 0) as t,
(
select cat_id,
count(distinct user_id ) as 使用者量
from trade
where buy_mount < 30
group by cat_id
order by count(distinct user_id ) desc) as m
) as tem2
on tem1.cat_id = tem2.cat_id
where 銷量排名<10 or 使用者量排名<10;
-- 建立年齡段資訊視圖
create view mytest.age_info as
(
select
b.user_id,
cat1,
cat_id,
buy_mount,
day as buy_day,
birthday,
(
case
when datediff(day,birthday)/30<0 then '未出生'
when datediff(day,birthday)/30<6 then '0-6個月'
when datediff(day,birthday)/365<1 then '6-12個月'
when datediff(day,birthday)/365<3 then '1-3歲'
when datediff(day,birthday)/365<7 then '3-7歲'
else '大于七歲'
end
) as 年齡分段,
if((floor(((to_days(`t`.`day`) - to_days(`b`.`birthday`)) / 365)) < 0), '未出生',
floor(((to_days(`t`.`day`) - to_days(`b`.`birthday`)) / 365))) AS `年齡`,
(
case gender
when 0 then '男'
when 1 then '女'
else '不明'
end
) as 性别
from babyinfo b
join trade t
on b.user_id = t.user_id
where t.buy_mount<30
);
-- 每個年齡段的人數和購買量：
select
年齡分段,
count(distinct user_id) as 人數,
sum(buy_mount) as 購買總量
from age_info
group by 年齡分段
order by field(年齡分段,'未出生','0-6個月','6-12個月','1-3歲','3-7歲','大于七歲');
create view mytest.multi_info
as
(
select
user_id,
cat_id,
cat1,
buy_mount,
day
from trade
where
user_id in
(
select
user_id
from trade
where buy_mount<30
group by user_id
having count(auction_id)>1
)
order by user_id,day);
計算複購率
-- 計算複購率
select
a.num1/count(distinct user_id) as 複購率
from
(select count(distinct user_id) as num1 from multi_info) as a ,
trade b;
-- 查詢有重複購買行為使用者複購的是否是同一小類的奶粉
select
t.num as 複購産品種類數,
count(user_id) as 使用者數
from
(select
user_id,count(distinct cat_id) as num
from multi_info
group by user_id) as t
group by t.num;
-- 查詢有重複購買行為使用者複購的是否是同一大類的奶粉
select
t.num as 複購産品種類數,
count(user_id) as 使用者數
from
(select
user_id,count(distinct cat1) as num
from multi_info
group by user_id) as t
group by t.num;
