数据仓库ADS层-离线数仓-大数据

- 日理万妓 2022-11-30 04:21 234阅读 0赞

目标：当日、当周、当月活跃设备数

--------------------

1.  活跃设备数统计

create external table ads_uv_count( 
    `dt` string COMMENT '统计日期',
    `day_count` bigint COMMENT '当日用户数量',
    `wk_count`  bigint COMMENT '当周用户数量',
    `mn_count`  bigint COMMENT '当月用户数量',
    `is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
    `is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
    ) COMMENT '活跃设备数'
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_uv_count/'
    ;
    
    #step2. 从日活跃表，周活跃表，月活跃表导入数据
    insert into table ads_uv_count 
    select  
      '2020-08-19' dt,
       daycount.ct,
       wkcount.ct,
       mncount.ct,
       if(date_add(next_day('2020-08-19','MO'),-1)='2020-08-19','Y','N') ,
       if(last_day('2020-08-19')='2020-08-19','Y','N') 
    from 
    (
       select  
          '2020-08-19' dt,
           count(*) ct
       from dws_uv_detail_day
       where dt='2020-08-19'  
    )daycount join 
    ( 
       select  
         '2020-08-19' dt,
         count (*) ct
       from dws_uv_detail_wk
       where wk_dt=concat(date_add(next_day('2020-08-19','MO'),-7),'_' ,date_add(next_day('2020-08-19','MO'),-1) )
    ) wkcount on daycount.dt=wkcount.dt
    join 
    ( 
       select  
         '2020-08-19' dt,
         count (*) ct
       from dws_uv_detail_mn
       where mn=date_format('2020-08-19','yyyy-MM')  
    )mncount on daycount.dt=mncount.dt
    ;

1.  每日新增表

create external table ads_new_mid_count
    (
    `create_date`     string comment '创建时间' ,
    `new_mid_count`   BIGINT comment '新增设备数量' 
    )  COMMENT '每日新增设备信息数量'
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_new_mid_count/';
    
    #step2.  从每日新增设备详情表统计数据
    insert into table ads_new_mid_count 
    select
    create_date,
    count(*)
    from dws_new_mid_day
    where create_date='2020-08-19'
    group by create_date;
    
    #step3. 验证导入是否正确
    SELECT * FROM ads_new_mid_count LIMIT 2;
    
    create_date|new_mid_count|
    -----------|-------------|
    2020-08-19 |          708|

然后是统计脚本

#!/bin/bash
    
    
    APP=gmall
    hive=$HIVE_HOME/bin/hive
    
    if [ -n "$1" ];then
            do_date=1
    else
            do_date=`date -d "-1 day" +%F`
    fi
    
    
    sql="
            INSERT INTO TABLE "$APP".ads_new_mid_count
                    SELECT
                            create_date,
                            count(*)
                    FROM "$APP".dws_new_mid_day
                    WHERE create_date='$do_date'
            GROUP BY create_date;
    "
    
    $hive -e "$sql"

--------------------

1.  留存用户数统计

create external table ads_user_retention_day_count 
    (
    `create_date` string comment '设备新增日期',
    `retention_day` int comment '截止当前日期留存天数',
    `retention_count` bigint comment '留存数量'
    ) COMMENT '每日用户留存情况'
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_user_retention_day_count/';
    
    #step2. 从用户留存详情表导入数据
    insert into table ads_user_retention_day_count 
    select
        create_date,
        retention_day,
        count(*) retention_count
    from dws_user_retention_day
    where dt='2020-08-19' 
    group by create_date,retention_day;
    
    #step3. 验证导入是否正确
    SELECT * FROM ads_user_retention_day_count LIMIT 2;
    
    create_date|retention_day|retention_count|
    -----------|-------------|---------------|
    2020-08-19 |            1|            832|
    2020-08-19 |            2|            739|
    2020-08-20 |            1|            128|

#!/bin/bash
    
    APP=gmall
    hive=$HIVE_HOME/bin/hive
    
    
    if [ -n "$1" ];then
            do_date=$1
    else
            do_date=`date -d "-1 day" +%F`
    fi
    
    
    sql="
    USE $APP;
    INSERT INTO TABLE ads_user_retention_day_count
            SELECT
                    create_date,
                    retention_day,
                    count(*) retention_count
            FROM dws_user_retention_day
            WHERE dt = '$do_date'
            GROUP BY create_date,retention_day;
    "
    
    echo $sql
    
    
    $hive -e "$sql"

--------------------

1.  用户留存比例

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 '\t'
    location '/warehouse/gmall/ads/ads_user_retention_day_rate/';
    
    #step2. 从留存明细表统计数据
    insert into table ads_user_retention_day_rate
    select 
        '2020-08-19', 
        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='2020-08-19' 
        group by create_date,retention_day
    ) ur join ads_new_mid_count nc on nc.create_date=ur.create_date;
    
    #step3. 验证导入数据是否正确
    SELECT * FROM ads_user_retention_day_rate LIMIT 2;

#!/bin/bash
    APP=gmall
    hive=$HIVE_HOME/bin/hive
    
    if [ -n "$1" ] ;then
    	do_date=$1
    else
    	do_date=`date -d "-1 day" +%F`
    fi
    
    sql="
    USE $APP;
    INSERT INTO TABLE ads_user_retention_day_rate
    SELECT
    	'$do_date',
    	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_countg
    	FROM dws_user_retention_day
    	WHERE dt = '$do_date'
    	GROUP BY create_date,retention_day
    )ur
    	JOIN ads_new_mid_count nc 
    		ON nc.create_date = ur.create_date;
    "
    $hive -e "$sql"

--------------------

1.  沉默用户统计

create external table ads_slient_count( 
     `dt` string COMMENT '统计日期',
     `slient_count` bigint COMMENT '沉默设备数'
    ) 
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_slient_count';
    
    #step2. 从日活详情表导入数据
    insert into table ads_slient_count
    select 
        '2020-09-03' dt,
        count(*) slient_count
    from 
    (
        select mid_id
        from dws_uv_detail_day
        where dt<='2020-09-03'
        group by mid_id
        having count(*)=1 and min(dt)<date_add('2020-09-03',-7)
    ) t1;

--------------------

1.  本周回流用户

create external table ads_back_count( 
        `dt` string COMMENT '统计日期',
        `wk_dt` string COMMENT '统计日期所在周',
        `wastage_count` bigint COMMENT '回流设备数'
    ) 
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_back_count';
    
    #step2. 从周活表和新增表导入数据
    insert into table ads_back_count
    select 
       '2019-02-20' dt,
       concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1)) wk_dt,
       count(*)
    from 
    (
        select t1.mid_id
        from 
        (
            select	mid_id
            from dws_uv_detail_wk
            where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1))
        )t1
        left join
        (
            select mid_id
            from dws_new_mid_day
            where create_date<=date_add(next_day('2019-02-20','MO'),-1) and create_date>=date_add(next_day('2019-02-20','MO'),-7)
        )t2
        on t1.mid_id=t2.mid_id
        left join
        (
            select mid_id
            from dws_uv_detail_wk
            where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7*2),'_',date_add(next_day('2019-02-20','MO'),-7-1))
        )t3
        on t1.mid_id=t3.mid_id
        where t2.mid_id is null and t3.mid_id is null
    )t4;
    
    #step3. 验证导入是否正确
    select * from ads_back_count;
    
    dt        |wk_dt                |wastage_count|
    ----------|---------------------|-------------|
    2020-09-03|2020-08-31_2020-09-06|            0|

#!/bin/bash
    
    if [ -n "$1" ];then
    	do_date=$1
    else
    	do_date=`date -d "-1 day" +%F`
    fi
    
    hive=/opt/module/hive/bin/hive
    APP=gmall
    
    echo "-----------导入日期$do_date-----------"
    
    sql="
    insert into table "$APP".ads_back_count
    select 
           '$do_date' dt,
           concat(date_add(next_day('$do_date','MO'),-7),'_',date_add(next_day('$do_date','MO'),-1)) wk_dt,
           count(*)
    from 
    (
        select t1.mid_id
        from 
        (
            select mid_id
            from "$APP".dws_uv_detail_wk
            where wk_dt=concat(date_add(next_day('$do_date','MO'),-7),'_',date_add(next_day('$do_date','MO'),-1))
        )t1
        left join
        (
            select mid_id
            from "$APP".dws_new_mid_day
            where create_date<=date_add(next_day('$do_date','MO'),-1) and create_date>=date_add(next_day('$do_date','MO'),-7)
        )t2
        on t1.mid_id=t2.mid_id
        left join
        (
            select mid_id
            from "$APP".dws_uv_detail_wk
            where wk_dt=concat(date_add(next_day('$do_date','MO'),-7*2),'_',date_add(next_day('$do_date','MO'),-7-1))
        )t3
        on t1.mid_id=t3.mid_id
        where t2.mid_id is null and t3.mid_id is null
    )t4;
    "
    
    $hive -e "$sql"

1.  流式用户

create external table ads_wastage_count( 
        `dt` string COMMENT '统计日期',
        `wastage_count` bigint COMMENT '流失设备数'
    ) 
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_wastage_count';
    
    #step2. 从日活表导入数据
    insert into table ads_wastage_count
    select
         '2019-02-20',
         count(*)
    from 
    (
        select mid_id
    from dws_uv_detail_day
        group by mid_id
        having max(dt)<=date_add('2019-02-20',-7)
    )t1;
    
    #step3. 验证导入数据是否正确
    SELECT * FROM ads_wastage_count LIMIT 2;
    
    dt        |wastage_count|
    ----------|-------------|
    2020-09-03|          137|

#!/bin/bash
    
    APP=gmall
    hive=$HIVE_HOME/bin/hive
    
    if [ -n "$1" ];then
    	do_date=$1
    else
    	do_date=`date -d "-1 day" +%F`
    fi
    
    sql="
    INSERT INTO TABLE ads_wastage_count
    	SELECT
    		'$do_date',
    		count(*)
    	FROM
    	(
    		SELECT mid_id
    		FROM dws_uv_detail_day
    		GROUP BY mid_id
    		HAVING max(dt) <= date_add('$do_date',-7)
    	)t1;
    "
    
    $hive -e "$sql"

--------------------

1.  最近连续三周活跃用户

create external table ads_continuity_wk_count( 
        `dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日期',
        `wk_dt` string COMMENT '持续时间',
        `continuity_count` bigint
    ) 
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_continuity_wk_count';
    
    #step2. 从日活表导入数据
    insert into table ads_continuity_wk_count
    select 
         '2020-09-03',
         concat(date_add(next_day('2020-09-03','MO'),-7*3),'_',date_add(next_day('2020-09-03','MO'),-1)),
         count(*)
    from 
    (
        select mid_id
        from dws_uv_detail_wk
        where wk_dt>=concat(date_add(next_day('2020-09-03','MO'),-7*3),'_',date_add(next_day('2020-09-03','MO'),-7*2-1)) 
        and wk_dt<=concat(date_add(next_day('2020-09-03','MO'),-7),'_',date_add(next_day('2020-09-03','MO'),-1))
        group by mid_id
        having count(*)=3
    )t1;
    
    #step3. 验证导入数据是否正确
    SELECT * FROM  ads_continuity_wk_count;
    
    dt        |wk_dt                |continuity_count|
    ----------|---------------------|----------------|
    2020-09-03|2020-08-17_2020-09-06|             863|

#!/bin/bash
    
    if [ -n "$1" ];then
    	do_date=$1
    else
    	do_date=`date -d "-1 day" +%F`
    fi
    
    hive=$HIVE_HOME/bin/hive
    APP=gmall
    
    echo "-----------导入日期$do_date-----------"
    
    sql="
    insert into table "$APP".ads_continuity_wk_count
    select 
         '$do_date',
         concat(date_add(next_day('$do_date','MO'),-7*3),'_',date_add(next_day('$do_date','MO'),-1)),
         count(*)
    from 
    (
        select mid_id
        from "$APP".dws_uv_detail_wk
        where wk_dt>=concat(date_add(next_day('$do_date','MO'),-7*3),'_',date_add(next_day('$do_date','MO'),-7*2-1)) 
        and wk_dt<=concat(date_add(next_day('$do_date','MO'),-7),'_',date_add(next_day('$do_date','MO'),-1))
        group by mid_id
        having count(*)=3
    )t1;"
    
    $hive -e "$sql"

--------------------

1.  最近一周连续活跃三天的用户

create external table ads_continuity_uv_count( 
        `dt` string COMMENT '统计日期',
        `wk_dt` string COMMENT '最近7天日期',
        `continuity_count` bigint
    ) COMMENT '连续活跃设备数'
    row format delimited fields terminated by '\t'
    location '/warehouse/gmall/ads/ads_continuity_uv_count';
    
    #step2. 从日活表导入数据
    insert into table ads_continuity_uv_count
    select
        '2020-08-22',
        concat(date_add('2020-08-22',-6),'_','2020-08-22'),
        count(*)
    from
    (
        select mid_id
        from
        (
            select mid_id      
            from
            (
                select 
                    mid_id,
                    date_sub(dt,rank) date_dif
                from
                (
                    select 
                        mid_id,
                        dt,
                        rank() over(partition by mid_id order by dt) rank
                    from dws_uv_detail_day
                    where dt>=date_add('2020-08-22',-6) and dt<='2020-08-22'
                )t1
            )t2 
            group by mid_id,date_dif
            having count(*)>=3
        )t3 
        group by mid_id
    )t4;
    
    #step3. 验证导入数据是否正确
    SELECT * FROM ads_continuity_uv_count;
    
    dt        |wk_dt                |continuity_count|
    ----------|---------------------|----------------|
    2020-08-22|2020-08-16_2020-08-22|             840|

#step1. 先查询日活表，以今天为终点，取到过去一周到今天的全部日活数据
    SELECT * 
    FROM dws_uv_detail_day 
    where dt >= date_add('2020-08-22',-6) and dt <= '2020-08-22'
    
    mid_id|user_id|version_code|version_name     |lang    |source|os               |area|model                         |brand             |sdk_version         |gmail                                                   |height_width               |app_time                                 |network   |lng                 |lat                           |dt        |
    ------|-------|------------|-----------------|--------|------|-----------------|----|------------------------------|------------------|--------------------|--------------------------------------------------------|---------------------------|-----------------------------------------|----------|--------------------|------------------------------|----------|
    0     |0      |12          |1.1.9            |en      |V     |8.0.2            |MX  |Huawei-0                      |Huawei            |V2.7.8              |81UIOKY7@gmail.com                                      |640*960                    |1597699408632                            |3G        |-66.8               |-12.2                         |2020-08-19|
    1     |1      |12          |1.1.2            |en      |Q     |8.0.6            |MX  |sumsung-10                    |Sumsung           |V2.2.3              |874SED9E@gmail.com                                      |750*1134                   |1597733931205                            |4G        |-37.6               |20.2                          |2020-08-19|
    10    |10     |1|11        |1.1.0|1.2.7      |pt|es   |Q|L   |8.2.3|8.1.2      |MX  |Huawei-4|sumsung-1            |Huawei|Sumsung    |V2.5.9|V2.3.4       |NYIJ0GVH@gmail.com|33HZ982V@gmail.com                   |640*1136|1080*1920         |1597752555318|1597699138839              |3G        |-39.5|-80.8         |11.0|10.2                     |2020-08-19|
    101   |101    |9           |1.3.9            |es      |G     |8.2.5            |MX  |HTC-13                        |HTC               |V2.8.5              |F1DNN752@gmail.com                                      |640*960                    |1597706015142                            |WIFI      |-57.8               |9.2                           |2020-08-19|
    102   |102    |17|7        |1.1.8            |es|pt   |F|N   |8.2.3|8.1.3      |MX  |Huawei-3|Huawei-1             |Huawei            |V2.5.5|V2.4.0       |90CVQ6VY@gmail.com|L06PE2H0@gmail.com                   |750*1134|640*1136          |1597734404809|1597756726117              |4G|3G     |-102.4|-110.8       |27.3|24.5                     |2020-08-19|
    103   |103    |12          |1.0.7            |pt      |L     |8.0.7            |MX  |HTC-7                         |HTC               |V2.5.6              |86F1N0V3@gmail.com                                      |640*960                    |1597739943204                            |3G        |-73.9               |13.9                          |2020-08-19|
    104   |104    |17|7        |1.0.3|1.2.6      |pt      |V|U   |8.1.0|8.2.9      |MX  |sumsung-11|Huawei-19          |Sumsung|Huawei    |V2.9.9|V2.9.1       |QL78S813@gmail.com|NO64H623@gmail.com                   |640*1136                   |1597732182975|1597743681450              |WIFI|4G   |-77.6|-55.7         |-52.1|-10.0                   |2020-08-19|
    ...
    
    
    
    #step2. 取到mid，活跃时间和活跃时间排名
    select 
            mid_id,
            dt,
        	rank() over(partition by mid_id order by dt) rank
    from dws_uv_detail_day
    where dt>=date_add('2020-08-22',-6) and dt<='2020-08-22'
    
    mid_id|dt        |rank|
    ------|----------|----|
    0     |2020-08-19|   1|
    0     |2020-08-20|   2|
    0     |2020-08-21|   3|
    0     |2020-08-22|   4|
    1     |2020-08-19|   1|
    1     |2020-08-21|   2|
    1     |2020-08-22|   3|
    10    |2020-08-19|   1|
    10    |2020-08-20|   2|
    10    |2020-08-21|   3|
    10    |2020-08-22|   4|
    ...
    
    
    #step3. 使用等差数列求出这个用户连续活跃天数，如果连续活跃，则date_dif始终是一致的
    select 
    	mid_id,
    	date_sub(dt,rank) date_dif
    from
    (
    	select 
            mid_id,
            dt,
        	rank() over(partition by mid_id order by dt) rank
    	from dws_uv_detail_day
    	where dt>=date_add('2020-08-22',-6) and dt<='2020-08-22'            
    )t1
    
    
    mid_id|date_dif  |
    ------|----------|
    0     |2020-08-18|
    0     |2020-08-18|
    0     |2020-08-18|
    0     |2020-08-18|
    1     |2020-08-18|
    1     |2020-08-19|
    1     |2020-08-19|
    10    |2020-08-18|
    10    |2020-08-18|
    10    |2020-08-18|
    10    |2020-08-18|