如何(a)重写连接三个表的查询和(b)提高执行效率

Question

我有三张表(A、B和C)，其中有关于采矿设施的各种数据。但是只有一个表(C)有两个具有坐标的列。我的最终目标是生成一个表，该表从A和B中提取关于我的名称、所有者、产品等的相似数据，并将它们放在C中类似列的旁边。

DDL设计如下：

-- Table "A"
CREATE TABLE "globalminfac_db".minfac (
    ROW_ID INT,
    MINERAL_COMMODITY_GENERAL TEXT,
    FACILITY_PRODUCES TEXT,
    COUNTRY TEXT,
    CITY TEXT,
    LOC_DESC TEXT,
    LOCATION_NAME TEXT,
    OPERATOR_NAME TEXT,
    OWNER_NAME TEXT,
    PRIMARY_OWNER TEXT,
    SECONDARY_OWNER TEXT,
    FINAL_DDLAT REAL,
    FINAL_DDLONG REAL,
);

-- Table "B"
CREATE TABLE "drc_db".OUTLOOK_TABLE (
    COUNTRY TEXT,
    COMMODITY TEXT,
    MINE_NAME TEXT,
    OPERATOR_NAME TEXT,
);

-- Table "C"
CREATE TABLE "drc_db".table2_drc (
    COMMODITY TEXT,
    MAJOR_OPERATOR_OWNER TEXT,
    LOCATION_MAIN_FACILITIES TEXT,
);

但是，我只能在这些表上执行内部连接，这会产生冗余行，在这些行中，它从每个名为"x“的表中取出所有行，并在我的新表中为每个行提供一行。请参阅下面的选择操作和示例数据的屏幕截图。我已经完成了使用匹配列连接表的初步工作，这是location/ is的名称。

SELECT minfac.mineral_commodity_general a_commodity, 
       outlook.commodity b_commodity, 
       table2.commodity c_commodity, 
       minfac.location_name a_locationName, 
       outlook.mine_name b_locationName, 
       table2.location_main_facilities c_locationName,
       minfac.operator_name a_operator, 
       outlook.operator_name b_operator, 
       table2.major_operator_owner c_operator, 
       minfac.final_ddlat Latitude, 
       minfac.final_ddlong Longitude
    FROM "drc_db".minfac_drc minfac
    INNER JOIN "drc_db".outlook_drc outlook 
        ON minfac.location_name LIKE concat(outlook.mine_name, '%')
    INNER JOIN "drc_db".table2_drc table2 
        ON SPLIT_PART(table2.location_main_facilities, ' ', 1) = SPLIT_PART(minfac.location_name, ' ', 1)

这就产生了下表：

如您所见，我有6行列出"Dikulushi矿“，坐标相同(表A中有3行为银、铜和钴，表B为1行，表C为2行)。

如何重写查询以返回上表，但只包含唯一的位置名称？

最后，重申我前面的观点，就是有一个包含所有三个表中的行和坐标的最后一个表。

我在Postgres 9.5.14中使用pgAdmin4。

对于这个问题或使我的查询更有效/更好的任何帮助都将不胜感激。

Answer 1

我的第一印象是你应该重新设计一下你的桌子。我不知道SPLIT_PART做什么，但听起来好像它抓住了字符串的一部分，而在ON子句中包含这样的函数对性能确实很不利。假设表A和表B各有1000行：

SELECT ...
FROM A
JOIN B
    ON f(A.x) = f(B.x)

在嵌套的循环连接中，会有对f的2*1000*1000=2 miljon调用。我将研究为联接条件添加生成列的可能性，并添加一个索引。

要从答案中删除冗余，可以使用不同的方法，但在以下情况下仍然会得到冗余信息：

GOLD, SILVER, ...
SILVER, GOLD, ...

如果a_commodity和b_commodity为某一行切换位置重要吗？如果没有，您可以按字母顺序排列如下：

SELECT distinct
       LEAST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...
       GREATEST(minfac.mineral_commodity_general, outlook.commodity, table2.commodity)
     , ...

对于最中间的人，你必须把最小和最大的一个排除在外。