高效可维护SQL方案：识别缺失质量标签的商品编码

现有数据表 #

表 article_description #

表 article_labels #

需求说明 #

需要通过匹配article_description中的关键词与对应质量标签，识别至少一个locale下缺失对应标签的article_code。例如含关键词"ASC"的商品应对应标签"QM0029"，这类标签-关键词对约30组。

已实现单组校验SQL，但通过UNION ALL复制查询的方式维护性极差，需更高效可维护的批量查询方案。

约束条件 #

• 商品描述均为大写，无需区分大小写，仅含大写字母/数字
• 原则上每个article_code对应0或1个质量标签，支持多标签更优
• 数据规模：约10万条商品编码、5种locale，需保证查询效率
• 关键词需为独立单词（前后有空格，可在字符串首尾/中间）
• 预期输出示例：

现有单组校验SQL #

WITH article_labels AS (
    SELECT article_code, locale, label_code
    FROM article_label
    WHERE label_code = 'QM0029'
), article_descriptions AS (
    SELECT article_code, locale
    FROM article_description
    WHERE article_description ~ '(^| )ASC( |$)'
      AND article_description IS NOT NULL
)
SELECT DISTINCT t1.article_code, 'ASC label missing' AS check_value
FROM article_descriptions AS t1
LEFT JOIN article_labels AS t2
    ON t1.article_code = t2.article_code
    AND t1.locale = t2.locale
WHERE t2.label_code IS NULL

标签-关键词映射表 #

解决方案 #

1. 构建可维护的映射表 #

首先将标签-关键词对存入一个持久化的映射表（比如命名为label_keyword_mapping），后续新增/修改标签-关键词对只需维护这张表，无需修改SQL逻辑。

2. 批量校验SQL实现 #

WITH mapped_keywords AS (
    -- 处理关键词的正则匹配规则，转义特殊字符（如*）
    SELECT
        label,
        keyword,
        '(^| )' || regexp_replace(keyword, '([\*\.\?\+\(\)\[\]\{\}\|\\])', '\\\1', 'g') || '( |$)' AS keyword_regex
    FROM label_keyword_mapping
),
article_desc_matches AS (
    -- 匹配所有含对应关键词的商品+locale组合
    SELECT DISTINCT
        ad.article_code,
        ad.locale,
        mk.keyword
    FROM article_description ad
    JOIN mapped_keywords mk
        ON ad.article_description ~ mk.keyword_regex
        AND ad.article_description IS NOT NULL
),
article_label_check AS (
    -- 关联已有标签，标记是否缺失
    SELECT
        adm.article_code,
        adm.keyword,
        CASE WHEN al.label_code IS NULL THEN 1 ELSE 0 END AS is_missing
    FROM article_desc_matches adm
    LEFT JOIN article_labels al
        ON adm.article_code = al.article_code
        AND adm.locale = al.locale
        AND al.label_code IN (SELECT label FROM label_keyword_mapping WHERE keyword = adm.keyword)
)
-- 筛选至少一个locale缺失标签的商品，生成提示信息
SELECT DISTINCT
    article_code,
    keyword || ' label missing' AS check_value
FROM article_label_check
WHERE is_missing = 1;

3. 性能优化建议 #

• 给article_description的article_code、locale字段建立联合索引；给article_description字段建立全文索引或基于正则的函数索引（如PostgreSQL的CREATE INDEX idx_article_desc_regex ON article_description (article_description);）
• 给article_labels的article_code、locale、label_code建立联合索引
• 给label_keyword_mapping的keyword字段建立索引，加速关联查询

说明 #

• 该方案通过映射表统一管理标签-关键词对，新增规则只需在映射表中添加记录，无需修改SQL
• 自动处理关键词中的特殊字符，避免正则匹配出错
• 满足“至少一个locale缺失则返回”的需求，同时支持多标签场景

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