SQL Server 2012：按Segment A分组统计Segment B触达与支付人数需求

首先我先梳理下你的需求核心：基于SQL Server 2012里的payment和contacts两张表，按Segment A的类别分组，分别统计两个关键指标：

• 该Segment A组中，收到Segment B沟通信息的客户总数
• 上述客户里完成支付的客户数量

假设表结构说明 #

因为你没给出具体表结构，我先基于业务场景做合理假设，你可以根据实际情况调整：

• contacts表：包含customer_id（客户唯一标识）、segment_a（客户所属的Segment A类别，对应3个分类）、received_segment_b（标记是否收到Segment B信息，比如1=收到，0=未收到）
• payment表：包含customer_id（支付客户标识）、payment_status（支付状态，比如1=已支付，0=未支付），也可以直接通过是否存在记录判断客户是否完成支付

SQL Server 2012 实现代码 #

下面的SQL语句可以直接实现你需要的统计逻辑：

SELECT
    c.segment_a,
    -- 统计收到Segment B的客户数量
    COUNT(DISTINCT CASE WHEN c.received_segment_b = 1 THEN c.customer_id END) AS total_received_b,
    -- 统计收到Segment B且完成支付的客户数量
    COUNT(DISTINCT CASE WHEN c.received_segment_b = 1 AND p.customer_id IS NOT NULL THEN c.customer_id END) AS paid_received_b
FROM
    contacts c
LEFT JOIN
    payment p ON c.customer_id = p.customer_id
    -- 如果payment表用状态字段判断有效支付，比如仅payment_status=1才算，就加上这个条件：
    -- AND p.payment_status = 1
GROUP BY
    c.segment_a
ORDER BY
    c.segment_a;

代码细节说明 #

1. 分组逻辑：用GROUP BY c.segment_a实现按Segment A类别分组统计
2. 去重统计：用COUNT(DISTINCT...)避免同一个客户因多条contacts记录被重复统计
3. 支付客户筛选：通过左连接payment表，筛选出既收到Segment B又有支付记录的客户；如果你的支付判断逻辑不同，直接调整条件即可
4. 适配多记录场景：如果一个客户在contacts表中有多条记录（比如分别记录收到A和B信息），可以用CTE先聚合客户的核心状态，再统计：

WITH customer_segments AS (
    SELECT
        customer_id,
        MAX(segment_a) AS segment_a, -- 假设一个客户仅属于一个Segment A
        CASE WHEN EXISTS (SELECT 1 FROM contacts c2 WHERE c2.customer_id = c.customer_id AND c2.segment_b IS NOT NULL) THEN 1 ELSE 0 END AS received_segment_b
    FROM
        contacts c
    GROUP BY
        customer_id
)
SELECT
    cs.segment_a,
    COUNT(DISTINCT CASE WHEN cs.received_segment_b = 1 THEN cs.customer_id END) AS total_received_b,
    COUNT(DISTINCT CASE WHEN cs.received_segment_b = 1 AND p.customer_id IS NOT NULL THEN cs.customer_id END) AS paid_received_b
FROM
    customer_segments cs
LEFT JOIN
    payment p ON cs.customer_id = p.customer_id
GROUP BY
    cs.segment_a
ORDER BY
    cs.segment_a;

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