MS SQL Server
  1. MS SQL Server
  3. Sélectionnez Première, Max et Dernière valeur non nulle par groupe
Intereting Posts
Supprimer une ligne d'une table SQL Server Le calcul SQL Server donne des résultats inattendus SQL Server 2005 Table Alter History Comment find des bases de données ayant une certaine table sur un server lié (SQL Server 2012)? Comment est-ce que vous préférez décharger une insertion de database, ainsi une réponse de Web est renvoyée plus rapidement? Exécutez l'instruction SQL plusieurs fois comme une boucle For T-SQL SELECT TOP Valeur et incrément 7645 Prédicat de text intégral vide ou vide SQL Server filtrant la string mais laissant des strings vides Que signifie la valeur -9 pour fetch_status dans SQL Server? Quel port mon instance SQL2008 fonctionne-t-elle? Quelle table est utilisée dans un triggersur de mise à jour? request de longue date d'application web (azuree) fonctions d'agrégation avec une phrase de condition différente Comment append des attributes à un nœud XML racine existant en utilisant TSQL?

Sélectionnez Première, Max et Dernière valeur non nulle par groupe

Essayer de sélectionner, par groupe, les première et dernière valeurs (chronologiquement) ainsi que la valeur max. J'avais écrit une requête qui fonctionne bien, sauf qu'elle ne gère pas les valeurs NULL . J'ai besoin d'ignorer les valeurs NULL .

Voici un exemple: 

 DECLARE @T table ( LabName VARCHAR(20) , CreatedOn date , LabValue int ) INSERT INTO @T ( LabName,CreatedOn,LabValue ) VALUES ('Creatinine', '2016-01-01', NULL) , ('Creatinine', '2016-02-01', 15) , ('Creatinine', '2016-03-01', 20) , ('Creatinine', '2016-04-01', 19) , ('SGOT (ST)', '2016-01-01', 25) , ('SGOT (ST)', '2016-02-01', 31) , ('SGOT (ST)', '2016-03-01', 25) , ('SGOT (ST)', '2016-04-01', NULL) SELECT DISTINCT * FROM ( SELECT LabName , FIRST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC) AS FirstValue , MAX(LabValue) OVER(PARTITION BY LabName) AS MaxValue , LAST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastValue FROM @T ) AS T

Ça fonctionnait bien jusqu'à ce que je réalise que certains laboratoires ne sont pas exécutés à certaines dates. Une fois que j'ai mis des NULL dans datatables de test, les résultats pour First et Last les incluront.

Voici le résultat que je reçois: 

 +------------+------------+----------+-----------+ | LabName | FirstValue | MaxValue | LastValue | +------------+------------+----------+-----------+ | Creatinine | NULL | 20 | 19 | | SGOT (ST) | 25 | 31 | NULL | +------------+------------+----------+-----------+

Voici le résultat que je veux: 

 +------------+------------+----------+-----------+ | LabName | FirstValue | MaxValue | LastValue | +------------+------------+----------+-----------+ | Creatinine | 15 | 20 | 19 | | SGOT (ST) | 25 | 31 | 25 | +------------+------------+----------+-----------+

Utilisez l'agrégation conditionnelle avec ROW_NUMBER() : 

 SELECT LabName, MAX(CASE WHEN seqnum_asc = 1 THEN LabValue END) as FirstValue, MAX(LabValue) as MaxValue, MAX(CASE WHEN seqnum_desc = 1 THEN LabValue END) as LastValue FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY LabName ORDER BY (CASE WHEN LabValue IS NOT NULL THEN 1 ELSE 2 END), CreatedOn ) as seqnum_asc, ROW_NUMBER() OVER (PARTITION BY LabName ORDER BY (CASE WHEN LabValue IS NOT NULL THEN 1 ELSE 2 END), CreatedOn DESC ) as seqnum_desc FROM @T t ) T GROUP BY LabName;

Votre database gère correctement les valeurs NULL . La première valeur pour Creatinine est en fait nulle et la dernière valeur pour SGOT (ST) est également nulle. Si vous souhaitez ignorer les lignes avec des valeurs nulles, ajoutez-les dans la clause WHERE : 

 SELECT DISTINCT * FROM ( SELECT LabName , FIRST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC) AS FirstValue , MAX(LabValue) OVER(PARTITION BY LabName) AS MaxValue , LAST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastValue FROM @T WHERE LabValue IS NOT NULL ) AS T;

Comme vous l'avez dit, il existe 13 colonnes de ce type dans lesquelles vous devez vérifier des valeurs non nulles.

Je pense que vous devriez d'abord filterr toutes les valeurs non nulles en utilisant CTE, puis en utilisant CTE vous pouvez écrire votre requête réelle. CTE réduira votre set de résultats et l'application de la fonction de window sur réduire resultset donnera de meilleures performances.

BTW, 13 de telles colonnes semblent t être mauvaise design de DB. Vous pouvez devoir interroger 100 à l'avenir. À mon humble avis, DISTINCT indique souvent une mauvaise design de database que la requête. 

 ;With CTE as (-- try to reduce resultset if possible SELECT * FROM @T WHERE LabValue IS NOT NULL ) SELECT DISTINCT * FROM ( SELECT LabName , FIRST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC) AS FirstValue , MAX(LabValue) OVER(PARTITION BY LabName) AS MaxValue , LAST_VALUE(LabValue) OVER(PARTITION BY LabName ORDER BY CreatedOn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastValue FROM CTE ) AS T