一、将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', 也有可能是''，需要自己查看。

二、具体案例分析

1. 统计不同月份的下单人数

select month(paid_time), count(distinct user_id) from order_info
where is_paid = '已支付'
group by month(paid_time)

2. 统计用户三月份的回购率（这个月买了，下个月又买了）和复购率(买的次数超过一次)

• 回购率

• 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

3. 统计男女用户的消费频次是否有差异

• 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

6. 统计消费的二八法则，消费的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

三、有关的时间提取函数

1. 选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒
   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