MySQL实现月度客户对比：统计loyal与comeback客户数量

需求回顾 #

需要统计2021年2月各渠道的两类客户数量：

• loyal客户：2021年1月与2月均有交易的客户
• comeback客户：2021年1月无交易、但2021年1月前有过交易，且2月有交易的客户

基于已有查询的扩展实现 #

以下是在你提供的两个基础查询上扩展的SQL方案，通过关联判断客户归属类别：

-- 复用你提供的2月客户+渠道查询
WITH feb_customers AS (
    SELECT DISTINCT YEAR(_date) as _year, MONTH(_date) as _month, channel as _chan, customer_id
    FROM table_1
    WHERE YEAR(_date) = 2021 and MONTH(_date) = 2
),
-- 复用你提供的1月客户查询
jan_customers AS (
    SELECT DISTINCT customer_id
    FROM table_1
    WHERE YEAR(_date) = 2021 and MONTH(_date) = 1
),
-- 新增：筛选2021年1月之前有交易的客户
before_jan_customers AS (
    SELECT DISTINCT customer_id
    FROM table_1
    WHERE YEAR(_date) < 2021 OR (YEAR(_date) = 2021 AND MONTH(_date) < 1)
)
-- 关联统计各渠道两类客户数量
SELECT 
    fc._chan,
    -- 统计loyal客户：2月客户同时存在于1月客户列表
    COUNT(CASE WHEN jc.customer_id IS NOT NULL THEN fc.customer_id END) AS loyal_customer_count,
    -- 统计comeback客户：2月客户不在1月列表，但存在于1月前客户列表
    COUNT(CASE WHEN jc.customer_id IS NULL AND bjc.customer_id IS NOT NULL THEN fc.customer_id END) AS comeback_customer_count
FROM feb_customers fc
LEFT JOIN jan_customers jc ON fc.customer_id = jc.customer_id
LEFT JOIN before_jan_customers bjc ON fc.customer_id = bjc.customer_id
GROUP BY fc._chan;

逻辑说明 #

1. feb_customers：提取2021年2月有交易的客户及其对应渠道，去重避免同一客户多交易重复统计
2. jan_customers：提取2021年1月有交易的客户列表
3. before_jan_customers：提取2021年1月之前有过交易的客户列表
4. 关联后通过CASE判断客户类别：
   • 左关联jan_customers后，jc.customer_id不为空则属于loyal客户
   • 左关联jan_customers为空、但关联before_jan_customers不为空则属于comeback客户
5. 最后按渠道分组，统计两类客户的数量

简化版实现（单CTE） #

如果想减少子查询数量，也可以用单CTE标记客户交易状态后统计：

WITH customer_trade_tags AS (
    SELECT 
        customer_id,
        -- 标记2021年2月是否有交易
        MAX(CASE WHEN YEAR(_date)=2021 AND MONTH(_date)=2 THEN 1 ELSE 0 END) AS has_feb_trade,
        -- 标记2021年1月是否有交易
        MAX(CASE WHEN YEAR(_date)=2021 AND MONTH(_date)=1 THEN 1 ELSE 0 END) AS has_jan_trade,
        -- 标记2021年1月前是否有交易
        MAX(CASE WHEN YEAR(_date)<2021 OR (YEAR(_date)=2021 AND MONTH(_date)<1) THEN 1 ELSE 0 END) AS has_before_jan_trade
    FROM table_1
    GROUP BY customer_id
),
feb_channel_map AS (
    SELECT DISTINCT customer_id, channel AS _chan
    FROM table_1
    WHERE YEAR(_date)=2021 AND MONTH(_date)=2
)
SELECT 
    fcm._chan,
    COUNT(CASE WHEN ctt.has_feb_trade=1 AND ctt.has_jan_trade=1 THEN fcm.customer_id END) AS loyal_customer_count,
    COUNT(CASE WHEN ctt.has_feb_trade=1 AND ctt.has_jan_trade=0 AND ctt.has_before_jan_trade=1 THEN fcm.customer_id END) AS comeback_customer_count
FROM feb_channel_map fcm
JOIN customer_trade_tags ctt ON fcm.customer_id=ctt.customer_id
GROUP BY fcm._chan;

内容的提问来源于stack exchange，提问作者Zaw Lynn Htut