sql - BigQuery SQL：从日志表创建每日当前状态表

问题描述

我有一个记录任何状态变化的日志表。例如：

Updated_at  Customer    Status
01/01/2020     A       Trial
01/01/2020     C       Trial
05/01/2020     B       Trial
06/03/2020     C       Free
15/03/2020     B       Full
05/04/2020     A       Free
07/05/2020     C       Full
10/09/2020     A       Full
10/09/2020     C       Remove

** Updated_at 是时间戳

我需要制作每日状态表，我可以在其中按天查看客户的当前状态：

Date         Customer   Status
01/01/2020      A   Trial
01/01/2020      C   Trial
02/01/2020      A   Trial
02/01/2020      C   Trial
|       
|       
05/01/2020      A   Trial
05/01/2020      B   Trial
05/01/2020      C   Trial
|
|       
06/03/2020      A   Trial
06/03/2020      B   Trial
06/03/2020      C   Free
|       
|       
15/03/2020      A   Trial
15/03/2020      B   Full
15/03/2020      C   Free
|       
|   
10/09/2020     A    Full
10/09/2020     B    Full
10/09/2020     C    Remove

也就是说，如果当天的状态没有变化，则从日志表中的最后一个变化日期开始记录状态。

我的尝试：

首先，我创建一个包含一系列日期的列

接下来，我需要加入一个带日期的日志表

WITH CTE_DATES AS
(
SELECT
  *
FROM
  UNNEST( GENERATE_DATE_ARRAY( CURRENT_DATE(), DATE('2019-05-30'), INTERVAL -1 DAY) )  as Date
)
SELECT  d.date,
lt.Customer,
lt.Status,
extract (date from updated_at) as LT_Date
FROM `logTable` lt join CTE_DATES d on  extract (date from updated_at)<=d.Date

但是通过这种方法，我不仅可以在最后一个日志日期之前获得状态

Date       Customer     Status  Updated_at
12/09/2020      A       Trial   01/01/2020
12/09/2020      A       Free    05/04/2020
12/09/2020      A       Full    10/09/2020

连接表以获取每个日期序列中的最后一条记录的正确方法是什么？

标签： sqlgoogle-bigquery

我强烈建议通过创建每行的日期来解决这个问题。这使得将数据放在您想要的行中变得很简单。它也比一次生成所有日期然后使用连接和其他机制生成数据更有效：

with lt as (
      select lt.*,
             lead(updated_at, 1, current_date) over (partition by customer order by updated_at) as next_updated_at
      from `logTable` lt
     )
select dte, lt.customer, lt.status
from lt cross join
     unnest(generate_date_array(lt.updated_at,
                                date_add(lt.next_updated_at, interval -1 day),
                                interval 1 day)
                               ) dte;

sql - BigQuery SQL：从日志表创建每日当前状态表

问题描述

解决方案

推荐阅读