如何避免无purchase_id时SQL统计采购记录的重复计数问题

当前SQL将purchases表与contacts表关联时，若一条采购记录（同一customer_id+purchase_dt）匹配到多条符合时间条件的接触记录，会导致该采购被重复统计。比如你提到的2023-02-01的采购实际应为2次，却因关联了4条接触记录被计数4次，核心原因是关联后每条匹配的接触记录都会带出同一条采购，造成重复计数。

以下是几种可行的解决方案：

方案1：使用COUNT(DISTINCT)直接去重 #

在计数时，基于能唯一标识单次采购的字段组合（这里是customer_id+purchase_dt）使用DISTINCT，确保同一次采购只被计数一次：

SELECT
    c.customer_id,
    -- 用CONCAT组合字段生成唯一标识，部分数据库支持直接COUNT(DISTINCT (p.customer_id, p.purchase_dt))
    COUNT(DISTINCT CONCAT(p.customer_id, '_', p.purchase_dt)) AS purchases_count
FROM purchases AS p
INNER JOIN contacts AS c
    ON c.customer_id = p.customer_id 
    AND p.purchase_dt BETWEEN c.contact_dt AND DATE_ADD(c.contact_dt, INTERVAL 30 DAY)
GROUP BY c.customer_id;

注：如果你的数据库支持行构造函数（如MySQL 8.0+、PostgreSQL），可以简化为COUNT(DISTINCT (p.customer_id, p.purchase_dt))，避免字符串拼接的潜在问题。

方案2：先对采购表去重再关联 #

先从purchases表中提取唯一的采购记录（去重后的子集），再与contacts表关联统计，从源头上避免重复：

SELECT
    c.customer_id,
    COUNT(p_unique.purchase_dt) AS purchases_count
FROM (
    -- 先获取每个客户的唯一采购日期记录
    SELECT DISTINCT customer_id, purchase_dt
    FROM purchases
) AS p_unique
INNER JOIN contacts AS c
    ON c.customer_id = p_unique.customer_id 
    AND p_unique.purchase_dt BETWEEN c.contact_dt AND DATE_ADD(c.contact_dt, INTERVAL 30 DAY)
GROUP BY c.customer_id;

这个方案适合purchases表本身就存在重复记录的场景，提前去重能减少关联时的数据量。

方案3：使用EXISTS子查询筛选后计数 #

通过EXISTS判断采购记录是否落在任意一次接触的30天窗口期内，再对符合条件的采购记录去重计数：

SELECT
    p.customer_id,
    COUNT(DISTINCT p.purchase_dt) AS purchases_count
FROM purchases p
WHERE EXISTS (
    SELECT 1
    FROM contacts c
    WHERE c.customer_id = p.customer_id
      AND p.purchase_dt BETWEEN c.contact_dt AND DATE_ADD(c.contact_dt, INTERVAL 30 DAY)
)
GROUP BY p.customer_id;

该方案逻辑更直观，且EXISTS子查询在数据库优化器下通常有较好的性能表现，尤其当contacts表的customer_id和contact_dt有索引时。

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