一、将csv格式的文件导入mysql
第一步:建表,本案例需要键两个表。
-- 表一
create table order_info(
order_id int primary key ,
user_id int,
is_paid varchar(10),
price float,
paid_time varchar(30));
-- 表二
create table user_info(
user_id int primary key,
sex varchar(10),
birth date);
第二步:导入csv格式的数据
load data local infile '文件路径' into table 表名 fields terminated by ',';
注意点:
- 语句要正确
- 路径不要有中文,是左斜杆,
- mysql 8.0 登陆使用 mysql --local-infile -uroot -p
- 要有fields terminated by ',' 是因为csv 文件是以逗号为分割符的
第三步:对日期数据进行规整
-- 先把时间格式标准化成1993-02-27 这样的
update 表名 set 字段名=replace(字段名, '/', '-') where 字段名 is not null;
-- 然后更新字符串为日期格式,然后才能使用日期函数进行操作
update 表名 set 字段名=str_to_date(字段名, '%Y-%m-%d %H:%i') where 字段名 is not null;
注意点:
- csv导入表后,有的字段没有显示并不代表null,有可能是'\r', 也有可能是'',需要自己查看。
二、具体案例分析
- 统计不同月份的下单人数
select month(paid_time), count(distinct user_id) from order_info
where is_paid = '已支付'
group by month(paid_time)
- 统计用户三月份的回购率(这个月买了,下个月又买了)和复购率(买的次数超过一次)
- 回购率
- a.先得到每个用户消费的月份
select user_id, date_format(paid_time, '%Y-%m-01') from order_time
where is_paid = '已支付'
group by user_id, date_format(paid_time, '%Y-%m-01')- b.将a得到的表与自己进行左链接
select * from (
select user_id, date_format(paid_time, '%Y-%m-01') from order_time
where is_paid = '已支付'
group by user_id, date_format(paid_time, '%Y-%m-01') ) t1
left join (
select user_id, date_format(paid_time, '%Y-%m-01') from order_time
where is_paid = '已支付'
group by user_id, date_format(paid_time, '%Y-%m-01') ) t2
on t1.user_id = t2.user_id- c.将t2付款时间-t1的付款时间 > 1,就是回购的用户
select t1.m, count(t1.m), count(t2.m) from (
select user_id, date_format(paid_time, '%Y-%m-01') as m from order_time
where is_paid = '已支付'
group by user_id, date_format(paid_time, '%Y-%m-01') ) t1
left join (
select user_id, date_format(paid_time, '%Y-%m-01') as m from order_time
where is_paid = '已支付'
group by user_id, date_format(paid_time, '%Y-%m-01') ) t2
on t1.user_id = t2.user_id and t1.m = date_sub(t2.m, interval 1 month)
group by t1.m
- 复购率
- a.先得到3月份每个用户购买的次数
select user_id, count(user_id) from order_info
where is_paid = '已支付' and month(paid_time)=3
group by user_id- b.复购率=当月购买大于1次的用户数/当月购买的用户数
select count(user_id) as 购买用户数, count(if(buy_num> 1, 1, null)) as 购买大于1次的用户数 from (
select user_id, count(user_id) as buy_num from order_info
where is_paid = '已支付' and month(paid_time)=3
group by user_id ) t
- 统计男女用户的消费频次是否有差异
- a.获得每个购买用户的性别,购买次数
select user_info.user_id, sex, count(user_info.user_id) as c_num from order_info
inner join user_info
on order_info.user_id = user_info.user_id
where order_info.is_paid = '已支付' and user_info.sex != ''
group by user_info.user_id, sex- b.统计那女消费频次
select sex, avg(c_num) from (
select user_info.user_id, sex, count(user_info.user_id) as c_num from order_info
inner join user_info
on order_info.user_id = user_info.user_id
where order_info.is_paid = '已支付' and user_info.sex != ''
group by user_info.user_id, sex) t
group by sex
4.统计多次消费的用户,第一次和最后一次消费间隔是多少
select user_id, max(paid_time), min(paid_time), datediff(max(paid_time), min(paid_time))
from order_info
where is_paid = '已支付'
group by user_id
having count(user_id) > 1
5.统计不同年龄段,用户消费金额是否有差异
- a.得到每个ID的年龄及消费金额
select order_info.user_id, age, sum(price) from order_info
inner join (
select user_id, (year(now)-year(birth)) as age from user_info
where birth > date('1901-00-00')) t
on order_info.user_id = user_info.user_id
where user_info.is_paid='已支付'
group by order_info.user_id, age- b.给年龄分段ceil((year(now)-year(birth))/10) ,并求平均消费金额
select age, avg(consume) from (
select order_info.user_id, age, sum(price) as consume from order_info
inner join (
select user_id, ceil((year(now)-year(birth))/10) as age from user_info
where birth > date('1901-00-00')) t
on order_info.user_id = user_info.user_id
where user_info.is_paid='已支付'
group by order_info.user_id, age) t1
group by age
- 统计消费的二八法则,消费的top20%用户,贡献了多少额度
- a.计算20%的用户是多少人
select count(*) * 0.2 from order_id
where is_paid = '已支付'
group by user_id
--约等于17000- b.每个用户消费的金额从高到底排序,并去前17000条
select user_id, sum(price) as p from order_info
where is_paid='已支付'
group by user_id
order by p desc
limit 17000- c.计算前17000的平均消费金额
select count(user_id), avg(p) from (
select user_id, sum(price) as p from order_info
where is_paid='已支付'
group by user_id
order by p desc
limit 17000) t
三、有关的时间提取函数
- 选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456
网友评论