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

问题:

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

回答1:

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.

回答2:

You should define an order (a primary key or something else) to get the FIRST not null value. So I used ID column to order 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

回答3:

You can do this with window functions. I have Partitioned the result into 2 parts and then used this partition for the row_number window function

1. having null values
2. having a valid valid

Then, using a basic case to get the ones having the row_number as 1 and which have a not null value 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 have added a id field, which helps to know which of the records was first inserted, we use it in ascending order within our window function

回答4:

Not sure if I got the question correctly
as it seems quite simple basically.
Try this query.

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