如何按年龄分组并排展示无重复的总销售额与客户数

刚好解决过类似的分组统计需求，来分享下这个按年龄分组展示客户数和销售额问题的完整解决方案：

需要按年龄分组（分为Teenager、Adult、Senior Citizen三类），展示特定年份的客户数量和总销售额，要求输出是包含这三列的结构化结果（示例：Teenager 57 48313.26；Adult 277 198512.89；Senior Citizen 50 44770）。

一开始我尝试用UNION ALL分别统计客户数和销售额，但这种方式会把客户数和销售额分成两行输出，没法合并成同一行的三列数据，完全不符合预期格式。原代码如下：

SELECT CASE WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' ELSE 'Teenager' END AS "AGE GROUP", COUNT(*) AS "NUMBER OF CUSTOMER" FROM customer GROUP BY CASE WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' ELSE 'Teenager' END UNION ALL SELECT CASE WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' ELSE 'Teenager' END AS "AGE GROUP", SUM(final_total) AS "TOTAL SALES" FROM customer c, orders o WHERE c.cust_id = o.cust_id AND EXTRACT(YEAR FROM order_datetime) = &check_year GROUP BY CASE WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' ELSE 'Teenager' END;

在Mureinik和Thorsten Kettner的帮助下，我意识到核心问题是统计客户数时要避免重复计数——因为一个客户可能有多笔订单，直接用COUNT(*)会把订单数当成客户数。所以改用COUNT(DISTINCT c.cust_id)来确保每个客户只被统计一次，同时直接关联客户表和订单表，一次性聚合出需要的两个指标。最终代码如下：

SELECT 
    CASE 
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' 
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' 
        ELSE 'Teenager' 
    END AS "AGE GROUP",
    COUNT(DISTINCT c.cust_id) AS "NUMBER OF CUSTOMER",
    SUM(final_total) AS "TOTAL SALES"
FROM customer c, orders o 
WHERE c.cust_id = o.cust_id 
AND EXTRACT(YEAR FROM order_datetime) = &check_year 
GROUP BY 
    CASE 
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen' 
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult' 
        ELSE 'Teenager' 
    END;

关键要点 #

• COUNT(DISTINCT c.cust_id)是核心：它会忽略同一客户的多笔订单，只统计唯一的客户数量；
• 直接关联两张表后分组，同时计算客户数和销售额，让结果按年龄组一行展示所有指标；
• 保留了原有的年龄分组逻辑和年份筛选参数&check_year，完美适配特定时间范围的统计需求。

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