实时数仓Hologres：画像分析 - BSI优化方案（Beta）

• 原始数据格式

• Bitmap格式：通过rb_tag表描述用户基础属性标签和uid的Bitmap关系，使用tag_name区分省份、性别等不同的标签。

  

• BSI格式（位切片构建）：通过bsi_gmv表描述用户行为标签值和uid的BSI关系，针对标签值的二进制表示，记录每一位切片对应的uid Bitmap，共计4个切片。

BSI表设计

表DDL语句如下：

• 用户原始属性标签表

  CREATE TABLE dws_userbase (
      uid int NOT NULL PRIMARY KEY,
      province text,
      gender text
      ...     -- 其他属性列
  )
  WITH (
      distribution_key = 'uid'
  );

• 用户uid字典编码表

  CREATE TABLE dws_uid_dict (
      encode_uid serial,
      uid int PRIMARY KEY
  );

• 用户原始行为标签表

  CREATE TABLE usershop_behavior (
      uid int NOT NULL,
      gmv int
  )
  WITH (
      distribution_key = 'uid'
  );

• Roaring Bitmap表示的用户属性标签表

  CREATE TABLE rb_tag (
      tag_name text,
      tag_val text,
      bitmap roaringbitmap
  );

• BSI表示的用户行为标签表（GMV）

  CREATE TABLE bsi_gmv (

  gmv_bsi bsi

  CREATE TABLE bsi_gmv (
      gmv_bsi bsi
  );

数据导入

• 通过原始属性标签表和uid字典编码表生成属性标签Roaring Bitmap数据并分桶

  INSERT INTO rb_tag
  SELECT
      'province',
      province,
      rb_build_agg (b.encode_uid) AS bitmap
  FROM
      dws_userbase a
      JOIN dws_uid_dict b ON a.uid = b.uid
  GROUP BY
      province;
  
  INSERT INTO rb_tag
  SELECT
      'gender',
      gender,
      rb_build_agg (b.encode_uid) AS bitmap
  FROM
      dws_userbase a
      JOIN dws_uid_dict b ON a.uid = b.uid
  GROUP BY
      gender;

• 通过原始行为标签表和uid字典编码表生成行为标签BSI数据并分桶

  INSERT INTO bsi_gmv
  SELECT
      bsi_build(array_agg(b.encode_uid),array_agg(a.gmv)) AS bitmap
  FROM
      usershop_behavior a
      JOIN dws_uid_dict b ON a.uid = b.uid
  ;

画像分析

通过BSI，可以非常便捷的将人群的属性标签和行为标签关联分析，包括人群圈选后的行为标签洞察、基于行为标签过滤的人群圈选等多个场景，示例如下：

BSI表设计

表DDL语句如下：

• Roaring Bitmap表示的用户属性标签表并分桶

  CREATE TABLE rb_tag (
      tag_name text,
      tag_val text,
      bucket int,
      bitmap roaringbitmap
  )
  WITH (
      distribution_key = 'bucket' -- 将分桶编号作为distribution_key
  );

• BSI表示的用户行为标签表（GMV）并分桶

  CREATE TABLE bsi_gmv (
      category text,
      bucket int,
      gmv_bsi bsi,
      ds date
  )
  WITH (
      distribution_key = 'bucket' -- 将分桶编号作为distribution_key
  );

数据导入

• 通过原始属性标签表和uid字典编码表生成属性标签Roaring Bitmap数据并分桶

  INSERT INTO rb_tag
  SELECT
      'province',
      province,
      encode_uid / 65536 AS "bucket",
      rb_build_agg (b.encode_uid) AS bitmap
  FROM
      dws_userbase a
      JOIN dws_uid_dict b ON a.uid = b.uid
  GROUP BY
      province,
      "bucket";
  
  INSERT INTO rb_tag
  SELECT
      'gender',
      gender,
      encode_uid / 65536 AS "bucket",
      rb_build_agg (b.encode_uid) AS bitmap
  FROM
      dws_userbase a
      JOIN dws_uid_dict b ON a.uid = b.uid
  GROUP BY
      gender,
      "bucket";

• 通过原始行为标签表和uid字典编码表生成行为标签BSI数据并分桶

  INSERT INTO bsi_gmv
  SELECT
      a.category,
      b.encode_uid / 65536 AS "bucket",
      bsi_build(array_agg(b.encode_uid),array_agg(a.gmv)) AS bitmap,
      a.ds
  FROM
      usershop_behavior a
      JOIN dws_uid_dict b ON a.uid = b.uid
  WHERE
      ds = CURRENT_DATE - interval '1 day'
  GROUP BY
      category,
      "bucket",
      ds;