你说的这个需求我之前也碰到过——既要拿到每个分组自身的统计结果，又要算出排除当前分组后剩下所有数据的聚合值，用窗口函数确实是最直接的思路，Hive里完全可以实现，我给你拆解下：

核心思路 #

我们可以先计算全局的总聚合值，再减去当前分组的聚合值，就能得到排除当前分组后的结果。窗口函数在这里的作用是让我们能在每行数据里同时拿到分组级和全局级的统计值。

具体SQL实现 #

假设你的表名叫product_ratings，我们以计算评分的总和、平均值为例：

SELECT
  date,
  product,
  rating,
  -- 当前product分组的评分总和
  SUM(rating) OVER (PARTITION BY product) AS group_rating_sum,
  -- 当前product分组的评分平均值
  AVG(rating) OVER (PARTITION BY product) AS group_rating_avg,
  -- 排除当前product分组后的全局评分总和 = 全局总和 - 当前分组总和
  SUM(rating) OVER () - SUM(rating) OVER (PARTITION BY product) AS exclude_group_sum,
  -- 排除当前product分组后的全局评分平均值 = (全局总和 - 当前分组总和) / (全局总条数 - 当前分组条数)
  (SUM(rating) OVER () - SUM(rating) OVER (PARTITION BY product)) 
  / (COUNT(*) OVER () - COUNT(*) OVER (PARTITION BY product)) AS exclude_group_avg
FROM product_ratings;

关键部分解释 #

• SUM(rating) OVER ()：不带PARTITION的窗口函数，计算的是全表所有数据的评分总和，同理COUNT(*) OVER ()是全表总条数。
• SUM(rating) OVER (PARTITION BY product)：按product分组计算当前组的评分总和，COUNT(*) OVER (PARTITION BY product)是当前组的条数。
• 两者相减/相除，就能直接得到排除当前分组后的聚合结果。

针对你给出的样本数据的结果示例 #

比如对于product=A的行，exclude_group_sum就是全表所有非A产品的评分总和，exclude_group_avg就是这些非A产品的平均评分。如果你的表只有A产品的数据，那排除后的总和会是0，平均值会返回null（可以用IFNULL函数处理这种分母为0的情况）。

额外优化：避免重复计算 #

如果觉得窗口函数写重复了，可以用子查询先把分组和全局的统计值预计算出来，再关联回原表，写法更清晰，也能减少重复计算的开销：

WITH stats AS (
  SELECT
    product,
    SUM(rating) AS group_sum,
    COUNT(*) AS group_cnt,
    SUM(SUM(rating)) OVER () AS global_sum,
    SUM(COUNT(*)) OVER () AS global_cnt
  FROM product_ratings
  GROUP BY product
)
SELECT
  pr.date,
  pr.product,
  pr.rating,
  s.group_sum,
  s.group_sum / s.group_cnt AS group_avg,
  s.global_sum - s.group_sum AS exclude_group_sum,
  CASE WHEN (s.global_cnt - s.group_cnt) > 0 
       THEN (s.global_sum - s.group_sum)/(s.global_cnt - s.group_cnt)
       ELSE NULL 
  END AS exclude_group_avg
FROM product_ratings pr
JOIN stats s ON pr.product = s.product;

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