Hive窗口函数row_number()与子查询联用方法详解与实战教程

在Hive实际开发中，窗口函数row_number()结合子查询是一种非常实用的组合技巧，特别适合需要对结果集进行排序、分区，并仅保留特定排名记录进行后续计算的场景。下面通过一个具体案例来详细拆解这一操作流程。

假设我们有一张销售数据表sales_data，字段包含订单ID（id）、产品ID（product_id）和销售额（amount）。业务目标很明确：首先按销售额从高到低为每个订单排名，然后基于这个排名计算每个产品的累积销售额——注意，这里只针对排名靠前的记录（比如第一名）进行累加。那么对应的SQL应该如何编写？

先看这段核心代码：

SELECT
  id,
  product_id,
  amount,
  SUM(amount) OVER (PARTITION BY product_id ORDER BY amount DESC) as cumulative_sales
FROM (
  SELECT
    id,
    product_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as row_num
  FROM
    sales_data
) subquery
WHERE
  subquery.row_num <= 1;

这个结构实际上包含了两层逻辑：首先在子查询（别名为subquery）中，对全表按amount降序分配行号——由于没有指定分区，这里的排名是全局的。接着在外部查询中，通过WHERE条件只保留全局排名第一的记录（即row_num <= 1）。然后，在保留的这几条记录上，使用SUM() OVER (PARTITION BY product_id ORDER BY amount DESC)计算每个产品的累积销售额。由于每个产品在子查询结果中最多只保留一条记录（只有该产品的最大销售额恰好是全局第一时才会出现），所以累积销售额其实就是该条记录的amount本身。换个角度理解，这条查询的实际效果是：找出全局销售额最高的那条订单，然后计算该订单所属产品的所有订单累积销售额——不过因为只保留了一条，结果看起来可能有些意外。当然，你可以调整WHERE条件中的阈值（比如改成<= 5），从而对每个产品的前几名进行累积计算。

这里的关键点在于：子查询先用row_number()做好排名过滤，外部查询再对过滤后的结果做分区聚合。这种组合方式在应对复杂统计场景时非常灵活，值得你在实际数据清洗或报表计算中多加尝试。