如何在基于时间戳列进行排序时获得列的最后一个非null值

Question

表T有A、B、C、TS列(时间戳(，其值定义如下

A B C    TS
d g null 3
h y gh   2
q r null 7

如果我写一个查询如下：

SELECT * from T order by TS desc Limit 1;

它给我的结果是：

A B C     TS
q r null  7

我想要的是永远不要得到一个空值。相反，它应该显示该列的最后一个非null值(如果有的话(。

期望结果：

A B C   TS
q r gh  7

Answer 1

尝试OLAP函数LAST_VALUE( .... IGNORE NULLS) OVER ...。我看到，如果您在窗口定义子句中按ts升序排序，您将获得有意义的数据——我希望这正是您所需要的。。。

WITH
-- your input ...
indata(A,B,C,TS) AS (
SELECT 'd','g',null,3
UNION ALL SELECT 'h','y','gh',2
UNION ALL SELECT 'q','r',null,7
)
-- real query starts here ...
SELECT
LAST_VALUE(a IGNORE NULLS) OVER w AS a
, LAST_VALUE(b IGNORE NULLS) OVER w AS b
, LAST_VALUE(c IGNORE NULLS) OVER w AS c
, ts
FROM indata
WINDOW w AS (ORDER BY ts)
ORDER BY ts DESC
LIMIT 1;
-- out  a | b | c  | ts 
-- out ---+---+----+----
-- out  q | r | gh |  7

Answer 2

一种方法是独立获取每一列：

select (select a from T where a is not null order by TS desc Limit 1),
(select b from T where b is not null order by TS desc Limit 1),
(select c from T where c is not null order by TS desc Limit 1),
(select ts from T where ts is not null order by TS desc Limit 1)