如何在PSQL中查找、提取并统计文本字段内嵌入的URL

核心思路是用PostgreSQL内置的正则匹配函数regexp_matches提取文本中的URL，再做分组统计即可，实现代码如下：

基础实现（统计每个URL的出现次数） #

WITH extracted_urls AS (
    SELECT 
        id,
        body,
        -- 正则提取所有符合规则的URL，g标记表示匹配同一段文本内的多个结果
        (regexp_matches(body, '[a-zA-Z][a-zA-Z0-9+.-]*://[^\\s<>''"]+', 'g'))[1] AS the_url
    FROM messages
    WHERE body LIKE '%://%'
)
SELECT 
    the_url,
    -- 统计URL出现在多少条不同的消息中
    COUNT(DISTINCT id) AS message_count,
    -- 统计URL累计出现的总次数（同一条消息内多次出现算多次）
    COUNT(*) AS total_count
FROM extracted_urls
GROUP BY the_url
ORDER BY total_count DESC;

调整说明 #

• 如果你只需要匹配http/https协议的URL，可以把正则的开头替换为https?://，缩小匹配范围，避免匹配到ftp、自定义协议等不需要的内容。
• 同一个URL会对应多条不同的消息，所以不能直接在分组后查询原始id和body字段，如果需要关联查看URL对应的原始消息，可以用聚合函数返回关联ID列表，示例如下：

WITH extracted_urls AS (
    SELECT 
        id,
        (regexp_matches(body, 'https?://[^\\s<>''"]+', 'g'))[1] AS the_url
    FROM messages
    WHERE body LIKE '%://%'
)
SELECT 
    the_url,
    COUNT(*) AS total_count,
    array_agg(DISTINCT id) AS related_message_id_list
FROM extracted_urls
GROUP BY the_url;

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