本文将为您介绍明细圈人函数bit_construct和bit_match的具体使用语法。

背景信息

在圈人场景中,通常会有通过明细表圈人的需求,例如一个用户有多条记录,不同的记录满足不同的条件,明细圈人通常需要找出满足某些条件组合的用户列表。

示例明细表如下,需要在明细表中找出有过click购物车view收藏页行为的user列表(此处满足条件的是user A)。
user action page
A click 购物车
B click 首页
A view 收藏页
B click 购物车
A click 收藏页
传统的查询方法是采用多轮条件过滤并使用JOIN语句来筛选出符合条件的结果,这种方法需要写复杂的SQL,同时多个JOIN语句也会消耗大量的资源。从Hologres V0.10版本开始,针对明细表圈人场景提供bit_construct和bit_match函数,只需要通过一轮过滤筛选和函数计算即可得到结果人群数据,降低明细圈人场景开发复杂度,快速得到结果。

使用限制

在Hologres中使用明细圈人函数,具体限制如下:
  • 该函数仅Hologres V0.10及以上版本支持,请在Hologres管控台查看当前实例版本,如果您的实例是V0.10以下版本,请您提交工单升级实例。
  • 明细圈人函数在使用之前,需要执行以下语句开启extension。extension是DB级别的函数,一个DB只需执行一次即可,新建DB需要重新执行。
    CREATE EXTENSION flow_analysis;

bit_construct

Hologres在V0.10及以上版本提供了bit_construct函数用于明细表圈人场景。

  • 函数说明

    bit_construct通过设置筛选条件,返回一个最多32位integer类型的bitmap。

  • 语法示例
    bit_construct(
      a ,
      b ,
      ....,
      a6
    )
  • 参数说明
    • 参数a和b等,代表筛选条件,为bool类型,最多支持32个条件,取值范围为a~z,a1~a6。
    • 函数返回值类型为int。

bit_match

Hologres在V0.10及以上版本提供了bit_match函数用于明细表圈人场景。

  • 函数说明

    bit_match用于对bit_construct筛选的结果进行进一步计算

  • 语法示例
    bit_match('expression', bitmask)
  • 参数说明
    参数 说明 示例
    expression bit_construct函数中的条件表达式,支持&(and)、 | (or)、 !(not)、^(XOR) 和() a&b
    bitmask bit_construct构造的bitmap名。

使用示例

使用明细圈人函数,完整语法示例如下:

  1. 开启extension。
    Superuser在DB中执行如下语句开启extension:
    CREATE EXTENSION flow_analysis;
  2. 准备表和数据。
    如下为一张记录用户购物行为的明细表,该表记录了用户的uid以及购物路径的行为,同时对该表插入部分数据。
    create table ods_app_dwd(
    event_time timestamptz,
    uid bigint,
    action text,
    page text,
    product_code text,
    from_days int
    );
    
    insert into ods_app_dwd values('2021-04-03 10:01:30', 274649163, 'click', '购物车', 'MDS', 1);
    insert into ods_app_dwd values('2021-04-03 10:04:30', 274649163, 'view', '收藏页', 'MDS', 4);
    insert into ods_app_dwd values('2021-04-03 10:06:30', 274649165, 'click', '购物车', 'MMS', 8);
    insert into ods_app_dwd values('2021-04-03 10:09:30', 274649165, 'view', '购物车', 'MDS', 10);
  3. 数据查询。

    查询出满足“某个时间范围内将商品既加入购物车又添加至收藏页”条件的精准用户。

    您可以使用如下两种方式进行数据查询:
    • 使用WHERE语句查询。
      说明 在查询语句中WHERE条件筛选后的数据越少,查询性能越好。
      WITH tbl as (
      SELECT uid, bit_or(bit_construct(
        a := (action='click' and page='购物车'),
        b := (action='view' and page='收藏页'))) as uid_mask
        FROM ods_app_dwd
      WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
      GROUP BY uid )
      SELECT uid from tbl where bit_match('a&b', uid_mask);
      其中关于SQL语句的释义如下:
      • bit_construct:筛选出加入购物车的用户为条件a,筛选出添加至收藏夹的用户为条件b。
      • bit_or:将筛选出的条件a和b取或,在该SQL语句示例中,同一个用户只要有一行符合条件,即认为符合条件。
      • bit_match:a&b取满足条件a和b的交集。
    • 使用HAVING子句调用查询。
      SELECT uid FROM (
          SELECT uid, bit_or(bit_construct(
            a := (action='click' AND page='购物车'),
            b := (action='view' AND page='收藏页'))) as uid_mask
          FROM ods_app_dwd
          WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
          GROUP BY uid 
          HAVING bit_match('a&b', bit_or(bit_construct(
            a := (action='click' and page='购物车'),
            b := (action='view' and page='收藏页'))))
      ) t