MySQL实战项目

 

本文主要通过MySQL对数据库进行特定场景查询，

例如对用户留存，商品回购率和复购率的探索。

 

场景一：在一份销售表中，我们需要提取上一个月付款用户量最高的三天是哪三天？并查询昨天每个用户最后的付款订单ID和金额。

SELECT DATA_FORMAT(pay_time,'%Y-%m-%d') AS pay_date,
       COUNT(DISTINCT user_id) AS user_count
FROM Orders
WHERE order_amount>0
AND MONTH(pay_date)=MONTH(NOW())-1
GROUP BY pay_date
ORDER BY user_count DESC
LIMIT 3;

在这份销售数据中，我们主要需要的是order_id（订单ID）,user_id（客户ID）,pay_time（交易时间）,order_amount（交易金额）。由于一天中可能有客户多次购买，所以需要对客户进行去重处理，另外对月份进行定位（上一个月），和过滤付款用户。然后进行排序直接锁定付款用户量最高的三天。

SELECT user_id,order_id,order_amount
FROM Orders
WHERE DATE_FORMAT(pay_time,'%Y-%m-%d')=DATE_SUB(CURDATE(),interval 1 day)
AND DATE_FORMAT(pay_time,'%Y-%m-%d')=MAX(DATE_FORMAT(pay_time,'%Y-%m-%d'))
GROUP BY user_id;

昨天每个用户最后的付款订单ID和金额，过滤交易时间（昨天），按user_id 进行分组，并选取交易时间最后的订单信息。

 

场景二：根据某网站的访问记录，我们得到两个各40亿条数据表格，即表A和表B，主要包含用户ID（user_id)和商品ID( goods_id)这两项，在防止数据倾斜的条件下，找出表A和表B共同的用户及其对应的商品ID

SELECT * 
FROM A INNER JOIN B
ON A.user_id IS NOT NULL 
AND A.user_id=B.user_id
UNION ALL
SELECT *
FROM A
WHERE A.user_id IS NULL;

这里主要解决空值导致的数据倾斜，现将非空值数据通过INNER JOIN联结，在合并空值，得到两个表共同的用户和商品。

 

场景三：对于一份用户登录日志表，查找出近一个月内，平均每天登录用户的数量

SELECT AVG(t1.user_num) 
FROM 
(SELECT DATE_FORMAT(log_time,'%Y-%m-%d') log_date,COUNT(DISTINCT user_id) user_num
FROM t 
WHERE t.log_date>=DATE_SUB(CURDATE(),INTERVAL 30 day)
AND t.log_date<CURDATE()
GROUP BY log_date)t1

这里需要的数据是用户ID（user_id），和时间日志（log_time）。按天将用户去重，并通过DATE_SUB和CURDATE函数将时间定为在1个月内，将得到的每天登录用户数量计算平均值。

 

场景四：对于一个网站来说，每天会通过不同渠道进来新的用户也会流失一部分用户，而那些留下来的用户就显得尤其珍贵，这部分用户也叫留存用户，这就是网站运营的重要指标之一。针对一份访问表（person_visit），统计出最近七天访问的新用户以及每个渠道的新用户中3日和7日的留存量。

SELECT user_id
FROM person_visit
GROUP BY user_id
HAVING MIN(DATE_FORMAT(visit_date,'%Y-%m_%d')) < CURDATE()
AND MIN(DATE_FORMAT(visit_date,'%Y-%m_%d'))>= DATE_SUB(CURDATE(),INTERVAL 7 DAY);

统计出最近七天访问的新用户，需要的数据有用户浏览时间(visit_date)，和用户ID(user_id)。通过用户进行分组，并过滤掉7天之前就访问过网站的用户。

SELECT plat,COUNT(DISTINCT user_id) '3日用户留存数量' FROM person_visit 
WHERE user_id IN
(SELECT user_id FROM person_visit 
GROUP BY user_id 
HAVING MIN(DATE_FORMAT(visit_date,'%y-%m-%d'))=DATA_SUB(CURDATE(),INTERVAL 7 DAY))
AND user_id IN
(SELECT user_id FROM person_visit 
WHERE DATE_FORMAT(visit_date,'%y-%m-%d')=DATA_SUB(CURDATE(),INTERVAL 5 DAY));

每个渠道的新用户中3日留存量，选取那些7天前新注册用户，且在第三日有访问网站的用户，按平台分组计算用户数量。当然在计算每个渠道的7天前新用户数量，就可以算出3日留存率了。

SELECT plat,COUNT(DISTINCT user_id) '7日用户留存数量' FROM person_visit 
WHERE user_id IN
(SELECT user_id FROM person_visit 
GROUP BY user_id 
HAVING MIN(DATE_FORMAT(visit_date,'%y-%m-%d'))=DATA_SUB(CURDATE(),INTERVAL 7 DAY))
AND user_id IN
(SELECT user_id FROM person_visit 
WHERE DATE_FORMAT(visit_date,'%y-%m-%d')=DATA_SUB(CURDATE(),INTERVAL 1 DAY));

和3日留存量类似，计算新用户在第7天有访问网站的数量。

 

场景五：对于商家而言，复购率和回购率是两项重要的指标，前者主要反映客户对商品的需求程度，后者反映客户对商品的忠诚度。通过某商品网站的交易数据提取其复购率和回购率。

SELECT OrderMonth,COUNT(c),COUNT(IF(c>1,1,NULL),COUNT(IF(c>1,1,NULL)/COUNT(c)
FROM (
SELECT DATE_FORMAT(paidtime,'%Y-%m-01')) OrderMonth,user_id,COUNT(user_id) c
FROM OrderInfo
WHERE idpaid='已支付'
GROUP BY OrderMonth,user_id
)
GROUP BY OrderMonth;

这里主要针对有效订单（‘ispaid’）的用户购买次数来统计，利用COUNT和IF函数过滤购买次数在2及以上的客户数量，从而计算复购率。

对于一个月之后的回购率，这里通过内联结将本月有购买下个月又有再购买的用户进行统计，算出回购用户数量。

SELECT A.t1,COUNT(A.t1)
FROM (SELECT user_id,DATE_FORMAT(paidtime,'%Y-%m-01') t1
FROM OrderInfo
WHERE idpaid='已支付'
GROUP BY user_id) A,(SELECT user_id,DATE_FORMAT(paidtime,'%Y-%m-01') t2
FROM OrderInfo
WHERE idpaid='已支付'
GROUP BY user_id) B
WHERE A.user_id=B.user_id
AND A.t1=DATE_SUB(B.t2,INTERVAL 1 MONTH)
GROUP BY A.t1;