Fill Null Values with Last Non-Null Amount - Oracle SQL

Fill null values with last non-null amount - Oracle SQL

last_value with IGNORE NULLS works fine in Oracle 10g:

select item, year, month, amount, 
       last_value(amount ignore nulls) 
         over(partition by item 
              order by year, month 
              rows between unbounded preceding and 1 preceding) from tab;

rows between unbounded preceding and 1 preceding sets the window for analytic function.

In this case Oracle is searching for LAST_VALUE inside the group defined in PARTITION BY (the same item) from the begining (UNBOUNDED PRECEDING) until current row - 1 (1 PRECEDING)

It's a common replacement for LEAD/LAG with IGNORE NULLS in Oracle 10g

However, if you're using Oracle 11g you can use LAG from the Gordon Linoff's answer (there is a small typo with "ignore nulls")

SQL - How to fill NULL values with NOT NULL value of previous matched record with LEFT Join

"Previous" has no meaning in SQL queries unless there is a column specifying the ordering. With such a column, you can do:

SELECT A.PERSON, B.SPORT,
       COALESCE(LAG(C.PLAYER IGNORE NULLS) OVER (PARTITION BY A.PERSON ORDER BY <ordering col>) as Player
FROM TABLE1 A LEFT JOIN 
     TABLE2 B
     ON A.ID = B.ID LEFT JOIN
     TABLE3 C
     ON B.SPORT = C.SPORT

If you just want NULL values to be filled in with any non-NULL value, use MAX():

SELECT A.PERSON, B.SPORT,
       COALESCE(MAX(C.PLAYER IGNORE NULLS) OVER (PARTITION BY A.PERSON) as Player
FROM TABLE1 A LEFT JOIN 
     TABLE2 B
     ON A.ID = B.ID LEFT JOIN
     TABLE3 C
     ON B.SPORT = C.SPORT

Filling NULL values with preceding Non-NULL values using FIRST_VALUE

You could use first_value (but last_value would also work too in this scenario). The import part is to specify rows between unbounded preceding and current row to set the boundaries of the window.

Answer updated to reflect updated question, and preference for first_value

select
first_value(t1.UniqueId ignore nulls) over (partition by t2.UniqueId
                                           order by t2.time_hit
                                           rows between unbounded preceding and current row) as UniqueId,
first_value(t1.items ignore nulls) over (partition by t2.UniqueId
                                        order by t2.time_hit
                                        rows between unbounded preceding and current row) as Items,
first_value(t1.start_time ignore nulls) over (partition by t2.UniqueId
                                        order by t2.time_hit
                                        rows between unbounded preceding and current row) as start_time,
t2.time_hit,
t2.item_value
from table2 t2
left join table1 t1 on t1.start_time = t2.time_hit
order by t2.time_hit;

Result

| UNIQUEID | ITEMS | START_TIME | TIME_HIT | ITEM_VALUE |
|----------|-------|------------|----------|------------|
|      123 |   one |   10:00:00 | 10:00:00 |          x |
|      123 |   one |   10:00:00 | 10:05:00 |          x |
|      123 |   one |   10:00:00 | 10:10:00 |          x |
|      123 |   one |   10:00:00 | 10:30:00 |          x |
|      456 |   two |   11:00:00 | 11:00:00 |          x |
|      456 |   two |   11:00:00 | 11:15:00 |          x |
|      789 | three |   11:30:00 | 11:30:00 |          x |

SQL Fiddle Example

Note: I had to use Oracle in SQL Fiddle (so I had to change the data types and a column name). But it should work for your database.

Easy way to fill in last non-null value partition by user? Redshift SQL

As a disclaimer, this answer is largely based on the excellent accepted answer to this SO question. This answer creates a pseudo-group for each block of records which should ultimately be assigned the same purchase amount. It then uses the FIRST_VALUE() function, which should be available on Redshift, to fill in the null gaps.

WITH cte AS (
    SELECT *, SUM(CASE WHEN purchase_amount IS NULL THEN 0 ELSE 1 END)
                  OVER (PARTITION BY user_id ORDER BY date) AS grp
    FROM yourTable
)

SELECT date, user_id,
       FIRST_VALUE(purchase_amount) OVER (PARTITION BY grp, user_id
                                          ORDER BY date) AS purchase_amount
FROM cte
ORDER BY user_id, date;

Demo

Note that the demo is for Postgres, but the code should also run on Redshift with no modification necessary.

Oracle SQL - replace null with previous known value

Try last_value analytic function:

SELECT "PROCESSO", "DATAM", "HOURM",
       last_value( "TEMP_T1" ignore nulls )
       OVER (order by "DATAM"
             rows between unbounded preceding and current row
       ) as new_temp
FROM table1

Demo ==> http://sqlfiddle.com/#!4/48207/2

=========== EDIT ===================
If you want to update the table, and there is no primary key (unique identifiers), you can try a solution based on rowid pseudocolumn

==>http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm) :

MERGE INTO table1 t1
USING (
    SELECT rowid rd, "PROCESSO", "DATAM", "HOURM",
           last_value( "TEMP_T1" ignore nulls )
           OVER (order by "DATAM"
                 rows between unbounded preceding and current row
           ) as new_temp
    FROM table1
) x
ON (t1.rowid = x.rd)
WHEN MATCHED THEN UPDATE SET t1."TEMP_T1" = x.new_temp
;

Demo ==> http://sqlfiddle.com/#!4/5a9a61/1

However you must ensure that there is no another process that deletes and inserts rows from/into this table while running the update, because when a row is deleted from the table, Oracle can assigng it's rowid to another, new row.

Oracle SQL, fill missing value with the closest non-missing

Your first version should work, with a slight tweak:

select A.*, 
       coalesce(V1, lag(V1 ignore nulls)  over (order by data)) V2
from Tab1 A;

The tweak is to remove the partition by v1 from the lag(). The coalesce() is just my preference for simpler expressions.

The same tweak should work for the second version as well.

Your version doesn't work because the lag() value must come from the same partition (or be null). When you have partition by v1, you are actually ensuring that v1 has the same value as in the current row.

replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

You can try the following:

* Updated **

-- Test Data
DECLARE @YourTable TABLE(Product INT, Timestamp DATETIME, Price NUMERIC(16,4))

INSERT INTO @YourTable
SELECT 5678, '20080101 12:00:00', 12.34
UNION ALL
SELECT 5678, '20080101 12:01:00', NULL
UNION ALL
SELECT 5678, '20080101 12:02:00', NULL
UNION ALL
SELECT 5678, '20080101 12:03:00', 23.45
UNION ALL
SELECT 5678, '20080101 12:04:00', NULL

;WITH CTE AS
(
    SELECT *
    FROM @YourTable
)

-- Query
SELECT A.Product, A.Timestamp, ISNULL(A.Price,B.Price) Price
FROM CTE A
OUTER APPLY (   SELECT TOP 1 *
                FROM CTE 
                WHERE Product = A.Product AND Timestamp < A.Timestamp
                AND Price IS NOT NULL
                ORDER BY Product, Timestamp DESC) B

--Results
Product Timestamp   Price
5678    2008-01-01 12:00:00.000 12.3400
5678    2008-01-01 12:01:00.000 12.3400
5678    2008-01-01 12:02:00.000 12.3400
5678    2008-01-01 12:03:00.000 23.4500
5678    2008-01-01 12:04:00.000 23.4500

Fill in values with the most recent non null with Snowflake SQL

You seem to want lag(. . . ignore nulls). Just one thing: SQL tables represent unordered sets (technically multisets). You need a column to specify the ordering.

So:

select t.*,
       coalesce(lag(category ignore nulls) over (partition by id order by <ordering col>) as imputed_category
from t;

Actually, it turns out that last_value() does this without needing the coalesce():

select t.*,
       last_value(category ignore nulls) over (partition by id order by <ordering col>) as imputed_category
from t;

---

Related Topics