一、广告点击次数分析
### --- 需求分析
~~~ # 广告：ad
~~~ action: 用户行为；0 曝光；1 曝光后点击；2 购买
~~~ duration: 停留时长
~~~ shop_id: 商家id
~~~ event_type: "ad"
~~~ ad_type: 格式类型；1 JPG；2 PNG；3 GIF；4 SWF
~~~ show_style: 显示风格，0 静态图；1 动态图
~~~ product_id: 产品id
~~~ place: 广告位置；首页=1，左侧=2，右侧=3，列表页=4
~~~ sort: 排序位置
### --- 公共字段：分时统计：
~~~ 曝光次数、不同用户id数（公共信息中的uid）、不同用户数(公共信息中的device_id)
~~~ 点击次数、不同用户id数、不同用户数(device_id)
~~~ 购买次数、不同用户id数、不同用户数(device_id)
~~~ DWD => DWS(不需要) => ADS；在某个分析中不是所有的层都会用到
二、加载计算ADS层：创建ADS层表
### --- 创建ADS层表
~~~ # 语法
drop table if exists ads.ads_ad_show;
create table ads.ads_ad_show(
cnt bigint,
u_cnt bigint,
device_cnt bigint,
ad_action tinyint,
hour string
) PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';
### --- 操作实例：创建ADS层表
hive (default)> drop table if exists ads.ads_ad_show;
hive (default)>
> create table ads.ads_ad_show(
> cnt bigint,
> u_cnt bigint,
> device_cnt bigint,
> ad_action tinyint,
> hour string
> ) PARTITIONED BY (`dt` string)
> row format delimited fields terminated by ',';
三、加载ADS层数据
### --- 创建ADS层数据加载脚本
[root@hadoop02 ~]# vim /data/yanqidw/script/advertisement/ads_load_ad_show.sh
#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table ads.ads_ad_show
partition (dt='$do_date')
select count(1),
count(distinct uid),
count(distinct device_id),
ad_action,
hour
from dwd.dwd_ad
where dt='$do_date'
group by ad_action, hour
"
hive -e "$sql"
### --- 计算ADS层广告点击次数计算
[root@hadoop02 ~]# sh /data/yanqidw/script/advertisement/ads_load_ad_show.sh 2020-07-21
### --- 查看广告点击数
hive (default)> show partitions ads.ads_ad_show;
partition
dt=2020-07-21
hive (default)> select count(*) from ads.ads_ad_show where dt='2020-07-21';
71
Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
