Ordered Analytical Functions not allowed in GROUP BY Clause报错解决咨询

报错根源 #

SQL的执行逻辑中GROUP BY聚合的优先级高于窗口函数，你当前将窗口函数与普通聚合函数放在同一查询层级，窗口函数引用的volume、a_flag都是未聚合的原始表字段，与GROUP BY的结果集字段范围冲突，因此会触发不允许在GROUP BY子句中使用有序分析函数的报错。

修正后的查询代码 #

SELECT 
    cust_no,
    cust_name,
    location,
    product,
    sum_volume,
    sum_weight,
    SUM(sum_volume) OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fixed_volume,
    SUM(bias_volume_base) OVER (PARTITION BY cust_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bias_volume
FROM (
    -- 内层优先完成分组聚合计算
    SELECT
        a.cust_no,
        b.cust_name,
        a.location,
        c.product,
        SUM(a.volume) AS sum_volume,
        SUM(a.weight) AS sum_weight,
        SUM(CASE WHEN a.a_flag = 'Y' THEN a.volume ELSE 0 END) AS bias_volume_base
    FROM test_table a
    JOIN test_table2 b ON a.cust_no = b.cust_no
    JOIN test_table3 c ON a.cust_no = c.cust_no
    GROUP BY 1,2,3,4
) grouped_data

实现逻辑说明 #

• 内层子查询优先完成你需要的4个维度分组聚合，计算出每个分组下的总成交量、总重量，以及满足a_flag='Y'条件的分组成交量
• 外层查询基于内层已经聚合完成的结果集执行窗口函数，按客户号维度汇总得到对应指标，既保留了原有GROUP BY的聚合逻辑，也满足了窗口函数的计算需求。

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