mysql案例学习

作者: 李静数据分析 | 来源:发表于2019-08-16 20:24 被阅读0次

    一、将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)

    1. 统计用户三月份的回购率(这个月买了,下个月又买了)和复购率(买的次数超过一次)
    • 回购率
    • 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
    1. 统计男女用户的消费频次是否有差异
    • 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
    1. 统计消费的二八法则,消费的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

    相关文章

      网友评论

        本文标题:mysql案例学习

        本文链接:https://www.haomeiwen.com/subject/psoqsctx.html