PostgreSQL查询需求：提取6/7位字符串末位数字并统计次数

实现步骤 #

• 筛选长度为6或7位的目标记录
• 根据字符串长度提取对应位置的字符：7位取末尾字符，6位取倒数第二位字符
• 仅保留提取结果为数字的记录
• 按提取的数字分组统计次数

完整查询语句 #

假设表名为 your_table，字符串列名为 target_column，执行以下SQL：

SELECT
  extracted_digit,
  COUNT(*) AS occurrence_count
FROM (
  SELECT
    CASE
      WHEN LENGTH(target_column) = 7 THEN RIGHT(target_column, 1)
      WHEN LENGTH(target_column) = 6 THEN SUBSTRING(target_column FROM LENGTH(target_column) - 1 FOR 1)
    END AS extracted_digit
  FROM your_table
  WHERE LENGTH(target_column) IN (6, 7)
) AS digit_extract
WHERE extracted_digit ~ '^\d$' -- 过滤非数字结果
GROUP BY extracted_digit
ORDER BY extracted_digit;

关键部分说明 #

• LENGTH(target_column) IN (6,7)：精准筛选需要处理的记录范围
• CASE 逻辑：
  • 7位字符串用 RIGHT() 快速获取最后一位
  • 6位字符串用 SUBSTRING() 定位到倒数第二位（LENGTH(target_column)-1 是该位置的起始索引）
• ~ '^\d$'：使用PostgreSQL正则表达式验证提取的字符是0-9的数字，避免统计非数字字符
• GROUP BY + COUNT(*)：完成数字出现次数的统计

示例验证 #

针对你给出的示例数据：

'BBB0123'（7位）提取'3'，'AA123A'（6位）提取'3'
查询结果会输出：
| extracted_digit | occurrence_count |
|-----------------|------------------|
| 3 | 2 |

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