Last active
June 7, 2024 17:25
-
-
Save maxjustus/c50a217128a55e05c7adf5d4f06c7c3c to your computer and use it in GitHub Desktop.
How to use the ClickHouse maxMap function to compute most recent value for each key, with the value containing both time and value(s).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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')} | |
*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment