开发者学堂课程【新电商大数据平台2020最新课程：电商项目之广告投放类统计表 SQL 实现】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/640/detail/10570
电商项目之广告投放类统计表 SQL 实现
内容介绍：
一、广告投放类指标统计4X [ADS]
二、广告投放类统计表
一、广告投放类指标统计4X [ADS]
create external table if not exists ads_nshop.ads. nshop_release_stat(
device_ type string comment ‘设备类型'，
os string comment '手机系统’，
customer _gender TINYINT comment ‘性别: 1男0女'，
age_ range string comment’年龄段’,
customer _natives string comment ‘所在地区',
release_ sources string comment " 投放渠道'。
release_ category string comment ‘投放浏览产品分类’，
visit_ total_ customers int comment "总访客数’，
visit_ total_ counts int comment " 总访问次故"
) partitioned by (bdp_ day string)
stored as parquet
location ' /data/nshop/ ads/operation/ads_nshop. release. stat/'
二、广告投放类统计表
insert overwrite table ads_ nshop.ads_nshop_ release_ stat partition(bdp_day=‘20200321’)
select
dr.device _type,
dr.os，
oc.customer _gender ，
oc.customer _age_ range ，
oc.customer _natives,
dr.release _sources
dr.release _category,
count(distinct dr .customer_id) visit_total_ customers,
count(1) visit_total_ counts
from
dwd_ nshop.dwd_ nshop_releasedatas dr
join
ods_ nshop.ods_02_customer oc
on
dr.customer_id=oc .customer_id
where
dr .bdp_day= " 20200321 "
group by
dr.device_ type ，
dr.os ,
oc.customer_gender ,
oc.customer _age_ range ，
oc.customer _natives,
dr.release_ sources
dr.release _category
