關(guān)于查詢所用的表，market_data表的字段介紹為：order_id(訂單ID),order_time(訂單時(shí)間),customer_name(用戶名稱),quantity(購買數(shù)量），sale(銷售額），profit(利潤)。
各項(xiàng)指標(biāo)的定義為：
R值為：用戶最后一次購買到現(xiàn)在（2016年12月31日）的時(shí)間間隔，輸出月份。
L值為：用戶第一次購買和最后一次購買之間的時(shí)間間隔，輸出月份。
F值為：用戶的總共購買次數(shù)，僅計(jì)算2016年的即可。
M值為：用戶的全部銷售額，僅計(jì)算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

關(guān)鍵點(diǎn)：
函數(shù)TimeStampDiff()：Mysql的查詢中，查詢兩日期之間相差多少天，多少周等的情況；
語法：TimeStampDiff(間隔類型，前一個(gè)日期，后一個(gè)日期)；
間隔類型：
FRAC_SECOND 毫秒，
SECOND 秒，
MINUTE 分鐘，
HOUR 小時(shí)，
DAY 天，
WEEK 星期
MONTH 月，
QUARTER 季度，
YEAR 年；
計(jì)算兩個(gè)日期之間相差的天數(shù)：
TimeStampDiff(DAY，'2018-08-06'，'2019-08-06')；
TimeStampDiff(DAY，'2018-08-06'，NOW())；

#2.查詢用戶的R值，F(xiàn)值和M值，注意F值和M值，僅計(jì)算2016年度的數(shù)字

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

關(guān)鍵點(diǎn)：
聚合函數(shù)COUNT()、SUM()...結(jié)合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

數(shù)據(jù)源：

sql4.PNG