summingmergetree-arrays-nested

summingmergetree-arrays-nested

truncate table logs;
truncate table agg_table;


CREATE TABLE logs(
    date Date,
    ts DateTime,
    groupId Int8,
    subGroupId Int8) ENGINE = MergeTree() PARTITION BY date ORDER BY (groupId);

CREATE TABLE agg_table
(
    date Date,
    groupId Int8,
    count UInt64,
    subGroupMap Nested(
        subGroupId Int8,
        count UInt64
    )
)
ENGINE = SummingMergeTree() 
PARTITION BY date
ORDER BY (groupId);



CREATE MATERIALIZED VIEW log_to_summing_table TO agg_table
AS SELECT date, groupId, CAST(1 as UInt64) as count, [subGroupId] as "subGroupMap.subGroupId", [CAST(1 as UInt64)] as "subGroupMap.count"
FROM logs ;

insert into logs values (today(), now(), 2,3)

select date, groupId, sum(count),sumMap(subGroupMap.subGroupId, subGroupMap.count) from agg_table group by date, groupId

----------
drop table agg_table
CREATE TABLE agg_table
(    groupId Int8,
    subGroupMap Nested(
        subGroupId Int8,
        count UInt64
    ))ENGINE = SummingMergeTree() 
ORDER BY (groupId);


insert into agg_table values(1, [1],[10]);
insert into agg_table values(1, [1,2,3],[10,20,20]);

select  sumMap(subGroupMap.subGroupId, subGroupMap.count) from agg_table

optimize table agg_table final

select * from agg_table