2
votes

Remplacement des valeurs nulles par la valeur non nulle précédente dans chaque groupe

Je me connecte à Microsoft SQL Server sur Tableau via une requête SQL personnalisée. J'ai une table avec 3 champs DateTime, TagName, Value, et je veux remplacer les valeurs nulles dans le champ Value par la dernière valeur non nulle (respectant la valeur DateTime) dans chaque groupe de TagName.

SELECT  Computer, DateTime
,       CASE 
        WHEN Value IS NULL 
        THEN                                
       (SELECT TOP 1 Value 
        FROM History 
        WHERE DateTime<T.DateTime 
              AND TagName='RM02EL00CPT81.rEp'
              AND DateTime >='2018-12-31 23:59:00' 
              AND wwRetrievalMode='Delta'
              AND Value IS NOT NULL ORDER BY DateTime DESC
       ) 
        ELSE Value 
        END 
        AS ValueNEW
FROM History T
WHERE  TagName='RM02EL00CPT81.rEp' AND DateTime >='2018-12-31 23:59:00' AND wwRetrievalMode='Delta'

sql-server gaps-and-islands null wonderware

0 commentaires

3 Réponses :

1
votes

Essayez ceci

DateTime                Computer    Valu
---------------------------------------------
15.04.2019 16:51:30     A           10
15.04.2019 16:52:42     A           10
15.04.2019 16:53:14     A           10
15.04.2019 17:52:14     A           15
15.04.2019 16:51:30     B           0
15.04.2019 16:52:42     B           0
15.04.2019 16:53:14     B           0
15.04.2019 17:52:14     B           15

Résultat

;WITH CTE([DateTime],TagName,Valu)
AS
(
SELECT '15.04.2019 16:51:30','A' , 10    UNION ALL
SELECT '15.04.2019 16:52:42','A' , NULL  UNION ALL
SELECT '15.04.2019 16:53:14','A' , NULL  UNION ALL
SELECT '15.04.2019 17:52:14','A' , 15    UNION ALL
SELECT '15.04.2019 16:51:30','B' , NULL  UNION ALL
SELECT '15.04.2019 16:52:42','B' , NULL  UNION ALL
SELECT '15.04.2019 16:53:14','B' , NULL  UNION ALL
SELECT '15.04.2019 17:52:14','B' , 15
)
SELECT [DateTime],TagName As Computer,
        ISNULL(CASE WHEN Valu IS NOT NULL   
            THEN Valu
            ELSE 
                (
                SELECT TOP 1 Valu FROM  
                CTE i
                WHERE i.TagName = o.TagName     
                ) END,0) As Valu
FROM CTE o

0 commentaires

2
votes

C'est une question "classique" de Gaps and Islands. Vous pouvez y parvenir sans 2 scans, ni jointure triangulaire en utilisant les fonctions de fenêtre:

WITH VTE AS(
    SELECT CONVERT(datetime, [DateTime],104) AS [DateTime],
           TagName,
           [Value]
    FROM (VALUES ('15.04.2019 16:51:30','A',10  ),
                 ('15.04.2019 16:52:42','A',NULL),
                 ('15.04.2019 16:53:14','A',NULL),
                 ('15.04.2019 17:52:14','A',15  ),
                 ('15.04.2019 16:51:30','B',NULL),
                 ('15.04.2019 16:52:42','B',NULL),
                 ('15.04.2019 16:53:14','B',NULL),
                 ('15.04.2019 17:52:14','B',15  )) V([DateTime],TagName,[Value])),
Grps AS(
    SELECT [DateTime],
           TagName,
           [Value],
           COUNT(CASE WHEN [Value] IS NOT NULL THEN 1 END) OVER (PARTITION BY TagName ORDER BY [DateTime]
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM VTE)
SELECT DateTime,
       TagName,
       ISNULL(MAX([Value]) OVER (PARTITION BY TagName, Grp),0) AS [Value]
FROM Grps
ORDER BY TagName, [DateTime]

2 commentaires

Cela a résolu mon problème et je n'avais jamais pensé comme ça. Merci beaucoup pour votre aide @Larnu

De rien, @sgokce. La fonction Fenêtre apporte des fonctionnalités puissantes / utiles à SQL Server, il vaut donc la peine de les découvrir. :)

0
votes

Vous essayez donc de récupérer des données de Wonderware Historian. Vous n'avez peut-être pas besoin de fenêtrage et de remplacement, car le moteur de récupération Historian devrait être en mesure de vous fournir les données dont vous avez besoin sans null. Essayez ceci:

select DateTime, TagName as Computer, Value
from History
where TagName in ('A', 'B') --put here the tagnames you want to retrieve
and DateTime > '2018-12-31'
AND wwRetrievalMode='Delta'
order by TagName, DateTime

3 commentaires

Merci pour votre réponse mais je ne comprends pas ce que vous voulez dire. Si vous parlez du mode de récupération Delta, je récupère déjà les données en mode Delta. Autant que je sache, dans ce mode, nous pourrions toujours obtenir des valeurs nulles. Au moins c'est ce qu'ils disent dans le "Guide des concepts de l'historien" et aussi ce que j'observe dans mes données.

@sgokce Correct, il peut renvoyer des valeurs nulles mais ne doit pas en renvoyer plusieurs à la suite; du guide de l'utilisateur de l'historien: "La valeur NULL initiale après un non-NULL est toujours retournée. Plusieurs valeurs NULL sont supprimées. La première non-NULL après un NULL est toujours retournée même si elle est la même que la valeur non-NULL précédente. " Si vous avez des valeurs nulles dans les données renvoyées, alors cette balise était probablement de mauvaise qualité à ce moment-là. Je pense que vous ne devriez utiliser que des valeurs de bonne qualité pour les visualisations Tableau. Vous pouvez donc simplement filtrer les valeurs nulles en ajoutant "et la valeur n'est pas nulle" à la condition et utiliser uniquement des valeurs de bonne qualité

Merci pour votre réponse détaillée. Le filtrage des valeurs NULL peut entraîner des lacunes dans les données et j'aimerais éviter ce genre de lacunes dans mon analyse. J'ai vu que nous pouvons également appliquer une "règle de qualité optimiste" (wwQualityRule) pour éviter les valeurs nulles. Le paramètre OPTIMISTIC de la règle de qualité vous permet de récupérer des informations éventuellement incomplètes mais qui peuvent néanmoins fournir de meilleurs résultats dans les modes d'extraction de compteur et d'intégrale où le cycle de calcul contient des écarts de données Ce paramètre calcule en utilisant la dernière bonne valeur connue avant l'écart ( si possible)