数据库：选择最后一个非空条目 [英] Database: Select last non-null entries
问题描述
这是一个我一直在困扰我的大脑的问题。假设我有一个表，其中有一系列时间戳和一个零件号作为主键。该表存储增量更改，这意味着对于每个时间戳，如果字段更改，则记录该更改。如果字段不更改，则对于新时间戳，它为NULL。
这是基本的想法。
Here's a question I've been racking my brain over. Let's say I have a table that has a series of timestamps and a part number as the primary key. The table stores incremental changes, meaning that for every timestamp, if a field changes, that change is recorded. If the field doesn't change, then for the new timestamp it is NULL. Here's the basic idea.
part | timestamp | x-pos | y-pos | status
------+-----------+-------+-------+--------
a5 | 151 | 5 | 15 | g
a5 | 153 | NULL | 17 | NULL
（part，timestamp）首要的关键。第二条记录中的 NULL 表示自第一条记录以来未发生变化的值。
(part, timestamp) is the primary key. The NULLs in the second record indicate values that are unchanged since the first record.
能够做的是为按部件分组的每个字段选择最近的值。例如，给定上面的条目，结果将是153,5,17，g的部分a5。
What I want to be able to do is select the most recent values for each field grouped by the part. For example, given the above entries, the results will be 153,5,17,g for part a5.
到目前为止，我有这个黑客一起查询。 / p>
As of now, I have this hacked together query.
((SELECT x-pos FROM part_changes WHERE x-pos IS NOT NULL
ORDER BY timestamp DESC
LIMIT 1)
UNION
(SELECT y-pos FROM part_changesWHERE y-pos IS NOT NULL
ORDER BY timestamp DESC
LIMIT 1)
UNION
(SELECT status FROM part_changes WHERE status IS NOT NULL
ORDER BY timestamp DESC
LIMIT 1))
但这会返回一个列，这意味着我可以使用group-by来组织。
But this returns a single column, meaning that I can use a group-by for organizing.
必须是一种更优雅的做事方式，例如以创造性的方式使用COALESCE或IS NULL。但我被困，不能想出来。任何人有一个想法？
There's got to be a more elegant way of doing thing, such as using COALESCE or IS NULL in a creative way. But I'm stuck and can't figure it out. Anybody got an idea?
不，我不能更改数据库结构。
And no, I can't change the database structure.
编辑：ruakh有正确的想法。现在唯一的问题是按部分分组。我似乎不能绕过 LIMIT 1 按多个部分进行分组。有任何想法吗？
ruakh has the right idea. The only problem now is grouping by part. I can't seem to get around the LIMIT 1 for grouping by multiple parts. Any ideas?
mdahlman，我不太熟悉postgresql中的分析函数。所以，如果这个解决方案比一个复杂的查询更容易，那么通过所有的方式发布你的想法。
mdahlman, I'm not too familiar with analytic functions in postgresql. So, if that solution would be easier than a complex query, then by all means post your idea.
编辑2：谢谢大家的帮助。
EDIT 2: Thank you all for the help. I think I've got a good enough grasp of what I need to do.
推荐答案
而不是使用 UNION ，听起来你真的想在字段列表中的子查询。也就是说，您不需要（SELECT ...）UNION（SELECT ...）UNION（SELECT ...），您需要 SELECT ...），（SELECT ...），（SELECT ...）。
Rather than using a UNION, it sounds like you really want subqueries in the field list. That is, instead of (SELECT ...) UNION (SELECT ...) UNION (SELECT ...), you want SELECT (SELECT ...), (SELECT ...), (SELECT ...).
例如：
SELECT part,
( SELECT x_pos
FROM part_changes
WHERE part = pc.part
AND x_pos IS NOT NULL
ORDER
BY timestamp DESC
LIMIT 1
) AS x_pos,
( SELECT y_pos
FROM part_changes
WHERE part = pc.part
AND y_pos IS NOT NULL
ORDER
BY timestamp DESC
LIMIT 1
) AS y_pos,
( SELECT status
FROM part_changes
WHERE part = pc.part
AND status IS NOT NULL
ORDER
BY timestamp DESC
LIMIT 1
) AS status
FROM ( SELECT DISTINCT
part
FROM part_changes
) AS pc
;
但是现在我真的考虑写一个存储过程。
But at this point I would really consider writing a stored procedure.
或者：
SELECT DISTINCT
part,
FIRST_VALUE(x_pos) OVER
( PARTITION BY part
ORDER BY CASE WHEN x_pos IS NULL
THEN NULL
ELSE TIMESTAMP
END DESC NULLS LAST
) AS x_pos,
FIRST_VALUE(y_pos) OVER
( PARTITION BY part
ORDER BY CASE WHEN y_pos IS NULL
THEN NULL
ELSE TIMESTAMP
END DESC NULLS LAST
) AS y_pos,
FIRST_VALUE(status) OVER
( PARTITION BY part
ORDER BY CASE WHEN status IS NULL
THEN NULL
ELSE TIMESTAMP
END DESC NULLS LAST
) AS status
FROM part_changes
;
这篇关于数据库：选择最后一个非空条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
