请求编写SQL按购物渠道区分新老客户（含全渠道）并统计

嘿，我来帮你搞定全渠道客户的统计SQL，顺便把完整的分类逻辑整合起来，确保三类客户的数量之和完全匹配对应新/老客户的总数，还支持后续按年龄范围拆分。

核心思路 #

首先我们需要给每个客户打两个标签：

1. 渠道类型标签：判断客户是仅线上、仅POS线下，还是全渠道交易；
2. 新老客户标签：区分首次交易的新客户，和有多次交易的老客户。

每个客户只会被分到一个渠道类别和一个新/老类别里，所以三类渠道客户数之和会自动等于对应新/老客户的总数。

完整SQL代码 #

WITH customer_stats AS (
    SELECT 
        t.customer_id,
        -- 标记客户是否有线上交易
        MAX(CASE WHEN t.channel = 'ONLINE' THEN 1 ELSE 0 END) AS has_online,
        -- 标记客户是否有POS线下交易
        MAX(CASE WHEN t.channel = 'POS' THEN 1 ELSE 0 END) AS has_pos,
        -- 统计客户总交易次数
        COUNT(*) AS total_transactions,
        -- 获取客户年龄（用于后续按年龄拆分）
        c.age
    FROM transactions t
    JOIN customers c ON t.customer_id = c.customer_id
    GROUP BY t.customer_id, c.age
),
customer_classification AS (
    SELECT 
        customer_id,
        age,
        -- 划分渠道类别
        CASE 
            WHEN has_online = 1 AND has_pos = 0 THEN '仅线上购物'
            WHEN has_online = 0 AND has_pos = 1 THEN '仅POS线下购物'
            WHEN has_online = 1 AND has_pos = 1 THEN '全渠道（线上+POS）购物'
        END AS channel_category,
        -- 划分新老客户：仅首次交易（交易次数=1）为新客户，其余为老客户
        CASE 
            WHEN total_transactions = 1 THEN '新客户'
            ELSE '老客户'
        END AS customer_type
    FROM customer_stats
)
-- 最终统计结果，支持按年龄范围拆分
SELECT 
    customer_type,
    channel_category,
    -- 可选：按年龄范围分组，取消注释下面这行并加到GROUP BY里
    -- CASE 
    --     WHEN age < 20 THEN '20岁以下'
    --     WHEN age BETWEEN 20 AND 30 THEN '20-30岁'
    --     WHEN age BETWEEN 31 AND 40 THEN '31-40岁'
    --     ELSE '40岁以上'
    -- END AS age_group,
    COUNT(*) AS customer_count
FROM customer_classification
-- 按年龄拆分时，把age_group加入GROUP BY
GROUP BY customer_type, channel_category
-- GROUP BY customer_type, channel_category, age_group
ORDER BY customer_type, channel_category;

代码说明 #

1. customer_stats CTE：聚合每个客户的交易数据，标记是否有线上/POS交易，统计交易次数，同时关联客户表获取年龄（方便后续按年龄拆分）；
2. customer_classification CTE：根据前面的标记给客户划分渠道类型，根据交易次数判断新老客户（交易次数为1的是首次交易的新客户，多次交易的是老客户）；
3. 最终查询：按新老客户、渠道类型分组统计客户数量，如果需要按年龄范围拆分，只需启用注释里的age_group逻辑并添加到GROUP BY即可。

自定义调整 #

如果你的新老客户定义是统计周期内首次交易的客户（比如2023年首次交易的为新客户），可以把customer_type的CASE逻辑改成：

CASE
    WHEN MIN(t.transaction_date) >= DATE '2023-01-01' THEN '新客户'
    ELSE '老客户'
END AS customer_type

（需要把MIN(t.transaction_date)加到customer_stats的SELECT字段里）

这样就能完全匹配你的需求啦！

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