如何在正则表达式上联接表

一尘不染

如何在正则表达式上联接表

sql

假设我有两个用于消息的表msg和用于移动网络代码的mnc。他们没有任何关系。但我想加入他们

SELECT msg.message,
    msg.src_addr,
    msg.dst_addr,
    mnc.name,
FROM "msg"
JOIN "mnc"
ON array_to_string(regexp_matches(msg.src_addr || '+' || msg.dst_addr, '38(...)'), '') = mnc.code

但是查询失败，并显示以下错误：

psql:marketing.sql:28: ERROR:  argument of JOIN/ON must not return a set
LINE 12: ON array_to_string(regexp_matches(msg.src_addr || '+' || msg...

有没有办法进行这种加入？还是我走错路了？

阅读 227

2021-03-17

共1个答案

一尘不染

正如@Milen已经提到的那样regexp_matches()，可能是出于您的目的使用的功能错误。您需要一个简单的正则表达式match（~）。实际上，LIKE运算符（~~）会更快：

LIKE大概最快

SELECT msg.message
      ,msg.src_addr
      ,msg.dst_addr
      ,mnc.name
FROM   mnc
JOIN   msg ON msg.src_addr ~~ ('%38' || mnc.code || '%')
           OR msg.dst_addr ~~ ('%38' || mnc.code || '%')
WHERE  length(mnc.code) = 3

此外，您只需要mnc.code3个字符。

使用正则表达式

您可以使用正则表达式编写相同的内容，但是绝对会更慢。这是一个接近原始示例的工作示例：

SELECT msg.message
      ,msg.src_addr
      ,msg.dst_addr
      ,mnc.name
FROM   mnc
JOIN   msg ON (msg.src_addr || '+' || msg.dst_addr) ~ (38 || mnc.code)
           AND length(mnc.code) = 3

这也需要msg.src_addr并且msg.dst_addr是必须的NOT NULL。

第二个查询演示了附加检查如何length(mnc.code) = 3进入JOIN条件或WHERE子句。这里效果一样。

使用regexp_matches（）

您 可以使用以下方法
进行这项工作regexp_matches()：

SELECT msg.message
      ,msg.src_addr
      ,msg.dst_addr
      ,mnc.name
FROM   mnc
JOIN   msg ON EXISTS (
    SELECT * 
    FROM   regexp_matches(msg.src_addr ||'+'|| msg.dst_addr, '38(...)', 'g') x(y)
    WHERE  y[1] = mnc.code
    )

但是相比起来，它会很慢-大概是这样。

说明：
您的regexp_matches（）表达式仅返回 第一个
匹配项的所有捕获的子字符串的数组。由于仅捕获一个子字符串（模式中的一对方括号），因此，您将独家获得 带有一个element的数组 。

您可以通过其他“全局”开关获得 所有匹配项 'g'
-但要包含多行。因此，您需要一个子选择来测试所有（或汇总）它们。将其放入EXISTS-半联接中，您将达到所需的目标。

也许您可以通过对这三个方面的 性能测试 进行报告？为此使用EXPLAIN
ANALYZE。

2021-03-17