Save last non null values
unknown
sql
a year ago
893 B
23
Indexable
--Save last non null values for b and c columns DROP TABLE IF EXISTS test_table; CREATE TABLE IF NOT EXISTS test_table ( id Int64, b Nullable(Int64), c Nullable(Int64), timestamp DateTime64 ) ENGINE ReplacingMergeTree ORDER BY id; DROP TABLE IF EXISTS test_table_mv; CREATE MATERIALIZED VIEW test_table_mv ENGINE = AggregatingMergeTree ORDER BY id AS SELECT tt.id AS id, argMaxState(tt.b, tt.timestamp) AS b, argMaxState(tt.c, tt.timestamp) AS c FROM test_table tt GROUP BY tt.id; INSERT INTO test_table (id, b, c, timestamp) VALUES (1, NULL, 2, now64()); INSERT INTO test_table (id, b, c, timestamp) VALUES (1, 2, NULL, now64()); INSERT INTO test_table (id, b, c, timestamp) VALUES (1, NULL, 4, now64()); INSERT INTO test_table (id, b, c, timestamp) VALUES (1, NULL, NULL, now64()); SELECT id, argMaxMerge(b), argMaxMerge(c) FROM test_table_mv GROUP BY id;
Editor is loading...