数据仓库 沉默用户数
沉默用户:用户仅在安装当天(次日)启动一次,后续时间无再启动行为。该指标可以反映新增用户质量和用户与APP的匹配程度。
需求:求只在安装当天启动过,且启动时间在一周前的沉默用户数。
数据来源:用户日活表(每日活跃设备表)
建表语句:
drop table if exists ads_slient_count; 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';
数据导入脚本:
①where dt<='$do_date' 提前过滤数据掉一部分数据,非必要
②having count(*)=1 表示只启动过一次
③min(dt)<=date_add('$do_date',-7) 表示启动时间在一周前。因为前面过滤出了启动次数为1的记录,所以dt只剩下一条数据,这里用min还是max并无区别,只是为了合于hive语法,必须用聚合。
#!/bin/bash hive=/opt/module/hive/bin/hive APP=gmall if [ -n "$1" ];then do_date=$1 else do_date=`date -d "-1 day" +%F` fi echo "-----------导入日期$do_date-----------" sql=" insert into table "$APP".ads_slient_count select '$do_date' dt, count(*) slient_count from ( select mid_id from "$APP".dws_uv_detail_day where dt<='$do_date' group by mid_id having count(*)=1 and min(dt)<=date_add('$do_date',-7) )t1;" $hive -e "$sql"