postgresql 在稀疏表中选择一行第一个非空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20739117/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select a row of first non-null values in a sparse table
提问by Alex B
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:
如何在 N 行的运行窗口中选择每列的第一个非空值？“第一个”由列中时间戳的顺序定义ts。查询上表将导致：
A | B | C
--+---+---
1 | 9 | 7
回答by Erwin Brandstetter
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 NULLevaluates to TRUEor FALSE. FALSEsorts 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.
a IS NULL评估为TRUE或FALSE。FALSE排序之前TRUE。这样，非空值首先出现。下一个排序ts（如您评论的时间戳列），然后您将其放入单个SELECT.
This would be simpler if Postgres supported IGNORE NULLS. The manual:
如果 Postgres 支持，这会更简单IGNORE NULLS。手册：
The SQL standard defines a
RESPECT NULLSorIGNORE NULLSoption forlead,lag,first_value,last_value, andnth_value. This is not implemented in PostgreSQL: the behavior is always the same as the standard's default, namelyRESPECT NULLS.
SQL标准定义了一个
RESPECT NULLS或IGNORE NULLS选项lead，lag，first_value，last_value，和nth_value。这在 PostgreSQL 中没有实现：行为总是与标准的默认值相同，即RESPECT NULLS.
One of the few omissions with regard to standard SQL in this area.
这是该领域中标准 SQL 的少数遗漏之一。
db<>fiddle here
SQL Fiddle.
回答by valex
You should define an order (a primary key or something else) to get the FIRST not null value. So I used IDcolumn to order rows in your table.
您应该定义一个顺序（主键或其他东西）来获取第一个非空值。所以我使用IDcolumn 来对表格中的行进行排序。
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)
回答by Akash
You can do this with window functions. I have Partitioned the result into 2 parts and then used this partition for the row_numberwindow function
你可以用窗口函数来做到这一点。我已将结果分成 2 部分，然后将此分区用于row_number窗口函数
- having null values
- having a valid valid
- 有空值
- 有一个有效的
Then, using a basic caseto get the ones having the row_numberas 1and which have a not nullvalue in them
然后，使用基本case来获取具有row_numberas1和其中具有not null值的那些
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 idfield, which helps to know which of the records was first inserted, we use it in ascending order within our window function
注意：我添加了一个id字段，它有助于了解首先插入的是哪条记录，我们在窗口函数中按升序使用它
回答by peter.petrov
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
