How to use the ClickHouse maxMap function to compute most recent value for each key, with the value containing both time and value(s).

maxMap-with-values.sql

-- https://fiddle.clickhouse.com/a43809c0-cd97-47e7-ab0b-ca85812a7590 for running example
drop table if exists x;
create temporary table x (id Int64, key String, value String, time Int64);

-- id 1
insert into x values (1, 'k-a', 'val one', 1), (1, 'k-a', 'val two', 2);
insert into x values (1, 'k-b', 'val two', 2), (1, 'k-b', 'val one', 3);
insert into x values(1, 'k-c', 'a', 2), (1, 'k-c', 'b', 30), (1, 'k-c', 'c', 1);

-- id 2
insert into x values (2, 'k-b', 'val three', 4), (2, 'k-b', 'val four', 5);

/*
maxMap takes a map as an argument and aggregates by key, producing an aggregated map of
key values with the max value for each key.

Because the value is a tuple of (time, value), the max is calculated by comparing
the time first, so we get the most recent value for each key.
*/
select id, maxMap(map(key, (time, value))) as values_map from x group by id;
/*
id, values_map
1, {'k-a':(2,'val two'),'k-b':(3,'val one'),'k-c':(30,'b')}
2, {'k-b':(5,'val four')}
*/

-- same as above, but using an AggregatingMergeTree to store and merge the incremental state
create table x_aggregating (
    id Int64,
    values_map_state AggregateFunction(maxMap, Map(String, Tuple(time Int64, value String)))
) engine=AggregatingMergeTree order by id;

insert into x_aggregating select 1, maxMapState(map('k-a', (1, 'val-1')::Tuple(time Int64, value String)));
insert into x_aggregating select 1, maxMapState(map('k-a', (2, 'val-2')::Tuple(time Int64, value String)));
insert into x_aggregating select 1, maxMapState(map('k-b', (100, 'val-1')::Tuple(time Int64, value String)));
insert into x_aggregating select 1, maxMapState(map('k-b', (1, 'other')::Tuple(time Int64, value String)));

select id, maxMapMerge(values_map_state) as values_map from x_aggregating group by id;
/*
id, values_map
1, {'k-a':(2,'val-2'),'k-b':(100,'val-1')}
*/