CODEWITHSUNDEEP
sqlnetezza
CodyLane
CodyLane

Reputation: 31

Netezza SQL: Fill missing values with most recent datum

I need to select data from an arbitrarily dated (but sorted) series, such that values are filled into a daily dated table, where missing values are filled by the source value for the most recent date. Here is an example:

Table X:

2013-01-06 foo
2013-01-08 bar

I would like a query on table X such that the result is of the form:

2013-01-06 foo
2013-01-07 foo
2013-01-08 bar
2013-01-09 bar

HOWEVER: I need to do this without using a correlated subquery (it is for use on an IBM Netezza box). I could go to the trouble of writing a user function... But before doing that I wanted to know if this can be achieved in vanilla SQL using only joins.

I have been unable to find a way, but have the feeling that it might be possible with clever use of joining.

Many thanks for your time.

Upvotes: 2

Views: 1244

Answers (1)

Niederee
Niederee

Reputation: 4295

You can solve this many different ways. If you are avoiding a stored proc. I would start by creating a temp table with the number of rows you want.

create temp table temp_seq as
select hw_hwid as any_list
from _v_disk
distribute on random;

Then I would use it to generate your list of dates cross join that to a list of foo and bar to get the expected table. After all that just left join to table X

SELECT sub_list.seq_dt,
     sub_list.foo_bar,
     nvl2(table_x.foo_bar,1,0) AS present_in_tblx
FROM (
SELECT seq.seq_dt,
     test_data.foo_bar
FROM (
SELECT row_number() over(
ORDER BY rowid) AS temp_seq
,
    to_date('2013-01-01','YYYY-MM-DD') AS start_dt
,
    start_dt+temp_seq-1 AS seq_dt
FROM temp_seq) seq cross join
(SELECT 'foo' AS foo_bar
UNION ALL
SELECT 'bar') test_data) sub_list left outer join

(SELECT date('2013-01-06') AS xdate,
      'foo' AS foo_bar
UNION ALL
SELECT date('2013-01-08'), 'bar') table_x

ON sub_list.seq_dt=table_x.xdate and sub_list.foo_bar=table_x.foo_bar
order by sub_list.seq_dt

Upvotes: 1

Related Questions