BitMap 位图

 复制代码
-- drop table user_tag_bitmap_local2
CREATE TABLE user_tag_bitmap_local2
(
    `tag` String,
    `tag_item` String,
    `p_day` Date,
    `origin_user` UInt64,
    `users` AggregateFunction(groupBitmap, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(p_day)
ORDER BY (tag, tag_item)
SETTINGS index_granularity = 8192;
-- 插入数据
insert into user_tag_bitmap_local2 (tag,tag_item,p_day,origin_user,users) 
select 'sex', 'f', '2022-01-25', 2005, groupBitmapState(toUInt64(2005))
select *,bitmapToArray(users) from user_tag_bitmap_local2
SELECT bitmapToArray(users) FROM user_tag_bitmap_local2 
WHERE tag = 'sex' AND tag_item = 'm'
-- 查找（合并）数据
SELECT bitmapToArray(groupBitmapMergeState((users))) 
FROM user_tag_bitmap_local2 WHERE tag = 'sex' AND tag_item = 'f'
-- 位运算
WITH
(
    SELECT groupBitmapMergeState(users)
    FROM user_tag_bitmap_local2
    WHERE tag_item = 'f'
) AS user_group_1,
(
    SELECT groupBitmapMergeState(users)
    FROM user_tag_bitmap_local2
    WHERE tag_item = 'm'
) AS user_group_2
SELECT bitmapToArray(bitmapAnd(user_group_1, user_group_2))