In BigQuery Is it possible to create a materialized view containing the latest row for each group in a base table.
e.g.
CREATE TABLE basetable (
group_id INT64, timestamp TIMESTAMP, value FLOAT64
);
INSERT INTO basetable (group_id, timestamp, value) VALUES
(1, '2020-01-01', 0.1),
(1, '2020-01-02', 0.2),
(2, '2020-01-02', 0.1),
(2, '2020-01-01', 0.2);
Base table
+----------+--------------+-------+
| group_id | timestamp | value |
+----------+--------------+-------+
| 1 | '2020-01-01' | 0.1 |
| 1 | '2020-01-02' | 0.2 |
| 2 | '2020-01-02' | 0.1 |
| 2 | '2020-01-01 | 0.2 |
+----------+--------------+-------+
I'd like materialized view to look as follows
Materialized view
+----------+--------------+-------+
| group_id | timestamp | value |
+----------+--------------+-------+
| 1 | '2020-01-02' | 0.2 |
| 2 | '2020-01-02' | 0.1 |
+----------+--------------+-------+
BigQuery materialized views do not support analytical functions or joins. Is there any other way to create such a view?
I was trying to solve the same problem and I found that it is now possible to do this with a single query by using the new MAX_BY aggregation. The support for materialized views was added on Aug 8, 2023.
The final query would be:
CREATE MATERIALIZED VIEW test as
SELECT
group_id,
MAX(timestamp) as timestamp,
MAX_BY(value, timestamp) as value,
FROM table
GROUP BY 1