apache-spark - 用以前的记录填充 NULLS - Netezza SQL

Question

我在 Aginity Workbench 上使用 Netezza SQL 并有以下数据:

id           DATE1              DATE2
1            2013-07-27         NULL
2            NULL               NULL
3            NULL               2013-08-02
4            2013-09-10         2013-09-23
5            2013-12-11         NULL
6            NULL               2013-12-19

我需要用 DATE1 字段中已填充的前面的值填充 DATE1 中的所有 NULL 值。对于 DATE2，我需要执行相同的操作，但顺序相反。所以我想要的输出如下:

id           DATE1              DATE2
1            2013-07-27         2013-08-02
2            2013-07-27         2013-08-02
3            2013-07-27         2013-08-02
4            2013-09-10         2013-09-23
5            2013-12-11         2013-12-19
6            2013-12-11         2013-12-19

我只有读取数据的权限。所以创建表或 View 是不可能的

Answer 1

怎么样this ?

select
  id
  ,last_value(date1 ignore nulls) over (
    order by id
    rows between unbounded preceding and current row
  ) date1
  ,first_value(date2 ignore nulls) over (
    order by id
    rows between current row and unbounded following
  ) date2

您也可以手动计算它，而不是依赖于窗口函数。

with chain as (
  select 
    this.*,
    prev.date1 prev_date1,
    case when prev.date1 is not null then abs(this.id - prev.id) else null end prev_distance,
    next.date2 next_date2,
    case when next.date2 is not null then abs(this.id - next.id) else null end next_distance
  from 
    Table1 this 
    left outer join Table1 prev on this.id >= prev.id
    left outer join Table1 next on this.id <= next.id
), min_distance as (
  select
    id,
    min(prev_distance) min_prev_distance,
    min(next_distance) min_next_distance
  from
    chain
  group by
    id
)
select
  chain.id,
  chain.prev_date1,
  chain.next_date2
from
  chain
  join min_distance on 
    min_distance.id = chain.id
    and chain.prev_distance = min_distance.min_prev_distance
    and chain.next_distance = min_distance.min_next_distance
order by chain.id

如果您无法通过减法计算 ID 之间的距离，只需将排序方案替换为 row_number()称呼。