PostgreSQL 13+中求多组#分隔字符串的最长完整令牌前缀

需求说明 #

在PostgreSQL 13+环境中，有一张行数不足100的表，表中存储用#分隔的字符串（无NULL值）。需要找出所有行从开头匹配的最长完整令牌前缀——必须匹配完整令牌，不允许仅匹配令牌的部分内容：

• 示例1：若多行值均为ABC#DEF#xxx#BAIT，最长前缀为ABC#DEF（或ABC#DEF#均可）
• 示例2：若两行值分别为ABC#123和ADE#456，最长前缀为空（首个令牌不匹配）

当前问题：使用自定义聚合函数lcp会返回部分令牌匹配的结果（如示例2中返回A），不符合需求；同时希望避免编写嵌套20层的CASE语句，寻求合理解决方案。

测试基础查询：

WITH source AS (
  SELECT 'ABC#DEF#123' AS value
  UNION ALL 
  SELECT 'ABC#DEF#456'
)

SELECT my_func(*) 
FROM source
GROUP BY value

解决方案 #

以下SQL通过内置函数实现需求，无需自定义聚合或嵌套CASE：

WITH source AS (
  SELECT 'ABC#DEF#123' AS value
  UNION ALL 
  SELECT 'ABC#DEF#456'
),
-- 拆分字符串为令牌数组，记录总行数
tokenized AS (
  SELECT 
    string_to_array(value, '#') AS tokens,
    COUNT(*) OVER () AS total_rows
  FROM source
),
-- 生成每个行的所有可能前缀令牌序列
prefixes AS (
  SELECT
    array_slice(tokens, 1, i) AS prefix_tokens,
    total_rows
  FROM tokenized,
       generate_series(1, array_length(tokens, 1)) AS i
),
-- 筛选所有行共有的前缀（出现次数等于总行数）
common_prefixes AS (
  SELECT
    prefix_tokens,
    COUNT(*) AS match_count
  FROM prefixes
  GROUP BY prefix_tokens
  HAVING COUNT(*) = MAX(total_rows)
),
-- 取最长的公共前缀
longest_prefix AS (
  SELECT prefix_tokens
  FROM common_prefixes
  ORDER BY array_length(prefix_tokens, 1) DESC
  LIMIT 1
)
-- 拼接为最终字符串，同时处理无公共前缀的情况
SELECT 
  array_to_string(prefix_tokens, '#') AS longest_full_token_prefix,
  array_to_string(prefix_tokens, '#') || '#' AS longest_full_token_prefix_with_trailing_hash
FROM longest_prefix
UNION ALL
SELECT '', ''
WHERE NOT EXISTS (SELECT 1 FROM longest_prefix);

方案说明 #

1. 拆分令牌：用string_to_array将每个字符串按#拆分为令牌数组，同时统计总行数
2. 生成前缀：通过generate_series生成每个令牌数组的所有前缀（从第1个令牌到全部令牌的组合）
3. 筛选公共前缀：分组统计每个前缀的出现次数，仅保留出现次数等于总行数的前缀（即所有行都包含的前缀）
4. 取最长前缀：按前缀的令牌数量倒序排序，取最长的那个
5. 结果拼接：将令牌数组拼接为字符串，同时处理无公共前缀时返回空字符串的场景

该方案适用于任意数量的令牌，无需硬编码逻辑，且针对行数<100的场景性能完全达标。

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