计算revenue中位数时遇SQL报错：列未在聚合或GROUP BY中

错误原因 #

这个报错的核心是聚合函数与窗口函数混用导致的分组逻辑冲突：

• 你在SELECT里用了count(*)（聚合函数），数据库会默认遵循GROUP BY规则——要求所有非聚合列必须包含在GROUP BY子句中，或者被聚合函数包裹。
• 而窗口函数percentile_disc(0.5) within group (order by revenue) over()里的revenue既没在GROUP BY中，也没被聚合，所以触发了这个错误。

另外，你的footwear子查询用description作为排除条件存在隐患：如果不同产品的description重复，会导致误排除非鞋类产品，建议改用唯一标识product_id。

修正方案 #

方案一：统一使用窗口函数 #

通过distinct去重，让窗口函数计算的结果只返回一行：

with footwear as (
    select
        i.product_id
    from
        info i
        inner join finance f on i.product_id = f.product_id
    where
        (i.description like '%shoe%'
        or i.description like '%trainer%'
        or i.description like '%foot%')
        and i.description is not null
)
select 
   distinct
   count(*) over() as num_of_products,
   percentile_disc(0.5) within group (order by f.revenue) over() as median_clothing_revenue
from
   info i
   inner join finance f on i.product_id = f.product_id
where
   i.product_id not in (select product_id from footwear);

方案二：拆分逻辑，先筛选再聚合 #

先把非鞋类产品的revenue单独提取到子查询，再直接做聚合计算，逻辑更清晰：

with footwear as (
    select
        i.product_id
    from
        info i
        inner join finance f on i.product_id = f.product_id
    where
        (i.description like '%shoe%'
        or i.description like '%trainer%'
        or i.description like '%foot%')
        and i.description is not null
),
non_footwear as (
    select f.revenue
    from info i
    inner join finance f on i.product_id = f.product_id
    where i.product_id not in (select product_id from footwear)
)
select
    count(*) as num_of_products,
    percentile_disc(0.5) within group (order by revenue) as median_clothing_revenue
from non_footwear;

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