如何在Partition BY基础上新增分区编号列而非行编号列？

要给每个PARTITION BY定义的分区分配唯一的全局编号（而非分区内的行编号），你可以使用窗口函数DENSE_RANK()（或RANK()，但DENSE_RANK()更常用，因为它会生成连续的编号，不会跳号），配合ORDER BY指定分区的字段即可。

核心逻辑 #

原来的ROW_NUMBER()是在每个分区内对行编号，而要给分区本身编号，我们需要一个能识别不同分组并分配统一编号的函数：

• DENSE_RANK() OVER(ORDER BY 分区字段)：会根据分区字段的不同值，为每个唯一的分区分配一个连续的编号，同一个分区内的所有行共享这个编号。
• 如果你的分区是多字段组合（比如PARTITION BY col1, col2），只需要在ORDER BY里同步这些字段即可。

示例代码 #

假设你原本的查询是这样的（给分区内的行加编号）：

SELECT 
  *,
  -- 分区内的行编号
  ROW_NUMBER() OVER(PARTITION BY value_expressions ORDER BY sort_column) AS row_in_partition
FROM your_dataset;

现在新增分区编号列，只需要添加DENSE_RANK()的窗口函数：

SELECT 
  *,
  ROW_NUMBER() OVER(PARTITION BY value_expressions ORDER BY sort_column) AS row_in_partition,
  -- 分区的全局编号
  DENSE_RANK() OVER(ORDER BY value_expressions) AS partition_number
FROM your_dataset;

实际效果演示 #

举个具体的订单表例子：

原始数据

执行查询后的结果

SELECT 
  customer_id,
  order_date,
  amount,
  ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date) AS row_in_partition,
  DENSE_RANK() OVER(ORDER BY customer_id) AS partition_number
FROM orders;

可以看到，partition_number为每个customer_id（也就是每个分区）分配了唯一的编号，同一个分区内的所有行编号一致。

额外说明 #

• 如果需要按分区的某个聚合值排序来分配编号，比如按每个分区的最大金额降序编号，可以这样写：

  DENSE_RANK() OVER(ORDER BY MAX(amount) OVER(PARTITION BY customer_id) DESC) AS partition_number
  

• 为什么不用ROW_NUMBER()？因为ROW_NUMBER()会给同一分区内的行分配不同的编号，无法实现“同一个分区共享一个编号”的需求。

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