数仓 DW层中主题表之页面浏览会话聚合表（PV、UV及其扩展指标统计以此表为基础）

数仓 DW层中主题表之页面浏览会话聚合表

1. 背景

1. 在大数据数仓设计中，数据一般划分层级从底到顶一般是是DIM维度表、ODS贴源层、DWD数据明细层、DWS数据服务层、ADS应用服务层
2. DW层中的数据一般存放有三个大的思路

• 一种是聚合各种维度表，这样就可以制造出大宽表，后续更高层级或者即席查询框架如kylin、presot等查询起来会很便利
• 一种是进行聚合，一般是按照主体或者维度进行聚合。注意这种聚合一般不会做很高层级的聚合，只是做初步聚合。
• 一种是按照业务需求来，没有固定套路。（这种不同公司不同项目组甚至不同时间都有很大差异性，具体就看需求方–如产品经理、财务部门、运营部门等）

3. 大数据处理数据来源一般是三个来源，行为日志埋点、爬虫、业务后台数据。

• 当然特殊情况还会有公司直接买数据，什么情况下需要直接买数据，这个就不方便透露了。
• 需要购买的数据，一般来自于在某方面已经形成了垄断或者接近垄断地位的公司或者集团。再多就不方便透露，大家如果进入大数据开发几年应该就会有体会了。

4. 本文讲解的是DW层按照主体进行聚合

• 按照页面浏览进行会话聚合

注意，有的公司并不会明显区分DW层位DWD和DWS，由公司直接将这2者视为一个层级，并不做明显区分。毕竟适合公司的才是最好的，而不是刻板地按照理论照本宣科。

2. 案例

2.1. 数据来源

1. 行为日志数据
2. 数据样式
   

2.2. 需求

• 基于每天的行为日志数据，提炼出如下表格所需要的数据
  

2.3. 解决思路

1. 先将对应表建立起来

CREATE DATABASE dws;
CREATE TABLE dws.`app_action_agr_session`(
`guid`              string    comment '唯一id，有账号就用账号，没有账号但是之前登录过账号，会根据打分规则绑定到一个账号，从没有登陆过账号则用deviceid',
`session_id`        string   comment '会话id',
`start_ts`          bigint   comment '页面浏览开始时间',
`end_ts`            bigint   comment '页面浏览结束时间',
`first_page_id`     string   comment '页面浏览第一个页面',
`last_page_id`      string   comment '页面浏览最后一个页面',
`pv_cnt`            int      comment '页面浏览个数',
`isnew`             int      comment '是否新访客',
`hour_itv`          int      comment '小时时段，按照开始时间的小时时段来计算，也就是向下取小时时间段',
`country`           string   comment '国家',
`province`          string   comment '省',
`city`              string   comment '城市',
`region`            string   comment '区域',
`device_type`       string   comment '设备类型'
)
PARTITIONED BY (dt string)
STORED AS PARQUET
;

注意，sql中，可以对数据库，对表，对字段都加注释，为了维护方便，最好加注释。
来自大数据开发的吐血建议！！！！

2. 因为数据字段较多，想办法将这些字段的获取分开提取，然后再join起来
3. 按照guid，sessionid进行分组聚合，然后提取pageid，国家，省，市，区，设备类型等几个字段

       SELECT
          guid                                    ,
          sessionid             as  session_id    ,
          first_value(properties['pageid'])  over(partition by guid,sessionid order by ts asc)  as first_page_id  ,
          first_value(properties['pageid'])  over(partition