1.6.3 画像表结构设计

表结构设计也是画像开发过程中需要解决的一个重要问题。

表结构设计的重点是要考虑存储哪些信息、如何存储（数据分区）、如何应用（如何抽取标签）这3个方面的问题。

不同业务背景有不同的设计方式，这里提供两种设计思路：一是每日全量数据的表结构；二是每日增量数据的表结构。

Hive需要对输入进行全盘扫描来满足查询条件，通过使用分区可以优化查询。对于用户标签这种日加工数据，随着时间的推移，分区数量的变动也是均匀的。

每日全量数据，即该表的日期分区中记录着截止到当天的全量用户数据。例如，“select count（*）from userprofile where data='20180701'”这条语句查询的是userprofile表截止到2018年7月1日的全量用户数据。日全量数据的优势是方便查询，缺点是不便于探查更细粒度的用户行为。

每日增量数据，即该表的日期分区中记录着当日的用户行为数据。例如，同样是“select count（*）from userprofile where data='20180701'”，这条语句查询的是userprofile表在2018年7月1日记录的当日用户行为数据。日增量数据可视为ODS层的用户行为画像，在应用时还需要基于该增量数据做进一步的建模加工。

下面详细介绍这两种表结构的设计方法。

1.日全量数据

日全量数据表中，在每天对应的日期分区中插入截止到当天为止的全量数据，用户进行查询时，只需查询最近一天的数据即可获得最新全量数据。下面以一个具体的日全量表结构的例子来进行说明。

CREATE TABLE `dw.userprofile_attritube_all `(
    `userid` string COMMENT 'userid', 
    `labelweight` string COMMENT '标签权重',)
COMMENT 'userid 用户画像数据'
PARTITIONED BY ( `data_date` string COMMENT '数据日期', `theme` string COMMENT '二级主题', `labelid` string COMMENT '标签id')

这里userid表示用户id，labelweight表示标签权重，theme表示标签归属的二级主题，labelid表示一个标签id。通过“日期+标签归属的二级主题+标签id”的方式进行分区，设置三个分区字段更便于开发和查询数据。该表结构下的标签权重仅考虑统计类型标签的权重，如：历史购买金额标签对应的权重为金额数量，用户近30日访问天数为对应的天数，该权重值的计算未考虑较为复杂的用户行为次数、行为类型、行为距今时间等复杂情况。

通过表名末尾追加“_all”的规范化命名形式，可直观看出这是一张日全量表。

例如，对于主题类型为“会员”的标签，插入“20190101”日的全量数据，可通过语句：insert overwrite table dw.userprofile_userlabel_all partition（data_date='20190101'，theme='member'，labelid='ATTRITUBE_U_05_001'）来实现。查询截止到“20190101”日的被打上会员标签的用户量，可通过语句：select count（distinct userid）from dw.userprofile_userlabel_all where data_date='20190101'来实现。具体的开发过程在4.1节中详细讲解。

2.日增量数据

日增量数据表，即在每天的日期分区中插入当天业务运行产生的数据，用户进行查询时通过限制查询的日期范围，就可以找出在特定时间范围内被打上特定标签的用户。下面以一个具体的日增量表结构的例子来说明。

CREATE TABLE dw.userprofile_act_feature_append (
  labelid STRING COMMENT '标签id',   
  cookieid STRING COMMENT '用户id',   
  act_cnt int COMMENT '行为次数',
  tag_type_id int COMMENT '标签类型编码'，
  act_type_id int COMMENT '行为类型编码') 
comment '用户画像-用户行为标签表'
PARTITIONED BY (data_date STRING COMMENT '数据日期')

这里，labelid表示标签名称；cookieid表示用户id；act_cnt表示用户当日行为次数，如用户当日浏览某三级品类商品3次，则打上次数为3；tag_type_id为标签类型，如母婴、3C、数码等不同类型；act_type_id表示行为类型，如浏览、搜索、收藏、下单等行为。分区方式为按日期分区，插入当日数据。

通过表名末尾追加“_append”的规范化命名形式，可直观看出这是一张日增量表。

例如，某用户在“20180701”日浏览某3C电子商品4次（act_cnt），即给该用户（userid）打上商品对应的三级品类标签（tagid），标签类型（tag_type_id）为3C电子商品，行为类型（act_type_id）为浏览。这里可以通过对标签类型和行为类型两个字段配置维度表的方式，对数据进行管理。例如对于行为类型（act_type_id）字段，可以设定1为购买行为、2为浏览行为、3为收藏行为等，在行为标签表中以数值定义用户行为类型，在维度表中维护每个数值对应的具体含义。

该日增量数据表可视为ODS层用户行为标签明细。在查询过程中，例如对于某用户id为001的用户，查询其在“20180701”日到“20180707”日被打上的标签，可通过命令：select*from dw.userprofile_act_feature_append where userid='001'and data_date>='20180701'and data_date<='20180707'查询。

该日增量的表结构记录了用户每天的行为带来的标签，但未计算打在用户身上标签的权重，计算权重时还需做进一步建模加工。标签权重算法详见4.6节的内容。

3.关于宽表设计

用户画像表结构如何设计，没有一定要遵循的固定的格式，符合业务需要、能满足应用即可。下面通过两个宽表设计的案例，提供另一种解决方案的思路。

用户属性宽表设计（见表1-10），主要记录用户基本属性信息。

用户日活跃宽表设计（见表1-11），主要记录用户每天访问的信息。