Moyen efficace de remplir les valeurs nulles dans les données de séries chronologiques à l'aide de T-SQL

Cela ressemble à une question «classique» sur les lacunes et les îles. En supposant que vous n'utilisez pas 2008 ou une version antérieure (qui ne sont (presque) pas entièrement compatibles), cela devrait obtenir le résultat que vous recherchez:

WITH CTE AS(
    SELECT [date],
           price,
           COUNT(CASE WHEN price IS NOT NULL THEN 1 END) OVER (ORDER BY [date]
                                                               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM price_table p)
SELECT [date],
       price,
       MIN(price) OVER (PARTITION BY grp) AS ff_price
FROM CTE;

db violon

Vous pouvez également utiliser APPLY :

SELECT t.*, t1.price AS ff_price
FROM price_table t OUTER APPLY
     (SELECT TOP (1) t1.*
      FROM price_table t1
      WHERE t1.[date] <= t.[date] AND t1.price IS NOT NULL
      ORDER BY t1.[date] DESC
     ) t1;

En supposant que votre colonne est DATE et que le prix est DECIMAL (5,2) , veuillez tester cette approche:

date        price   ff_price    WindowedMax
2016-07-10  NULL    NULL        NULL
2016-07-11  0.79    0.79        0x9B3B0B050200014F
2016-07-12  NULL    0.79        0x9B3B0B050200014F
2016-07-13  NULL    0.79        0x9B3B0B050200014F
2016-07-14  0.69    0.69        0x9E3B0B0502000145
2016-07-15  NULL    0.69        0x9E3B0B0502000145
2016-07-21  0.88    0.88        0xA53B0B0502000158
2016-07-22  NULL    0.88        0xA53B0B0502000158

Ceci est une solution que j'ai implémentée avec un problème similaire et vous pouvez trouver l'explication détaillée ici . La raison pour laquelle cette approche est bonne est que elle ne nécessite pas de tri explicite , tant que vous avez un index par date .

Ce qu'il fait est essentiellement d'utiliser un MAX fenêtré avec la concaténation des 3 octets qui composent votre colonne de date (c'est pourquoi j'ai mentionné que votre colonne doit être DATE , sinon DATETIME aura besoin de 8 octets, vous pouvez éditer la requête pour travailler avec ça) avec les octets qui composent votre colonne de prix (qui font 5 octets, également supposés). Il s'agit de la partie CAST (P. [date] AS BINARY (3)) + CAST (P. [price] AS BINARY (5)) .

Lorsque vous calculez ceci et ORDER BY P. [date] ROWS UNBOUNDED PRECEDING , le moteur effectue essentiellement un roulement maximum avec des valeurs dont les octets les plus significatifs sont vos dates. Le résultat maximum sera toujours mis à jour lorsque la date change, mais considérant que la concaténation d'une valeur avec NULL comme prix donnera également NULL (en tant que binaire), puis le MAX ignorera toujours cette valeur et conservera le MAX non nul précédent (par P. [date] ROWS UNBOUNDED PRECEDING ).

Ceci est le résultat binaire du MAX fenêtré (j'ai ajouté un enregistrement précédent avec NULL donc vous voyez que le résultat est NULL pour les valeurs de prix nulles) :

SELECT
    P.[date],
    P.[price],
    ff_price = CONVERT(
        DECIMAL(5,2),       -- Original price datatype
        SUBSTRING(
            MAX(
                CAST(P.[date] AS BINARY(3)) +   -- 3: datalength of P.[date] column
                CAST(P.[price] AS BINARY(5))    -- 5: datalength of P.[price] column
            ) OVER (ORDER BY P.[date] ROWS UNBOUNDED PRECEDING),

            4,  -- Position to start that's not the binary part of the date

            5))-- Characters that compose the binary of the original price datatype
FROM
    price_table  AS P