SQL求解用戶復(fù)購率

• 1、說說問題
• 2、解決方案
• 3、附加需求：求每個月總體的復(fù)購率
• 4、附錄：輸入數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)SQL

也可在 微信公眾號 上觀看

1、說說問題

遇到了一個問題：給定一個訂單表，里面包含用戶的商品購買記錄，求解用戶每個月的復(fù)購率。

復(fù)購率定義：12月下單的用戶中歷史上在廣告和自然渠道支付成功超過2單的用戶數(shù) / 12月支付成功的用戶總數(shù)

直接上輸入數(shù)據(jù)（文末有數(shù)據(jù)創(chuàng)建sql），看前后結(jié)果更直觀點兒。

字段解釋：

• order_id：訂單id
• pass_id：用戶id
• flow_info：來源，(3,4)代表廣告渠道，其他代表自然渠道。注意：in 的反義不是 not in！
• status：訂單狀態(tài)，200代表支付成功
• app_id：業(yè)務(wù)線id，這個關(guān)系不大
• time：訂單創(chuàng)建時間

結(jié)果：

字段解釋：

• channel：頻道
• repurchase_user：重復(fù)購買人數(shù)
• all_users：總下單人數(shù)
• proportion：比例

2、解決方案

思路：
首先拆分問題，問題需要每個月，咱就先查一個月的，例如12月的。
（1）查詢到12月下單的用戶集合 N，注意要去重！
（2）查詢12月底及之前的歷史時間內(nèi)、且是N中用戶的下單記錄集合F，注意這里最直白就是用 where pass_id in ( N )，為了加速使用left join。
（3）統(tǒng)計F中用戶的下單數(shù)集合 及數(shù)量大于等于2的結(jié)果，并獲得12月內(nèi)的復(fù)購用戶數(shù)A。
（4）查詢12月內(nèi)下單的總用戶數(shù)B
（5）A/B得到結(jié)果。

selectf1.channel,f1.repurchase_user,f2.all_users,ROUND(f1.repurchase_user / f2.all_users, 2) as proportion from(# 查詢在12月重復(fù)購買的用戶數(shù)selectchannel,sum(if(order_nums > 1, 1, 0)) as repurchase_userfrom(SELECTpass_id,channel,count(order_id) as order_numsfrom(SELECTt1.*,t2.order_idfrom(-- 12月內(nèi)頻道、用戶去重數(shù)據(jù)selectdistinct pass_id,casewhen flow_info in (3, 4) then 'ad'when flow_info not in (3, 4)or flow_info is null then 'nad'end channelfromorder_infowhereapp_id = 4and `status` = 200and time BETWEEN '2021-12-01'and '2021-12-31') t1left JOIN (# 獲取12月及之前用戶的全部購買記錄selectpass_id,casewhen flow_info in (3, 4) then 'ad'when flow_info not in (3, 4)or flow_info is null then 'nad'end channel,order_idfromorder_infowhereapp_id = 4and `status` = 200and time <= '2021-12-31') t2 ON t1.pass_id = t2.pass_idand t1.channel = t2.channel) tGROUP BY channel,pass_id) mgroup bychannel) f1left join (# 查詢在12月購買的總用戶數(shù)SELECTchannel,count(pass_id) as all_usersfrom(selectdistinct pass_id,casewhen flow_info in (3, 4) then 'ad'when flow_info not in (3, 4)or flow_info is null then 'nad'end channelfromorder_infowhereapp_id = 4and `status` = 200and time BETWEEN '2021-12-01'and '2021-12-31') pgroup bychannel) f2 on f1.channel = f2.channel

3、附加需求：求每個月總體的復(fù)購率

思路比較簡單：就是將每個各個頻道的數(shù)據(jù)進行相加。

首先將前面的數(shù)據(jù)進行轉(zhuǎn)儲成一個新的表tb_repurchase_rate，并新加一列時間date，例如’2021-12’，之后看sql…

select* from(selectdate,'ALL' as channel,sum(repurchase_user) as repurchase_user,sum(all_users) as all_users,sum(repurchase_user) / sum(all_users) as proportionfromtb_repurchase_ratewheredate between '202111'and '202201'group bydateUNION ALLselectdate,channel,repurchase_user,all_users,proportionfromtb_repurchase_ratewheredate between '202111'and '202201') t order byt.date,t.channel desc

4、附錄：輸入數(shù)據(jù)結(jié)構(gòu)及數(shù)據(jù)SQL

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;-- ---------------------------- -- Table structure for order_info -- ---------------------------- DROP TABLE IF EXISTS `order_info`; CREATE TABLE `order_info` (`order_id` varchar(255) NOT NULL,`pass_id` varchar(255) DEFAULT NULL,`flow_info` int DEFAULT NULL,`status` int DEFAULT NULL,`app_id` int DEFAULT NULL,`time` date DEFAULT NULL,PRIMARY KEY (`order_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- ---------------------------- -- Records of order_info -- ---------------------------- BEGIN; INSERT INTO `order_info` VALUES ('100', 'abc', 3, 200, 4, '2021-11-18'); INSERT INTO `order_info` VALUES ('101', 'ccc', 4, 200, 4, '2022-01-03'); INSERT INTO `order_info` VALUES ('102', 'aaa', NULL, 200, 4, '2021-10-05'); INSERT INTO `order_info` VALUES ('103', 'ccc', 3, 200, 4, '2021-09-09'); INSERT INTO `order_info` VALUES ('104', 'ddd', 1, 200, 3, '2021-12-16'); INSERT INTO `order_info` VALUES ('105', 'abc', 3, 200, 4, '2021-12-09'); INSERT INTO `order_info` VALUES ('106', 'def', 3, 200, 4, '2021-12-16'); INSERT INTO `order_info` VALUES ('107', 'aaa', NULL, 200, 4, '2021-12-22'); INSERT INTO `order_info` VALUES ('108', 'abc', 4, 200, 4, '2021-11-06'); INSERT INTO `order_info` VALUES ('109', 'ppp', 3, 200, 4, '2021-12-24'); INSERT INTO `order_info` VALUES ('110', 'ppp', 3, 200, 4, '2021-10-20'); INSERT INTO `order_info` VALUES ('111', 'aaa', NULL, 200, 4, '2021-12-17'); COMMIT;SET FOREIGN_KEY_CHECKS = 1;

總結(jié)

以上是生活随笔為你收集整理的工作问题：SQL求解用户复购率的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。