选择每列的最后一个值，使用单个查询(Select the last value of each co

具有下列数据（空白表示NULL）：

ID    ColA    ColB    ColC
1     15              20
2     11      4       
3             3

我怎样才能在一个查询中的每一列的最后一个非空值？因此，对于给定的数据将是导致：

ColA    ColB    ColC
11      3       20

我没有太多发现，似乎做类似我形容是一些功能COALESCE ，但预计将在我的情况下，这是行不通的。

Answer 1:

看起来你必须与普通的SQL运行每列一个单独的查询。对于一个小桌子，只有3列， @ Guffa的查询应该罚款。

3个窗口函数

你可以用三个窗口函数做同样在一个查询：不知道这是快于三个独立的子查询：

SELECT first_value(cola) OVER (ORDER BY cola IS NULL, id DESC) AS cola
      ,first_value(colb) OVER (ORDER BY colb IS NULL, id DESC) AS colb
      ,first_value(colc) OVER (ORDER BY colc IS NULL, id DESC) AS colc
FROM   tbl
LIMIT  1;

`count()`作为窗口函数

你也可以利用这样一个事实count()不计算NULL值。

WITH x AS (
   SELECT CASE WHEN count(cola) OVER w = 1 THEN cola ELSE NULL END AS cola
         ,CASE WHEN count(colb) OVER w = 1 THEN colb ELSE NULL END AS colb
         ,CASE WHEN count(colc) OVER w = 1 THEN colc ELSE NULL END AS colc
   FROM   tbl
   -- WHERE id > x -- safe to ignore a certain portion from a large table?
   WINDOW w AS (ORDER  BY id DESC)
   )
SELECT max(cola) AS cola, max(colb) AS colb, max(colc) AS colc
FROM   x

对更大的表多列，一个递归CTE或程序的功能将是相当快：

递归CTE

WITH RECURSIVE x AS (
   SELECT cola, colb, colc
         ,row_number() OVER (ORDER  BY id DESC) AS rn
   FROM   tbl
   )

   , y AS (
   SELECT rn, cola, colb, colc
   FROM   x
   WHERE  rn = 1

   UNION ALL
   SELECT x.rn
        , COALESCE(y.cola,x.cola)
        , COALESCE(y.colb,x.colb)
        , COALESCE(y.colc,x.colc)
   FROM   y
   JOIN   x ON  x.rn = y.rn + 1
   WHERE  y.cola IS NULL OR y.colb IS NULL OR y.colc IS NULL
   )
SELECT cola, colb, colc
FROM   y
ORDER  BY rn DESC
LIMIT  1;

PL / pgSQL函数

我的钱是在这一个最佳的性能：

CREATE OR REPLACE FUNCTION f_last_nonull(OUT cola int
                                       , OUT colb int
                                       , OUT colc int) AS
$func$
DECLARE
   r record;
BEGIN

FOR r IN
   SELECT t.cola, t.colb, t.colc 
   FROM   tbl t
   ORDER  BY t.id DESC
LOOP
   IF cola IS NULL AND r.cola IS NOT NULL THEN cola := r.cola; END IF;
   IF colb IS NULL AND r.colb IS NOT NULL THEN colb := r.colb; END IF;
   IF colc IS NULL AND r.colc IS NOT NULL THEN colc := r.colc; END IF;

   EXIT WHEN NOT (cola IS NULL OR colb IS NULL OR colc IS NULL);
END LOOP;

END
$func$ LANGUAGE plpgsql;

呼叫：

SELECT * FROM f_last_nonull();

cola | colb | colc
-----+------+------
 11  | 3    | 20

与测试EXPLAIN ANALYZE 。将是很好，如果你能回来与解决方案的比较。

Answer 2:

您可以使用子查询：

select
  (select ColA from TheTable where ColA is not null order by ID desc limit 1) as ColA,
  (select ColB from TheTable where ColB is not null order by ID desc limit 1) as ColB,
  (select ColC from TheTable where ColC is not null order by ID desc limit 1) as Col