如何用SQL正则表达式关联两表获取匹配的uaid？

表结构与数据 #

myTable1 #

创建语句：

CREATE TABLE myTable1
(
    ua  text,
    platform text
);

INSERT INTO myTable1
    ("ua", "platform")
VALUES ('name:Safari,version:16.2', 'desktop'),
       ('name:Safari,version:15.2', 'desktop'),
       ('name:Firefox,version:105.0', 'desktop'),
       ('name:Chrome,version:100.0.4798.88', 'desktop'),
       ('name:Chrome,version:100.0.7898.70', 'desktop'),
       ('name:Chrome,version:99.0.6723.59', 'desktop')
;

数据：

myTable2 #

创建语句：

CREATE TABLE myTable2
(
    uaid  int,
    keyword      text,
    platform         text
);

INSERT INTO myTable2
    ("uaid", "keyword", "platform")
VALUES ('7014', '%Windows%Chrome/99.0%', 'Chrome'),
       ('7014', '%Maintosh%Chrome/99.0%', 'Chrome'),
       ('7014', '%X11%Chrome/99.0%', 'Chrome'),
       ('7068', '%Windows%Chrome/100.0%', 'Chrome'),
       ('7068', '%Maintosh%Chrome/100.0%', 'Chrome'),
       ('7026', '%Windows%Firefox/105.0%', 'Firefox'),
       ('7026', '%Macintosh%Firefox/105.0%', 'Firefox'),
       ('2015', '%Android%Firefox/105.0%', 'Firefox Mobile'),
       ('7030', '%(Macintosh;%) Version/16.2 Safari/%', 'Safari'),
       ('7030', '%(Macintosh;%) Version/16.2.1 Safari/%', 'Safari'),
       ('7036', '%(Macintosh;%) Version/15.2 Safari/%', 'Safari')
;

数据：

期望结果 #

解决方案：用正则表达式关联获取uaid #

核心逻辑是先从myTable1的ua字段提取浏览器名称和主版本号，再将myTable2的LIKE匹配规则转为正则表达式，关联匹配后去重得到对应uaid。

以PostgreSQL为例（支持~正则运算符） #

SELECT 
    t1.ua,
    t1.platform,
    DISTINCT ON (t1.ua) t2.uaid
FROM myTable1 t1
JOIN myTable2 t2 ON 
    -- 匹配浏览器名称
    t2.platform = substring(t1.ua FROM 'name:([^,]+)')
    -- 将LIKE通配符转为正则规则，匹配版本信息
    AND replace(replace(t2.keyword, '%', '.*'), '_', '.') ~ 
        concat(substring(t1.ua FROM 'name:([^,]+)'), '/', substring(t1.ua FROM 'version:(\d+\.\d+)'))
ORDER BY t1.ua, t2.uaid;

以MySQL为例（支持REGEXP运算符） #

SELECT 
    t1.ua,
    t1.platform,
    DISTINCT t2.uaid
FROM myTable1 t1
JOIN myTable2 t2 ON 
    -- 提取浏览器名称并匹配
    t2.platform = SUBSTRING_INDEX(SUBSTRING_INDEX(t1.ua, ',', 1), ':', -1)
    -- 转换LIKE通配符为正则，匹配版本
    AND REPLACE(REPLACE(t2.keyword, '%', '.*'), '_', '.') REGEXP 
        CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.ua, ',', 1), ':', -1), '/', SUBSTRING_INDEX(SUBSTRING_INDEX(t1.ua, ',', 2), ':', -1))
GROUP BY t1.ua, t1.platform, t2.uaid;

逻辑说明 #

1. 提取浏览器信息：从myTable1.ua中拆分出浏览器名称（如Safari）和主版本号（如16.2）。
2. 转换匹配规则：将myTable2.keyword中的%替换为正则的.*（匹配任意长度字符），_替换为.（匹配单个字符），把LIKE模式转为正则模式。
3. 关联去重：通过浏览器名称关联两张表，用正则验证版本匹配，最后去重得到每个ua对应的唯一uaid。

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