SQL第二期——用户生命周期表的计算

关于查询所用的表，market_data表的字段介绍为：order_id(订单ID),order_time(订单时间),customer_name(用户名称),quantity(购买数量），sale(销售额），profit(利润)。
各项指标的定义为：
R值为：用户最后一次购买到现在（2016年12月31日）的时间间隔，输出月份。
L值为：用户第一次购买和最后一次购买之间的时间间隔，输出月份。
F值为：用户的总共购买次数，仅计算2016年的即可。
M值为：用户的全部销售额，仅计算2016年的即可。

#1.查询所有用户的R值和L值
SELECT customer_name,
             TIMESTAMPDIFF(MONTH,MAX(order_time),'2016-12-31') R,
             TIMESTAMPDIFF(MONTH,MIN(order_time),MAX(order_time)) L,
FROM market_data
GROUP BY customer_name

sql1.PNG

关键点：
函数TimeStampDiff()：Mysql的查询中，查询两日期之间相差多少天，多少周等的情况；
语法：TimeStampDiff(间隔类型，前一个日期，后一个日期)；
间隔类型：
FRAC_SECOND 毫秒，
SECOND 秒，
MINUTE 分钟，
HOUR 小时，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年；
计算两个日期之间相差的天数：
TimeStampDiff(DAY，'2018-08-06'，'2019-08-06')；
TimeStampDiff(DAY，'2018-08-06'，NOW())；

#2.查询用户的R值，F值和M值，注意F值和M值，仅计算2016年度的数字

SELECT customer_name,
             TIMESTAMPDIFF(MONTH,MAX(order_time),'2016-12-31') R,
             COUNT(CASE WHEN YEAR(order_time)='2016' THEN order_id END) F,
             ROUND(SUM(CASE WHEN YEAR(order_time)='2016' THEN sale ELSE 0 END)) M
FROM market_data
GROUP BY customer_name

sql2.PNG

关键点：
聚合函数COUNT()、SUM()...结合CASE语句和IF语句的使用
CASE语句
CASE input_expr
WHEN when_expr THEN result_expr
ELSE else_result_expr
END
IF语句
IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

#3.查询用户的R值，L值和用户生命周期划分。生命周期划分如下：
#（新用户：R<=6 and L<=12；忠诚用户：R<=6 and L>12; 流失的老用户：R>6 and L>12; 一次性用户：R>6 and L<=12）

SELECT customer_name '用户名',
        R,
        L,
        CASE 
             WHEN R<=6 AND L<=12 THEN '新用户'
             WHEN R<=6 AND L>12 THEN '忠诚用户'
             WHEN R>6 AND L>12 THEN '流失的老用户'
             ELSE '一次性用户'
        END AS '用户分类'
FROM (SELECT customer_name,
             TIMESTAMPDIFF(MONTH,MAX(order_time),'2016-12-31') R,
             TIMESTAMPDIFF(MONTH,MIN(order_time),MAX(order_time)) L
        FROM market_data 
        GROUP BY customer_name) A

sql3.PNG

数据源：

sql4.PNG