Hologres如何支持超高基数UV计算(基于roaringbitmap实现)

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

BEGIN;CREATETABLE IF NOT EXISTS public.ods_app(     uid text,     country text,     prov text,     city text,     channel text,     operator text,     brand text,     ip text,     click_time text,yeartext,     month text,     day text,     ymd textNOTNULL);CALL set_table_property('public.ods_app','bitmap_columns','country,prov,city,channel,operator,brand,ip,click_time, year, month, day, ymd');--distribution_key根据需求设置，根据该表的实时查询需求，从什么维度做分片能够取得较好效果即可CALL set_table_property('public.ods_app','distribution_key','uid');--用于做where过滤条件，包含完整年月日时间字段推荐设为clustering_key和event_time_columnCALL set_table_property('public.ods_app','clustering_key','ymd');CALL set_table_property('public.ods_app','event_time_column','ymd');CALL set_table_property('public.ods_app','orientation','column');COMMIT;

BEGIN;CREATETABLE public.uid_mapping(     uid textNOTNULL,     uid_int32 serial,            PRIMARY KEY (uid));--将uid设为clustering_key和distribution_key便于快速查找其对应的int32值CALL set_table_property('public.uid_mapping','clustering_key','uid');CALL set_table_property('public.uid_mapping','distribution_key','uid');CALL set_table_property('public.uid_mapping','orientation','row');COMMIT;

begin;createtable dws_app(    country text,  prov text,  city text,  ymd textNOTNULL,--日期字段  uid32_bitmap roaringbitmap,-- UV计算  pv integer,-- PV计算  primary key(country, prov, city, ymd)--查询维度和时间作为主键，防止重复插入数据);CALL set_table_property('public.dws_app','orientation','column');--clustering_key和event_time_column设为日期字段，便于过滤CALL set_table_property('public.dws_app','clustering_key','ymd');CALL set_table_property('public.dws_app','event_time_column','ymd');--distribution_key设为group by字段CALL set_table_property('public.dws_app','distribution_key','country,prov,city');end;

WITH
-- 其中ymd = '20210329'表示上一天的数据    user_ids AS(SELECT uid FROM ods_app WHERE ymd ='20210329'GROUPBY uid ),new_ids AS(SELECT user_ids.uidFROM user_ids LEFT JOIN uid_mapping ON(user_ids.uid= uid_mapping.uid)WHERE uid_mapping.uidISNULL)INSERTINTO uid_mapping SELECT  new_ids.uidFROM    new_ids
;

WITH
    aggregation_src AS(SELECT country, prov, city, uid_int32 FROM ods_app INNER JOIN uid_mapping ON ods_app.uid= uid_mapping.uidWHERE ods_app.ymd='20210329')INSERTINTO dws_app SELECT  country
,prov
,city
,'20210329',RB_BUILD_AGG(uid_int32),COUNT(1)FROM    aggregation_src
GROUPBY country
,prov
,city
;

--运行下面RB_AGG运算查询，可先关闭三阶段聚合开关性能更佳（默认关闭）set hg_experimental_enable_force_three_stage_agg=off    
--可以查询基础维度任意组合，任意时间段的uv pvSELECT  country
,prov
,city
,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))AS uv
,sum(1)AS pv
FROM    dws_app
WHERE   ymd ='20210329'GROUPBY country
,prov
,city;--查一个月         SELECT  country
,prov
,RB_CARDINALITY(RB_OR_AGG(uid32_bitmap))AS uv
,sum(1)AS pv
FROM    dws_app
WHERE   ymd >='20210301'and ymd <='20210331'GROUPBY country
,prov;该查询等价于
SELECT  country
,prov
,city
,COUNT(DISTINCT uid)AS uv
,COUNT(1)AS pv
FROM    ods_app
WHERE   ymd ='20210329'GROUPBY country
,prov
,city;SELECT  country
,prov
,COUNT(DISTINCT uid)AS uv
,COUNT(1)AS pv
FROM    ods_app
WHERE   ymd >='20210301'and ymd <='20210331'GROUPBY country
,prov;