MYSQL练习：用户行为分析

数据来源于https://www.jianshu.com/p/4f0a10ea170e
根据原链接的问题所做的用户行为分析
原链接使用的是SQLSERVER，这里我使用的是Mysql

题目
1-统计不同月份的下单人数
2-统计用户三月份的回购率和复购率
3-统计男女的消费频次是否有差异
4-统计多次消费的用户，第一次和最后一次消费时间的间隔
5-统计不同年龄段的用户消费金额是否有差异
6-统计消费的二八法则，消费的top20%用户，贡献了多少额度

1. 统计不同月份下单人数：
数据类型如下：

第一遍代码：

SELECT Concat(SUBSTR(paidTime,1,4),'-',SUBSTR(paidTime,6,2)) AS MONTH,
COUNT(*) AS XDRS
FROM orderinfo
WHERE isPaid='已支付'
GROUP BY MONTH;

输出：

【代码错误总结】

1. Mysql拼接字段使用 Concat(列m,‘文本’,列n)
2. 从paidTime文本里提取日期的方式需要改进
3. 统计下单人数，需要排除重复下单的人，count(*)不正确，应该用count(distinct(userid))进行排查
4. 好像由于表格创建时使用的varchar属性，不能用时间提取函数来提取日期，改用Substr()提取，第一个数是提取的字符串，第2个数指字符串开始的位置（起始位置是第1位），第3个数指从开始位置提取的个数。

第二遍代码：

SELECT Concat(SUBSTR(paidTime,1,4),'-0',SUBSTR(paidTime,6,1)) AS MONTH,
COUNT(DISTINCT(userID)) AS XDRS
FROM orderinfo
WHERE isPaid='已支付'
GROUP BY MONTH;

【总结】
由于源数据只有3/4/5月数据，所以合并时偷懒直接在3/4/5前面拼接字段时加了个‘0’，但是如果数据碰到10/11/12月份时行不通，需要再次修改代码

第三遍代码：

SELECT Concat(SUBSTR(paidTime,1,4),'-',IF(SUBSTR(paidTime,7,1)='/',Concat('0',SUBSTR(paidTime,6,1)),SUBSTR(paidTime,6,2)))AS MONTH,
COUNT(DISTINCT(userID)) AS XDRS
FROM orderinfo
WHERE isPaid='已支付'
GROUP BY MONTH;

【总结】
使用IF判断，跟excel里面if使用的方式一样，if（判断表达式，正确显示，错误显示）：如果paidtime里面第7位数是/，则拼接‘0’和第6位数，反之则从第6位数开始提取2个数

2. 统计用户三月份的回购率和复购率

• 复购率：本月消费中多少人消费一次以上的占比
  思路：先计算出消费一次以上的人数，用group by userid 和having count(*)>1

第一遍代码：

SELECT (COUNT(*)/(SELECT COUNT(*) FROM orderinfo WHERE SUBSTR(paidTime,6,1)='3' AND isPaid='已支付'))
AS FGL FROM
(SELECT userID
FROM orderinfo
WHERE SUBSTR(paidTime,6,1)='3'
AND isPaid='已支付'
GROUP BY userID HAVING COUNT(orderID)>1) AS a;

输出

【错误】：统计购买人数时又没有去重。。。

第二遍去重后代码：

SELECT (COUNT(*)/(SELECT COUNT(DISTINCT(userID)) FROM orderinfo WHERE SUBSTR(paidTime,6,1)='3' AND isPaid='已支付'))
AS '复购率' FROM
(SELECT DISTINCT(userID