sql - Postgresql 加入 2 个具有 km 范围的表

Question

我需要帮助连接不同距离数字的 KM 范围内的两个表。范围不同，我需要将功能 1 与功能 2 和 3 结合使用。

所以我需要一个范围连接，但不知道如何解决这个问题。特征 2 和 3 可能存在于特征 1 的 2 个区域中，那么特征 1 应该是“1”。但是我没有这样做的好主意。

所以 NR 1 从 0.0 到 0.1 在 feature1 1 中，f2=A 和 f3=X但是从4.2到6.0的NR 3在feature1 2和1中，所以feature1应该是1

---

NR  fromKM  toKM    feature1
1   0       1.4     1
1   1.4     3.8     2
1   3.8     7.2     1
1   7.2     36.7    2
2   0       14.6    1
3   0       5.2     2
3   5.2     10.6    1

和

NR  fromKM  toKM    featrure2   featrure3
1   0       0.1     A           X
1   0.1     0.3     B           Y
1   0.5     1.3     C           X
1   1.4     2.0     A           X
1   4.0     7.2     C           X
2   0.1     0.4     A           Y
2   0.4     1.6     C           X
3   0.1     4.2     B           X
3   4.2     6.0     B           Y
3   6.0     10.2    A           Y

所以输出应该是

NR  fromKM  toKM    feature1    featrure2   featrure3
1   0       0.1     1           A           X
1   0.1     0.3     1           B           Y
1   0.5     1.3     1           C           X
1   1.4     2.0     2           A           X
1   4.0     7.2     1           C           X
2   0.1     0.4     1           A           Y
2   0.4     1.6     1           C           X
3   0.1     4.2     2           B           X
3   4.2     6.0     1           B           Y
3   6.0     10.2    1           A           Y

Answer 1

您需要使用一个等式(对于 NR)和两个不等式(对于 fromKM 和 toKM)来JOIN。您通常加入等式，但这实际上不是必需的，您可以加入返回 bool 结果的任何表达式。

假设您的表名为 t1 和 t2，您将使用:

SELECT
    t1.NR, t2.fromKM, t2.toKM, feature1, feature2, feature3
FROM
    t1 
    JOIN t2
        ON t2.NR = t1.NR AND t2.fromKM >= t1.fromKM AND t2.toKM <= t1.toKM
ORDER BY
    t1.NR, t2.fromKM

...你会得到:

nr | fromkm | tokm | feature1 | feature2 | feature3-: | -----: | ---: | -------: | :------- | :------- 1 |    0.0 |  0.1 |        1 | A        | X        1 |    0.1 |  0.3 |        1 | B        | Y        1 |    0.5 |  1.3 |        1 | C        | X        1 |    1.4 |  2.0 |        2 | A        | X        1 |    4.0 |  7.2 |        1 | C        | X        2 |    0.1 |  0.4 |        1 | A        | Y        2 |    0.4 |  1.6 |        1 | C        | X        3 |    0.1 |  4.2 |        2 | B        | X        3 |    6.0 | 10.2 |        1 | A        | Y       

Which is your desired output (except for one C, which I guess was your smallish mistake).

You can check the setup and test it at dbfiddle here

---

If you would like maximum performance, you can use some specific PostgreSQL features, such as

1. Use the numranges data type, and its "overlaps" (&&) operator, or the "contains range" (@>) one.
2. Use GIST index on the numranges

That would mean doing:

CREATE INDEX idx_t1 ON t1 USING gist (NR, (numrange(fromKM, toKM, '[]'))) ;
CREATE INDEX idx_t2 ON t2 USING gist (NR, (numrange(fromKM, toKM, '[]'))) ;

并通过以下方式查询:

SELECT
    t1.NR, t2.fromKM, t2.toKM, feature1, feature2, feature3
FROM
    t1 
    JOIN t2
        ON t2.NR = t1.NR AND numrange(t1.fromkm, t1.tokm, '[]') && numrange(t2.fromkm, t2.tokm, '[]')
ORDER BY
    t1.NR, t2.fromKM ;

当您有大表时，这很有意义。您可以在 dbfiddle here 查看第二个专门选项