ck_sql_action
通过位图存储、查询数据
将user_id通过位图存储
最新文档
Bitmap Functions | ClickHouse Docs https://clickhouse.com/docs/en/sql-reference/functions/bitmap-functions#bitmap_functions-bitmapbuild
SELECT bitmapToArray(
subBitmap(
bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500])
, toUInt32(2), toUInt32(10)
)
) AS res;
历史版本方法名
-- rename bitmapSubsetOffsetLimit to subBitmap and modify the rb_offset_… · ClickHouse/ClickHouse@3dcff21 · GitHub https://github.com/ClickHouse/ClickHouse/commit/3dcff2124cef5e9af8a0b13a494bdef8b2ad4d7e
-- Merge pull request #7525 from godfreyd/en-docs/CLICKHOUSEDOCS-426-bit… · ClickHouse/ClickHouse@1c9b444 · GitHub https://github.com/ClickHouse/ClickHouse/commit/1c9b444e78d440788432cb2b2790d69b45ff5108
- `range_start` – The subset starting point. Type: [UInt32](../../data_types/int_uint.md).
- `cardinality_limit` – The subset cardinality upper limit. Type: [UInt32](../../data_types/int_uint.md).
SELECT bitmapToArray(
bitmapSubsetLimit(
bitmapBuild([0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,100,200,500])
, toUInt32(2), toUInt32(10)
)
) AS res;
SELECT *
FROM system.build_options
SELECT *
FROM system.build_options
SELECT version();
DROP TABLE IF EXISTS test_db.tmp_bitmap_table;
-- bm 存储 bitMap，类型为UInt64
CREATE TABLE test_db.tmp_bitmap_table
(
id UInt32,
bm AggregateFunction(groupBitmap,Int64)
)
ENGINE = Memory();
-- 插入user_id，位图存储
insert into test_db.tmp_bitmap_table
select 2,groupBitmapState(id) from test_db.app_user where app_id=123
select count(*) from test_db.app_user where app_id=123
select groupBitmap(id) from test_db.app_user where app_id=123
;
select * from test_db.tmp_bitmap_table;
分页查询
select 1,
bitmapToArray(
bitmapSubsetLimit(
bm, toUInt32(0), toUInt32(10)
)
)
from test_db.tmp_bitmap_table
union all
select 2,
bitmapToArray(
bitmapSubsetLimit(
bm, toUInt32(789), toUInt32(9)
)
)
from test_db.tmp_bitmap_table;
返回的是user_id的列表
但发现上边的查询返回的第一个元素都为789，偏移量不生效？
select min(id) from test_db.app_user where app_id=123
发现最小值为789
查看clickhouse 版本号 构建信息
SELECT *
FROM system.build_options
SELECT version();
How to check your ClickHouse version https://www.propeldata.com/blog/how-to-check-your-clickhouse-version
