MySQL实战-获取时间段内的首访vs复访

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

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

注意：

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

首访

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

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

方法1、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 MIN(add_time) >= UNIX_TIMESTAMP('2024-08-01')
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 不在子查询集合中。

如果数据量更大（如千万级），可以将子查询结果提前生成物化视图或缓存到临时表中进行加速。

方法2、NOT EXISTS

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_log 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

逻辑执行顺序

1. 扫描 reception 表中过滤出 2024-08-01 ~ 2024-08-30 范围的记录，生成临时表（可走索引）；

2. 对于这部分记录，每个 customer_id 调用子查询一次：

   • 子查询在 reception_log（你写的别名）中查找 是否存在更早记录；

   • 如有，即排除；如无，即保留；

3. 通过 GROUP BY customer_id 聚合；

4. 使用 HAVING COUNT(*) = 1 保证 8 月只来访一次。

🔁 子查询执行次数：理论上是外层符合条件的每一条记录会触发一次 NOT EXISTS 检查，但 MySQL 在开启 semi-join 优化时可减少查询次数。

🔬 性能对比

💡建议

• 在中小数据量下（10万级内）差别不大；

• 若你关心性能（尤其客户量超过 10 万、甚至百万），推荐使用：GROUP BY + NOT IN (子查询提前执行) 的写法

复访

查询  时间段（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. 复访场景不需要“排除历史”

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