如何筛选邮件日志数据集：按prizedraw出现规则保留目标记录

核心思路是：保留所有含prizedraw的记录，但如果记录包含短语you have been entered in our prizedraw，则必须确保prizedraw在该记录中出现至少2次才保留。

通用SQL实现（兼容多数数据库） #

以下语句通过计算字符串长度差来统计prizedraw的出现次数，适配MySQL、SQL Server等主流数据库：

SELECT *
FROM your_table
WHERE 
  emaildescription LIKE '%prizedraw%'
  AND (
    -- 情况1：不包含指定短语，直接保留
    emaildescription NOT LIKE '%you have been entered in our prizedraw%'
    OR 
    -- 情况2：包含指定短语，但prizedraw出现次数≥2才保留
    (
      emaildescription LIKE '%you have been entered in our prizedraw%'
      AND (LENGTH(emaildescription) - LENGTH(REPLACE(emaildescription, 'prizedraw', ''))) / LENGTH('prizedraw') >= 2
    )
  );

针对不同数据库的优化写法 #

• PostgreSQL：可以用REGEXP_COUNT直接统计出现次数，更简洁：

  SELECT *
  FROM your_table
  WHERE 
    emaildescription LIKE '%prizedraw%'
    AND (
      emaildescription NOT LIKE '%you have been entered in our prizedraw%'
      OR (
        emaildescription LIKE '%you have been entered in our prizedraw%'
        AND REGEXP_COUNT(emaildescription, 'prizedraw', 'gi') >= 2
      )
    );
  

  其中'gi'表示不区分大小写匹配，若不需要可去掉该参数。

• SQL Server：将LENGTH替换为LEN即可：

  SELECT *
  FROM your_table
  WHERE 
    emaildescription LIKE '%prizedraw%'
    AND (
      emaildescription NOT LIKE '%you have been entered in our prizedraw%'
      OR (
        emaildescription LIKE '%you have been entered in our prizedraw%'
        AND (LEN(emaildescription) - LEN(REPLACE(emaildescription, 'prizedraw', ''))) / LEN('prizedraw') >= 2
      )
    );
  

逻辑解释 #

1. 第一个条件emaildescription LIKE '%prizedraw%'确保只筛选含目标关键词的记录；
2. 分两种场景处理：
   • 不含指定短语的记录直接保留；
   • 含指定短语的记录，通过长度差计算prizedraw的出现次数（原字符串长度减去替换掉所有prizedraw后的长度，再除以prizedraw的长度，得到出现次数），只有次数≥2时才保留。

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