前提 今天是 2020-6-14日
▶ 需求一 [ 最近 1 7 30 天 个渠道统计]
建表语句
CREATE EXTERNAL TABLE ads_traffic_stats_by_channel
(
-- 维度属性
`dt` STRING COMMENT '统计日期',
`recent_days` BIGINT COMMENT '最近天数,1:最近1天,7:最近7天,30:最近30天',
`channel` STRING COMMENT '渠道',
-- 统计的指标
-- 访客指 设备，主键是mid_id
`uv_count` BIGINT COMMENT '访客人数',
`avg_duration_sec` BIGINT COMMENT '会话平均停留时长，单位为秒',
`avg_page_count` BIGINT COMMENT '会话平均浏览页面数',
`sv_count` BIGINT COMMENT '会话数',
-- 跳出的会话： 当前会话的页面访问总数为1
-- 会话跳出数 / 会话总数
`bounce_rate` DECIMAL(16, 2) COMMENT '跳出率'
) COMMENT '各渠道流量统计'
步骤：
- dws_traffic_session_page_view_1d 表 >
流量域会话粒度页面浏览最近1日汇总表
from dws_traffic_session_page_view_1d`
- 取2020-06-14日期,最近30天所有的数据
where dt > date_sub('2020-06-14',30)
- recent_days 最近日期，1 7 30。将数据 复制三份后筛选。
（1）笛卡尔积，目的：复制三份
lateral view explode(`array`(1,7,30)) tmp as recent_days
（2）排除掉一些 不符合要求的数据
and dt > date_sub('2020-06-14',recent_days)
例如
不符合要求的数据
对于2020-06-16 该条数据来说，已经不属于 1天内的数据了，应该排除。
-
group by recent_days,channel
-
▞ 求 访客数 统计访问人数
注意：小明一天内访问了10次，计一个访问人数，所以需要去重。
count(distinct mid_id) uv_count
- ▞ 求： 会话平均停留时长
注意：单位为 秒
取完均值 需要注意类型转换
bigint(avg(during_time_1d) / 1000) avg_duration_sec
- 会话平均浏览页面数 同理
- 会话总数 count 数次数
count(*) sv_count
- 跳出率 = 会话跳出数 / 会话总数
`page_count_1d` BIGINT COMMENT '最近1日访问页面数'
当 该值为1 时 则为跳出会话。
if(page_count_1d = 1,1,0)
计算 总会话跳出数
sum(`if`(page_count_1d = 1,1,0))
计算会话跳出率
cast(sum(`if`(page_count_1d = 1,1,0)) / count(*) * 100 as DECIMAL(16, 2)) bounce_rate
完整sql
/*
数据源表的粒度： dws_traffic_session_page_view_1d
一天中一个设备的一个session是一行
目标表所需的行数: 3 * 渠道数
计算思路： 聚合
*/
-- 每天覆盖写 14日 - 今天统计的结果
insert overwrite table ads_traffic_stats_by_channel
-- 截止到当天 14日 - 昨天
select * from ads_traffic_stats_by_channel
-- 都可以拼接 union all 没有幂等性
-- union 可以实现幂等性(能去重)
union
-- 注意：要导入的数据的类型必须和建表时的类型是匹配的
-- 今天
select
-- group by后，select后面能写什么？ 只能写group by 后的字段，常量，聚合函数中的字段
'2020-06-14' dt,
recent_days,
channel,
count(distinct mid_id) uv_count,
bigint(avg(during_time_1d) / 1000) avg_duration_sec,
bigint(avg(page_count_1d)) avg_page_count,
count(*) sv_count,
cast(sum(`if`(page_count_1d = 1,1,0)) / count(*) * 100 as DECIMAL(16, 2)) bounce_rate
from dws_traffic_session_page_view_1d
-- ②复制3份
lateral view explode(`array`(1,7,30)) tmp as recent_days
-- ①取最近30天的数据
where dt > date_sub('2020-06-14',30)
-- ③ 将复制3份后的数据，按照dt再进行过滤
and dt > date_sub('2020-06-14',recent_days)
group by recent_days,channel;
补充说明
insert overwrite ：覆盖写是为了保证 多天的数据 聚合在一个文件中，如果不是覆盖写，会将每天的 数据都产生一个文件，久而久之就会产生很多小文件。
union ：去重操作 为了 幂等性 ， 因为 当14日 多次执行该sql时，会产生多份数据。
插入操作，将 之前所有的统计数据 查出来 再加上今天的数据，重新入库。
注意事项
- 小文件：
两个小文件
将该值 改为true ，可将两个文件 合并为一个文件
set hive.merge.soarkfiles=true
效果：
变为一个文件
-
小文件合并阈值：
当文件 大小 大于 该值，才会产生新的文件，该参数可依据自身环境调整。
网友评论