Hive 3中row_number() over(partition by...)函数分组排名异常问题排查

嘿，这个问题其实是窗口函数里PARTITION BY的用法理解偏差导致的！咱们一步步捋清楚：

首先看你的子查询temp_1——它是按code_pde分组聚合的结果，这意味着每个code_pde在temp_1里只有唯一的一行数据。当你在row_number()里加了PARTITION BY code_pde，等于把每一行单独拆成一个分区，每个分区里就一条记录，那row_number()给每条记录分配的序号可不都是1嘛！

你之前怀疑是code_pde的空格导致分区识别问题，其实完全不是这个原因——哪怕把空格去掉，每个code_pde还是唯一的，每个分区依旧只有一行，结果还是一样的。

修正后的SQL #

你想要的是所有code_pde按nb_pde降序的全局排名，所以根本不需要PARTITION BY子句，直接在窗口函数里指定ORDER BY nb_pde DESC就行。修改后的完整SQL如下：

DROP TABLE IF EXISTS temp_2;
CREATE TEMPORARY TABLE temp_2 AS 
SELECT 
    code_pde, 
    nb_pde,
    row_number() OVER(ORDER BY nb_pde DESC) AS rank 
FROM ( 
    SELECT 
        code_pde, 
        SUM(den_05) AS nb_pde 
    FROM z_lab_int_hive_socle.gddi_303_elodi_32_agreg_mois_unit 
    WHERE code_pde IS NOT NULL 
      AND pde_moteur IS NOT NULL 
      AND pde_moteur = 'non' 
    GROUP BY code_pde 
) AS temp_1 ;

INSERT OVERWRITE TABLE z_lab_int_hive_socle.gddi_303_elodi_33_ranking_pde_non_moteur_unit 
SELECT 
    rank AS rang, 
    code_pde AS code_pde_non_moteur, 
    nb_pde AS nombre_pde_non_moteur 
FROM temp_2;

补充：并列排名的处理 #

如果有多个code_pde的nb_pde数值相同，你可以根据业务需求选择不同的排名函数：

• rank()：会跳过并列后的序号（比如两个第1名，下一个直接是第3名）
• dense_rank()：不会跳过序号（两个第1名之后，下一个是第2名）
  把上面的row_number()换成对应的函数即可。

小提醒 #

PARTITION BY的作用是把数据集拆成多个子组，然后在每个子组内计算窗口函数——比如按部门分区，每个部门内按业绩排名。你的场景是全局排名，所以完全不需要分区哦！

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