Bigquery - 时间表

Bigquery支持时间表，使得用户能够查询历史数据，而且无需编写复杂查询语句。下面是使用时间表的示例代码：

创建时间表：

CREATE TABLE mytable_with_history ( id INT64 NOT NULL, value STRING ) AS SELECT 1, 'hello' UNION ALL SELECT 2, 'world' UNION ALL SELECT 3, 'foo' UNION ALL SELECT 4, 'bar'

INSERT INTO mytable_with_history(id, value) SELECT 1, 'goodbye'

创建时间表所需的必要元素：

先定义一个具有有效日期范围的表，并包含从开始日期到结束日期的所有日期，可以使用以下语句创建：

CREATE TABLE mytable_with_date_range( start_date DATE, end_date DATE, ) AS SELECT DATE_SUB('2022-04-01', INTERVAL 365 DAY), DATE_SUB('2022-04-01', INTERVAL 1 DAY)

使用以下示例语句创建mytable_with_history_temporal表：

CREATE TABLE mytable_with_history_temporal ( id INT64 NOT NULL, value STRING, valid_from TIMESTAMP NOT NULL OPTIONS ( description="Start time of current row data" ), valid_to TIMESTAMP OPTIONS ( description="End time of current row data" ), ) PARTITION BY RANGE_BUCKET(valid_from, GENERATE_ARRAY(DATE '2022-02-02', DATE '2022-04-01', INTERVAL 1 DAY)) OPTIONS( description="The temporal table representing history of mytable_with_history", )

将数据加载进新的表中：

INSERT INTO mytable_with_history_temporal(id, value, valid_from, valid_to) WITH history_with_new_values AS ( SELECT id, value, created_at, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at DESC) AS newest FROM mytable_with_history UNION ALL SELECT id, value, created_at, 1 FROM mytable_with_history WHERE id NOT IN (