sql - 将编辑差异扁平化到主记录上——我可以更简单或更有效地做到这一点吗？

Question

我正在开发一个需要随时间跟踪用户编辑的系统。由于各种限制，我们需要保持原始记录的原始状态，并将编辑内容合并到代表当前状态的一行中。

我主要针对“重放”编辑的结果，以便编辑的列显示其最近编辑的值，而未编辑的列显示原始值。

通过简化示例，给定原始记录表:

books:
book_id | title  | color | year
-------------------------------
1       | First  | blue  | null
2       | Second | green | null
3       | Third  | red   | 1992

以及对所有未更改值为空的记录所做的编辑表:

edits:
edit_id | book_id | title | color | year
----------------------------------------
101     | 1       | Uno   | null  | 2003
102     | 1       | Ett   | teal  | null
103     | 2       | null  | null  | 1999

我正在生成如下输出:

book_id | title  | color | year
-------------------------------
1       | Ett    | teal  | 2003
2       | Second | green | 1999
3       | Third  | red   | 1992

我当前的实现按预期工作(在 PostgreSQL 9.6 上)，但我隐隐觉得我可能缺少一种更简单或更有效的方法来实现它:

SELECT
  books.id,
  COALESCE(
    (
      array_agg(edits.title ORDER BY edits.id DESC)
      FILTER (WHERE edits.title IS NOT NULL)
    )[1],
    books.title
  ) as title
  -- [... repeat for other fields ...]
FROM books
LEFT JOIN edits
  ON books.id = edits.book_id
GROUP BY books.id;

有什么想法吗？

Answer 1

如果您创建一个返回最后一个非空值的聚合，您可以这样做:

select b.book_id, 
       coalesce(last(e.title order by e.edit_id), b.title) as title,
       coalesce(last(e.color order by e.edit_id), b.color) as color,
       coalesce(last(e.year order by e.edit_id), b.year) as year
from books b
  left join edits e on b.book_id = e.book_id
group by b.book_id
order by b.book_id;

参见 the Postgres Wiki用于实现 last()(和 first())函数。

这可能会更快，因为它不必为了选择最后一个值而将所有值都保存在内存中。它在聚合期间只在内存中保留一个值。