Save last non null values
unknown
sql
2 years ago
893 B
29
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...