数据仓库用户留存主题(代码片段)

关键词：

需求：计算前1天，2天，3天的用户留存数量和用户留存率。

用户留存：某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分用户占当时新增用户的比例即是留存率。例如，5月份新增用户200，这200人在6月份启动过应用的有100人，7月份启动过应用的有80人，8月份启动过应用的有50人；则5月份新增用户一个月后的留存率是50%，二个月后的留存率是40%，三个月后的留存率是25%。

实现思路：例如，要计算2019-02-11的1日留存用户，需要知道两种数据，一是2019-02-10新增了哪些用户（在每日新增设备明细表中），二是2019-02-11的活跃用户有哪些（用每日活用户表中）。如果一个用户既是2019-02-10的新增用户，又是2019-02-11的活跃用户，此用户即为2019-02-10的1日留存用户。

数据来源：DWS层的日活表和新增设备明细表。

DWS层（每日留存用户明细表）

建表语句。

分析：

①此表采用了分区表的形式，以每天的日期为分区。因为每天都有自己的1日留存，2日留存，3日留存。。。等情况，数据量相对较大。但因为都是针对新增设备的留存明细，数据量只能说相对较大，并没有达到非用分区表不可的地步。

②表中有两个重要的字段，设备新增时间和直到今日的留存天数。例如，在2010-02-11那天，数据可能出现设备新增日期为2019-02-10，留存天数为1的数据；设备新增日期为2019-02-09，留存天数为2的数据。。。等等。

③DWS层还只是一个留存明细表，具体有多少留存，还需要取ADS做count操作。

drop table if exists dws_user_retention_day;
create external table dws_user_retention_day 
(
    `mid_id` string COMMENT ‘设备唯一标识‘,
    `user_id` string COMMENT ‘用户标识‘, 
    `version_code` string COMMENT ‘程序版本号‘, 
    `version_name` string COMMENT ‘程序版本名‘, 
　　`lang` string COMMENT ‘系统语言‘, 
　　`source` string COMMENT ‘渠道号‘, 
　　`os` string COMMENT ‘安卓系统版本‘, 
　　`area` string COMMENT ‘区域‘, 
　　`model` string COMMENT ‘手机型号‘, 
　　`brand` string COMMENT ‘手机品牌‘, 
　　`sdk_version` string COMMENT ‘sdkVersion‘, 
　　`gmail` string COMMENT ‘gmail‘, 
　　`height_width` string COMMENT ‘屏幕宽高‘,
　　`app_time` string COMMENT ‘客户端日志产生时的时间‘,
　　`network` string COMMENT ‘网络模式‘,
　　`lng` string COMMENT ‘经度‘,
　　`lat` string COMMENT ‘纬度‘,
   `create_date`    string  comment ‘设备新增时间‘,
   `retention_day`  int comment ‘截止当前日期留存天数‘
)  COMMENT ‘每日用户留存情况‘
PARTITIONED BY (`dt` string)
stored as parquet
location ‘/warehouse/gmall/dws/dws_user_retention_day/‘

数据导入，以2019-02-11的1日，2日，3日留存为例

分析：

①在sql中，用户活跃日期固定为统计日期（2019-02-11），而设备新增日期随着留存天数retention_day 不同，在统计日期（2019-02-11）上减去相应的天数。

②将所有的sql结果进行union all，就得到了2019-02-11相对于2019-02-10的1日留存，相对于2019-02-09的2日留存和相对于2019-02-08的3日留存。若要计算其他留存天数，进行类似重复操作即可。

insert overwrite table dws_user_retention_day
partition(dt="2019-02-11")
select
    nm.mid_id,
    nm.user_id,
    nm.version_code,
    nm.version_name,
    nm.lang,
    nm.source,
    nm.os,
    nm.area,
    nm.model,
    nm.brand,
    nm.sdk_version,
    nm.gmail,
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    1 retention_day 
from dws_uv_detail_day ud join dws_new_mid_day nm  on ud.mid_id =nm.mid_id 
where ud.dt=‘2019-02-11‘ and nm.create_date=date_add(‘2019-02-11‘,-1)

union all
select  
    nm.mid_id,
    nm.user_id , 
    nm.version_code , 
    nm.version_name , 
    nm.lang , 
    nm.source, 
    nm.os, 
    nm.area, 
    nm.model, 
    nm.brand, 
    nm.sdk_version, 
    nm.gmail, 
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    2 retention_day 
from  dws_uv_detail_day ud join dws_new_mid_day nm   on ud.mid_id =nm.mid_id 
where ud.dt=‘2019-02-11‘ and nm.create_date=date_add(‘2019-02-11‘,-2)

union all
select  
    nm.mid_id,
    nm.user_id , 
    nm.version_code , 
    nm.version_name , 
    nm.lang , 
    nm.source, 
    nm.os, 
    nm.area, 
    nm.model, 
    nm.brand, 
    nm.sdk_version, 
    nm.gmail, 
    nm.height_width,
    nm.app_time,
    nm.network,
    nm.lng,
    nm.lat,
    nm.create_date,
    3 retention_day 
from  dws_uv_detail_day ud join dws_new_mid_day nm   on ud.mid_id =nm.mid_id 
where ud.dt=‘2019-02-11‘ and nm.create_date=date_add(‘2019-02-11‘,-3);

ADS层留存用户数和留存用户比率

建表语句

分析：注意，此表既包括留存数量，也包括用户留存比率（某日的n天留存，有2个条件）

drop table if exists ads_user_retention_day_rate;
create external table ads_user_retention_day_rate 
(
     `stat_date`          string comment ‘统计日期‘,
     `create_date`       string  comment ‘设备新增日期‘,
     `retention_day`     int comment ‘截止当前日期留存天数‘,
     `retention_count`    bigint comment  ‘留存数量‘,
     `new_mid_count`     bigint comment ‘当日设备新增数量‘,
     `retention_ratio`   decimal(10,2) comment ‘留存率‘
)  COMMENT ‘每日用户留存情况‘
row format delimited fields terminated by ‘	‘
location ‘/warehouse/gmall/ads/ads_user_retention_day_rate/‘;

导入数据。

①数据来源于上一张用户每日留存明细表。

②where条件dt=‘2019-02-11‘，说明要拿出每日留存明细表中2019-02-11的所有数据（不同留存天数）。

②group by create_date,retention_day，分组条件有2个，其实只用一个条件分组也可以，但是select的字段必须是分组字段，或者在聚合函数里，为了方便，用这两个条件分组，且不影响结果。

③留存率 = 留存用户数 / 当天新增用户总数。所以需要 join ADS层的ads_new_mid_count ，里面保存了每日新增的用户（设备）数量。

insert into table ads_user_retention_day_rate
select 
    ‘2019-02-11‘, 
    ur.create_date,
    ur.retention_day, 
    ur.retention_count, 
    nc.new_mid_count,
    ur.retention_count/nc.new_mid_count*100
from 
(
    select
        create_date,
        retention_day,
        count(*) retention_count
    from dws_user_retention_day
    where dt=‘2019-02-11‘ 
    group by create_date,retention_day
) ur join ads_new_mid_count nc on nc.create_date=ur.create_date;

数据仓库用户新增主题(代码片段)

...道两件事，一是今天所有的活跃用户，因此，新增用户的数据来源，还是DWS层的设备日活表，新增用户，就是部分日活的用户。二是要知道哪些用户在以前活跃过，用今日活跃用户减去以前活跃过的用户，剩下的就是新增用户。... 查看详情

数据仓库系统及特点(代码片段)

...就是一个典型的OLTP系统。OLTP在使用过程中积累了大量的数据。关系数据库概念提出之后，联机事务处理一直是数据库应用的主流。OLTP的特点是：1）对相应时间要求非常高；2）用户数量非常庞大，主要是操作人员；3）数据库的... 查看详情

数据仓库hive(代码片段)

数据仓库什么时候需要用到数据仓库？一个公司里面不同项目可能用到不同的数据源，有的存在MySQL里面，又的存在MongoDB里面，甚至还有些要做第三方数据。但是现在又想把数据整合起来，进行数据分析。此时数据仓库(DataWarehou... 查看详情

数据仓库|概述|集群环境搭建(代码片段)

 数据仓库DW数据来源：爬虫日志采集系统业务数据财务系统数据去向：报表系统、用户画像推荐系统、机器学习、风控系统项目需求分析①数据采集平台搭建②实现用户行为数据仓库的分层搭建③实现业务数据仓库的分层搭... 查看详情

hadoop之hive(代码片段)

数据仓库数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持。数据仓库是存数据的，企业的各种数据往里面存，主要目的是为了分析有效数据，后续会基于它产出供分析挖掘的数据，或... 查看详情

sql计算留存率(代码片段)

用SQL计算用户的留存率需求描述有用户活跃表dws_user_active_t，它的字段有：ds'操作日期'device '设备类型'1手机2PAD3浏览器 user_id'用户id'active_score '活跃度'计算出用户留存率（公式见下）ÿ... 查看详情

hive关于用户留存率的计算(代码片段)

首先用户留存率一般是面向新增用户的概念,是指某一天注册后的几天还是否活跃,是以每天为单位进行计算的.一般收到的需求都是一个时间段内的新增用户的几天留存(1)找到这个时间段内的新增用户(也可能含有地区啊的各种附... 查看详情

hive面试题系列第三题-用户留存问题(代码片段)

视频讲解地址：https://www.bilibili.com/video/BV1Rd4y1T7iU/?spm_id_from=333.788&vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第三题-用户留存问题题目：求用户1日、3日、7日留存率概念问题：第N日活跃用户留存率&# 查看详情

用户留存率sql题(代码片段)

原文：牛客每个人最近的登录日期(五)_牛客题霸_牛客网--方法一SELECTt0.date,IFNULL(ROUND(COUNT(DISTINCTt2.user_id)/(COUNT(t1.user_id)),3),0)FROM(SELECTdateFROMloginGROUPBYdate)t0LEFTJOIN(SELECTuser_id,MIN(date)ASdateFROMlog 查看详情

数据仓库的架构与设计(代码片段)

公司之前的数据都是直接传到Hdfs上进行操作，没有一个数据仓库，趁着最近空出几台服务器，搭了个简陋的数据仓库，这里记录一下数据仓库的一些知识。涉及的主要内容有：什么是数据仓库？数据仓库的架构数据仓库多维数... 查看详情

数据仓库的架构与设计(代码片段)

公司之前的数据都是直接传到Hdfs上进行操作，没有一个数据仓库，趁着最近空出几台服务器，搭了个简陋的数据仓库，这里记录一下数据仓库的一些知识。涉及的主要内容有：什么是数据仓库？数据仓库... 查看详情

数据仓库期末复习(代码片段)

1、什么是“数据仓库”？数据仓库是用于存储海量数据以及供分析决策的工具。补充：特点：面向主题的、集成的、非易失的、随时间变化的2、由于原始数据和导出数据的差异而引发数据分离的自然扩展过程形成的... 查看详情

数据仓库期末复习(代码片段)

1、什么是“数据仓库”？数据仓库是用于存储海量数据以及供分析决策的工具。补充：特点：面向主题的、集成的、非易失的、随时间变化的2、由于原始数据和导出数据的差异而引发数据分离的自然扩展过程形成的... 查看详情

hive面试题系列第三题-用户留存问题(代码片段)

...vd_source=aa4fb0436f6d978af872cafb81a01178Hive面试题系列第三题-用户留存问题题目：求用户1日、3日、7日留存率概念问题：第N日活跃用户留存率：以基准日的活跃用户数为主，第N日后依然活跃的用户占基准日活跃用户... 查看详情

hive数仓项目之访问咨询主题看板增量的流程(代码片段)

 往期内容：Hive数仓项目架构说明、环境搭建及数据仓库基础知识Hive数仓项目之数仓分层、数仓工具的使用Hive数仓项目之访问咨询主题看板：数据的采集、转换、分析导出今日内容:访问咨询主题看板_增量的流程(操作)1.... 查看详情

数据仓库dws层之用户行为宽表(代码片段)

...便之后关联用户维度信息后，进行不同角度的统计分析。数据来源：DWD层相关的业务数据表创建用户行为宽表：这张宽表整合了下单、支付和评论3种行为。droptableifexistsdws_user_action;createexternaltabledws_user_action(user_idstringcomment‘用... 查看详情

数据仓库-学习理解(代码片段)

概要：数据仓库是一个过程而不是一个项目；是一个环境而不是一件产品。数据仓库将多个数据源的数据按照一定主题集成起来，经过抽取、清洗、转换。整合后的数据不允许随便修改，定期更新，这个过程... 查看详情

presto+superset数据仓库及bi(代码片段)

基于Presto和superset搭建数据分析平台。Presto可以作为数据仓库，能够连接多种数据库和NoSql，同时查询性能很高；Superset提供了Presto连接，方便数据可视化和dashboard生成。基本概念datawarehouse数据仓库整合各类数据库数据，面向主... 查看详情