mysql计算用户次日/周/月留存的常用窗口函数-LEAD

在日常的分析任务中，我们一般需要利用mysql语法直接计算用户的次日留存率，那么该怎么实现呢？不说废话，直接上思路和案例。

比如 一个餐饮公司需要计算用户每日的次日复购情况，该怎么实现呢？（注意：该餐饮公司周末两天不送餐）

实现思路

1、获取用户基本信息：用户id、用户名称、用餐日期

2、使用if判断， 使用DAYOFWEEK函数判断是否是星期五，如果是星期五则使用DATE_ADD加2天，否则取原来的日期

3、使用LEAD窗口函数获取该用户下一个用餐日期

4、通过嵌套，利用GROUP BY分组，最后判断两个日期相减是否等于1，如果等于1则计算为复购

演示代码

SELECT
delivery_date,
COUNT(DISTINCT user_id) as 当日订餐用户数
,count( if(第几天复购=1,user_id,null)
) as 次日用餐人数
from 
(SELECT
	user_id,
	user_name,
	activity_type_name,
	delivery_date,
	WEEK ( delivery_date, 7 ) AS 周,
	MONTH ( delivery_date ) AS 月,if(DAYOFWEEK(delivery_date)=6,DATE_ADD(delivery_date,INTERVAL 2 day),delivery_date) as 实际日期,
	LEAD(delivery_date,1) over(PARTITION by user_id ORDER BY delivery_date ) as 下一个日期,
	DATEDIFF(LEAD(delivery_date,1) over(PARTITION by user_id ORDER BY delivery_date ),if(DAYOFWEEK(delivery_date)=6,DATE_ADD(delivery_date,INTERVAL 2 day),delivery_date)) as 第几天复购

-- 	lag(WEEK ( delivery_date, 7 ),1) over(PARTITION by user_id ORDER BY delivery_date ) as 上一个周期
FROM
	dw_user_orders 
WHERE
	from_type = '订餐' 
	AND status_name = '送餐'  
	and delivery_date BETWEEN '2023-11-10' and '2024-1-11' 
	)s
	GROUP BY delivery_date

扩展学习

在MySQL中，可以使用DAYOFWEEK()函数来判断当前日期是星期几。

SELECT DAYOFWEEK(NOW());

这将返回一个数字，代表当前日期是星期几： 1 表示星期日 2 表示星期一 3 表示星期二 4 表示星期三 5 表示星期四 6 表示星期五 7 表示星期六

LEAD函数用于访问当前行的下一行的column1的值。通过指定ORDER BY子句，你可以指定结果集的排序顺序。如果没有指定排序顺序，默认情况下按照查询返回的顺序进行排序。 除了LEAD函数之外，你还可以使用LAG函数来访问上一行的数据，使用FIRST_VALUE函数来访问窗口中第一行的数据，以及使用LAST_VALUE函数来访问窗口中最后一行的数据。 以下是对应的示例：

SELECT column1, 
LEAD(column1) OVER (ORDER BY column1) AS next_value

LAG(column1) OVER (ORDER BY column1) AS previous_value, column2,
FIRST_VALUE(column2) OVER (ORDER BY column1) AS first_value,
LAST_VALUE(column2) OVER (ORDER BY column1) AS last_value 
FROM your_table;

mysql中日期的加减 date_add()、date_sub() 函数

DATE_ADD() ：从日期增加指定的时间间隔，返回的是一个字符串

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

DATEDIFF函数用于返回两个日期的天数

语法：dateaiff(date1,date2)

参数说明

date1: 比较日期1

date2: 比较日期2

DATEDIFF函数返回date1 - date2的计算结果，date1和date2两个参数需是有效的日期或日期时间值;如果参数传递的是日期时间值，DATEDIFF函数仅将日期部分用于计算，并忽略时间部分(只有值的日期部分参与计算)

如需转载请保留本文出处: https://zhe94.com/953.html