23
votes

Comment remplacer la valeur nulle par la valeur de la ligne suivante

J'ai besoin de support dans mon code de requête SQL. Je dois remplacer la valeur nulle dans une colonne par une valeur non nulle de la ligne suivante.

à titre d'exemple, nous pouvons utiliser ce code:

SELECT r#, 
  value
 ,case when value is null  then Lead(value) OVER ( order by  r#  asc) else value end as RESULT 
FROM @value
order by r#

Lorsque j'utilise la fonction lead, j'obtiens cette valeur mais cela ne fonctionne pas avec les NULL. Ce dont j'ai besoin, c'est d'obtenir:

1 January
2 January
3 February
4 March
5 March
6 December
7 December
8 December
9 November
10 November
11 November
12 November
13 NULL

Bu à partir de ma requête:

declare   @value table (r# int, value varchar(15))
insert into @value ( r#, value ) values
 (1, NULL   ) ,
 (2, 'January'), 
 (3, 'February' ), 
 (4, NULL    ),
 (5, 'March'  ),
 (6, NULL    ),
(7, Null  ),
(8, 'December' ),
(9, Null ),
(10, Null  ),
(11, Null  ),
(12, 'November' ),
(13, Null )
select * from @value

J'ai:

sql sql-server

5 commentaires

Suivant - selon quoi?

Quels dbms utilisez-vous? (Serveur SQL?)

oui, serveur SQL. Dans la valeur de la colonne, j'ai des valeurs nulles et je veux les remplacer par la valeur de la ligne suivante (de la même colonne) par exemple pour la ligne 1 Je veux la valeur de janvier, pour les lignes 9, 10 et 11 novembre

"J'utilise la fonction lead": veuillez inclure ce code (en éditant la question) - sinon nous devinons comment vous abordez le problème.

Votre problème est que LEAD obtient la valeur suivante. Et cela peut également être nul (dans vos données, cela fonctionne pour janvier où il y a une valeur nulle, mais pas pour décembre où vous avez besoin de la prochaine mais une ligne). LEAD n'a pas la capacité «d'obtenir la ligne suivante qui remplit la condition» - pour cela, vous avez besoin d'une forme de jointure ou de requête interne (et il y a quelques options dans les réponses).

4 Réponses :

16
votes

La prochaine approche peut aider. Vous avez besoin d'un opérateur APPLY supplémentaire pour trouver le premier enregistrement avec une NULL non NULL :

T-SQL:

r#  value
1   January
2   January
3   February
4   March
5   March
6   December
7   December
8   December
9   November
10  November
11  November
12  November
13  NULL

Production:

SELECT v1.[r#], COALESCE(v1.[value], v2.[value]) AS [value]
FROM @value v1
OUTER APPLY (
   SELECT TOP 1 [Value]
   FROM @value 
   WHERE (v1.[r#] < [r#]) AND [value] IS NOT NULL
) v2

2 commentaires

Cette instruction CASE serait plus COALESCE(v1.[value], v2.[value]) AS [value] que COALESCE(v1.[value], v2.[value]) AS [value]

Cela ressemble à The Last non NULL Puzzle où Itzik Ben-Gan a présenté une solution très efficace.

3
votes

Vous pouvez essayer ci-dessous le code sql en utilisant une sous-requête associée

r#  Value
----------
1   January
2   January
3   February
4   March
5   March
6   December
7   December
8   December
9   November
10  November
11  November
12  November
13  NULL

OU (sans instruction Case)

SELECT v1.r#,ISNULL(v1.VALUE,( SELECT TOP 1 value
                            FROM @value  v2
                            WHERE v2.r# > v1.r# 
                            AND v2.[value] IS NOT NULL
                           ) 
                      ) AS [MonthNames]
FROM @value v1

Résultat

SELECT v1.r#,CASE WHEN value IS NOT NULL THEN v1.Value     
             ELSE ( SELECT TOP 1 value
                    FROM @value  v2
                    WHERE v2.r# > v1.r# AND v2.[value] IS NOT NULL
                  ) END  Value 
FROM @value v1

0 commentaires

1
votes

la requête ci-dessous fonctionne dans SQL Server:

;WITH CTE_Value
AS (
    SELECT R#, Value
    FROM @value AS T
    WHERE Value IS NOT NULL

    UNION ALL

    SELECT t.r#, c.Value
    FROM @value AS t
    INNER JOIN CTE_Value AS c ON t.r# + 1 = c.r#
    WHERE t.Value IS NULL
    )
SELECT *
FROM CTE_Value

UNION ALL

SELECT v.*
FROM @value AS v
LEFT JOIN CTE_value AS c ON v.r# = c.r#
WHERE c.r# IS NULL
ORDER BY r#

0 commentaires

5
votes

Vous pouvez le faire avec les fonctions de fenêtre. Malheureusement, SQL Server ne prend pas en charge l'option IGNORE NULL sur LEAD() , ce n'est donc pas une option.

Cependant, vous pouvez utiliser deux niveaux de fonctions de fenêtre:

select v.r#, v.value,
       coalesce(v.value, max(value) over (partition by next_r#)) as imputed_value
from (select v.*,
             min(case when value is not null then r# end) over (order by r# desc) as next_r#
      from @value v
     ) v
order by v.r#;

Sur 13 lignes, la différence de performances n'est probablement pas perceptible. Cependant, cela devrait avoir de bien meilleures performances à mesure que le nombre de lignes augmente.

1 commentaires

@PatrycjaKowalczyk. . . Y a-t-il une raison pour laquelle vous n'avez pas accepté cette réponse?