SQL嵌套CTE与子查询聚合计算问题：客户消费占比统计求助

需求说明 #

• 目标：统计2022年01客户组中，消费超过350美元的客户占比，以及消费超过1000美元的客户占比
• 涉及数据表：sales_order、sales_order_item

关联规则与注意事项 #

• 两张表通过order_id字段关联
• 仅统计2022年的订单数据
• 客户可拥有多笔不同日期的订单
• sales_order_item表的row_total是单品金额，一单对应多个row_total值，需汇总计算客户总消费

表结构与示例数据 #

sales_order表 #

sales_order_item表 #

错误代码与报错信息 #

尝试的SQL代码：

with orders as (
     select
         so.order_id,
         so.customer_id as customer_id,
         soi.row_total as row_total
     from sales_order as so
     inner join sales_order_item as soi
     on so.order_id = soi.order_id
     where customer_group_id = 01 and extract(year from so.order_date) = 2022),
    
 customer_sums as (
    select 
        customer_id,
        sum(row_total)
    from orders
    group by customer_id),
    
  customer_counts as(
    (select count(customer_id)
     from customer_sums
     group by customer_id
     having sum(row_total) >350) as customers_350,
    (select count(customer_id)
     from customer_sums
     group by customer_id
     having sum(row_total) >1000) as customers_1000,
    (select count(customer_id)
     from customer_sums
     group by customer_id
     having sum(row_total) >0) as all_customers),

 select 
     all_customers,
    (customers_350/all_customers *100) as %over350, 
    (customers_1000/all_customers *100) as %over1000
 from customer_counts

报错信息：

"ERROR: syntax error at or near "as" LINE 22: having sum(row_total) >350) >as customers_350"

预期结果 #

针对2022年01客户组，预期结果为：

• 2/5（40%）的客户消费超过350美元
• 1/5（20%）的客户消费超过1000美元

问题分析与修正代码 #

错误原因 #

1. customer_counts CTE写法违反SQL语法：不能直接用逗号分隔多个子查询并赋予别名
2. customer_sums中sum(row_total)未指定别名，后续引用会出错
3. where条件字段名错误：原表字段为customer_group，代码中误写为customer_group_id
4. 整数除法会导致占比计算精度丢失，需转换为浮点型计算

修正后的SQL代码 #

WITH customer_sums AS (
    SELECT 
        so.customer_id,
        SUM(soi.row_total) AS total_spent
    FROM sales_order so
    INNER JOIN sales_order_item soi ON so.order_id = soi.order_id
    WHERE so.customer_group = '01'  -- 若字段为数字类型则去掉引号
      AND EXTRACT(YEAR FROM so.order_date) = 2022
    GROUP BY so.customer_id
),
customer_metrics AS (
    SELECT
        COUNT(*) AS all_customers,
        SUM(CASE WHEN total_spent > 350 THEN 1 ELSE 0 END) AS customers_over_350,
        SUM(CASE WHEN total_spent > 1000 THEN 1 ELSE 0 END) AS customers_over_1000
    FROM customer_sums
)
SELECT
    all_customers,
    ROUND((customers_over_350::FLOAT / all_customers) * 100, 2) AS percentage_over_350,
    ROUND((customers_over_1000::FLOAT / all_customers) * 100, 2) AS percentage_over_1000
FROM customer_metrics;

代码说明 #

1. customer_sums CTE：直接关联两张表，过滤出目标客户组和年份的数据，按客户ID汇总总消费
2. customer_metrics CTE：用CASE WHEN条件计数，统计总客户数及两类达标客户数
3. 最终查询：将数值转换为浮点型计算占比，用ROUND保留两位小数，避免整数除法导致的精度问题

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