SQL中Partition Over语法报错求助：实现订单发货统计需求

原始数据表（Order shipment） #

期望输出表 #

错误SQL语句 #

select 
   order_id
 , count(distinct(shipment_id)
 , avg(pkg_weight) over (partition by shipment_id)
 
from table1
group by  order_id

错误信息 #

column "pkg_weight" must appear in the GROUP BY clause or be used in an aggregate function

错误原因 #

1. 混合使用聚合函数与窗口函数：外层GROUP BY order_id要求所有非聚合字段必须在GROUP BY中，但窗口函数avg(pkg_weight) over (partition by shipment_id)会为每条原始记录生成结果，无法直接和GROUP BY后的聚合结果兼容。
2. 需求匹配偏差：你需要的是每个订单下不同包裹的总重量，而非每个包裹的平均重量，窗口函数在这里完全没必要。
3. 语法疏漏：count(distinct(shipment_id)缺少闭合括号，属于次要语法错误。

正确SQL写法 #

方法1：先去重包裹记录再聚合

利用DISTINCT先去掉同一包裹的重复行，再按订单统计：

SELECT
    order_id,
    COUNT(DISTINCT shipment_id) AS Distinct_shipment_id,
    SUM(pkg_weight) AS total_pkg_weight
FROM (
    SELECT DISTINCT
        order_id,
        shipment_id,
        pkg_weight
    FROM table1
) AS unique_shipments
GROUP BY order_id;

方法2：先按包裹聚合再汇总

由于同一包裹的pkg_weight值一致，先按order_id和shipment_id分组取单条重量记录，再统计订单维度的结果：

SELECT
    order_id,
    COUNT(shipment_id) AS Distinct_shipment_id,
    SUM(shipment_weight) AS total_pkg_weight
FROM (
    SELECT
        order_id,
        shipment_id,
        MAX(pkg_weight) AS shipment_weight -- 用MAX/MIN/AVG都可以，因为值相同
    FROM table1
    GROUP BY order_id, shipment_id
) AS shipment_weights
GROUP BY order_id;

两种方法都能得到目标结果，数据量较大时方法2的性能表现可能更优。

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