时间戳

首页 > 解决方案 > 如何使用 Bigquery 中的字段历史表重新创建旧快照

database - 如何使用 Bigquery 中的字段历史表重新创建旧快照

问题描述

我目前正在研究一个有趣的问题。我正在尝试重新创建表的状态，就像它在给定的前一个日期一样。我有 2 张桌子

  1. 表 A：由实时数据组成，每小时刷新一次。
  2. 表 A_field_history：包含对表 A 中的字段所做的更改。

下图包含当前状态，其中表 A 具有实时更新的数据，表 A_field_history 仅捕获对表 A 上的字段所做的更改。 表A和表A_field_history的基本结构

我正在尝试在特定给定日期重新创建表 A。下图由 2020 年 6 月 30 日的表格状态组成。要求是能够根据任何给定日期重新创建表 A 的状态。 表 A 截至上一个给定日期

标签： databasegoogle-bigquerydata-cleaningbusiness-intelligence

解决方案

我实际上确定了一种回滚（实际上，而不是在实际表上）在给定特定日期之后进行的所有更新的方法。以下是遵循的步骤：

创建虚拟表：

WITH
Table_A AS
(
SELECT 1 As ID, '2020-6-28' as created_date, 10 as qty, 100 as value
Union ALL
SELECT 2 As ID, '2020-5-29' as created_date, 20 as qty, 200 as value),

Table_A_field_history AS
(
SELECT 'xyz' id,'2020-07-29'    created_date,'12345'    created_by,'qty'    field,'10'  new_value,'200' old_value,'1'   A_id
UNION ALL
SELECT 'abc' id,'2020-07-24'    created_date,'12345'    created_by,'qty'    field,'20'  new_value,'10' old_value,'2'    A_id
UNION ALL
SELECT 'xyz' id,'2020-07-29'    created_date,'12345'    created_by,'value'  field,'100' new_value,'2000' old_value,'1'  A_id
UNION ALL
SELECT 'abc' id,'2020-07-24'    created_date,'12345'    created_by,'value'  field,'200' new_value,'5000' old_value,'2'  A_id
UNION ALL

SELECT 'xyz' id,'2020-06-29'    created_date,'12345'    created_by,'qty'    field,'200' new_value,'' old_value,'1'  A_id
UNION ALL
SELECT 'abc' id,'2020-05-30'    created_date,'12345'    created_by,'qty'    field,'10'  new_value,'' old_value,'2'  A_id
UNION ALL
SELECT 'xyz' id,'2020-06-29'    created_date,'12345'    created_by,'value'  field,'2000'    new_value,'' old_value,'1'  A_id
UNION ALL
SELECT 'abc' id,'2020-05-30'    created_date,'12345'    created_by,'value'  field,'5000'    new_value,'' old_value,'2'  A_id

),

步骤 1. 创建日期 cte 以根据给定日期过滤数据：

`date_spine 
AS

(
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 Day)) AS as_of_date 
),`

步骤 2. 上面创建的日期 cte 可以用作我们查询的 Spine，交叉连接以映射as_of_date历史表中所做的所有更改。

    date_changes
AS
(
SELECT DISTINCT
date.as_of_date,
hist.A_id 
FROM Table_A_field_history hist CROSS JOIN date_spine date
),

第 3 步。现在我们已经as_of_date映射到所有历史交易，现在我们可以获得最大更改日期。

most_recent_changes AS (
  SELECT
    dc.as_of_date,
    dc.A_id ,
    MAX(fh.created_date) AS created_date,
  FROM date_changes dc
  LEFT JOIN Table_A_field_history AS fh
    ON dc.A_id   = fh.A_id  
  WHERE CAST(fh.created_date AS DATE) <= dc.as_of_date
  GROUP BY  dc.as_of_date,
    dc.A_id 
),

步骤 4. 现在将最大更改日期映射到实际created_date和历史表

past_changes AS (
  SELECT
    mr.as_of_date,
    mr.A_id,
    mr.created_date,
    a.id AS entry_id,
    a.created_by AS created_by_id,
    CASE WHEN a.field='qty' THEN a.new_value ELSE '' END AS qty,
    CASE WHEN a.field='value' THEN a.new_value ELSE '' END AS value,
  FROM most_recent_changes AS mr
  LEFT JOIN Table_A_field_history AS a
    ON mr.A_id  = a.A_id 
    AND mr.created_date = a.created_date
  WHERE a.id IS NOT NULL
)

步骤 5. 现在我们可以使用as_of_date来获取表 A 的历史状态。

Select *
From past_changes x
WHERE x.as_of_date = '2020-07-29'

推荐阅读