如何用ClickHouse SQL替换URL指定模式？解析现有方案并求优化

我正在处理一个包含url字段的表，需要替换每个URL中存在的特定模式。

示例表 #

| url                         |
|-----------------------------|
| url-1.html                  |
| url-2.html                  |
| url-3.html?q=my+string-1    |
| url-3.html?q=my+string-2    |
| url-4.html?q=other+string   |
| url-4.html?q=no-string      |

需要替换的模式 #

• text1 → textone
• my+string → mystringReplaced
• other+string → otherstringReplaced

尝试的SQL #

WITH
['text1', 'my+string', 'other+string'] AS replaced,
['textone', 'mystringReplaced', 'otherstringReplaced'] AS substitute,
['url-1.html', 'url-2.html', 'url-3.html?q=my+string-1', 'url-3.html?q=my+string-2', 'url-4.html?q=other-string', 'url-4.html?q=no-string'] AS urls
SELECT
FinalURL.2 as FinalURL
FROM
(
    SELECT
        arrayMap((x, y) -> if(positionCaseInsensitive(urllTemp, x) > 0,(x, replaceAll(urllTemp,replaced[toInt64(y)],substitute[toInt64(y)])),(urllTemp,urllTemp)),replaced,range(1, length(replaced) + 1)) AS FinalURL
    FROM
    (
        SELECT arrayJoin(urls) AS urllTemp
    )
)
 FORMAT TabSeparatedWithNames

当前执行结果 #

期望结果 #

| FinalURL                          |
|-----------------------------------|
| url-1.html                        |
| url-2.html                        |
| url-3.html?q=mystringReplaced-1   |
| url-3.html?q=mystringReplaced-2   |
| url-4.html?q=otherstringReplaced  |
| url-4.html?q=no-string            |

请问为何当前SQL会得到这样的结果？是否有更高效的实现方式？

问题原因 #

你当前的SQL用arrayMap对每个替换规则独立处理原URL，生成对应结果后组合成数组，而非叠加所有匹配规则的替换效果：

1. arrayMap遍历replaced数组的每个元素，对单个URL生成一组独立处理后的结果，最终输出包含3个元素的数组。
2. 你取数组第2个元素的逻辑，并没有把多个替换的结果串联，只是拿到了第二个规则处理后的独立结果，所以最终每条URL对应一个多元素数组，而非单一替换后的URL。

高效实现方式 #

推荐两种简洁高效的写法，都能输出你期望的单条结果：

方法1：用arrayReduce链式应用替换规则 #

通过arrayReduce把所有替换规则依次作用在URL上，实现多规则叠加替换：

WITH
    ['text1', 'my+string', 'other+string'] AS replaced,
    ['textone', 'mystringReplaced', 'otherstringReplaced'] AS substitute,
    ['url-1.html', 'url-2.html', 'url-3.html?q=my+string-1', 'url-3.html?q=my+string-2', 'url-4.html?q=other+string', 'url-4.html?q=no-string'] AS urls
SELECT
    arrayReduce(
        (acc, idx) -> replaceAll(acc, replaced[idx], substitute[idx]),
        urllTemp,
        range(length(replaced))
    ) AS FinalURL
FROM (SELECT arrayJoin(urls) AS urllTemp)
FORMAT TabSeparatedWithNames

方法2：直接嵌套replaceAll（适合规则数量少的场景） #

如果替换规则不多，直接嵌套replaceAll更直观，性能也有保障：

WITH
    ['url-1.html', 'url-2.html', 'url-3.html?q=my+string-1', 'url-3.html?q=my+string-2', 'url-4.html?q=other+string', 'url-4.html?q=no-string'] AS urls
SELECT
    replaceAll(
        replaceAll(
            replaceAll(urllTemp, 'text1', 'textone'),
            'my+string', 'mystringReplaced'
        ),
        'other+string', 'otherstringReplaced'
    ) AS FinalURL
FROM (SELECT arrayJoin(urls) AS urllTemp)
FORMAT TabSeparatedWithNames

两种方法都会让每个URL返回单条记录，所有匹配的模式都会被正确替换。

内容的提问来源于stack exchange，提问作者lino