MySQL 5.6 时间区间 复购率 查询笔记

如果有的选，我也不想在不支持窗体函数的数据库上这么拐弯抹角的写sql。

这个需求的本质是 多维度切分不同的订单和用户组，然后组内排序然后比较相邻行的日期。用窗体函数会非常的舒服，可是没有如果。

这是最终要导出的报表

# 用户下单金额
DROP VIEW IF EXISTS `v_user_kedanjia`;
CREATE VIEW v_user_kedanjia AS 
SELECT
	ui.user_id,
	IFNULL( COUNT(DISTINCT om.internal_pay_id), 0) order_num,
	IFNULL( SUM( om.order_amt ), 0.00 ) order_amt_sum
FROM
	ty_user_info ui
	LEFT JOIN ty_order_master om ON ui.user_id = om.user_info_id 
	AND om.pay_time IS NOT NULL 
	AND om.order_status != 3 
	AND om.is_delete = 0 
WHERE
	ui.is_delete = 0 
GROUP BY
	ui.user_id;
	

# 不同价格分组用户
DROP VIEW IF EXISTS `v_user_kedanjia_2`;
CREATE VIEW v_user_kedanjia_2 AS
SELECT 1 `num`, '0~1000' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum >= 0 AND order_amt_sum <= 1000
UNION
SELECT 2 `num`, '1000~2000' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum > 1000 AND order_amt_sum <= 2000
UNION
SELECT 3 `num`, '2000~3000' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum > 2000 AND order_amt_sum <= 3000
UNION
SELECT 4 `num`, '3000~5000' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum > 3000 AND order_amt_sum <= 5000
UNION
SELECT 5 `num`, '5000~10000' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum > 5000 AND order_amt_sum <= 10000
UNION
SELECT 6 `num`, '10000以上' `order_amt`, user_id FROM v_user_kedanjia WHERE order_amt_sum > 10000;


# 复购率
# 建视图是因为必须先通过 user_info_id, order_date 排序否则 rownum 会乱掉
DROP VIEW IF EXISTS `v_user_kedanjia_fugoulv_1`;
CREATE VIEW v_user_kedanjia_fugoulv_1 AS
(SELECT 1 'num', user_info_id, DATE_FORMAT(creator_time, '%Y-%m-%d') order_date, order_num FROM ty_order_master WHERE pay_time IS NOT NULL AND order_status != 3 AND order_amt >= 0 AND order_amt <= 1000 ORDER BY user_info_id, order_date)
UNION
(SELECT 2 'num', user_info_id, DATE_FORMAT(creator_time, '%Y-%m-%d') order_date, order_num FROM ty_order_master WHERE pay_time IS NOT NULL AND order_status != 3 AND order_amt > 1000 AND order_amt <= 2000 ORDER BY user_info_id, order_date)
UNION
(SELECT 3 'num', user_info_id, DATE_FORMAT(creator_time, '%Y-%m-%d') order_date, order_num FROM ty_order_master WHERE pay_time IS NOT NULL AND order_status != 3 AND order_amt > 2000 AND order_amt <= 3000 ORDER BY user_info_id, order_date)
UNION
(SELECT 4 'num', user_info_id, DATE_FORMAT(creator_time, '%Y-%m-%d') order_date, order_num FROM ty_order_master WHERE pay_time IS NOT NULL AND order_status != 3 AND order_amt > 3000 AND order_amt <= 5000 ORDER BY user_info_id, order_date)
UNION
(SELECT 6 'num', user_info_id, DATE_FORMAT(creator_time, '%Y-%m-%d') order_date, order_num FROM ty_order_master WHERE pay_time IS NOT NULL AND order_status != 3 AND order_amt > 10000 ORDER BY user_info_id, order_date);

# rownum 为每一条数据重新编号
SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r WHERE user_info_id = 4453;
SELECT TIMESTAMPDIFF(DAY,'2021-11-01','2022-04-10');

# 每一条数据重新编号
# 比较相邻的两条数据 判断时间间隔是否为N天
SELECT
	DISTINCT r1.user_info_id, r1.order_num, r2.order_num
FROM
	(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
	LEFT JOIN
	(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
	ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
WHERE r1.num = 6 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 0 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 60;


# 客单价
SELECT
	t2.order_amt,
	COUNT(t2.user_id) `用户数(包含未下单的用户)`,
	COUNT(t2.user_id) / (SELECT COUNT(user_id) FROM v_user_kedanjia) `占比`,
	COUNT(CASE WHEN t1.order_num = 1 THEN t1.user_id END) `首单用户数`,
	COUNT(CASE WHEN t1.order_num > 1 THEN t1.user_id END) `非首单用户数`,
	
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE r1.num = t2.num AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 0 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 7) 
	/
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 0 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 7)
	`1-7天复购率`,
	
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE r1.num = t2.num AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 8 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 15) 
	/
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 8 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 15)
	
	`8-15天复购率`,
	
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE r1.num = t2.num AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 16 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 26) 
	/
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 16 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 26) 
	
	`16-23天复购率`,
	
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE r1.num = t2.num AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 24 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 30) 
	/
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 24 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 30) 
	
	`24-30天复购率`,
	
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE r1.num = t2.num AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 31 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 60) 
	/
	(SELECT
		COUNT(DISTINCT r1.user_info_id)
	FROM
		(SELECT (@index1:=@index1+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index1:=0) r) r1
		LEFT JOIN
		(SELECT (@index2:=@index2+1) rownum, order_date, user_info_id, order_num, num FROM v_user_kedanjia_fugoulv_1,(SELECT @index2:=0) r) r2
		ON r1.user_info_id = r2.user_info_id AND r1.rownum+1 = r2.rownum
	WHERE TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) >= 31 AND TIMESTAMPDIFF(DAY,r1.order_date,r2.order_date) <= 60)
	
	`31-60天复购率`
FROM
	v_user_kedanjia t1
	LEFT JOIN v_user_kedanjia_2 t2 ON t1.user_id = t2.user_id
GROUP BY t2.num	
ORDER BY t2.num;

效果截图