将常见查询保存为列？

Question

我使用PostgreSQL，有很多类似这样的查询语句：

SELECT <col 1>, <col 2>
     , (SELECT sum(<col x>)
        FROM   <otherTable> 
        WHERE  <other table foreignkeyCol>=<this table keycol>) AS <col 3>
FROM   <tbl>

考虑到子查询在每个情况下都是相同的，是否有一种将该子查询作为伪列存储在表中的方法？实质上，我想能够从表A选择一列作为来自表B的特定列的总和，其中记录相关联。这种做法可行吗？

- ibrewster

---

你不能为此创建一个视图吗？ - Ilion

Answer 1

有没有一种方法可以将子查询作为伪列存储在表中？

像VIEW这样的解决方案是完全有效的。请尝试。

但是还有一种更贴近您问题的方式：计算字段或生成列。Postgres 11引入了STORED生成列。详情请见： 在PostgreSQL中计算/计算/虚拟/派生列 对于旧版本，或者如果您喜欢VIRTUAL生成列（尚未实现，直到Postgres 15），则可以使用以表类型为参数的函数模拟该功能。

考虑以下测试用例，源自您的描述：

CREATE TABLE tbl_a (a_id int, col1 int, col2 int);
INSERT INTO tbl_a VALUES (1,1,1), (2,2,2), (3,3,3), (4,4,4);

CREATE TABLE tbl_b (b_id int, a_id int, colx int);
INSERT INTO tbl_b VALUES
  (1,1,5),  (2,1,5),  (3,1,1)
, (4,2,8),  (5,2,8),  (6,2,6)
, (7,3,11), (8,3,11), (9,3,11);

创建一个函数来模拟col3:

CREATE FUNCTION col3(tbl_a)
  RETURNS int8
  LANGUAGE sql STABLE AS
$func$
SELECT sum(colx)
FROM   tbl_b b
WHERE  b.a_id = $1.a_id
$func$;

现在您可以查询：

SELECT a_id, col1, col2, tbl_a.col3
FROM   tbl_a;

或者甚至：

SELECT *, a.col3 FROM tbl_a a;

请注意，我写的是tbl_a.col3/a.col3，而不仅仅是col3。这是必要的。

与Oracle中的"虚拟列"不同，它不会自动包含在SELECT * FROM tbl_a中。你可以使用一个VIEW来实现。

为什么这样做有效？

通常引用表列的方法是使用属性表示法：

SELECT tbl_a.col1 FROM tbl_a;

调用函数的常见方式是使用函数表示法：

SELECT col3(tbl_a);

一般来说，最好坚持这些规范的方式，这与SQL标准相符。

但是Postgres也允许属性表示法。这些也可以工作：

SELECT col1(tbl_a) FROM tbl_a;
SELECT tbl_a.col3;

在手册中可以找到更多相关信息。
你现在可能已经看出来了，这个看起来像是你要在表tbl_a中添加一列，而col3()实际上是一个函数，它以tbl_a（或其别名）的当前行作为行类型参数，并计算一个值。

SELECT *, a.col3
FROM   tbl_a AS a;

如果存在实际列col3，它优先级最高，系统不会寻找以行tbl_a为参数的该名称函数。
"美妙"之处在于：您可以添加或删除来自tbl_a的列，并且最后一个查询将动态返回所有当前列，而视图仅返回创建时存在的列（早期绑定与*的后期绑定）。
当然，现在您必须放弃依赖的函数才能删除表。并且在对表进行更改时要注意不使函数失效。
我仍然不会使用它。对于无辜的读者来说太令人惊讶了。

- Erwin Brandstetter

---

该函数应该标记为“STABLE”（表示在单个查询的执行过程中，如果使用相同参数多次调用它，它将返回相同的值），而不是“IMMUTABLE”（表示无论数据库内容或时间的流逝，它都将始终为相同的参数返回相同的值）。其中一个作用是防止在索引中使用该函数，这是您想要的，因为如果对tbl_b进行更改，则索引将变得损坏。如果仅使用作为参数传递的行的值，则可以使用“IMMUTABLE”。 - kgrittn

@kgrittn：当然，我已经相应地修改了函数声明。忘记还涉及另一个表格了。 - Erwin Brandstetter

@ErwinBrandstetter - 但是如果你执行\d tbl_a;或SELECT * FROM tbl_a;，你看不到“持久/计算/生成”列吗？另外，如果你能查看一下我的答案这里，我将不胜感激 - 或许我没有正确使用你的代码？还是这是PostgreSQL中的一个bug？ - Vérace

@Vérace：该函数不会自动成为表的一部分，您必须像指示的那样拼写出来。我进一步澄清了一些内容。 - Erwin Brandstetter

@ErwinBrandstetter，我使用这样的字段表示法时遇到了一个字段不存在的错误... https://stackoverflow.com/questions/56816432/pgsql-calling-function-with-column-notation-error - Leo

Answer 2

显然，根据lion的评论，这是通过视图来处理的。因此，在我的情况下，我使用了以下命令：

CREATE VIEW <viewname> AS
SELECT *, (SELECT sum(<col x>)
FROM   <otherTable
WHERE  <otherTable foreignkeyCol>=<thisTable keycol>) AS <col 3>
FROM   <tablename>

这基本上为我提供了另一个表格，其中包括所需的列。

- ibrewster

Answer 3

除了查看，您可以创建一个用于求和的函数。

CREATE FUNCTION sum_other_table( key type_of_key ) RETURNS bigint
AS $$ SELECT sum( col_x ) FROM table_1 where table_1.key = key $$ LANGUAGE SQL;

然后将其用作您的聚合器：

SELECT col_1, col_2, sum_other_table( key ) AS col_3
FROM table_2 WHERE table_2.key = key;

请注意，sum_other_table()函数的返回类型取决于您要求和的列的类型。

- Johann Oskarsson

Answer 4

到目前为止，已经有三个答案，它们都可以有效地解决问题。根据情况，任何一个都可能是“最佳解决方案”。对于小表，它们的性能应该非常接近，但是它们都不太可能扩展到拥有数百万行的表。如果要在大型数据集上快速获得所需的结果，最快的方法可能是（使用Erwin的设置）：

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id, col1, col2;

如果a_id被声明为主键，并且在9.1或更高版本下运行，那么GROUP BY子句可以简化，因为col1和col2在a_id上是函数依赖的。

SELECT a_id, col1, col2, sum(colx)
FROM tbl_a LEFT JOIN tbl_b b using(a_id)
GROUP BY a_id;

这个视图可以按照这种方式定义并进行扩展，但我认为使用函数的方法不一定会考虑所有相同的执行路径，因此可能不会使用最快的执行路径。

- kgrittn

---

在LANGUAGE SQL中，简单函数将被内联，并且查询优化器可能会提供最佳解决方案。但这是否发生在特定情况下是另一个问题。 - Johann Oskarsson

即使函数被内联，我的版本仍然会导致相关子查询而不是连接。我怀疑优化器敢不敢进行转换。 - Erwin Brandstetter