mysql 练习 exercise3

create database markt;

use markt;

create table user_buy_all(
uuid int,
pay_tm datetime,
product varchar(10),
amount decimal,
primary key(uuid,pay_tm)
);

create table user_base(
uuid int primary key,
age int,
gender varchar(2),
province varchar(10)
);

insert into user_buy_all values
(1,'2018-05-23 12:05:30','waimai',50),
(1,'2018-05-28 12:05:31','waimai',51),
(1,'2018-06-03 12:05:32','maiyao',52),
(1,'2018-06-12 12:05:33','maiyao',53),
(2,'2018-07-01 12:05:34','maiyao',54),
(2,'2018-06-21 12:05:35','wanle',55),
(2,'2018-06-20 12:05:36','wanle',56),
(3,'2018-06-19 12:05:37','waimai',57),
(3,'2018-06-14 12:05:38','waimai',58),
(3,'2018-06-30 12:05:39','waimai',59);

insert into user_base values
(1,20,'男','北京'),
(2,21,'男','上海'),
(3,22,'女','北京'),
(4,23,'男','浙江'),
(5,25,'男','湖南'),
(6,27,'女','北京');

select * from user_buy_all;
select * from user_base;


-- 分product、province的购买人数、次数、总额
select 
product,
province,
count(distinct user_buy_all.uuid) as 购买人数,
count(*) as 次数,
sum(amount) as 总额
from user_buy_all 
left join user_base
on user_buy_all.uuid=user_base.uuid
group by product,province;


#以下为老师答案
select 
    product,
    province,
    count(distinct user_buy_all.uuid) as 人数,
    count(*) as 次数,
    sum(amount) as 总额
from user_buy_all
left join user_base
on user_base.uuid=user_buy_all.uuid
group by product,province;
 
-- 所有购买用户的age、gender、province以及首次购买的pay_tm、product、amount

select age,
gender,
province,user_buy_all.pay_tm as 首次购买时间,
user_buy_all.product as 首次购渠道,
user_buy_all.amount as 首次购金额
from user_buy_all 
left join user_base
on user_buy_all.uuid=user_base.uuid
left join (select uuid,min(pay_tm) as 首次购买时间 from user_buy_all group by uuid) as t
on user_buy_all.uuid=t.uuid
where user_buy_all.pay_tm=t.首次购买时间;

#以下为老师答案
#查询每个用户的首次购买时间
select uuid,min(pay_tm) from user_buy_all group by uuid;

select user_buy_all.uuid,age,gender,province,pay_tm,product,amount
from user_buy_all
left join user_base 
on user_base.uuid=user_buy_all.uuid
left join (select uuid,min(pay_tm) as 首次购买时间 from user_buy_all group by uuid) as t
on user_buy_all.uuid=t.uuid
where pay_tm=首次购买时间;

-- 每个用户的平均购买时间间隔
select  user_buy_all.uuid,
min(pay_tm) as 最小购买时间,
max(pay_tm) as 最大购买时间,
timestampdiff(day,min(pay_tm),max(pay_tm)) as 间隔天数,
count(*),
timestampdiff(day,min(pay_tm),max(pay_tm))/(count(*)-1) as 平均购买时间间隔
from user_buy_all  
group by user_buy_all.uuid;

#以下为老师答案
#平均购买时间间隔=首次购买到最后一次购买的时间差/间隔次数
#间隔次数=订单数-1
select 
    uuid,
    min(pay_tm) as 首次购买时间,
    max(pay_tm) as 最后一次购买时间,
    timestampdiff(day,min(pay_tm),max(pay_tm)) as 间隔总天数,
    timestampdiff(day,min(pay_tm),max(pay_tm))/(count(*)-1) as 平均购买时间间隔
from user_buy_all
group by uuid;