ClickHouse Cloud中查找重复5次及以上字符的替代方案（RE2不支持反向引用）

问题背景 #

需要判断字符串中是否存在连续重复5次及以上的字符，示例字符串如'12344444156'、'abcrrrrrggds'。原本使用通用正则表达式.*(.)\1{4,}.*，但ClickHouse采用的RE2引擎不支持反向引用，执行以下SQL时出现编译错误：

WITH '12344444156' as str
SELECT str, extract(str, '.*(.)\\1{4,}.*');

报错信息：

SQL Error [427] [07000]: Code: 427. DB::Exception: OptimizedRegularExpression: cannot compile re2: .(.)\1{4,}., error: invalid escape sequence: \1. Look at https://github.com/google/re2/wiki/Syntax for reference. Please note that if you specify regex as an SQL string literal, the slashes have to be additionally escaped. For example, to match an opening brace, write '(' -- the first slash is for SQL and the second one is for regex: While processing '12344444156' AS str, extract(str, '.(.)\1{4,}.'). (CANNOT_COMPILE_REGEXP) (version 24.6.1.4410 (official build))
, server ClickHouseNode [uri=https://w2z74jyoma.ap-southeast-2.aws.clickhouse.cloud:8443/default, options={use_server_time_zone=false,use_time_zone=false}]@248459710

解决方案 #

方法1：枚举字符构造正则表达式 #

直接构造匹配任意字符连续重复5次及以上的正则，避开反向引用：

-- 使用extract提取包含重复段的字符串
WITH '12344444156' as str
SELECT 
    str,
    extract(str, '.*(?:0{5,}|1{5,}|2{5,}|3{5,}|4{5,}|5{5,}|6{5,}|7{5,}|8{5,}|9{5,}|a{5,}|b{5,}|c{5,}|d{5,}|e{5,}|f{5,}|g{5,}|h{5,}|i{5,}|j{5,}|k{5,}|l{5,}|m{5,}|n{5,}|o{5,}|p{5,}|q{5,}|r{5,}|s{5,}|t{5,}|u{5,}|v{5,}|w{5,}|x{5,}|y{5,}|z{5,}|A{5,}|B{5,}|C{5,}|D{5,}|E{5,}|F{5,}|G{5,}|H{5,}|I{5,}|J{5,}|K{5,}|L{5,}|M{5,}|N{5,}|O{5,}|P{5,}|Q{5,}|R{5,}|S{5,}|T{5,}|U{5,}|V{5,}|W{5,}|X{5,}|Y{5,}|Z{5,})') as matched_segment;

如果只需要布尔值判断（是否存在重复），用match函数更高效：

WITH '12344444156' as str
SELECT 
    str,
    match(str, '.*(?:0{5,}|1{5,}|2{5,}|3{5,}|4{5,}|5{5,}|6{5,}|7{5,}|8{5,}|9{5,}|a{5,}|b{5,}|c{5,}|d{5,}|e{5,}|f{5,}|g{5,}|h{5,}|i{5,}|j{5,}|k{5,}|l{5,}|m{5,}|n{5,}|o{5,}|p{5,}|q{5,}|r{5,}|s{5,}|t{5,}|u{5,}|v{5,}|w{5,}|x{5,}|y{5,}|z{5,}|A{5,}|B{5,}|C{5,}|D{5,}|E{5,}|F{5,}|G{5,}|H{5,}|I{5,}|J{5,}|K{5,}|L{5,}|M{5,}|N{5,}|O{5,}|P{5,}|Q{5,}|R{5,}|S{5,}|T{5,}|U{5,}|V{5,}|W{5,}|X{5,}|Y{5,}|Z{5,})') as has_repeated_chars;

方法2：拆分字符统计连续重复次数 #

通过拆分字符串为单个字符，结合窗口函数统计连续相同字符的次数，判断是否存在≥5的情况：

WITH '12344444156' as str
SELECT 
    str,
    max(continuous_count) >= 5 as has_repeated_chars
FROM (
    SELECT 
        char,
        count(*) OVER (PARTITION BY grp) as continuous_count
    FROM (
        SELECT 
            substring(str, number, 1) as char,
            number - row_number() OVER (ORDER BY number) as grp
        FROM numbers(length(str))
    )
);

该方法无需枚举字符，适合包含特殊字符的场景，通用性更强。

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