sql - 查询多个 Postgres 表

Question

我有一个查询，我想对多个表进行查询，然后在最后加入结果。

这行得通，但我希望有一种更简洁的方法来实现同样的事情

SELECT key, a.c as "TableA count", b.c as "TableB count", c.c as "TableC count", d.c as "TableD count"
FROM
(
    SELECT key, count(key) as c
    FROM table_a
    WHERE data = 'foo'
    GROUP BY key
) a
FULL OUTER JOIN
(
    SELECT key, count(key) as c
    FROM table_b
    WHERE data = 'foo'
    GROUP BY key
) b USING(key)
FULL OUTER JOIN
(
    SELECT key, count(key) as c
    FROM table_c
    WHERE data = 'foo'
    GROUP BY key
) c USING(key)
FULL OUTER JOIN
(
    SELECT key, count(key) as c
    FROM table_d
    WHERE data = 'foo'
    GROUP BY key
) d USING(key);

预期输出:

 key |  A count |  B count | C  count |  D count |
-----+----------+----------+----------+----------+
5740 |        5 |      102 |      129 |          |
5741 |     1017 |    22163 |    34888 |      218 |
5742 |      797 |    10846 |    19285 |      159 |
5743 |    24966 |   398067 |   572504 |     4772 |
5744 |    31942 |   278944 |    73628 |     5665 |
5745 |     3975 |    54322 |    98578 |     1103 |
5746 |     1353 |     4751 |    33129 |      259 |

Answer 1

我认为如果您使用 key 来连接该表是因为 key 位于同一域中(至少在概念上是这样)。

如果您在 PK 中拥有或可能拥有包含所有 key 值的表格，您可以做得更好。

部分解决方案

仅当 key 是表的主键时

SELECT key, total_a, total_b, total_c, total_d
   FROM key_table, 
        lateral (
          SELECT count(key) as total_a
          FROM table_a
          WHERE key = key_table.key AND data = 'foo'
        ) a,
        lateral (
          SELECT count(key) as total_b
          FROM table_b
          WHERE key = key_table.key AND data = 'foo'
        ) b,
        lateral (
          SELECT count(key) as total_c
          FROM table_c
          WHERE key = key_table.key AND data = 'foo'
        ) c,
        lateral (
          SELECT count(key) as total_d
          FROM table_d
          WHERE key = key_table.key AND data = 'foo'
        ) d
    WHERE total_a IS NOT NULL OR total_b IS NOT NULL OR  total_c IS NOT NULL OR  total_d IS NOT NULL
    ORDER BY key

改变模型解决方案

其他解决方案是更改同一个表中具有 table_Ls 数据的数据模型。对于现有查询，您可以查看。如果 table_L 表在同一个域中(例如不同国家的城市)并且列相同(或差异很小)，这是一个很好的解决方案。

新表必须有一个新字段 source_data，其值为 'a', 'b', 'c', d' 四个 View 如下:

 CREATE VIEW table_a AS 
   SELECT key, foo, others_field1, other_field2, ...
     FROM table_abcd
     WHERE source_data = 'a'
   WITH CHECK OPTION;

这样做你可以做类似的事情。主要问题是 table_L 表的外键，如果有的话，您可以对相关表执行相同的操作。

看:https://www.postgresql.org/docs/current/static/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS

触发数据到另一个表

如果表在不同的域中并且此查询非常重要，您可以在另一个表中触发该数据。例如 key 是添加数据的人的用户名(你想为此付钱给他)，而 table_L 是非常不同的东西。

您可以向每个 table_L 表添加触发器以触发对具有所需字段(包括 source_table 字段)的其他表 recolected_data 的更新，然后使用简单的查询 group by查询。