开发者学堂课程【新电商大数据平台2020最新课程:电商项目之用户指标类统计表 SQL 实现】学习笔记,与课程紧密联系,让用户快速学习知识。
课程地址:https://developer.aliyun.com/learning/course/640/detail/10561
电商项目之用户指标类统计表 SQL 实现
内容介绍:
一、用户类指标统计表4X【ADS】
二、用户类指标统计表
一、用户类指标统计表 4X 【ADS】
create external table if not exists ads_ nshop. ads_nshop_ customer_ stat(
customer_ gender TINYINT COMMENT ‘性别: 1男0女’,
age_range string COMMENT ‘年龄段’,
customer_ natives string COMMENT '所在地区’,
total_counts int comment ‘总用户数’,
add_ counts int comment ‘新增用户数',
active_ counts int comment ‘活跃用户数'
) partitioned by (bdp_day string)
stored as parquet
location'/data/nshop/ ads/operation/ads_nshop_customer_stat/'
二、用户类指标统计表
insert overvrite table ads_ nshop.ads_nshop_customer_stat partition(bdp_day=’20200321’ )
select
c.customer_gender,
c.customer_age_range ,
c. customer_natives ,
count(distinct c.customer_id) total_counts,
count(casewhen from_unixtime(cast(c.customer_ctime/1000 as bigint), ' yyMMdd')='20191101'
then c.customer_id end) add_ counts,
count(distinct p.user_id) active_counts
from
ods_ nshop.ods_02_customer c
join
dwd_nshop.dwd_nshop_ actlog_pdtview p
on
p.bdp_ day=' 20200321'
and
p.user_ id=c.customer_id
group by
c .customer_gender,
c.customer_ age_range,
c. customer_natives
运行结果如下: