sql计算留存_有关用户留存模型的一种设计方法

热文回顾：Kylin 新定位：分析型数据仓库

正文

没错，我的工种就是属于那种史上被人吐槽为最没技术含量、最打杂的工种——数据仓库开发工程师。

即使这样，我们也没有放弃理想啊，每天都在思考怎么把工作做的更好，即使打杂，也要打的优雅~~

今天分享一个用户留存模型的设计

1、抛出问题

用户留存是用户分析中最常用到的指标之一。

我们常常接到这样的需求：

~我们要看1天、2天、3天、4天 … 7天的留存~

~我们要看1天、2天、3天、4天 … 28天的留存~

还有一些不按套路出牌的：

~我们要看第33天的留存~

~我们要看第56天的留存~

…

让你在代码里写连着7天的留存，就已经写到手抽筋了，恨不得写个代码生成器来生成代码。

后面再来个连着28天的留存… 本来以为这就是终结了，谁知后面还有33天、49天、56天…

2、设计方案

可以这么来设计

CREATE EXTERNAL TABLE IF NOT EXISTS  GDM_USER_LEFT_INFO_DAY(  uuid  string COMMENT '用户id'  liucun_map map<string,string> COMMENT '用户90天留存情况 map(lc1:1,lc2:1...lc90:1)'  ) COMMENT '日活跃1~90天用户留存信息'  PARTITIONED BY (    day string    )STORED AS ORC

当然了，字段不限于这两个，可以依据实际需求，添加其它属性。

只计算近90天的留存，是因为经过考察，有99%需求，都是计算90天以内的留存。

伪代码如下：

           insert overwrite table gdm_user_left_info_day partition (day)            select                t1.uuid,                str_to_map                (                    concat_ws                    (',',                        collect_set                        (                            concat('lc',cast(datediff(t2.day,t1.day) as string),'@@@','1')                         )                    ),',','@@@'                ) as liucun_map,                t1.day            from            (               select                    day,                    uuid               from active where day>='$day' and day<='$dayago90'             ) t1 left join            (                select                     day,                     uuid                from active  where   day>='$dayago1' and day<='$dayago91'             ) t2 on t1.uuid=t2.uuid            where datediff(t2.day,t1.day)>0;

其中，active 为日活跃用户表。这样以来，每天更新近90天的用户留存，不仅解决了跑数的问题，同时，表里已经计算好了1~90天用户的留存情况  。

--要计算2019-08-01日活跃用户的 7日留存用户数，20日留存用户数： select      sum(liucun_map['lc7'])),     sum(liucun_map['lc20']))  from gdm_user_left_info_day  where day='2020-01-01'    ;

感觉文章有用，请分享给你的朋友~

你可能还喜欢 点击下方图片即可阅读

记一次数据岗位大厂面试(已斩offer)

从0到1构建用户画像系统技术和方法论

有赞指标库实践

有赞数据仓库实践之路

【认证】阿里云大数据专业 ACP 认证

有ACP意向的同学可以私聊我 领取 新版考试大纲和样题以及报名优惠码和在线题库 尽我所能帮到大家 非诚勿扰，谢谢！

欢迎加入 技术交流群。戳：快来加入数据交流群吧

推荐阅读

1.  记一次数据岗位大厂面试(已斩offer)

2.  两个案例搞定数据指标口径的一致性

3.  用户画像 模型设计与存储

4.  案例实践 | 有赞指标库实践

5.  2020年 5种将死的编程语言！

6.   一套 SQL 搞定离线+实时数据仓库

7. 漫谈系列 | 数仓第一篇NO.1 『基础架构』

8. 漫谈系列 | 数仓第二篇NO.2 『数据模型』

9. 漫谈系列 | 数仓第三篇NO.3 『数据处理』

觉得内容不错的话 请分享到朋友圈哦~ ▼  福利时刻  ▼ 

01. 后台回复「经典」,即可领取大数据数仓经典书籍。

02. 后台回复「中台」,即可领取大厂中台架构高清ppt。

03. 后台回复「加群」,或添加小助微信ID：iom1128  拉您入群(备注方向：大数据|数仓|分析|Flink|资源|python|)或领取资料。

Q: 关于数据仓库，你还想了解什么？

欢迎留言区与大家分享

觉得不错，请把这篇文章分享给你的朋友哦

入群请联系小助手：iom1128『紫霞仙子』

 

       ！关注不迷路~ 各种干货、资源定期分享！

学习小密圈