日活/月活

#日活select login_data,count(distinct user_id)from dm.login_tablegroup by login_data

#月活select extract(year_month  from login_data) as month,count(distinct user_id)from dm.login_tablegroup by extract(year_month  from login_data)

EXTRACT()函数用来提取时间，返回日期/时间的单独部分，比如年、月、日、小时、分钟等等，如下：

1 SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,               EXTRACT(MONTH FROM OrderDate) AS OrderMonth,               EXTRACT(DAY FROM OrderDate) AS OrderDay,               EXTRACT(year_month FROM OrderDate) AS ym FROM Orders

首先，计算每个用户第一次登录的时间（最早的登录时间），即用户注册时间表

#返回用户注册时间表select user_id,min(login_date) as register_datefrom dm.login_tablegroup by user_id

第二，计算留存

通过用户id连接用户首次登录时间表（注册时间表）与登录时间表，然后按日期分组汇总每日首次登录的用户数量（即新增用户数）。

次日留存率的计算方式是，先判断用户注册时间与登录时间差是不是1，如果是，就是次留的客户，然后除以登录时间，从而得到次日留存率。

以此类推可计算7日留存率，15日留存率……

datediff(login_date,register_date)>0这个条件是用来判断登录时间是不是晚于注册时间

selectregister_date, #计算每日新增用户count(distinct a.user_id) as new_user, #计算次日留存率count(dinstinct if(datediff(login_date,register_date)=1,a.user_id,null))/count(distinct a.user_id) as remain_1, #计算7日留存率 count(dinstinct if(datediff(login_date,register_date)=7,a.user_id,null))/count(distinct a.user_id) as remain_7,#计算15日留存率   count(dinstinct if(datediff(login_date,register_date)=15,a.user_id,null))/count(distinct a.user_id) as remain_15from(select user_id,min(login_date) as register_datefrom dm.login_tablegroup by user_id) a left join dm.login_table b on a.user_id = b.user_id and datediff(login_date,register_date)>0 group by register_date

第一步，按用户id，登陆时间表，进行排序

select user_id,login_date,rank() over (partition by user_id order by login_date)from dm.login_table

连续登录的时间有如下特征比如10011这个用户：

登录时间2022-08-07减去5等于2022-08-02，

登录时间2022-08-08减去5等于2022-08-02，

即登陆的时间减去排名总是等于同一日期的，就代表着他这两天是连续登录的。

第二，判断一个用户是不是连续登录就是判断结果是不是有重复日期，最大连续登录天数就是查找用户登陆中重复日期最多的即可。

使用函数date_sub()对结果进行简单变换处理，如下：

select user_id,date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_datefrom dm.login_table

运行结果

第三，对用户登陆天数聚合，求得连续登陆天数

select user_id,primary_date,count(1) as cntfrom(selectuser_id,date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_datefrom dm.login_table) tgroup by user_id,primary_date

第四，使用max()函数求用户登陆日期中连续登陆天数中最大值

select user_id, max(cnt) as '连续登陆天数'from(select user_id,primary_date,count(1) as cntfrom(selectuser_id,date_sub(login_date,interval rank() over (partition by user_id order by login_date) day) as primary_datefrom dm.login_table) tgroup by user_id,primary_date) tgroup by user_id

关于date_sub函数

DATE_SUB()函数接受两个参数：DATE_SUB(start_date,interval expr unit)start_date是DATE或DATETIME的起始值。expr是一个字符串，用于确定从起始日期减去的间隔值。unit是expr可解析的间隔单位，例如DAY，MONTH，HOUR等

更多细节请参考：