在稀疏表中选择一行第一个非空值

Using the following table,

使用下表,

A|   B|   C
-----------
1|null|null
4|null|null
4|   9|null
5|   1|   7

How do I select the first non-null value of each column in a running window of N rows? E.g. querying the above table would result in:

如何在N行的运行窗口中选择每列的第一个非空值?例如。查询上表将导致:

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

4 个解决方案

#1

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

窗口函数first_value()允许一个相当简短和优雅的解决方案:

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 / FALSE. FALSE (0) sorts before TRUE (1). 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.

IS NULL计算结果为TRUE / FALSE。 FALSE(0)在TRUE(1)之前排序。这样,首先是非空值。接下来按ts(你评论的时间戳列)排序,你就可以在一个SELECT中得到它。

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.

您应该定义一个订单(主键或其他内容)以获取FIRST非null值。所以我使用ID列来排序表中的行。

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

您可以使用窗口功能执行此操作。我将结果分成两部分,然后将此分区用于row_number窗口函数

having null values

具有空值

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

然后,使用基本案例来获取row_number为1并且其中具有非空值的那些

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

注意:我添加了一个id字段,它有助于知道哪个记录首次插入,我们在窗口函数中按升序使用它

#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

不确定我是否正确地得到了问题,因为它看起来很简单。试试这个查询。 SQL小提琴: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

#1