在 BigQuery 中创建包含历史记录表的effective_from_date和effective_to

问：

我有一个表，其中为更改附加了数据。没有删除或更新，只有追加由云运行作业完成。

基表

Supplier_ID	Supplier_Name	Supplier_Contact	Last_Modified
123	美国广播公司	03 483 394	2023-05-01 12:34:56
124	ABD公司	02 848 939	2023-05-01 12:34:56
123	美国广播公司	03 483 345	2023-05-02 10:45:05
124	ABD公司	02 848 837	2023-05-02 10:45:05
123	美国广播公司	03 478 102	2023-05-08 11:09:15

我想创建另一个如下所示的表。

Supplier_ID	Supplier_Name	Supplier_Contact	Effective_From	Effective_To	is_active
123	美国广播公司	03 483 394	2023-05-01 12:34:56	2023-05-02 10:45:05	N
123	美国广播公司	03 483 345	2023-05-02 10:45:05	2023-05-08 11:09:15	N
123	美国广播公司	03 478 102	2023-05-08 11:09:15	9999-12-21 00:00:00	Y
124	ABD公司	02 848 939	2023-05-01 12:34:56	2023-05-02 10:45:05	N
124	ABD公司	02 848 837	2023-05-02 10:45:05	9999-12-21 00:00:00	Y

实现目标的最佳方式是什么？我试图找到一种使用 dbt 执行此操作的方法，但找不到任何方法。

sql google-bigquery sql-update dbt scd

    with raw as (
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 483 394' as Supplier_Contact, cast('2023-05-01 12:34:56' as datetime) as Last_Modified
    union all 
    select 
    '124' as Supplier_ID, 'ABD' as Supplier_Name, '02 848 939' as Supplier_Contact, cast('2023-05-01 12:34:56' as datetime) as Last_Modified
    union all
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 483 345' as Supplier_Contact, cast('2023-05-02 10:45:05' as datetime) as Last_Modified
    union all
    select 
    '124' as Supplier_ID, 'ABD' as Supplier_Name, '02 848 837' as Supplier_Contact, cast('2023-05-02 10:45:05' as datetime) as Last_Modified
    union all
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 478 102' as Supplier_Contact, cast('2023-05-08 11:09:15' as datetime) as Last_Modified
    ),
    raw2 as (
    select Supplier_ID,Supplier_Name,Supplier_Contact,Last_Modified as Effective_From,lead(Last_Modified) OVER (partition by raw.Supplier_ID ORDER BY raw.Supplier_ID,raw.Last_Modified asc) as Effective_To   from raw order by raw.Supplier_ID,raw.Last_Modified asc
    )
    select *EXCEPT(Effective_To),ifnull(raw2.Effective_From,'9999-12-21 00:00:00') as Effective_To,
if(raw2.Effective_To is null, 'Y','N') as is_active
 from raw2

在 BigQuery 中创建包含历史记录表的effective_from_date和effective_to_date的表格

Create table with effective_from_date and effective_to_date from history table in BigQuery

评论

评论