2
votes

Sélectionnez la première valeur non nulle par date pour chaque colonne sur plusieurs lignes

J'ai une table comme celle-ci:

+-------------------------+------+------+------+------+  
|          Date           |  A   |  B   |  C   |  D   |  
+-------------------------+------+------+------+------+  
| 2010-11-16 10:02:00.000 | 10   | 14   | 1250 | 17   |  
+-------------------------+------+------+------+------+

J'ai besoin d'une requête pour extraire la première valeur non nulle de chaque colonne. Une sorte de "coalescence verticale".

Le résultat souhaité serait:

+-------------------------+------+------+------+------+  
|          Date           |  A   |  B   |  C   |  D   |  
+-------------------------+------+------+------+------+  
| 2010-11-16 10:02:00.000 | 10   | NULL | NULL | NULL |  
| 2010-09-21 00:00:00.000 | 86   | 14   | NULL | 17   |  
| 2010-07-27 00:00:00.000 | 125  | 12   | NULL | 11   |  
| 2010-05-29 15:24:00.000 | NULL | NULL | 1250 | NULL |  
+-------------------------+------+------+------+------+

le tableau est trié par date (desc)
le tableau actuel a beaucoup plus de colonnes (40) et de lignes (jusqu'à 5000)

Modifier:

Ma table actuelle a beaucoup plus de colonnes et de lignes (environ 40 colonnes et jusqu'à 5000 lignes disons). Je crains que plusieurs requêtes triées ne détériorent les performances de la requête. Cependant, si aucune solution plus propre n'apparaît, je vais y aller.

sql sql-server null tsql sql-server-2008

0 commentaires

3 Réponses :

4
votes

Ceci est une approche possible, si votre ordre décroît de la colonne [Date] :

Entrée:

;WITH DatesCTE AS (
    SELECT
        [Date] = MAX([Date]), 
        [DateA] = MAX(CASE WHEN A IS NOT NULL THEN [Date] END),
        [DateB] = MAX(CASE WHEN B IS NOT NULL THEN [Date] END),
        [DateC] = MAX(CASE WHEN C IS NOT NULL THEN [Date] END),
        [DateD] = MAX(CASE WHEN D IS NOT NULL THEN [Date] END)
    FROM #Table
)
SELECT 
    d.[Date],
    A = MAX(CASE WHEN t.[Date] = d.[DateA] THEN A END),
    B = MAX(CASE WHEN t.[Date] = d.[DateB] THEN B END),
    C = MAX(CASE WHEN t.[Date] = d.[DateC] THEN C END),
    D = MAX(CASE WHEN t.[Date] = d.[DateD] THEN D END)
FROM DatesCTE d
CROSS APPLY #Table t
GROUP BY d.[Date]

Déclaration:

Date                    A   B   C       D
2010-11-16 10:02:00.000 10  14  1250    17

Résultat:

SELECT 
    [Date] = (SELECT TOP 1 [Date] FROM #Table WHERE [Date] IS NOT NULL ORDER BY [Date] DESC),
    [A] = (SELECT TOP 1 [A] FROM #Table WHERE [A] IS NOT NULL ORDER BY [Date] DESC),
    [B] = (SELECT TOP 1 [B] FROM #Table WHERE [B] IS NOT NULL ORDER BY [Date] DESC),
    [C] = (SELECT TOP 1 [C] FROM #Table WHERE [C] IS NOT NULL ORDER BY [Date] DESC),
    [D] = (SELECT TOP 1 [D] FROM #Table WHERE [D] IS NOT NULL ORDER BY [Date] DESC)

Mise à jour - une autre approche possible, utilisant des fonctions d'agrégation:

CREATE TABLE #Table (
    [Date] datetime,
    A int,
    B int,
    C int,
    D int
)
INSERT INTO #Table
    ([Date], A, B, C, D)
VALUES
    ('2010-11-16T10:02:00.000', 10  , NULL, NULL, NULL),  
    ('2010-09-21T00:00:00.000', 86  , 14  , NULL, 17  ),  
    ('2010-07-27T00:00:00.000', 125 , 12  , NULL, 11  ),  
    ('2010-05-29T15:24:00.000', NULL, NULL, 1250, NULL)

2 commentaires

cela pourrait fonctionner, malheureusement ma table actuelle a beaucoup plus de colonnes et cette solution produira une requête vraiment moche, cependant, je l'envisage, merci (question modifiée)

@Jacopo J'ai mis à jour la réponse avec une autre approche utilisant des fonctions d'agrégation. Je vois, que Salman A utilise une approche similaire, il vous suffit de tester. Merci.

1
votes

Vous pouvez essayer quelque chose comme celui-ci pour éviter les commandes. Je sais qu'il est assez fastidieux au début de définir 50 variables, mais vous n'aurez pas à vous inquiéter plus tard.

declare @A int = (select top 1 A from #Table where A is not null)
declare @B int = (select top 1 B from #Table where B is not null)
declare @C int = (select top 1 C from #Table where C is not null)
declare @D int = (select top 1 D from #Table where D is not null)

select top 1 Date,@A,@B,@C,@D
from #Table

0 commentaires

1
votes

Je n'ai pas testé cela mais il semble assez méchant:

with cte as (
    select max(case when a is not null then date end) as date_a
         , max(case when b is not null then date end) as date_b
         , max(case when c is not null then date end) as date_c
         , max(case when d is not null then date end) as date_d
    from t
)
select max(date) as date
     , min(case when date = date_a then a end) as a
     , min(case when date = date_b then b end) as b
     , min(case when date = date_c then c end) as c
     , min(case when date = date_d then d end) as d
from t
cross join cte

Sans les fonctions de fenêtre:

with cte as (
    select date
         , a, max(case when a is not null then date end) over () as date_a
         , b, max(case when b is not null then date end) over () as date_b
         , c, max(case when c is not null then date end) over () as date_c
         , d, max(case when d is not null then date end) over () as date_d
    from t
)
select max(date) as date
     , min(case when date = date_a then a end) as a
     , min(case when date = date_b then b end) as b
     , min(case when date = date_c then c end) as c
     , min(case when date = date_d then d end) as d
from cte

Démo sur db violon

0 commentaires