如何在MySQL中筛选所有行均处于两个日期区间内的分组？

我需要从local_data_store.result_api表中筛选出满足以下条件的结果：

• 按account_id分组（共享键为account_id）
• 分组内所有记录的posted_date必须落在「不早于90天前，且不晚于7天前」的区间内
• 最终返回有效分组下的account_id和company_id

有效/无效分组示例 #

有效分组（account_id=1234） #

所有记录的posted_date都符合区间要求：

account_id,company_id,posted_date
1234,A,2018-02-28
1234,B,2018-03-13
1234,C,2018-04-23
1234,D,2018-05-15

无效分组（account_id=5678） #

分组内存在超出区间的日期（如2018-02-01早于90天前，2018-05-21晚于7天前），因此整个account_id需被排除：

account_id,company_id,posted_date
5678,Z,2018-02-01
5678,Y,2018-03-13
5678,X,2018-04-23
5678,W,2018-05-21

现有查询的问题 #

第一版子查询方案 #

SELECT DISTINCT account_id, company_id 
FROM local_data_store.result_api 
WHERE account_id NOT IN ( 
    SELECT account_id 
    FROM local_data_store.result_api 
    GROUP BY account_id 
    HAVING posted_date > DATE_SUB(NOW(), INTERVAL 7 DAY) 
) 
AND account_id IN ( 
    SELECT account_id 
    FROM local_data_store.result_api 
    GROUP BY account_id 
    HAVING posted_date > DATE_SUB(NOW(), INTERVAL 90 DAY) 
) 
GROUP BY account_id, company_id 
LIMIT 100000;

这里的核心问题是：分组后直接用HAVING posted_date > ...只会取分组内的某一条记录的posted_date（比如MySQL中默认取第一条），无法判断所有记录是否符合条件，导致筛选逻辑不准确。

无嵌套查询尝试（性能超时） #

SELECT DISTINCT account_id, company_id, 
COUNT(ra1.posted_date > DATE_SUB(NOW(), INTERVAL 90 DAY)) AS day90, 
COUNT(ra1.posted_date > DATE_SUB(NOW(), INTERVAL 7 DAY)) as day7 
FROM local_data_store.result_api ra1 
GROUP BY posted_date, account_id;

这个查询的问题有三点：

1. 分组逻辑错误：按posted_date和account_id分组会把每个日期单独拆分，不符合按account_id整体分组的需求；
2. COUNT()用法无效：COUNT(condition)统计的是条件为真的行数，但错误的分组导致统计结果没有意义；
3. 缺少合适索引，导致375k行数据也出现数据库连接超时。

优化后的查询方案 #

方案1：用NOT EXISTS排除无效account_id #

这个逻辑是：检查每个account_id下是否存在任何超出区间的记录，不存在则保留该分组的记录：

SELECT DISTINCT ra.account_id, ra.company_id
FROM local_data_store.result_api ra
WHERE NOT EXISTS (
    SELECT 1
    FROM local_data_store.result_api ra_invalid
    WHERE ra_invalid.account_id = ra.account_id
    AND (
        ra_invalid.posted_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
        OR ra_invalid.posted_date > DATE_SUB(NOW(), INTERVAL 7 DAY)
    )
)
LIMIT 100000;

方案2：用分组聚合判断整体区间 #

通过分组后取MIN(posted_date)和MAX(posted_date)，确保整个分组的日期都落在要求区间内：

SELECT account_id, company_id
FROM local_data_store.result_api
WHERE account_id IN (
    SELECT account_id
    FROM local_data_store.result_api
    GROUP BY account_id
    HAVING MIN(posted_date) >= DATE_SUB(NOW(), INTERVAL 90 DAY)
    AND MAX(posted_date) <= DATE_SUB(NOW(), INTERVAL 7 DAY)
)
GROUP BY account_id, company_id
LIMIT 100000;

性能优化关键 #

为了彻底解决超时问题，必须给表添加复合索引：

CREATE INDEX idx_account_posted ON local_data_store.result_api(account_id, posted_date);

这个索引可以让数据库快速定位每个account_id的日期范围，避免全表扫描，大幅提升分组和关联查询的效率。

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