在稀疏表中选择一行第一个非空值 [英] Select a row of first non-null values in a sparse 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
如何选择每列的第一个非空值在N行的运行窗口中?由列 ts
中的时间戳顺序定义的第一。查询上表将导致:
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
推荐答案
窗口函数> first_value()
提供了一个简短而优雅的解决方案:
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
计算为 TRUE
或 FALSE
。 FALSE
在 TRUE
之前排序。这样,非空值首先出现。接下来按 ts
SELECT 中完成。
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
.
如果Postgres支持 IGNORE NULLS
,这会更简单。 手册:
This would be simpler if Postgres supported IGNORE NULLS
. The manual:
SQL标准为$定义了
RESPECT NULLS
或IGNORE NULLS
选项b $ b线索
,滞后
,第一值
,last_value
和nth_value
。这不是在PostgreSQL中实现的
:其行为始终与
标准的默认行为相同,即RESPECT NULLS
。
The SQL standard defines a
RESPECT NULLS
orIGNORE NULLS
option 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的少数遗漏之一。
One of the few omissions with regard to standard SQL in this area.
这篇关于在稀疏表中选择一行第一个非空值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!