sql - 在 Postgres View 中折叠联合的结果

Question

基本问题

在 Postgres 9.2 中有没有一种方法可以创建一个 View 来聚合来自两个表的数据，并涉及一些逻辑？

我们需要的逻辑是:

1. 一个表中的数据优先于另一个
2. 具有相同键(多个字段的组合)的后续行会覆盖之前的行。
3. 状态为 D 的行删除给定“键”的任何前面的行。

更多细节和例子

我在 Postgres 数据库中有 2 个模式。它们每个都有相同的表和列，但数据不同。一种用于官方数据，一种用于提议的更改。

注意:我确信有更好的方法，但这是旧设置，无法更改。这是一个非常简化的虚构示例，但显示了我需要的情况和结果。

所以我们有一个特征表，描述了一个小部件。官方数据中每种类型的数据只有一个(一个小部件将有一种尺寸、一种颜色等)。

提议修改，一旦通过，修改官方数据。给定类型的数据可能有多个待定更改。

官方架构

CREATE TABLE characteristics (
    widget_id      integer NOT NULL,
    variation_id   integer NOT NULL,
    value          varchar(10),
    action_date    date,
    status         char(1)
);

official.characteristics 中的示例数据:

1,1,GI Joe,12/25/2012,C
1,2,Green,12/25/2012,C
1,3,M,12/25/2012,C
1,4,Plastic,12/25/2012,C
2,1,GI Joe,12/25/2012,C
2,2,Green,12/25/2012,C
2,3,L,12/25/2012,C
2,4,Plastic,12/25/2012,C

所以我们有 2 个小部件，一个是中型、绿色和塑料的。一种是大的、绿色的和塑料的。

提议的架构

CREATE TABLE characteristics (
    widget_id      integer NOT NULL,
    variation_id   integer NOT NULL,
    value          varchar(10),
    action_date    date,
    status         char(1)
);

proposed.characteristics中的示例数据:

1,2,Blue,2/22/2013,C
1,4,Plastic,2/22/2013,D
2,2,Purple,2/10/2013,C
2,2,Green,2/22/2013,D
2,3,XL,2/22/2013,C

如果我们想要查看所有建议更改的结果，我们可以查询两个表，用新数据替换旧数据，或者 D 行删除任何以前的数据。

SELECT
  'o' as src,
  lpad(widget_id::text,4,'0'::text) || '_' || lpad(variation_id::text,4,'0'::text) as key,
  *
FROM
  proposed.characteristics
ORDER BY
  key ASC,
  action_date::date ASC

第二个查询是相同的，但是在另一个表上并且以 'p' 作为 src。

使用 PHP，我可以查询每个表，首先是官方的，其次是建议的更改，然后将数据放入带有 key 的数组中(widget_id || '_' || variation_id) 作为键。任何新行都会覆盖旧行。如果 status 为 D(表示删除)，则删除具有该键的行(尽管随后提议的更改可能会重新添加它)。

所以对于上面的数据，我们最终会得到:

o,0001_0001,1,1,GI Joe,12/25/2012,C
p,0001_0002,1,2,Blue,2/22/2013,C
o,0001_0003,1,3,M,12/25/2012,C
o,0002_0001,1,1,GI Joe,12/25/2012,C
p,0002_0003,2,3,XL,2/22/2013,C
o,0002_0004,2,4,Plastic,12/25/2012,C

总结

有没有一种方法可以创建一个 View ，我可以在其中直接查询上面的结果？
还有用于删除工作的 D，更新的更改会覆盖以前的更改或官方数据吗？

Answer 1

^{由于缺少信息，假设当前版本为 PostgreSQL 9.2。}

一种方法是 CTE用UNION ALL两个表，并使用 NOT EXISTS 获取每个小部件的最后一个有效版本反半连接:

CREATE VIEW my_viw AS
WITH x AS (
   SELECT 'o' as src, * FROM official.characteristics
   UNION ALL
   SELECT 'p' as src, * FROM proposed.characteristics
   )
SELECT lpad(widget_id::text, 4, '0')
       || '_' || lpad(variation_id::text, 4, '0') AS key, * -- pick columns
FROM   x
WHERE  NOT EXISTS (
   SELECT 1 FROM x y
   WHERE  y.widget_id = x.widget_id
   AND    y.variation_id = x.variation_id
   AND    y.action_date > x.action_date
   )
AND   (status <> 'D' OR status IS NULL)
ORDER  BY widget_id, variation_id

返回您概述的结果，除了我在评论中指出的错误。

->sqlfiddle

一步一步

1. 使用简单快速的 UNION ALL 从两个表中获取所有行在 CTE 中
2. 排除行，其中相同(widget_id，variation_id)的后面行存在 NOT EXISTS
3. 排除带 status = 'D' 的行.
4. ORDER BY 并从 widget_id, variation_id 合成 key .

要点

• 使用原始列操作很可能更快 widget_id, variation_id并且只合成 key在决赛中SELECT .更少的代码，更容易索引。

• 使用 CTE，因为在两个地方需要结果。

• 列 status应该定义 NOT NULL , 将使 WHERE 条件更简单。

• 在两个表上使用如下所示的多列索引可能有助于提高性能。不确定它可以在 UNION ALL 之后使用.用 EXPLAIN ANALYZE 测试看看。

  CREATE INDEX characteristics_mult_idx
  ON official.characteristics (widget_id, variation_id, action_date DESC)