在 BigQuery 中,是否可以创建一个物化视图,其中包含基表中每个组的最新行。
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 |
+----------+--------------+-------+
我希望物化视图如下所示
Materialized view
+----------+--------------+-------+
| group_id | timestamp | value |
+----------+--------------+-------+
| 1 | '2020-01-02' | 0.2 |
| 2 | '2020-01-02' | 0.1 |
+----------+--------------+-------+
BigQuery 物化视图不支持分析函数或联接。
还有其他方法可以创建这样的视图吗?