前提 今天是 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 '各渠道流量统计'

步骤：

1. dws_traffic_session_page_view_1d 表 > 流量域会话粒度页面浏览最近1日汇总表

from dws_traffic_session_page_view_1d`

2. 取2020-06-14日期,最近30天所有的数据

where dt > date_sub('2020-06-14',30)

3. 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天内的数据了，应该排除。

3. group by recent_days,channel

4. ▞ 求 访客数 统计访问人数
   注意：小明一天内访问了10次，计一个访问人数，所以需要去重。

count(distinct mid_id) uv_count

5. ▞ 求： 会话平均停留时长
   注意：单位为 秒
   取完均值 需要注意类型转换

 bigint(avg(during_time_1d) / 1000) avg_duration_sec

6. 会话平均浏览页面数 同理
7. 会话总数 count 数次数

  count(*) sv_count

8. 跳出率 = 会话跳出数 / 会话总数

   `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时，会产生多份数据。

插入操作，将 之前所有的统计数据 查出来 再加上今天的数据，重新入库。

注意事项

1. 小文件：

两个小文件

将该值 改为true ，可将两个文件 合并为一个文件

set hive.merge.soarkfiles=true

效果：

变为一个文件

2. 小文件合并阈值：

   
   
   

   当文件 大小 大于 该值，才会产生新的文件，该参数可依据自身环境调整。