没错,我的工种就是属于那种史上被人吐槽为最没技术含量、最打杂的工种——数据仓库开发工程师。
即使这样,我们也没有放弃理想啊,每天都在思考怎么把工作做的更好,即使打杂,也要打的优雅~~
今天分享一个用户留存模型的设计
用户留存是用户分析中最常用到的指标之一。
我们常常接到这样的需求:
~我们要看1天、2天、3天、4天 … 7天的留存~
~我们要看1天、2天、3天、4天 … 28天的留存~
还有一些不按套路出牌的:
~我们要看第33天的留存~
~我们要看第56天的留存~
…
让你在代码里写连着7天的留存,就已经写到手抽筋了,恨不得写个代码生成器来生成代码。
后面再来个连着28天的留存… 本来以为这就是终结了,谁知后面还有33天、49天、56天…
可以这么来设计
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='2019-08-01'
哈哈 ,是不是很方便?