Je sais que cela a déjà été demandé, mais pourquoi la solution ci-dessous ne fonctionne-t-elle pas? Je souhaite remplir la valeur avec la dernière valeur non nulle triée par idx .
Ce que je vois:
with base as (
select 1 as idx
, 2 as value
union
select 2 as idx
, 4 as value
union
select 3 as idx
, null as value
union
select 4 as idx
, null as value
union
select 5 as idx
, 10 as value
)
select idx
, coalesce(value
, last_value(value) over (order by case when value is null then -1
else idx
end))
from base
order by idx
Ce que je veux:
idx | coalesce -----+---------- 1 | 2 2 | 4 3 | 4 4 | 4 5 | 10 (5 rows)
Code:
idx | coalesce -----+---------- 1 | 2 2 | 4 3 | 4 | 5 | 10 (5 rows)
3 Réponses :
Pour voir pourquoi votre solution ne fonctionne pas, regardez simplement le résultat si vous commandez par ordre dans votre cadre de fenêtre:
with base as (
select 1 as idx
, 2 as value
union
select 2 as idx
, 4 as value
union
select 3 as idx
, null as value
union
select 4 as idx
, null as value
union
select 5 as idx
, 10 as value
)
select idx, value from base
order by case when value is null then -1
else idx
end;
idx | value
-----+-------
3 |
4 |
1 | 2
2 | 4
5 | 10
La fonction de fenêtre last_value () choisira le dernier valeur dans l'image courante. Sans modifier aucune des valeurs par défaut du cadre, ce sera la ligne actuelle.
Je m'attends à ce que idx 3 et 4 aient la valeur 4, qui est la dernière valeur non nulle commandée par idx. Je vais mettre à jour la question.
Mais l'instruction order by case ... ne déplace-t-elle pas toutes les valeurs nulles au début de la fenêtre?
Cela les déplace au début de la fenêtre, mais le cadre par défaut de la fonction de fenêtre va du début de la fenêtre à la ligne courante . Ainsi, last_value retournera la ligne actuelle, qui est nulle.
Ce que vous voulez, c'est lag (ignore les valeurs nulles) . Voici une façon de faire ce que vous voulez, en utilisant deux fonctions de fenêtre. Le premier définit le regroupement des valeurs NULL et le second attribue la valeur:
select idx, value,
(array_remove(array_agg(value) over (order by idx), null))[count(value) over (order by idx)]
from base b
order by idx;
Vous pouvez également le faire sans sous-requêtes en utilisant des tableaux. En gros, prenez le dernier élément sans compter NULLs:
select idx, value, coalesce(value, max(value) over (partition by grp))
from (select b.*, count(value) over (order by idx) as grp
from base b
) b
order by idx;
Voici un violon db .
Je comprends et j'aime votre première solution, mais pourquoi ma solution ne fonctionne-t-elle pas?
@GordonLinoff pourriez-vous, s'il vous plaît, commenter les performances de ces requêtes? L'option avec tableau fonctionnera-t-elle pour une grande table?
Eh bien, la dernière_valeur ici n'a pas de sens pour moi à moins que vous ne puissiez me le signaler. En regardant l'exemple, vous avez besoin de la dernière valeur non par laquelle vous pouvez l'obtenir: Je forme un groupe avec les valeurs nulles et la valeur non nulle précédente afin que je puisse obtenir la première valeur non.
with base as (
select 1 as idx , 2 as value union
select 2 as idx, -14 as value union
select 3 as idx , null as value union
select 4 as idx , null as value union
select 5 as idx , 1 as value
)
Select idx,value,
first_value(value) Over(partition by rn) as new_val
from(
select idx,value
,sum(case when value is not null then 1 end) over (order by idx) as rn
from base
) t
voici le code
Max (.) Fonctionne si value est monotone, comme dans l'exemple, mais pas en général.
Voulez-vous partager un échantillon de données
Changez simplement sélectionnez 2 comme idx, 4 comme valeur en sélectionnez 2 comme idx, -4 comme valeur . votre_valeur pour idx 3 et 4 vaut 2, au lieu de -4.