Skip to content

Instantly share code, notes, and snippets.

@den-crane
Created October 15, 2018 15:06
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save den-crane/f7382cd4f1f859ff6ac46afe7dc9925a to your computer and use it in GitHub Desktop.
Save den-crane/f7382cd4f1f859ff6ac46afe7dc9925a to your computer and use it in GitHub Desktop.
Populate AggregatingMergeTree through null table
create table z(a date, b Int64) Engine=MergeTree Partition by toYYYYMM(a) order by a;
insert into z select today(), number from numbers(1000000000);
insert into z select yesterday(), number from numbers(1000);
create table mv_z_store(a date, max_b AggregateFunction(MAX,Int64)) ENGINE = AggregatingMergeTree Partition by toYYYYMM(a) order by a;
create table temp(a date, b Int64) Engine=Null;
create MATERIALIZED VIEW mv_z to mv_z_store AS SELECT a, maxState(b) AS max_b FROM temp GROUP BY a;
insert into temp select * from z;
drop table mv_z;
drop table temp;
create MATERIALIZED VIEW mv_z to mv_z_store AS SELECT a, maxState(b) AS max_b FROM z GROUP BY a;
insert into z select yesterday()-1, number from numbers(100);
select a, maxMerge(max_b) from mv_z group by a
a maxMerge(max_b)
2018-08-07 99
2018-08-08 999
2018-08-09 999999999
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment