按年度统计到访与购买客户数的SQL查询结果异常求助

核心问题 #

你的查询中到访客户数统计异常的根本原因是：

• 关联ACTIVITY_P时，未将活动的年份与对应发票的年份绑定，导致统计了所有历史中符合活动类型的客户，而非当前年度有购买行为且同年有到访的客户。
• LEFT JOIN ACTIVITY_P中的OR i.I_CUSTOMER_M IS NULL条件会引入无购买记录的客户，与你统计“有购买行为客户的到访数”的需求冲突。
• 多表直接关联产生的笛卡尔积，会让COUNT(DISTINCT)统计时包含跨年度的重复客户ID。

修正后的查询语句 #

采用预聚合子查询的方式，分别统计各年度的购买客户和到访客户，再关联结果，避免多表关联的笛卡尔积问题：

WITH yearly_buyers AS (
    SELECT
        YEAR(i.D_INVOICEDATE) AS invoice_year,
        COUNT(DISTINCT c.S_CUSTNO) AS buying_customer
    FROM sao.customer_M c
    JOIN sao.invoice_p i 
        ON i.I_CUSTOMER_M = c.I_CUSTOMER_M
    WHERE
        i.i_invoice_p > 0
        AND i.B_PROFORMA = 0
        AND i.B_CREDITNOTE = 0
        AND i.B_CANCEL = 0
        AND i.dt_deleted IS NULL
        AND DATEDIFF(YEAR, i.D_INVOICEDATE, GETDATE()) <= 2
    GROUP BY YEAR(i.D_INVOICEDATE)
),
yearly_visitors AS (
    SELECT
        YEAR(a.D_ACTIVITYDATE) AS activity_year,
        COUNT(DISTINCT a.I_CUSTOMER_M) AS visited_customer
    FROM sao.ACTIVITY_P a
    WHERE
        a.I_ACTIVTYPE_C = 11
        AND DATEDIFF(YEAR, a.D_ACTIVITYDATE, GETDATE()) <= 2
    GROUP BY YEAR(a.D_ACTIVITYDATE)
)
SELECT
    COALESCE(yv.visited_customer, 0) AS 'Visited Customer',
    yb.buying_customer AS 'Buying Customer',
    yb.invoice_year AS 'Year'
FROM yearly_buyers yb
LEFT JOIN yearly_visitors yv 
    ON yb.invoice_year = yv.activity_year
ORDER BY yb.invoice_year;

关键改动说明 #

• 预聚合拆分：
  • yearly_buyers：单独统计各年度有购买行为的客户数，用JOIN替代原LEFT JOIN（因为WHERE条件已过滤掉无发票的客户，原LEFT JOIN实际等价于INNER JOIN）。
  • yearly_visitors：单独统计各年度有到访记录（活动类型11）的客户数，避免与购买表关联时产生笛卡尔积。
• 年份绑定：通过ON yb.invoice_year = yv.activity_year确保统计的是同一年度的购买客户与到访客户的对应关系。
• 处理空值：用COALESCE将无到访记录的年度的到访数设为0，保证结果完整性。

验证逻辑 #

对比你单独查询2022年的正确结果，修正后的查询会：

• 准确统计2022年有购买行为的客户数（1452）
• 同时统计2022年有到访记录的客户数（235）
• 自动关联对应年度，不会混入其他年份的到访数据

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