oracle的case when用法(oracle中case when的用法)

【题目】:

某电商平台面试题:

下面为用户登录表,分别计算用户的次日、3日留存数及留存率

oracle的case when用法(oracle中case when的用法)

【解题思路】

此题的核心是怎么计算用户的留存数/留存率?用户留存率是电商行业经常用到的指标,用户的留存数指“第一天登录,以后几天还继续登录的用户数”,"留存率=次日的留存数/当日总的用户数"。因此先要计算出每日的总的用户数、次日的留存数,然后再计算留存率。

一、计算每个用户登录天数间隔

利用表的自联结计算间隔天数,a表作为前面登录的表(计算当日的用户数),b表作为后登录的表(计算后面的留存数),间隔日期为{“b表的登录日期”-“a表的登录日期”},通过a、b两个表的“用户ID”关联可以计算在某一日用户在后面几日的留存数。

oracle的case when用法(oracle中case when的用法)

select a.登录序号,a.用户ID,a.登录日期 as 登录日期a,b.登录日期 as 登录日期b,datediff(b.登录日期,a.登录日期) as 间隔天数from 用户登录表 aleft join 用户登录表 bon a.用户ID=b.用户IDand a.登录日期< b.登录日期order by a.登录序号,a.登录日期;

得到一个用户在各个日期与后面几日的间隔天数的笛卡尔积的表

oracle的case when用法(oracle中case when的用法)

二、计算留存数及留存率

1.计算留存数

把第一步的结果作为临时表dates,用case when聚合函数筛选出符合间隔的天数,“1”代表次日留存,“3”代表3日留存,以此类推,然后把满足条件的用户ID筛选取出来,因满足条件的用户ID有多个,所以需要用distinct去重,然后对满足条件的”用户ID” 进行计数就行了。SQL语句和结果如下:

select dates.登录日期a
,count(distinct dates.用户ID)  as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

oracle的case when用法(oracle中case when的用法)

2.计算留存率

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,count(distinct case when dates.间隔天数=1 then dates.用户ID else null end) as 次日留存数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff (b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

oracle的case when用法(oracle中case when的用法)

3、扩展一下,后续可以计算3日,7日,10日、30日的留存率,现增加3日留存,把时间间隔更改一下即可 ,

select dates.登录日期a
,count(distinct dates.用户ID) as 当日用户数
,concat(round(count(distinct case when dates.间隔天数=1 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 次日留存率
,concat(round(count(distinct case when dates.间隔天数=3 then dates.用户ID else null end)/count(distinct dates.用户ID)*100,2),'%') as 3日留存率
from
(select a.登录序号,a.用户ID,
a.登录日期 as 登录日期a,
b.登录日期 as 登录日期b,
datediff(b.登录日期,a.登录日期) as 间隔天数
from 用户登录表 a
left join 用户登录表 b
on a.用户ID=b.用户ID
and a.登录日期< b.登录日期
order by a.登录序号,a.登录日期
) dates
group by dates.登录日期a
order by  dates.登录日期a;

oracle的case when用法(oracle中case when的用法)

【本题考点】

1、业务的理解。在电商行业、互联网行业经常要计算用户的留存率,怎么用sql语句构建并计算用户的留存数是非常重要的

2、Datediff()函数的应用

Datediff() 函数返回两个日期之间的天数,表达式:

datediff(date1,date2)

date1 和 date2 参数是合法的日期或日期/时间表达式,只有值的日期部分参与计算。Datediff计算结果得出的值是Date1-Date2的值。

3.case when 函数的应用

条件筛选时经常用到case when 函数,详细见case when 函数的介绍

创业项目群,学习操作 18个小项目,添加 公众号:李佰秒 微信:3219087951  备注:小项目

本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 3300536702@qq.com 举报,一经查实,本站将立刻删除。
如若转载,请注明出处:https://www.hljqsb.cn/19983.html