在Oracle SQL Developer中计算月度滚动客户总数的最优方法

要实现你需要的滚动累计活跃客户总数（每个月底仍在活跃的客户数量，即已添加且未流失，或流失日期晚于当月），这里有两种高效的方案，根据你的数据量选择最合适的：

方案一：累计新增减累计流失（大数据量最优） #

这个方法通过先统计每月新增和流失的客户数，再用窗口函数计算滚动累计值，性能更优，适合数据量较大的场景：

WITH months AS (
    -- 生成需要统计的所有月份（从最早添加月份到最晚流失月份/当前月份）
    SELECT ADD_MONTHS(TRUNC(MIN(date_added), 'MONTH'), LEVEL - 1) AS month_end
    FROM your_table
    CONNECT BY ADD_MONTHS(TRUNC(MIN(date_added), 'MONTH'), LEVEL - 1) <= COALESCE(TRUNC(MAX(date_lost), 'MONTH'), TRUNC(SYSDATE, 'MONTH'))
),
new_customers AS (
    -- 统计每月新增客户数
    SELECT TRUNC(date_added, 'MONTH') AS month, COUNT(*) AS new_count
    FROM your_table
    GROUP BY TRUNC(date_added, 'MONTH')
),
lost_customers AS (
    -- 统计每月流失客户数（仅包含有流失记录的客户）
    SELECT TRUNC(date_lost, 'MONTH') AS month, COUNT(*) AS lost_count
    FROM your_table
    WHERE date_lost IS NOT NULL
    GROUP BY TRUNC(date_lost, 'MONTH')
)
-- 计算每个月的滚动累计活跃客户数
SELECT TO_CHAR(m.month_end, 'MON') AS Month,
       SUM(NVL(n.new_count, 0)) OVER (ORDER BY m.month_end) 
       - SUM(NVL(l.lost_count, 0)) OVER (ORDER BY m.month_end) AS Count
FROM months m
LEFT JOIN new_customers n ON m.month_end = n.month
LEFT JOIN lost_customers l ON m.month_end = l.month
ORDER BY m.month_end;

步骤说明： #

1. 生成月份维度：用CONNECT BY生成覆盖所有统计周期的月份列表，确保不会遗漏任何需要计算的月份。
2. 聚合新增/流失数据：分别按月份统计新增和流失的客户数，把全表扫描的工作量降到最低。
3. 滚动累计计算：用窗口函数SUM() OVER (ORDER BY month_end)计算累计新增和累计流失，两者相减得到当月月底的活跃客户总数。

方案二：直接统计活跃客户（小数据量更直观） #

如果你的客户数据量不大，这个方法更直观易懂，直接判断每个客户在对应月份是否活跃：

WITH months AS (
    SELECT ADD_MONTHS(TRUNC(MIN(date_added), 'MONTH'), LEVEL - 1) AS month_end
    FROM your_table
    CONNECT BY ADD_MONTHS(TRUNC(MIN(date_added), 'MONTH'), LEVEL - 1) <= COALESCE(TRUNC(MAX(date_lost), 'MONTH'), TRUNC(SYSDATE, 'MONTH'))
)
SELECT TO_CHAR(m.month_end, 'MON') AS Month,
       COUNT(DISTINCT c.cust_no) AS Count
FROM months m
JOIN your_table c 
    ON c.date_added <= m.month_end
    AND (c.date_lost IS NULL OR c.date_lost > m.month_end)
GROUP BY m.month_end
ORDER BY m.month_end;

逻辑说明： #

对每个月份，统计所有满足以下条件的客户：

• 客户添加日期早于或等于当月月底
• 客户没有流失记录，或者流失日期晚于当月月底
  通过COUNT(DISTINCT)去重得到当月活跃客户总数。

测试你的示例数据 #

用你给出的示例数据测试，两种方案都会输出：

Month Count
MAY   4
JUN   6
JUL   7
AUG   7

完全符合你的期望结果。

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