SQL Server中GROUP BY内使用CASE语句实现客户订单分布直方图

核心实现逻辑 #

整个统计过程采用两层聚合架构，从根源避免粒度偏差：

• 第一层：按客户维度聚合，计算每个客户的累计订单总量
• 第二层：对客户级的订单总量做内嵌CASE分桶标记，再按分桶结果分组，统计每个分桶下的客户总数
• 分桶规则严格匹配需求：订单数大于10的客户统一归入10+分桶区间，其余订单数保留原始值作为分桶标识

可直接运行的参考SQL #

兼容所有SQL引擎的通用写法 #

WITH per_customer_orders AS (
    -- 第一层：计算单客户的总订单量
    SELECT
        customerid,
        COUNT(orderid) AS order_count
    FROM Orders
    -- 过滤无客户归属的无效订单，避免统计偏差
    WHERE customerid IS NOT NULL
    GROUP BY customerid
)
-- 第二层：内嵌CASE实现分桶逻辑，按桶统计客户数
SELECT
    CASE
        -- 分桶判断：大于10单的客户统一归入10+区间
        WHEN order_count > 10 THEN '10+'
        -- 类型对齐：与'10+'的字符串类型匹配，避免触发SQL类型不匹配报错
        ELSE CAST(order_count AS VARCHAR(10))
    END AS order_bucket,
    COUNT(customerid) AS customer_count
FROM per_customer_orders
-- 分组逻辑中内嵌相同的CASE判断，完成分桶分组
GROUP BY
    CASE
        WHEN order_count > 10 THEN '10+'
        ELSE CAST(order_count AS VARCHAR(10))
    END
-- 修正字典序排序问题，保证直方图从左到右按订单数递增排列
ORDER BY
    CASE
        WHEN order_count > 10 THEN 999
        ELSE order_count
    END;

支持别名引用的简化写法 #

对于MySQL 8.0+、PostgreSQL、SQL Server等允许在GROUP BY中直接引用SELECT列别名的引擎，可以省略重复书写CASE判断的冗余步骤：

WITH per_customer_orders AS (
    SELECT
        customerid,
        COUNT(orderid) AS order_count
    FROM Orders
    WHERE customerid IS NOT NULL
    GROUP BY customerid
)
SELECT
    CASE
        WHEN order_count > 10 THEN '10+'
        ELSE CAST(order_count AS VARCHAR(10))
    END AS order_bucket,
    COUNT(customerid) AS customer_count
FROM per_customer_orders
-- 直接引用SELECT中定义的分桶别名完成分组
GROUP BY order_bucket
ORDER BY
    CASE
        WHEN order_count > 10 THEN 999
        ELSE order_count
    END;

关键注意事项 #

• 避坑提醒：不要直接在原始订单表上套用分桶逻辑后直接分组统计，否则会因为订单行的行粒度问题，将同一个客户按订单数重复计数，得到完全失真的统计结果。
• 类型一致性要求：CASE语句所有分支的返回值必须为相同数据类型，因为'10+'是字符串类型，所以数值类型的订单数需要通过CAST转换为同类型字符串，否则会触发执行错误。
• 边界值灵活调整：如果需求调整为订单数大于等于10即归入10+区间，只需将CASE判断中的order_count >10修改为order_count >=10即可。
• 排序逻辑优化：如果直接按分桶的字符串结果排序，会出现字典序导致的乱序（如排序结果为1、10+、2），需要通过独立的数值排序逻辑，将10+固定放在排序序列的最右端，匹配直方图的常规展示习惯。

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