主页 / dba / 问题 / 229787
Accepted
cesium133
Asked: 2019-02-15 12:01:06 +0800 CST

如何 (a) 重写连接三个表的查询，以及 (b) 使其执行更高效

  • 772

我有三个表（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,
);

但是，我只能在这些表上执行 INNER JOIN，这会产生冗余行，它从每个表中获取名称为“x”的所有行，并在我的新表中为每个行分配一行。请参阅下面我的 SELECT 操作和示例数据的屏幕截图。我已经完成了使用匹配列（即位置/矿名）连接表的初步工作。

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 行用于银、铜和钴；1 行来自表 B，2 行来自表 C）。

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

重申我之前的观点的最终目标是拥有一个包含来自所有三个表的行和坐标的最终表。

我在 Postgres 9.5.14 中使用 pgAdmin4。

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

postgresql join

1 个回答

  1. Best Answer

    我的第一印象是您应该稍微重新设计一下表格。我不确定 SPLIT_PART 做了什么，但听起来好像它抓住了字符串的一部分，并且在 ON 子句中具有这样的功能对性能来说真的很糟糕。假设表 A 和 B 各有 1000 行：

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

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

    要从答案中删除冗余，您可以使用 distinct，但在以下情况下您仍然会获得冗余信息：

    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)
     , ...

    对于中间的一个，您将不得不排除最小和最大的一个。

    • 3

相关问题