Redshift单查询实现分组平均及Top5值平均的方法

问题描述 #

在Redshift中，需要通过单条SQL查询同时获取分组（按manufacturer）的整体价格平均值，以及每组内价格最高的Top5记录的平均值。尝试关联子查询时遇到错误：ERROR: This type of correlated subquery pattern is not supported yet，需要无需执行两次查询的解决方案。

示例数据 #

manufacturer | model | price
Citroen        C1      1
Citroen        C2      2
Citroen        C3      3
Citroen        C4      4
Citroen        C5      5
Citroen        C6      6
Ford           F1      7
Ford           F2      8
Ford           F3      9
Ford           F4      10
Ford           F5      11
Ford           F6      12 
Ford           F6      19 
GenMotor       G1      20
GenMotor       G3      25
GenMotor       G4      22

预期输出 #

manufacturer | average_price | average_top_5_price
Citroen        3.5             4.0
Ford           10.85           12.2
GenMotor       22.33           22.33

解决方案 #

利用Redshift支持的窗口函数ROW_NUMBER()对每组内的价格进行降序排名，再通过条件聚合计算Top5的平均值，无需关联子查询：

SELECT
    manufacturer,
    -- 计算分组整体平均，保留两位小数
    ROUND(AVG(price)::DECIMAL, 2) AS average_price,
    -- 计算每组Top5（排名<=5）的价格平均，保留两位小数
    ROUND(AVG(CASE WHEN rn <= 5 THEN price END)::DECIMAL, 2) AS average_top_5_price
FROM (
    -- 子查询：为每个厂商的价格按降序分配排名
    SELECT
        manufacturer,
        price,
        ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY price DESC) AS rn
    FROM your_table_name -- 替换为你的实际表名
) ranked_data
GROUP BY manufacturer
ORDER BY manufacturer;

说明 #

1. 窗口函数排名：ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY price DESC)会按厂商分组，每组内价格从高到低分配唯一排名；如果需要将相同价格的记录视为同一名次（可能导致Top5包含更多记录），可以替换为RANK()或DENSE_RANK()。
2. 条件聚合：CASE WHEN rn <=5 THEN price END仅保留排名前5的价格，AVG()会自动忽略NULL值，从而计算Top5的平均值。
3. 精度处理：::DECIMAL和ROUND()用于控制结果的小数位数，匹配预期输出格式。

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