Hive row_number()与子查询联用教程

在Hive实际开发中，窗口函数row_number()与子查询的组合是一种高效的数据处理方式，尤其适用于需要对结果集排序、分区并仅保留特定排名记录的场景。以下通过一个具体案例演示这一技术。

销售数据排名与累积计算需求

假设存在销售数据表sales_data，包含订单ID（id）、产品ID（product_id）和销售额（amount）。业务需求明确：先按销售额从高到低对每个订单进行全局排名，再基于排名结果，针对排名靠前的记录（例如第一名）计算每个产品的累积销售额。如何编写SQL实现？

核心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;

逻辑拆解与效果说明

该SQL包含两层处理逻辑：

• 子查询（别名为subquery）内，对全表按amount降序分配全局行号（未指定分区，因此排名基于全部数据）。
• 外层查询通过WHERE条件仅保留全局排名第一的记录（row_num <= 1）。
• 在保留的记录上，使用SUM() OVER (PARTITION BY product_id ORDER BY amount DESC)计算每个产品的累积销售额。由于每个产品在子查询结果中最多保留一条记录（仅当该产品的最大销售额恰好是全局第一时），累积销售额实际上等于该条记录的amount本身。

换个角度理解：该查询先找出全局销售额最高的订单，再计算该订单所属产品的所有订单累积销售额。但因子查询只保留一条记录，结果可能出乎意料。实际使用时，可调整WHERE条件阈值（例如改为<= 5），从而对每个产品的前几名进行累积计算。

技术应用场景与价值

此组合方式的核心在于：子查询利用row_number()实现排名过滤，外层查询再对过滤后的结果执行分区聚合。这种结构在处理复杂统计场景时具有极高灵活性，适用于数据清洗、报表计算等任务，值得在实践中反复运用。