Clickhouse随笔SQL-BitMap函数实践

show create table user_info_merge_tree_dt;

CREATE TABLE default.user_info_merge_tree_dt
(
    `id`   Int64,
    `name` String,
    `dt`   Date
)
    ENGINE = ReplacingMergeTree(id)
        PARTITION BY dt
        PRIMARY KEY id
        ORDER BY id
        SETTINGS index_granularity = 8192;

select *
from default.user_info_merge_tree_dt;


insert into user_info_merge_tree_dt
values (1, '1', '2022-12-12');


optimize table user_info_merge_tree_dt final;



create table tag_audience
(
    tag_name varchar(32),
    ids AggregateFunction(groupBitmap, UInt32) comment 'bitmap存储'
) engine ReplacingMergeTree()
      order by tag_name
      primary key tag_name;


select tag_name, bitmapToArray(ids) as ids
from tag_audience;


insert into tag_audience
values ('a1', bitmapBuild(cast(array(1, 2, 3, 4, 5) as Array(UInt32))));



select tag_name, bitmapToArray(ids)
from tag_audience
where tag_name = 'a1';



insert into tag_audience
select tag_name, bitmapOr(ids, bitmapBuild(cast(array(6, 7, 8) as Array(UInt32))))
from tag_audience;

 

 

-- https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/groupbitmapor
-- if add suffix -State on the function, then return bitmap object.  eg: groupBitmapOr -> groupBitmapOrState
-- bitmap聚合之后计算基数
select tag_name, groupBitmapOr(ids) as cnt
from tag_audience
group by tag_name;

--  bitmap聚合之后返回新的bitmap
select tag_name, arraySort(bitmapToArray(groupBitmapOrState(ids)))
from tag_audience
group by tag_name;

 

create table order_amount_sum
(
    province_id UInt64,
    amount      UInt64,
    dt          datetime
) engine = SummingMergeTree()
      PARTITION BY toDate(dt)
      PRIMARY KEY province_id
      ORDER BY province_id;

-- 2022-12-16 15:39:04

select *
from order_amount_sum;



optimize table order_amount_sum  final ;

select now();

insert into order_amount_sum
values (2, 2, now())