Ignorer les valeurs nulles dans une fonction de fenêtre postgresql rank()

Question

J'écris une requête SQL utilisant PostgreSQL qui doit classer les personnes qui "arrivent" à un endroit donné. Cependant, tout le monde n'arrive pas. J'utilise un rank() pour générer des rangs d'arrivée, mais dans les cas où l'heure d'arrivée est nulle, au lieu de renvoyer un rang nul, la fonction rank() La fonction d'agrégation les traite simplement comme s'ils étaient arrivés après tous les autres. Ce que je veux, c'est que ces personnes qui ne se présentent pas aient un rang de NULL au lieu de ce rang imputé.

Voici un exemple. Supposons que j'ai un tableau dinner_show_up qui ressemble à ça :

 | Person | arrival_time | Restaurant |
 +--------+--------------+------------+
 | Dave   |     7        | in_and_out |
 | Mike   |     2        | in_and_out | 
 | Bob    |   NULL       | in_and_out | 

Bob ne se montre jamais. La requête que j'écris serait :

select Person, 
       rank() over (partition by Restaurant order by arrival_time asc) 
               as arrival_rank
from dinner_show_up; 

Et le résultat sera

 | Person | arrival_rank | 
 +--------+--------------+
 | Dave   |     2        | 
 | Mike   |     1        | 
 | Bob    |     3        |  

Ce que je veux qu'il se passe à la place, c'est ça :

 | Person | arrival_rank | 
 +--------+--------------+
 | Dave   |     2        | 
 | Mike   |     1        | 
 | Bob    |     NULL     |

Answer 1

Il suffit d'utiliser un case autour de la rank() :

select Person, 
       (case when arrival_time is not null
             then rank() over (partition by Restaurant order by arrival_time asc) 
        end) as arrival_rank
from dinner_show_up;

Answer 2

Une solution plus générale pour toutes les fonctions d'agrégation, et pas seulement pour rank(), est de partitionner par 'arrival_time is not null' dans la clause over(). Cela aura pour effet de placer toutes les lignes arrival_time nulles dans le même groupe et de leur donner le même rang, laissant les lignes non nulles être classées uniquement les unes par rapport aux autres.

Afin de donner un exemple significatif, j'ai créé un CTE comportant plus de lignes que le jeu de problèmes initial. Veuillez pardonner les larges rangées, mais je pense qu'elles mettent mieux en évidence les différentes techniques.

with dinner_show_up("person", "arrival_time", "restaurant") as (values
   ('Dave' ,    7, 'in_and_out')
  ,('Mike' ,    2, 'in_and_out')
  ,('Bob'  , null, 'in_and_out')
  ,('Peter',    3, 'in_and_out')
  ,('Jane' , null, 'in_and_out')
  ,('Merry',    5, 'in_and_out')
  ,('Sam'  ,    5, 'in_and_out')
  ,('Pip'  ,    9, 'in_and_out')
)

select 
   person
  ,case when arrival_time is not null then         rank() over (                                      order by arrival_time) end as arrival_rank_without_partition
  ,case when arrival_time is not null then         rank() over (partition by arrival_time is not null order by arrival_time) end as arrival_rank_with_partition
  ,case when arrival_time is not null then percent_rank() over (                                      order by arrival_time) end as arrival_pctrank_without_partition
  ,case when arrival_time is not null then percent_rank() over (partition by arrival_time is not null order by arrival_time) end as arrival_pctrank_with_partition
from dinner_show_up

Cette requête donne les mêmes résultats pour arrival_rank_with/without_partition. Cependant, les résultats pour pctrank() diffèrent : sans_partition est faux, allant de 0% à 71.4%, alors que with_partition donne correctement pctrank() allant de 0% à 100%.

Ce même schéma s'applique également à la fonction d'agrégation ntile().

Elle fonctionne en séparant toutes les valeurs nulles des valeurs non nulles aux fins du classement. Ainsi, Jane et Bob sont exclus du classement par percentile de 0 à 100 %.

 |person|arrival_rank_without_partition|arrival_rank_with_partition|arrival_pctrank_without_partition|arrival_pctrank_with_partition|
 +------+------------------------------+---------------------------+---------------------------------+------------------------------+
 |Jane  |null                          |null                       |null                             |null                          |
 |Bob   |null                          |null                       |null                             |null                          |
 |Mike  |1                             |1                          |0                                |0                             |
 |Peter |2                             |2                          |0.14                             |0.2                           |
 |Sam   |3                             |3                          |0.28                             |0.4                           |
 |Merry |4                             |4                          |0.28                             |0.4                           |
 |Dave  |5                             |5                          |0.57                             |0.8                           |
 |Pip   |6                             |6                          |0.71                             |1.0                           |

Answer 3

select Person, 
   rank() over (partition by Restaurant order by arrival_time asc) 
           as arrival_rank
from dinner_show_up
where arrival_time is not null
union 
select Person,NULL as arrival_rank
from dinner_show_up
where arrival_time is null;