gistfile1.txt

drop database uniq_test;
create database uniq_test;
USE uniq_test;

create table events(ts DateTime, uid UInt64, site_id UInt32, browser LowCardinality(String))
Engine=MergeTree partition by toYYYYMM(ts) order by (site_id, toStartOfHour(ts));

CREATE MATERIALIZED VIEW uniq_aggr
ENGINE = AggregatingMergeTree
partition by toYYYYMM(day)  order by (site_id, browser, day)
settings min_rows_for_wide_part = 0
as select
        toDate(ts) day,
        site_id,
        browser,
        uniqExactState(uid) uniq_exact,
        uniqState(uid) uniq,
        uniqHLL12State(uid) hll12,
        uniqCombinedState(15)(uid) uniqCombined15
from events group by site_id, browser, day;

CREATE MATERIALIZED VIEW uniq_aggr_128
ENGINE = AggregatingMergeTree
partition by toYYYYMM(day)  order by (site_id, browser, day)
settings min_rows_for_wide_part = 0, index_granularity = 128
as select
        toDate(ts) day,
        site_id,
        browser,
        uniqExactState(uid) uniq_exact,
        uniqState(uid) uniq,
        uniqHLL12State(uid) hll12,
        uniqCombinedState(15)(uid) uniqCombined15
from events group by site_id, browser, day;

set min_insert_block_size_rows_for_materialized_views=100, max_insert_block_size=100000;

insert into events
   select toDateTime('2021-01-01 00:00:00') + number/100,
          cityHash64(number),
	  toInt64(log2(number+1)*100+number%100),
	  ['NA', 'ie', 'firefox', 'safary','chrome'][toInt64(log10(number+1))%5+1]
from numbers(1000000000);

insert into events
   select toDateTime('2021-01-02 00:00:00') + number/100,
          cityHash64(number),
	  toInt64(log2(number+1)*100+number%100),
	  ['NA', 'ie', 'firefox', 'safary','chrome'][toInt64(log10(number+1))%5+1]
from numbers(500000000);

OPTIMIZE TABLE uniq_aggr_128 FINAL;
OPTIMIZE TABLE uniq_aggr FINAL;