开发者学堂课程【新电商大数据平台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
运行结果如下：
