Hologres:Intended user identification functions

Background information

In intended user identification scenarios, you need to identify intended users from tables. For example, a table contains multiple records of a single user and different records meet different conditions. To identify intended users, you need to query the users who meet a combination of specific conditions.

Limits

bit_construct

In Hologres V0.10 and later, Hologres provides the bit_construct function for intended user identification.

• Function description

  The bit_construct function returns a bitmap of the INTEGER type based on the specified filter conditions. The returned value can be up to 32 bits in length.

• Syntax

  bit_construct(
    a ,
    b ,
    ....,
    a6
  )

• Parameters

  • The parameters such as a and b specify filter conditions whose returned values are of the BOOLEAN type. A maximum of 32 conditions are supported. Valid values: a to z and a1 to a6.

  • This function returns a value of the INT type.

bit_match

In Hologres V0.10 and later, Hologres provides the bit_match function for intended user identification.

• Function description

  The bit_match function is used to further calculate the output of the bit_construct function.

• Syntax

  bit_match('expression', bitmask)

• Parameters

  Parameter	Description	Example
  expression	The conditional expression in the bit_construct function. The expression can contain the following operators and characters: AND (&), OR (|), NOT (!), and XOR (^).	a&b
  bitmask	The name of the bitmap that is constructed by the bit_construct function.	N/A

Examples

The following sample code provides an example on how to use intended user identification functions:

1. Install an extension.

   Execute the following statement to install an extension in a database as the superuser:

   CREATE EXTENSION flow_analysis;

2. Prepare a table and data.

   Create a table that records user IDs and events in the online shopping process of users. Then, insert data into the table. Use the following sample statements:

   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', 'Shopping cart', 'MDS', 1);
   insert into ods_app_dwd values('2021-04-03 10:04:30', 274649163, 'view', 'Favorites', 'MDS', 4);
   insert into ods_app_dwd values('2021-04-03 10:06:30', 274649165, 'click', 'Shopping cart', 'MMS', 8);
   insert into ods_app_dwd values('2021-04-03 10:09:30', 274649165, 'view', 'Shopping cart', 'MDS', 10);

3. Query data.

   Query the users who added a product to the shopping cart and to favorites within a specific period of time.

   You can query data by using one of the following methods:

   • Use the WHERE clause.

     Note

     The less data is queried by using the WHERE clause, the better query performance is.

     WITH tbl as (
     SELECT uid, bit_or(bit_construct(
       a := (action='click' and page='Shopping cart'),
       b := (action='view' and page='Favorites'))) 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);

     Functions in the SQL statements:

     • bit_construct: uses the Click the shopping cart event as Condition a and the View favorites event as Condition b to query the users who meet the filter conditions.

     • bit_or: performs the bitwise OR operation on the users who meet Condition a and the users who meet Condition b. In this example, if a record of a user meets Condition a or b, the user meets the filter conditions.

     • bit_match: uses the a&b expression to query the users who meet both Condition a and Condition b.

   • Use the HAVING clause.

     SELECT uid FROM (
         SELECT uid, bit_or(bit_construct(
           a := (action='click' AND page='Shopping cart'),
           b := (action='view' AND page='Favorites'))) 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='Shopping cart'),
           b := (action='view' and page='Favorites'))))
     ) t