Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select a row of first non-null values in a sparse table

Using the following table:

A | B    | C    | ts
--+------+------+------------------
1 | null | null | 2016-06-15 10:00
4 | null | null | 2016-06-15 11:00 
4 |    9 | null | 2016-06-15 12:00
5 |    1 |    7 | 2016-06-15 13:00

How do I select the first non-null value of each column in a running window of N rows? "First" as defined by the order of timestamps in columns ts. Querying the above table would result in:

A | B | C
--+---+---
1 | 9 | 7
like image 517
Alex B Avatar asked Dec 23 '13 07:12

Alex B


People also ask

How do I find the first non NULL value in SQL?

SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function.

How do I get previous non NULL values in SQL?

With a subquery in the SELECT clause The first method uses a subquery inside the SELECT clause to get the first non-NULL value before the current row. First, we create a subquery that returns the first non-NULL value before the current row. The last non-NULL value carries forward but only after the starting row.

How would you SELECT the first non NULL value from either column B or column A in that order )?

The window function first_value() allows for a rather short and elegant solution: SELECT first_value(a) OVER (ORDER BY a IS NULL, ts) AS a , first_value(b) OVER (ORDER BY b IS NULL, ts) AS b , first_value(c) OVER (ORDER BY c IS NULL, ts) AS c FROM t LIMIT 1; a IS NULL evaluates to TRUE or FALSE .


1 Answers

The window function first_value() allows for a rather short and elegant solution:

SELECT first_value(a) OVER (ORDER BY a IS NULL, ts) AS a
     , first_value(b) OVER (ORDER BY b IS NULL, ts) AS b
     , first_value(c) OVER (ORDER BY c IS NULL, ts) AS c
FROM   t
LIMIT  1;

a IS NULL evaluates to TRUE or FALSE. FALSE sorts before TRUE. This way, non-null values come first. Order by ts (timestamp column like you commented) next and you've got it in a single SELECT.

This would be simpler if Postgres supported IGNORE NULLS. The manual:

The SQL standard defines a RESPECT NULLS or IGNORE NULLS option for lead, lag, first_value, last_value, and nth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namely RESPECT NULLS.

One of the few omissions with regard to standard SQL in this area.

db<>fiddle here
SQL Fiddle.

like image 97
Erwin Brandstetter Avatar answered Oct 01 '22 23:10

Erwin Brandstetter