Filling NULLS with Previous Entries - Netezza SQL

Question

I am using Netezza SQL on an Agine Workbench and have the following data:

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 

I need to populate all the NULL values ​​in DATE1 with the previous values ​​in the filled DATE1 field. With DATE2, I need to do the same, but in reverse order. So my desired result would be as follows:

 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 

I only have read access to the data. Therefore, creating tables or views is out of the question.

Answer 1

How about 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 

You can also manually calculate this, rather than relying on window functions.

 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 

If you cannot calculate the distance between the identifiers by subtracting, simply replace the ordering scheme with row_number() .

Answer 2

I think Netezza supports the order by clause for max() and min() . So you can do:

 select max(date1) over (order by date1) as date1, min(date2) over (order by date2 desc) as date2 . . . 

EDIT:

In Netezza, you can do this with last_value() and first_value() :

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

Netezza does not seem to support IGNORE NULL on LAG() , but it does that.

Answer 3

I only tested this on Oracle, so hopefully it works on Netezza:

Violin: http://www.sqlfiddle.com/#!4/7533f/1/0

 select id, coalesce(date1, t1_date1, t2_date1) as date1, coalesce(date2, t3_date2, t4_date2) as date2 from (select t.*, t1.date1 as t1_date1, t2.date1 as t2_date1, t3.date2 as t3_date2, t4.date2 as t4_date2, row_number() over(partition by t.id order by t.id) as rn from tbl t left join tbl t1 on t1.id < t.id and t1.date1 is not null left join tbl t2 on t2.id > t.id and t2.date1 is not null left join tbl t3 on t3.id < t.id and t3.date2 is not null left join tbl t4 on t4.id > t.id and t4.date2 is not null order by t.id) x where rn = 1 

Answer 4

Here you can specify NULL dates with the most recent minimum / odd dates using self-joins. This query should work in most databases

 select t1.id, max(t2.date1), min(t3.date2) from tbl t1 join tbl t2 on t1.id >= t2.id join tbl t3 on t1.id <= t3.id group by t1.id 

http://www.sqlfiddle.com/#!4/acc997/2