MySQL 业务用户分析案例

数据分为两个表，一个是订单表，一个是用户信息表

一、将.sql文件导入到数据库

1. 连接到mysql主机
   在cmd窗口或者Mysql命令行窗口，使用管理员权限打开，连接到mysql数据库
   语法命令：mysql 【-h 主机名 -P端口号】 -u用户名 -p密码
   如果连接到local host本地主机，则【】括号内的可省略
2. 创建数据库
   如果sql文件的内容已有数据库或者文件中有创建数据库的语句，则无需创建数据库；如果没有的话需要新建数据库

show databases;#查看数据库中的表
create database sale_data charset utf8; #建立数据库
use sale_data; #使用这个数据库

3. 导入sql文件，语法：source sql文件路径，注意如果路径为复制路径，则需要将“\”替换为“/”

source C:/Users/Administrator/Documents/Navicat/MySQL/Servers/practise/orderinfo.sql
source C:/Users/Administrator/Documents/Navicat/MySQL/Servers/practise/userinfo.sql

注意：在navicat查询窗口运行以上两句source导入语句，执行报错，但是在cmd命令行窗口输入是可以导入的
数据表查看

-- 查看数据表
show databases; #查询数据库有哪些
use qing_sale_data; # 进入指定数据库
show tables; # 查看该数据库中有哪些表
desc orderinfo; #查看表结构
desc userinfo;

-- 题目预览
-- 1、统计不同月份的下单人数
-- 2、统计用户三月份的回购率和复购率
-- 3、统计男女用户消费频次是否有差异
-- 4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
-- 5、统计不同年龄段，用户的消费金额是否有差异
-- 6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额
开始做题：
-- 1、统计不同月份的下单人数
-- 分析点：是否支付要筛选已支付部分，月份的分组要按照年月分组，一个用户可能购买多笔，用户ID要去重
-- 从如下查询中可知，当isPaid为未支付时，时间都是'0000-00-00'，但是为了避免异常，还是筛选下 00:00:00'，所以过滤时可以通过是否支付来筛选

select year(paidTime) 'year', month(paidTime) 'month', count(distinct userid) 'user_count'
from orderinfo
where isPaid = '已支付' and paidTime <> '0000-00-00 00:00:00'
group by year(paidTime), month(paidTime);

-- 2、统计用户三月份的回购率和复购率
-- 回购率：3月份有多少用户购买了后，在下个月又购买了的占3月总购买人数比例
-- 复购率：当月购买了多次的用户占当月用户的比例

-- 回购率
-- 注意点：不要用原数据表进行join，非常耗内存，使用汇总后的数据再join速度会快些
-- 思路: 先统计出来每个月用户的购买次数,再使用date_sub函数将月份关联,统计每月的回购率

SELECT
    a.date,
    sum( IF ( b.cons > 0, 1, 0 ) ) 'huigou',
    sum( IF ( b.cons > 0, 1, 0 ) ) / count( a.userid ) 
FROM
    (
    SELECT
        date_format( paidTime, '%Y-%m-01' ) 'date',
        userid,
        count( 1 ) 'cons' 
    FROM
        orderinfo 
    WHERE
        isPaid = '已支付' 
        AND paidTime <> '0000-00-00 00:00:00' 
    GROUP BY
        userid,
        date_format( paidTime, '%Y-%m-01' ) 
    ) a
    LEFT JOIN (
    SELECT
        date_format( paidTime, '%Y-%m-01' ) 'date',
        userid,
        count( 1 ) 'cons' 
    FROM
        orderinfo 
    WHERE
        isPaid = '已支付' 
        AND paidTime <> '0000-00-00 00:00:00' 
    GROUP BY
        userid,
        date_format( paidTime, '%Y-%m-01' ) 
    ) b ON a.userid = b.userid 
    AND b.date = date_add( a.date, INTERVAL 1 MONTH ) 
GROUP BY
    a.date;

-- 复购率，思路：先求出每个用户3月的购买次数，计算复购的人数，复购人数除以总人数

SELECT
    sum( IF ( a.cons > 1, 1, 0 ) ) 'fugou',
    sum( IF ( a.cons > 1, 1, 0 ) ) / count( 1 ) 'fugou_rate' 
FROM
    ( SELECT userid, count( 1 ) cons FROM orderinfo WHERE isPaid = '已支付' AND paidTime BETWEEN '2016-03-01 00:00:00' AND '2016-04-01 00:00:00' GROUP BY userid ) a;

-- 3、统计男女用户消费频次是否有差异
-- 思路:先计算出每个用户的购买次数, 再筛选出user表中有性别的数据,
-- 两个表内联后统计分组统计男女平均消费次数
-- 注意点:当用户未支付时,用户表中的性别为空,所以可以根据这个特性区筛选已经消费了的用户,不知道为啥用is not null筛选不出来了，所以用的<>''

SELECT
    c.sex,
    avg( c.cons ) 'freq' 
FROM
    (
    SELECT
        a.userid,
        count( 1 ) 'cons',
        sex 
    FROM
        orderinfo a
        INNER JOIN ( SELECT userid, sex FROM userinfo WHERE sex <> '' ) b ON a.userid = b.userid 
    GROUP BY
        a.userid 
    ) c 
GROUP BY
    c.sex;

-- 4、统计多次消费的用户，第一次和最后一次消费间隔是多少天
-- 思路:By用户筛求出每个用户消费的最大时间和最小时间,再使用datediff求差,最后再筛选消费次数大于1的用户

SELECT
    userid,
    min( paidTime ) 'min_time',
    max( paidTime ) 'max_time',
    DATEDIFF( max( paidTime ), min( paidTime ) ) AS 'user_life' 
FROM
    orderinfo 
WHERE
    isPaid = '已支付' 
    AND paidTime <> '0000-00-00 00:00:00' 
GROUP BY
    userid 
HAVING
    count( 1 ) > 1;

-- 5、统计不同年龄段，用户的消费金额是否有差异
-- 思路：①先对用户的年龄进行分层，②再对订单表里用户的消费金额求和，③两个表内连，④再分层统计用户的消费金额
注意点：除了使用case when函数外，if和年龄直接除以10也是可以对年龄进行分层的

SELECT
    t.label,
    sum( t.money ) 't_money' 
FROM
    (
    SELECT
        a.userid,
        a.birth,
        a.sex,
        b.money,
        timestampdiff( YEAR, birth, now( ) ) 'age',
    CASE
            
            WHEN timestampdiff( YEAR, birth, now( ) ) < 11 THEN
            '1-10' 
            WHEN timestampdiff( YEAR, birth, now( ) ) <= 25 AND timestampdiff( YEAR, birth, now( ) ) >= 11 THEN
            '10-24' 
            WHEN timestampdiff( YEAR, birth, now( ) ) <= 40 AND timestampdiff( YEAR, birth, now( ) ) > 25 THEN
            '25-40' ELSE '>40' 
        END AS 'label' 
    FROM
        userinfo a
        INNER JOIN ( SELECT userid, sum( price ) 'money' FROM orderinfo GROUP BY userid ) b ON a.userid = b.userid 
    WHERE
        a.birth > '1900-01-01' 
    ) t 
GROUP BY
    t.label;

-- 6、统计消费的二八法则，消费的top20%用户，贡献了多少消费额(20%的用户贡献了80%的消费额)
-- 思路：首先统计出每位用户的消费额，按消费额降序排序，再统计总共多少人，算出20%的用户贡献了多少消费额

select sum(a.money)/318503081.4470062
from 
(
    select 
        userid, 
        sum(price) 'money'
    from orderinfo
    where isPaid = '已支付'
    group by userid
    order by sum(price) desc
    limit 17130
) a;

计算总人数和总销售额如下：

select 
    count(distinct userid)*0.2 'p',
    sum(price) 'm'
from orderinfo
where isPaid='已支付';