PostgreSQL中如何统计text字段current_ids内的元素数量

针对你存储在current_ids字段（格式如|ABX1-001|ABX1-002|ABX1-004|）的元素统计需求，这里提供两种简单高效的实现方式：

方法一：基于字符串长度计算 #

通过统计分隔符|的出现次数推导元素数量，逻辑是分隔符总数减1（因为首尾各有一个分隔符）：

SELECT
    day,
    study_id,
    site_id,
    current_ids,
    -- 计算|的数量后减1，得到元素总数
    (length(current_ids) - length(replace(current_ids, '|', ''))) - 1 AS current_id_count
FROM candidates;

逻辑说明 #

1. length(current_ids)：获取字段的总长度
2. replace(current_ids, '|', '')：去掉所有|字符
3. 总长度减去替换后的长度，得到|的总个数
4. 最后减1，排除首尾的分隔符，得到实际元素数量

方法二：通过数组转换计算 #

将字符串按|拆分为数组，再通过数组长度推导元素数量：

SELECT
    day,
    study_id,
    site_id,
    current_ids,
    -- 拆分字符串为数组后，长度减1得到元素总数
    array_length(string_to_array(current_ids, '|'), 1) - 1 AS current_id_count
FROM candidates;

逻辑说明 #

1. string_to_array(current_ids, '|')：将字符串按|拆分，得到类似['', 'ABX1-001', 'ABX1-002', '']的数组
2. array_length(..., 1)：获取数组的长度
3. 减1后得到实际元素数量

验证示例 #

拿你提供的示例数据来看：

• 第一条数据的total_ids为|ABX1-001|ABX1-002|ABX1-004|ABX1-004|，两种方法计算后都会得到4，和total字段的数值一致
• 第二条数据的total_ids有9个元素，计算结果也会和total字段匹配

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