如何统计每周新增商机及阶段变更数量？方案与SQL咨询

一、数据更新方案可行性分析 #

• 每周更新主表、每日创建副本更新的方案完全可行，但需注意以下细节：
  • 副本命名规范：建议按日期格式命名（如ThisWeek_20240520），避免混淆不同日期的版本
  • 主表更新时机固定：选在每周固定时间（如周一凌晨）完成主表更新，确保主表存储上周完整全量数据
  • 存储优化：若商机数据量较大，长期保留每日副本会占用较多存储，可定期清理过期副本（如保留近30天）

二、SQL统计逻辑修正与实现 #

你当前的SQL逻辑存在问题：left join使用<>作为匹配条件会生成大量无意义关联结果，无法准确筛选新增或阶段变更的商机。以下是针对需求的正确实现：

1. 统计新增distinct商机数量 #

SELECT COUNT(DISTINCT t.opportunity) AS 新增distinct商机数量
FROM ThisWeek t
WHERE NOT EXISTS (
    SELECT 1 FROM lastWeek l 
    WHERE l.opportunity = t.opportunity
);

该语句筛选本周存在但上周完全没有的商机，去重后计数，对应你期望的2（CSK、TGS）

2. 统计新增商机条目数量 #

SELECT COUNT(*) AS 新增商机条目数量
FROM ThisWeek t
WHERE NOT EXISTS (
    SELECT 1 FROM lastWeek l 
    WHERE l.opportunity = t.opportunity
);

直接统计所有新增商机的条目数（无需去重），对应期望的7（CSK、TGS）

3. 统计阶段变更商机数量 #

SELECT COUNT(DISTINCT t.opportunity) AS 阶段变更商机数量
FROM ThisWeek t
JOIN lastWeek l 
    ON t.opportunity = l.opportunity
    AND t.Stage <> l.Stage;

通过内连接匹配两周都存在且阶段不同的商机，去重后得到变更的商机数，对应期望的2（ABS）

4. 整合所有统计结果（可选） #

若需一次性获取所有统计值，可用CTE整合：

WITH 新增统计 AS (
    SELECT 
        COUNT(DISTINCT opportunity) AS 新增distinct商机数,
        COUNT(*) AS 新增商机条目数
    FROM ThisWeek
    WHERE NOT EXISTS (SELECT 1 FROM lastWeek WHERE opportunity = ThisWeek.opportunity)
),
阶段变更统计 AS (
    SELECT COUNT(DISTINCT t.opportunity) AS 阶段变更商机数
    FROM ThisWeek t
    JOIN lastWeek l ON t.opportunity = l.opportunity AND t.Stage <> l.Stage
)
SELECT 
    新增distinct商机数,
    新增商机条目数,
    阶段变更商机数
FROM 新增统计, 阶段变更统计;

三、补充说明 #

• 若商机有唯一标识（如opportunity_id），建议用唯一标识替代opportunity字段匹配，避免名称重复导致统计错误
• 若需查看阶段变更的具体记录，可调整SQL返回详细信息：

SELECT 
    t.opportunity,
    l.Stage AS 上周阶段,
    t.Stage AS 本周阶段
FROM ThisWeek t
JOIN lastWeek l 
    ON t.opportunity = l.opportunity
    AND t.Stage <> l.Stage
GROUP BY t.opportunity, l.Stage, t.Stage;

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