如何编写嵌套聚合SQL统计下单次数相同的客户数量

基于你提供的示例表tbl，以下分别实现两种预期结果的SQL语句：

形式二：结构化统计结果 #

先统计每个客户的下单次数，再按下单次数分组统计对应客户数量：

WITH tbl AS (
    SELECT 'UK'  "CUST_GRP", '00001' "CUST_ID", 'John'  "CUST_NAME", '001' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack'  "CUST_NAME", '002' "ORDER_ID", 'Coca Cola' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack'  "CUST_NAME", '003' "ORDER_ID", 'Fanta' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa'  "CUST_NAME", '004' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa'  "CUST_NAME", '005' "ORDER_ID", 'Coffee' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '006' "ORDER_ID", 'Wine' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '007' "ORDER_ID", 'Juice' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '008' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary'  "CUST_NAME", '009' "ORDER_ID", 'Wine' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary'  "CUST_NAME", '020' "ORDER_ID", 'Else' "GOODS" FROM DUAL 
),
customer_order_count AS (
    -- 统计每个客户的下单次数
    SELECT CUST_ID, COUNT(DISTINCT ORDER_ID) AS order_times
    FROM tbl
    GROUP BY CUST_ID
)
-- 按下单次数分组统计客户数量
SELECT order_times AS "下单次数", COUNT(CUST_ID) AS "客户数量"
FROM customer_order_count
GROUP BY order_times
ORDER BY order_times;

执行结果：

形式一：文本化统计结果 #

基于形式二的统计逻辑，将结果拼接为指定格式的文本：

WITH tbl AS (
    SELECT 'UK'  "CUST_GRP", '00001' "CUST_ID", 'John'  "CUST_NAME", '001' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack'  "CUST_NAME", '002' "ORDER_ID", 'Coca Cola' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00002' "CUST_ID", 'Jack'  "CUST_NAME", '003' "ORDER_ID", 'Fanta' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa'  "CUST_NAME", '004' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'ITA' "CUST_GRP", '00003' "CUST_ID", 'Lisa'  "CUST_NAME", '005' "ORDER_ID", 'Coffee' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '006' "ORDER_ID", 'Wine' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '007' "ORDER_ID", 'Juice' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00004' "CUST_ID", 'Messi' "CUST_NAME", '008' "ORDER_ID", 'Beer' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary'  "CUST_NAME", '009' "ORDER_ID", 'Wine' "GOODS" FROM DUAL UNION ALL
    SELECT 'FRA' "CUST_GRP", '00005' "CUST_ID", 'Mary'  "CUST_NAME", '020' "ORDER_ID", 'Else' "GOODS" FROM DUAL 
),
customer_order_count AS (
    SELECT CUST_ID, COUNT(DISTINCT ORDER_ID) AS order_times
    FROM tbl
    GROUP BY CUST_ID
),
order_customer_count AS (
    SELECT order_times, COUNT(CUST_ID) AS customer_count
    FROM customer_order_count
    GROUP BY order_times
)
SELECT CONCAT('下单', order_times, '次的客户数量为', customer_count) AS "TEXT"
FROM order_customer_count
ORDER BY order_times;

执行结果：

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