实时精准营销（人群圈选）

该方案将每个用户的全部标签以整型数组（int[]）存储在用户表中，借助PostgreSQL的GIN索引加速数组的包含查询，实现标签的交、并、差圈选。该方案无需额外插件，原生SQL即可完成，适合快速验证与中小规模场景。

表结构设计如下：

• 创建用户标签表

  CREATE TABLE t_user (
      uid   int PRIMARY KEY,       -- 用户ID
      uname text,                   -- 用户名（演示用）
      tag   int[] NOT NULL          -- 标签数组，每个元素为一个标签ID
  );

• 写入用户数据（示例）

  INSERT INTO t_user VALUES
  (1, 'user_001', ARRAY[1,2,3,10,20]),
  (2, 'user_002', ARRAY[2,3,5,10]),
  (3, 'user_003', ARRAY[1,3,10,30]),
  (4, 'user_004', ARRAY[2,4,10,20]),
  (5, 'user_005', ARRAY[1,5,30]);

• 创建GIN索引以加速数组查询

  CREATE INDEX idx_user_tag ON t_user USING GIN (tag);

• 圈选查询示例：
  • 交集圈选（同时包含标签1、3、10的用户，即AND关系）

    SELECT uid, uname FROM t_user WHERE tag @> ARRAY[1,3,10];

  • 并集圈选（包含标签1、2、3中任意一个的用户，即OR关系）

    SELECT uid, uname FROM t_user WHERE tag && ARRAY[1,2,3];

  • 差集圈选（包含标签1和3，但不包含标签5的用户）

    SELECT uid, uname FROM t_user
    WHERE tag @> ARRAY[1,3]
      AND NOT tag @> ARRAY[5];

  • 标签实时更新（追加与删除标签）

    为用户1追加标签99。

    UPDATE t_user SET tag = tag || 99 WHERE uid = 1;

    删除用户1的标签20。

    UPDATE t_user SET tag = array_remove(tag, 20) WHERE uid = 1;

  @>运算符表示“包含”，即左侧数组包含右侧数组的所有元素。&&运算符表示“相交”，即两侧数组有共同元素。GIN索引会自动加速这两个运算符。方案一在单用户标签数不超过数百、用户量百万级时性能良好；当用户量达到千万级以上，且需要多标签组合圈选时，数组扫描与GIN索引的位图扫描成本会显著上升。

该方案借鉴搜索引擎的倒排索引思想，将“用户-标签”关系拆分为独立的倒排表：每行存储一个(tag, uid)对。这样每个标签对应的所有用户可以通过主键索引范围扫描快速获取，再通过GROUP BY与HAVING实现多标签的交、并、差运算。

表结构设计如下：

• 倒排表：每个(标签, 用户)一行

  CREATE TABLE t_user_tag (
      tag int NOT NULL,
      uid int NOT NULL,
      PRIMARY KEY (tag, uid)
  );

• 写入倒排数据（示例）

  INSERT INTO t_user_tag VALUES
  (1,1),(2,1),(3,1),(10,1),(20,1),
  (2,2),(3,2),(5,2),(10,2),
  (1,3),(3,3),(10,3),(30,3),
  (2,4),(4,4),(10,4),(20,4),
  (1,5),(5,5),(30,5);

  主键(tag, uid)自带B-tree复合索引，无需额外创建索引。

• 圈选查询示例：
  • 交集圈选（同时拥有标签1、3、10的用户）

    SELECT uid
    FROM t_user_tag
    WHERE tag IN (1,3,10)
    GROUP BY uid
    HAVING count(*) = 3;   -- 标签个数

  • 并集圈选（拥有标签1、3、10中任意一个的用户）

    SELECT uid
    FROM t_user_tag
    WHERE tag IN (1,3,10)
    GROUP BY uid;

  • 差集圈选（拥有标签1和3，但不拥有标签5的用户）

    SELECT uid
    FROM t_user_tag
    WHERE tag IN (1,3)
    GROUP BY uid
    HAVING count(*) = 2
    EXCEPT
    SELECT uid FROM t_user_tag WHERE tag = 5;

  • 标签实时更新

    为用户6添加标签10。

    INSERT INTO t_user_tag VALUES (10,6) ON CONFLICT DO NOTHING;

    删除用户1的标签20。

    DELETE FROM t_user_tag WHERE uid=1 AND tag=20;

  方案二将标签关系拆分为行级存储，写入与删除均为轻量的单行操作，更新成本极低，适合标签频繁变动的场景。但在亿级用户、千万级标签时，单个热门标签可能对应数千万行，多标签组合的GROUP BY聚合仍需扫描大量数据，查询延迟会随数据量线性增长。

该方案使用PostgreSQL的roaringbitmap扩展，将每个标签对应的用户集合以压缩位图（Roaring Bitmap）存储。Roaring Bitmap将32位整数空间分桶，对每个桶根据数据密度自适应选择密集数组或位图压缩，兼顾存储效率与运算速度。圈选时通过位图的AND（交集）、OR（并集）、ANDNOT（差集）运算在毫秒级完成亿级用户的筛选。

roaringbitmap插件支持的核心特性：

• 取值范围：40亿（int4，0到2的31次方减1），覆盖绝大多数用户ID空间。
• 压缩存储：相比普通位图大幅节省空间，1亿用户位图仅约数十MB。
• 位图运算：支持AND、OR、XOR、ANDNOT等集合运算及聚合函数。
• 转换函数：rb_build构造位图，rb_to_array输出数组，rb_cardinality计算基数。

该方案需要先在RDS控制台安装roaringbitmap插件（要求版本为11及以上）。

表结构设计如下：

• 标签位图表：每个(标签, 偏移量桶)一行，存储该桶内的用户位图

  CREATE TABLE t_tag_users (
      tag        int NOT NULL,            -- 标签ID
      uid_offset int NOT NULL,            -- 用户ID分桶偏移量（每桶2的31次方）
      userbits   roaringbitmap NOT NULL,  -- 该桶内拥有此标签的用户位图
      PRIMARY KEY (tag, uid_offset)
  );

  当用户ID超过int4范围（约42亿）时，通过uid_offset分桶存储；绝大多数场景uid_offset=0即可。

• 写入位图数据（示例）

  -- 标签1：用户1、3、5拥有
  INSERT INTO t_tag_users VALUES (1, 0, rb_build(ARRAY[1,3,5]));
  -- 标签3：用户1、2、3拥有
  INSERT INTO t_tag_users VALUES (3, 0, rb_build(ARRAY[1,2,3]));
  -- 标签10：用户1、2、3、4拥有
  INSERT INTO t_tag_users VALUES (10, 0, rb_build(ARRAY[1,2,3,4]));

• 圈选查询示例
  • 交集圈选（同时拥有标签1、3、10的用户）

    SELECT uid_offset, rb_and_agg(userbits) AS ub
    FROM t_tag_users
    WHERE tag IN (1,3,10)
    GROUP BY uid_offset;
     
    -- 将位图转为用户ID数组查看结果
    SELECT uid_offset, rb_to_array(rb_and_agg(userbits)) AS uids
    FROM t_tag_users
    WHERE tag IN (1,3,10)
    GROUP BY uid_offset;

  • 并集圈选（拥有标签1、3、10中任意一个的用户）

    SELECT uid_offset, rb_or_agg(userbits) AS ub
    FROM t_tag_users
    WHERE tag IN (1,3,10)
    GROUP BY uid_offset;

  • 差集圈选（拥有标签1和3，但不拥有标签10的用户）

    WITH tag_1_3 AS (
        SELECT uid_offset, rb_and_agg(userbits) AS ub
        FROM t_tag_users
        WHERE tag IN (1,3)
        GROUP BY uid_offset
    ),
    tag_10 AS (
        SELECT uid_offset, rb_or_agg(userbits) AS ub
        FROM t_tag_users
        WHERE tag = 10
        GROUP BY uid_offset
    )
    SELECT a.uid_offset, rb_andnot(a.ub, b.ub) AS result
    FROM tag_1_3 a JOIN tag_10 b USING (uid_offset);

  • 统计圈选人数（不展开用户ID，仅返回数量）

    SELECT uid_offset, rb_cardinality(rb_or_agg(userbits)) AS user_cnt
    FROM t_tag_users
    WHERE tag IN (1,3,10)
    GROUP BY uid_offset;

  • 标签实时更新（追加与删除单个用户的标签，参数值userbits请填写实际取值）

    -- 为用户6添加标签10（位图追加）
    UPDATE t_tag_users
    SET userbits = rb_add(userbits, rb_build(ARRAY[6]))
    WHERE tag = 10 AND uid_offset = 0;
     
    -- 删除用户1的标签10
    UPDATE t_tag_users
    SET userbits = rb_remove(userbits, rb_build(ARRAY[1]))
    WHERE tag = 10 AND uid_offset = 0;

  方案三通过位图压缩与位运算，将亿级用户的集合运算压缩到毫秒级，是三种方案中性能最高、最适合超大规模场景的方案。