查询近6个月每月均有活动的用户ID最优方案咨询

针对你5000万条数据的场景，绝对要避免JOIN操作——这类操作在大数据量下会带来极高的IO和内存开销。下面是一个基于聚合分组的轻量方案，只需要一次扫描就能完成需求：

核心SQL语句 #

SELECT client_id
FROM activities
WHERE created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY client_id
HAVING COUNT(DISTINCT DATE_TRUNC('month', created_at)) = 6;

可选优化：用数值型月份替代日期截断 #

如果你的数据库支持EXTRACT(YEAR_MONTH FROM created_at)（比如PostgreSQL、MySQL），可以换成更高效的数值分组，减少日期处理的开销：

SELECT client_id
FROM activities
WHERE created_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY client_id
HAVING COUNT(DISTINCT EXTRACT(YEAR_MONTH FROM created_at)) = 6;

关键优化点：索引加持 #

为了让这个查询跑得飞快，必须创建复合索引：

CREATE INDEX idx_activities_created_client ON activities(created_at, client_id);

这个索引会让数据库直接通过时间范围过滤出近6个月的记录，同时不需要回表就能拿到client_id，完全避免全表扫描。

方案原理 #

1. 过滤范围：先筛选出近6个月的活动数据，减少后续处理的数据量
2. 分组聚合：按用户ID分组，统计该用户在这6个月内有多少个不同的月份有活动
3. 筛选结果：只保留月份数恰好为6的用户——这意味着他们每个月都至少有一次活动

测试数据验证 #

用你提供的测试数据执行上述SQL，会返回client_id = 1，完全符合预期：

• client_id 1在2019-06到2019-11这6个月每个月都有活动
• client_id 2只有6月和11月的记录，月份数为2，被排除
• client_id 3只有11月的记录，月份数为1，被排除

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