Amazon Redshift多聚合Rollup：客户级多指标视图构建求助

错误原因 #

你遇到的aggregate function calls may not have nested aggregate or window functions错误，是因为Redshift不允许在聚合函数（如SUM、COUNT）内部嵌套其他聚合函数或窗口函数。比如直接写MAX(SUM(sales) OVER (...))这类语句会触发该错误。

分步实现方案 #

通过拆分计算步骤，用CTE（公共表表达式）分层完成聚合和窗口函数运算，即可规避该问题。以下是完整的SQL实现：

-- 1. 计算客户基础聚合指标：总销售额、总销量、总订单数等
WITH customer_base AS (
  SELECT
    customer_id,
    SUM(sales) AS total_sales,
    SUM(units) AS total_units,
    COUNT(DISTINCT order_id) AS total_orders,
    COUNT(DISTINCT product_category) AS category_count,
    COUNT(DISTINCT product_division) AS division_count
  FROM your_sku_dataset -- 替换为你的实际表名
  GROUP BY customer_id
),
-- 2. 计算每个客户各品类的销售额
customer_category_sales AS (
  SELECT
    customer_id,
    product_category,
    SUM(sales) AS category_sales
  FROM your_sku_dataset
  GROUP BY customer_id, product_category
),
-- 3. 筛选每个客户销售额最高的品类（若有并列可改用RANK()）
top_category AS (
  SELECT
    customer_id,
    product_category AS top_sales_category,
    category_sales AS top_category_sales,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY category_sales DESC) AS rn
  FROM customer_category_sales
),
-- 4. 计算每个客户各部门的销售额
customer_division_sales AS (
  SELECT
    customer_id,
    product_division,
    SUM(sales) AS division_sales
  FROM your_sku_dataset
  GROUP BY customer_id, product_division
),
-- 5. 筛选每个客户销售额最高的部门
top_division AS (
  SELECT
    customer_id,
    product_division AS top_sales_division,
    division_sales AS top_division_sales,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY division_sales DESC) AS rn
  FROM customer_division_sales
)
-- 6. 关联所有结果，计算占比并输出最终视图
SELECT
  cb.customer_id,
  cb.total_sales,
  cb.total_units,
  cb.total_orders,
  cb.category_count,
  cb.division_count,
  tc.top_sales_category,
  tc.top_category_sales,
  ROUND((tc.top_category_sales::FLOAT / cb.total_sales) * 100, 2) AS top_category_sales_pct,
  td.top_sales_division,
  td.top_division_sales,
  ROUND((td.top_division_sales::FLOAT / cb.total_sales) * 100, 2) AS top_division_sales_pct
FROM customer_base cb
LEFT JOIN top_category tc ON cb.customer_id = tc.customer_id AND tc.rn = 1
LEFT JOIN top_division td ON cb.customer_id = td.customer_id AND td.rn = 1;

关键细节说明 #

1. 分层计算：将基础聚合、品类/部门级聚合、最大值筛选拆分为独立CTE，彻底避免嵌套聚合/窗口函数的情况。
2. 并列值处理：如果存在多个品类/部门销售额并列最高的情况，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()，并配合LIST_AGG函数合并所有并列项，示例：

   -- 修改top_category的筛选逻辑，保留所有并列最高品类
   top_category AS (
     SELECT
       customer_id,
       LIST_AGG(product_category, ', ') WITHIN GROUP (ORDER BY category_sales DESC) AS top_sales_categories,
       MAX(category_sales) AS top_category_sales
     FROM customer_category_sales
     WHERE category_sales = (SELECT MAX(category_sales) FROM customer_category_sales cc WHERE cc.customer_id = customer_category_sales.customer_id)
     GROUP BY customer_id
   )
   

3. 数据类型转换：计算占比时将销售额转换为FLOAT类型，避免整数除法导致的精度丢失。

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