sql - 如何选择集合中的最后一个非NULL列

Question

我正在创建一个表，该表具有针对每个逻辑行的一系列修订，并使用NULL值指示特定单元格具有与上一个修订相同的值。例如：

id | logical_id | revision | x | y | z | other
---+------------+----------+---+---+---+--------
 1 |     1      |     1    | 1 | 2 | 5 | blue
 2 |     2      |     1    | 3 | 5 | 9 | red
 3 |     1      |     2    | 9 |   |   |
 4 |     2      |     2    |   | 7 |   | orange
 5 |     1      |     3    |   | 6 |   |
 6       3      |     1    | 0 | 0 | 0 | white

我需要一个查询，该查询接受每个逻辑ID并为修订后的每个字段（“常规字段”）（在本例中为 x， y， z和 other）构建一个包含最新条目的行。尽管完全可以接受需要根据这些字段的实际含义修改查询（并且我希望如此），但这样的字段的数量是任意的。

因此，对于上表，输出将是：

logical_id | x | y | z | other
-----------+---+---+---+--------
    1      | 9 | 6 | 5 | blue
    2      | 3 | 7 | 9 | orange
    3      | 0 | 0 | 0 | white

我发现了这个问题“ Database: Select last non-null entries”，该问题与我的问题几乎完全相同，但问题与Postgresql有关，给出的解决方案要么使用SQLite中不可用的语言构造（FIRST_VALUE，PARTITION BY？ ），似乎只能回答部分问题（例如，仅针对单个逻辑ID查找结果），或者当我在SQLite中尝试它们时，它们根本无法正常工作。

另外，我还希望能够通过将所有“常规字段”设置为NULL来将 logical_id标记为已删除。

Answer 1

要查找每个逻辑ID具有最大修订版本的行的非NULL值，可以使用子查询：

SELECT logical_id,
       (SELECT x
        FROM MyTable
        WHERE logical_id = T.logical_id
          AND x IS NOT NULL
        ORDER BY revision DESC
        LIMIT 1) AS x,
       (SELECT y
        FROM MyTable
        WHERE logical_id = T.logical_id
          AND y IS NOT NULL
        ORDER BY revision DESC
        LIMIT 1) AS y,
       ...
FROM (SELECT DISTINCT logical_id
      FROM MyTable
      -- WHERE ...
     ) AS T

另外，也可以使用GROUP BY进行此操作，但是要在使用MAX搜索最大修订版本时能够获得实际的列值，则必须将修订版本和其他值组合为单个字符串，然后提取之后的列值：

SELECT logical_id,
       substr(max(printf('%9d', revision) || x), 10) AS x,
       substr(max(printf('%9d', revision) || y), 10) AS y,
       ...
FROM MyTable
-- WHERE ...
GROUP BY logical_id

要删除最新修订为NULL的逻辑ID，请添加WHERE子句，如下所示：

...
WHERE logical_id NOT IN (SELECT logical_id
                         FROM (SELECT logical_id,
                                      max(revision) AS revision
                               FROM MyTable
                               GROUP BY logical_id)
                         JOIN MyTable USING (logical_id, revision)
                         WHERE x     IS NULL
                           AND y     IS NULL
                           AND z     IS NULL
                           AND other IS NULL)
...