查询指定日期范围内出现至少3次的邮件记录

针对你的需求——从含3500万+条记录的表中，筛选2024年6月期间、关联至少3个不同PROPERTY的邮件，以下是补充计数逻辑后的SQL方案：

基础统计：仅获取符合条件的邮件及计数 #

SELECT EMAIL, COUNT(PROPERTY) AS property_count
FROM your_table_name
WHERE LAST_SENT_DATE >= '2024-06-01' 
  AND LAST_SENT_DATE < '2024-07-01'
GROUP BY EMAIL
HAVING COUNT(PROPERTY) >= 3;

关键逻辑说明： #

• 日期过滤：用>= '2024-06-01' AND < '2024-07-01'精准匹配6月全月数据，避免BETWEEN可能包含7月1日零点记录的问题。
• 计数逻辑：由于同一邮件的PROPERTY无重复，COUNT(PROPERTY)直接等价于该邮件在6月的出现次数。
• 分组过滤：GROUP BY EMAIL按邮件聚合，HAVING子句筛选出计数≥3的结果。

获取符合条件的所有原始记录 #

如果需要保留每条记录的完整信息（而非仅统计数），可以用以下两种方式：

方式1：子查询关联 #

SELECT t.*
FROM your_table_name t
INNER JOIN (
    SELECT EMAIL
    FROM your_table_name
    WHERE LAST_SENT_DATE >= '2024-06-01' 
      AND LAST_SENT_DATE < '2024-07-01'
    GROUP BY EMAIL
    HAVING COUNT(PROPERTY) >= 3
) qualified_emails ON t.EMAIL = qualified_emails.EMAIL
WHERE t.LAST_SENT_DATE >= '2024-06-01' 
  AND t.LAST_SENT_DATE < '2024-07-01';

方式2：窗口函数（推荐查看计数+原始记录的场景） #

SELECT EMAIL, PROPERTY, LAST_SENT_DATE, property_count
FROM (
    SELECT 
        EMAIL, 
        PROPERTY, 
        LAST_SENT_DATE,
        COUNT(PROPERTY) OVER (PARTITION BY EMAIL) AS property_count
    FROM your_table_name
    WHERE LAST_SENT_DATE >= '2024-06-01' 
      AND LAST_SENT_DATE < '2024-07-01'
) filtered_records
WHERE property_count >= 3;

大表性能优化建议 #

针对3500万+条记录的表，需注意查询效率：

• 建立联合索引：创建(EMAIL, LAST_SENT_DATE)或(LAST_SENT_DATE, EMAIL)的联合索引，让分组和日期过滤都能利用索引加速。
• 优先使用覆盖索引：如果仅需统计EMAIL和计数，可创建包含PROPERTY的覆盖索引（如(EMAIL, LAST_SENT_DATE, PROPERTY)），避免回表查询。
• 避免不必要的字段：查询时只选择需要的字段，减少数据传输量。

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