Save last non null values

 avatar
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...