使用 BITMAP 对数据去重

Matrixone 支持使用 BITMAP 处理不同值（distinct values）的计数。

本篇文章将介绍 BITMAP 的一些应用场景和案例。

应用场景

在 MatrixOne 中，BITMAP 用于优化特定类型的查询操作，尤其是在处理具有低基数（low cardinality）的列时，帮助用户在大数据环境中实现了快速的数据分析和决策支持。以下是一些 BITMAP 的使用场景：

用户行为分析：假设一个电商平台想要分析不同用户的行为模式。他们有一个事件表，记录了用户的每一次点击事件，包括用户 ID、时间戳、事件类型等。通过使用 BITMAP，可以快速地对用户的行为进行分类和统计，例如，找出所有进行过“购买”行为的用户数量。
多维度分析：在数据仓库中，经常需要对多个维度进行分析，例如，分析特定时间段内不同地区的销售情况。通过 BITMAP，可以快速地对日期和地区这两个维度进行筛选，从而提高查询效率。
统计不同值的数量：在处理具有大量唯一值的列时，如产品类别或用户状态，使用 BITMAP 可以高效地计算这些列中不同值的数量。例如，一个社交媒体平台可能需要统计其用户活跃状态（在线、离线）的不同数量。
层次化聚合加速：在需要进行层次化聚合查询时，如对销售数据进行季度和年度汇总，BITMAP 可以加速计算过程。通过使用 BITMAP，可以快速地对数据进行分组和聚合，从而得到所需的统计信息。
优化复杂查询：对于包含多个条件的复杂查询，BITMAP 可以快速筛选出符合条件的数据。例如，一个金融公司可能需要找出同时满足“高净值客户”和“投资于特定基金”的用户。

开始前准备

已完成单机部署 MatrixOne。

案例

根据上述场景一设计案例，分析电商平台不同用户的行为模式。

步骤

1. 建立用户表并导入数据

准备一个名为 user_behavior_table 的表以及对应的 csv 数据，这个 csv 数据表共有 39270760 行数据。

CREATE TABLE user_behavior_table(
user_id int,--用户 id
behavior varchar(100),--行为，包括 browser,purchase,returns
occur_year varchar(100)--行为发生年份
);

LOAD DATA INFILE '/your_path/user_behavior_table.csv' INTO TABLE user_behavior_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

2. 定义预计算表

把粗粒度的计算结果保存在预计算表中，后续各种不同维度聚合可以使用预计算表中的结果，经过简单的计算就可以得到结果，加速查询。

CREATE TABLE precompute AS
SELECT
  behavior,
  occur_year,
  BITMAP_BUCKET_NUMBER(user_id) as bucket,
  BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(user_id)) as bitmap 
FROM user_behavior_table
GROUP BY  behavior,occur_year,bucket;

3. 按不同维度聚合筛选数据

计算在用户行为和年份聚合情况下 user_id 的去重数量，反应的是在不同年份进行商品浏览、购买和退货的用户数量。

mysql> SELECT
    ->     behavior,
    ->     occur_year,
    ->     SUM(BITMAP_COUNT(bitmap))
    ->     FROM precompute
    ->     GROUP BY  behavior,occur_year;
+----------+------------+---------------------------+
| behavior | occur_year | sum(bitmap_count(bitmap)) |
+----------+------------+---------------------------+
| browser  | 2022       |                    939995 |
| browser  | 2023       |                   1003173 |
| purchase | 2022       |                    669474 |
| purchase | 2023       |                    660605 |
| returns  | 2023       |                      4910 |
| returns  | 2022       |                      4350 |
+----------+------------+---------------------------+
6 rows in set (0.01 sec)

mysql> select behavior,occur_year,count(distinct user_id) from user_behavior_table group by behavior,occur_year;
+----------+------------+---------------------------+
| behavior | occur_year | sum(bitmap_count(bitmap)) |
+----------+------------+---------------------------+
| browser  | 2022       |                    939995 |
| browser  | 2023       |                   1003173 |
| purchase | 2022       |                    669474 |
| purchase | 2023       |                    660605 |
| returns  | 2023       |                      4910 |
| returns  | 2022       |                      4350 |
+----------+------------+---------------------------+
6 rows in set (3.26 sec)

计算 2022-2023 年进行商品浏览、购买和退货的用户数量。

mysql> SELECT behavior, SUM(cnt) FROM (
    -> SELECT
    -> behavior,
    -> BITMAP_COUNT(BITMAP_OR_AGG(bitmap)) cnt
    -> FROM precompute
    -> GROUP BY behavior,bucket
    -> )
    -> GROUP BY behavior;
+----------+----------+
| behavior | sum(cnt) |
+----------+----------+
| browser  |  1003459 |
| purchase |   780308 |
| returns  |     9260 |
+----------+----------+
3 rows in set (0.01 sec)

mysql> select behavior,count(distinct user_id) from user_behavior_table group by behavior;
+----------+-------------------------+
| behavior | count(distinct user_id) |
+----------+-------------------------+
| browser  |                 1003459 |
| purchase |                  780308 |
| returns  |                    9260 |
+----------+-------------------------+
3 rows in set (1.44 sec)

对比两种查询的返回时间，显然使用 BITMAP 更高效。通过使用 BITMAP，商家能够迅速地筛选出特定类型的事件，从而统计出具有某种行为的用户总数。

参考文档

BITMAP
COUNT