以最简单的方式为每个用户选择每列的最后一个值

Question

这是前面一个相关问题的扩展版本。我已经发布了一个新的问题，Erwin Brandstetter建议我这么做。(在人们回答了我的第一个问题后，我意识到我实际上想要这个)

具有以下数据(空白表示NULL)：

ID    User  ColA    ColB    ColC
1     1     15              20
2     1     11      4       
3     1             3
4     2     5       5       10
5     2     6 
6     2             8
7     1             1

如何以最简单的方式为所有用户获取每列的最后一个非NULL值？因此，给定数据的结果是：

User  ColA    ColB    ColC
1     11      1       20
2     6       8       10

我没有发现太多，似乎与我描述的功能类似的函数是COALESCE，但在我的情况下，它并没有像预期的那样工作。

注意：如果可能的话是标准SQL，否则是PostgreSQL。所涉及的列的计数可能会更改，因此不与这三个特定列绑定的解决方案将是最好的。

Answer 1

"标准"；SQL

与我在上一个问题上发布的内容类似，递归CTE非常优雅，可能是在标准SQL中实现这一点的最快方法，尤其是对于每个用户的许多行。

WITH RECURSIVE t AS (
SELECT row_number() OVER (PARTITION BY usr ORDER  BY id DESC) AS rn
,usr, cola, colb, colc
FROM   tbl
)
, x AS (
SELECT rn, usr, cola, colb, colc
FROM   t
WHERE  rn = 1
UNION ALL
SELECT t.rn, t.usr
, COALESCE(x.cola, t.cola)
, COALESCE(x.colb, t.colb)
, COALESCE(x.colc, t.colc)
FROM   x
JOIN   t USING (usr)
WHERE  t.rn = x.rn + 1
AND    (x.cola IS NULL OR x.colb IS NULL OR x.colc IS NULL)
)
SELECT DISTINCT ON (usr)
usr, cola, colb, colc
FROM   x
ORDER  BY usr, rn DESC;

->sqlfiddle用于请求的PostgreSQL。

唯一的非标准元素是DISTINCT ON，它是标准中DISTINCT的扩展。将最终的SELECT替换为标准SQL:

SELECT usr
,max(cola) As cola
,max(colb) As colb
,max(colc) As colc
FROM   x
GROUP  BY usr
ORDER  BY usr;

请求"；标准SQL"；用途有限。该标准仅存在于纸面上。没有一个RDBMS实现100%的标准SQL——这也是毫无意义的，因为该标准到处都包含一些毫无意义的部分。可以说，PostgreSQL的实现是最接近标准的。

PL/pgSQL函数

这个解决方案是PostgreSQL特有的，但应该表现得很好。

我是在同一张桌子上建造的，正如上面小提琴所展示的那样。

CREATE OR REPLACE FUNCTION f_last_nonull_per_user()
RETURNS SETOF tbl AS
$func$
DECLARE
_row tbl;  -- table name can be used as row type
_new tbl;
BEGIN
FOR _new IN
SELECT * FROM tbl ORDER BY usr, id DESC
LOOP
IF _new.usr = _row.usr THEN 
_row.id := _new.id;   -- copy only id
IF _row.cola IS NULL AND _new.cola IS NOT NULL THEN
_row.cola := _new.cola; END IF;   -- only if no value found yet
IF _row.colb IS NULL AND _new.colb IS NOT NULL THEN
_row.colb := _new.colb; END IF;
IF _row.colc IS NULL AND _new.colc IS NOT NULL THEN
_row.colc := _new.colc; END IF;
ELSE
IF _new.usr <> _row.usr THEN  -- doesn't fire on first row
RETURN NEXT _row;
END IF;   
_row := _new;  -- remember row for next iteration
END IF;
END LOOP;
RETURN NEXT _row;  -- return row for last usr
END
$func$ LANGUAGE plpgsql;

呼叫：

SELECT * FROM f_last_nonull_per_user();

返回整行，包括填充所有列所需的最小id。

Answer 2

此查询很容易转换为MS SQL。如果您需要更多具体内容，请添加评论。Mysql查询：

SQLFIDDLExample

SELECT
t1.User,
(SELECT ColA 
FROM Table1
WHERE ColA is not null
AND Table1.User = t1.User
ORDER BY ID DESC
LIMIT 1 ) as ColA,
(SELECT ColB 
FROM Table1
WHERE ColB is not null
AND Table1.User = t1.User
ORDER BY ID DESC
LIMIT 1 ) as ColB,
(SELECT ColC 
FROM Table1
WHERE ColC is not null
AND Table1.User = t1.User
ORDER BY ID DESC
LIMIT 1 ) as ColC
FROM Table1 t1
GROUP BY t1.User

结果：

| USER | COLA | COLB | COLC |
-----------------------------
|    1 |   11 |    1 |   20 |
|    2 |    6 |    8 |   10 |