MySQL实战-获取时间段内的[首访vs复访]客户ids

表：reception，add_time为接待时间，customer_id为客户id

表：project_customer，flag为客户类型，id为客户id

注意：

• 首访、复访的定于需要在查全局的前提下，8月来访1次的不能直接算作首访，需要看该客户在之前是否来访过
• reception 中 同一个 customer_id 可出现多次
• 需要获取的是复访客户数，而不是重复到访次数
• 给 reception(customer_id, add_time) 建复合索引；

NOT IN + DISTINCT 与 NOT EXISTS 在mysql不同版本间对比

首访

查询  时间段（2024 年 8 月到 12 月）接待的首访客户ids

筛选：客户在2024年8月到12月仅到访过1次，并且在2024年8月前没到到访过。

反连接（Anti‑Join）（5.4最优）

SELECT m.customer_id
FROM (
    SELECT customer_id
    FROM reception
    WHERE project_id = 1
    AND add_time BETWEEN UNIX_TIMESTAMP('2024-08-01') AND UNIX_TIMESTAMP('2024-09-01')
    GROUP BY customer_id
    HAVING COUNT(*) = 1
) AS m
LEFT JOIN reception AS r2
ON r2.customer_id = m.customer_id
AND r2.add_time < UNIX_TIMESTAMP('2024-08-01')
WHERE r2.customer_id IS NULL;

执行步骤

① 子查询 m 执行（即 FROM 子句中的派生表）：

• 从 reception 表中筛选：

  • project_id = 1

  • add_time 在时间区间 [@start, @end] 之间

• 然后按 customer_id 分组

• 使用 HAVING COUNT(*) = 1：仅保留这个时间范围内只来访一次的客户

• 输出结果为唯一 customer_id 列表（这些客户在该时间段内仅来访一次）

② 外层 LEFT JOIN：

• 将上述结果（命名为 m）与 reception 表再次连接：

  • 条件是 m.customer_id = r2.customer_id

  • 且 r2.add_time < @start：检查该客户在这段时间之前有没有接待记录

③ WHERE 子句过滤：

• WHERE r2.customer_id IS NULL

  • 表示该 customer_id 在 @start 之前没有出现过记录

  • 实质上是排除有早于指定时间的接待记录的客户

这个写法在 5.5 下会被当作两张索引扫描＋一次外连接，通常比 NOT IN/NOT EXISTS 子查询更稳定、高效。

NOT EXISTS

语句 B（嵌套子查询 GROUP BY + HAVING + NOT EXISTS）

SELECT m.customer_id
FROM (
    SELECT customer_id FROM reception WHERE customer_id > 0 AND add_time BETWEEN UNIX_TIMESTAMP('2024-08-01') AND UNIX_TIMESTAMP('2024-12-31')   
    GROUP BY customer_id
    HAVING COUNT(*) = 1
) AS m
WHERE NOT EXISTS (
    SELECT 1
    FROM reception AS r
    WHERE  r.customer_id = m.customer_id
    AND r.add_time < UNIX_TIMESTAMP('2024-08-01')
); -> 2025.0630给出的代码

语句 A（单层 NOT EXISTS + GROUP BY）

SELECT log1.customer_id 
FROM reception log1  
WHERE log1.add_time BETWEEN UNIX_TIMESTAMP('2024-08-01') AND UNIX_TIMESTAMP('2024-12-31')   
AND NOT EXISTS (        
    SELECT 1        
    FROM reception excluded      
    WHERE excluded.customer_id = log1.customer_id        
   AND excluded.add_time < UNIX_TIMESTAMP('2024-08-01') 
)  GROUP BY log1.customer_id    HAVING COUNT(*) = 1

差异分析

逻辑执行顺序

① 过滤主表 reception log1

• 执行 WHERE log1.add_time BETWEEN 2024-08-01 AND 2024-12-31 过滤条件；

• 如果 add_time 有索引，这一步会进行 范围扫描（range scan）。

• 结果是一个满足日期范围的临时结果集（假设为 N 条记录）。

② 对临时结果做 NOT EXISTS 子查询判断

• 对上面筛选出的每一条记录：

  • 用当前 log1.customer_id 去执行子查询

  • 如果该子查询返回任何一行（即存在历史记录），则该 log1.customer_id 被排除。

MySQL 5.5/5.6 中，这会使用 嵌套循环子查询，对每条外部行执行一次内部查询。

✅ 优化建议：给 (customer_id, add_time) 添加联合索引，以便子查询快速匹配。

③ 对剩下的结果按 customer_id 分组 (GROUP BY)

• 对满足条件的记录，按 customer_id 分组。

④ 筛选仅出现 1 次的 (HAVING COUNT(*) = 1)

• 只保留在该时间段内只出现一次的 customer_id。

执行顺序图解

NOT IN

SELECT customer_id
FROM reception
WHERE 
    add_time >= UNIX_TIMESTAMP('2024-08-01')
   AND 
    add_time < UNIX_TIMESTAMP('2025-01-01')
GROUP BY customer_id
HAVING COUNT(*) = 1
AND customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM reception
    WHERE add_time < UNIX_TIMESTAMP('2024-08-01')
);

逻辑执行顺序

1. 子查询 SELECT DISTINCT customer_id ... add_time < 8月 先执行一次，生成一个“8月前来访过的客户集合”；
2. 外层再筛选 8 月内记录；
3. GROUP BY customer_id；
4. HAVING 同时判断 COUNT = 1，MIN(add_time) >= 8月1日，且该 customer_id 不在子查询集合中。

复访

查询  时间段（2024 年 8 月到 12 月）接待的复访客户数

筛选：客户在2024年8月到12月之前的到访过，并且在2024年12月前来过2次。（简而言之就是8月到12月有访问且累计访问>1的客户）

方法1、EXISTS（最优）

SELECT DISTINCT customer_id
FROM reception
WHERE add_time >= UNIX_TIMESTAMP('2024-08-01')
AND add_time < UNIX_TIMESTAMP('2025-01-01')
AND EXISTS (
    SELECT 1 FROM reception r2
    WHERE r2.customer_id = reception.customer_id
    GROUP BY r2.customer_id
    HAVING COUNT(*) > 1
);

推荐用 EXISTS，性能更优于 IN 和 JOIN（尤其大表时）

标准 SQL 逻辑执行顺序（理论）

• 先执行 FROM 和 WHERE：从 reception 表筛选出满足时间条件的行（add_time 在 8 月区间的）。

• 对每一行，判断 EXISTS 子查询条件是否成立。

• EXISTS 子查询会用当前外层行的 customer_id 作为条件。

• 满足的行输出 customer_id。

• 对结果做 DISTINCT 去重。

子查询执行次数

• 按照 SQL 语义，EXISTS 子查询看似会针对 外层每一行单独执行一次。

• 但实际 MySQL 优化器通常不会简单地逐行执行子查询，尤其是这种与外层表关联的子查询。

• MySQL 会尝试做semi-join优化，批量处理外层表的 customer_id，避免 N 次子查询调用。

• 但由于这里 GROUP BY r2.customer_id HAVING COUNT(*) > 1，逻辑比较复杂，执行器可能会先预计算满足 COUNT(*)>1 的客户列表，再快速匹配。

方法2、INNER JOIN

SELECT COUNT(DISTINCT log1.customer_id) AS mcount
FROM reception AS log1
INNER JOIN (
    SELECT customer_id
    FROM reception
    WHERE add_time < UNIX_TIMESTAMP('2025-01-01')
    GROUP BY customer_id
    HAVING COUNT(*) > 1
) AS log2 ON log1.customer_id = log2.customer_id
WHERE log1.add_time >= UNIX_TIMESTAMP('2024-08-01')
AND log1.add_time < UNIX_TIMESTAMP('2025-01-01')

标准 SQL 逻辑执行顺序（理论）

1.子查询 log2 执行：

• 这个子查询会从全表中过滤出 累计访问次数 >1 的客户。

• MySQL 会做一次分组聚合扫描，扫描的行数等于整个表。

2.外层 log1 查询执行：

• 读取8月及之后的数据；

• 再和 log2 做 customer_id 连接，只保留同时满足“8月访问”和“累计>1”的客户；

• 结果去重后计数。

推荐：EXISTS 写法更优（尤其在大数据量下）

🔬 性能对比

首访 VS 复访

从性能角度看，查询“首访”客户通常比“复访”客户更慢，原因如下：

首访查询通常更慢的原因：

1. 首访需要排除历史记录（负判断）

• 你需要判断客户 在8月前没有任何访问记录，这类逻辑常常要用：

  • NOT EXISTS

  • NOT IN

• 这类排除式查询性能较差，尤其是在没有索引或数据量大的时候。

2. 需要聚合+排除

复访查询通常更快的原因：

1.复访查询更容易正向判断

你只需判断：

• 访问次数是否 > 1

• 或某客户在不同时间段都出现过（如 8 月来过 & 8 月前也来过）

这类逻辑可以直接写成：HAVING COUNT(*) > 1，或用 EXISTS 来判断是否出现多次，MySQL 优化器能较好地执行。

2. 复访场景不需要“排除历史”

你只关心客户“是否来过多次”，不用确认“他从未来过”，因此查询路径更直、更快。