SQL 用前一个非 NULL 行填充 NULL 行

Question

所以我有一个 bq 表，它被创建为多个日历日期快照，连接到 trx 数据。请找到下面的查询来填充表格

  SELECT
    GENERATE_DATE_ARRAY(date_add(DATE(CURRENT_TIMESTAMP), interval -20 day), DATE('2020-08-22')) AS date_array
  )
         
 ,dim_date AS (
  SELECT
    sn_date
  FROM
    date_array_table,
    UNNEST(date_array) AS sn_date
    )
  
 ,data_test as (
 select date('2020-08-20') as date, 1 as id, 1000 as num
 UNION ALL
 select date('2020-08-18') as date, 1 as id, 130 as num
 UNION ALL
 select date('2020-08-18') as date, 2 as id, 300 as num
 UNION ALL
 select date('2020-08-13') as date, 2 as id, 250 as num
 )

 ,jjoin as (
 select
 *
 from dim_date
 left join 
 data_test
 on 1=1 and sn_date = date
 )

 select *
 from jjoin
 order by 1 desc

结果如下img

接下来我想用 NULL 值填充快照行，每个 id 按日期填充以前的非 NULL 行。我尝试使用 max 或 first_value 但它仍然是 NULL。示例:

select sn_date
coalesce(num, max (num) over (partition by id order by date)
from jjoin

但它不显示先前的非 Null 行。有什么建议吗？谢谢

预期:

--------------------------
sn_date | date | id | num
--------------------------
08/22   | 08/20| 1  | 1000
08/21   | 08/20| 1  | 1000
08/20   | 08/20| 1  | 1000
08/19   | 08/18| 1  | 130
08/18   | 08/18| 1  | 130
08/18   | 08/18| 2  | 300
08/17   | 08/13| 1  | 250
08/16   | 08/13| 1  | 250
08/15   | 08/13| 1  | 250

Answer 1

您可以使用last_value():

select sn_date, date, id, num,
       last_value(date ignore nulls) over (order by date desc),
       last_value(id ignore nulls) over (order by date desc),
       last_value(num ignore nulls) over (order by date desc)

我应该注意到，SQL 标准在 lag() 以及 first_value() 和 last_value( )。当我考虑解决这个问题时，我考虑的是 lag()。我认为 BigQuery 是唯一支持 ignore null 但不支持 lag() 的数据库。