Select row of first nonzero values ​​in sparse table

Question

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 to choose the first nonzero value of each column in the working window from N rows? The first is determined by the order of the timestamps in the ts columns. A query to the above table will result in:

 A | B | C --+---+--- 1 | 9 | 7 

Answer 1

The window function first_value() allows a fairly 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 . So nonzero values ​​come first. This is followed by ts by ts (timestamp column, as you commented), and you will get it in one SELECT .

This would be easier if Postgres supported IGNORE NULLS . Leadership:

The SQL standard defines RESPECT NULLS or IGNORE NULLS options for lead , lag , first_value , last_value and nth_value . This is not implemented in PostgreSQL: the behavior always matches the standard default value, namely RESPECT NULLS .

One of the few omissions regarding standard SQL in this area.

db <> fiddle here
SQL Fiddle

Answer 2

You must determine the order (primary key or something else) to get the FIRST value not null. So I used the ID column to arrange the rows in your table.

 select (select A from t where A is not null ORDER BY id LIMIT 1), (select b from t where b is not null ORDER BY id LIMIT 1), (select c from t where c is not null ORDER BY id LIMIT 1) 

SQLFiddle demo

Answer 3

You can do this using window functions . I divided the result into 2 parts and then used this section for the row_number function

• with zero values
• valid valid

Then, using the base case to get those that have row_number as 1 and that have not null in them

SQLFIDDLE

 SELECT max ( CASE WHEN a_row_num = 1 AND a IS NOT NULL THEN a END ) AS A, max ( CASE WHEN b_row_num = 1 AND B IS NOT NULL THEN B END ) AS B, max ( CASE WHEN c_row_num = 1 AND C IS NOT NULL THEN C END ) AS C FROM ( SELECT a, row_number ( ) over ( partition BY a IS NULL ORDER BY ID ) a_row_num, b, row_number ( ) over ( partition BY b IS NULL ORDER BY ID ) b_row_num, c, row_number ( ) over ( partition BY c IS NULL ORDER BY ID ) c_row_num FROM test ) AS sub_query 

Output:

 | A | B | C | |---|---|---| | 1 | 9 | 7 | 

NOTE: I added an id field that helps to know which of the records was first inserted, we use it in ascending order in our window function

Answer 4

Not sure if I got the question correctly as it seems pretty simple in principle.
Try this request.
SQL Fiddle: http://sqlfiddle.com/#!11/ac585/8

 WITH t0 AS ( SELECT A FROM TableName t0 WHERE (A IS NOT NULL) ORDER BY ID ASC LIMIT 1 ), t1 AS ( SELECT B FROM TableName WHERE (B IS NOT NULL) ORDER BY ID ASC LIMIT 1 ), t2 AS ( SELECT C FROM TableName WHERE (C IS NOT NULL) ORDER BY ID ASC LIMIT 1 ) SELECT t0.A, t1.B, t2.C FROM t0 JOIN t1 ON 1=1 JOIN t2 ON 1=1