Hive(3) function row_number() over(partition by...) issue

Question

Trying to rank items from a list by column nb_pde descending so :

DROP TABLE IF EXISTS temp_2;

CREATE TEMPORARY TABLE temp_2 AS

    SELECT
        code_pde, nb_pde,row_number() OVER(PARTITION BY code_pde 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;

Subquery temp_1 result set is this, and it's OK :

code_pde nb_pde
-------- -------
01 8E    3392
01 70    265487
01 7F    310
01 82    8
01 M1    630058
01 M2    110083
01 M3    531438
01 30    1321617
01 37    1141343

But the final result set is this :

rang code_pde nombre_pde
---- -------- ----------
1    01 37    1141343
1    01 8E    3392
1    01 M1    630058
1    01 M3    531438
1    01 7F    310
1    01 M2    110083
1    01 70    265487
1    01 30    1321617
1    01 82    8

All ranks are 1 :-(

Just like if, for each row, only two first characters of the STRING values code_pde were considered. Tried to first compact code_pde values (ex : 0137 instead of 01 37) but result is still the same one...

Any idea of what is wrong ? It's the first time I have this kind of problem, after numerous uses of row_number() over(partition by...

score 0 · Answer 1 · answered Jan 30 '22 at 11:57

PARTITION BY code_pde means that row_number will reset when it receives new code_pde. And all rows in your data example have different codes. If you have many rows with the same code_pde, they will be assigned 1, 2, 3... according to the ORDER BY. See more details how windowing functions work:https://stackoverflow.com/a/55909947/2700344

Hive(3) function row_number() over(partition by...) issue

1 Answers1