T-SQL calculer la moyenne mobile

Je travaille avec SQL Server 2008 R2, en essayant de calculer une moyenne mobile. Pour chaque enregistrement de mon point de vue, je voudrais recueillir les valeurs de l'250 enregistrements précédents, puis de calculer la moyenne de cette sélection.

Mon point de vue, les colonnes sont comme suit:

TransactionID | TimeStamp           | Value | MovAvg
----------------------------------------------------
            1 | 01.09.2014 10:00:12 |     5 |    
            2 | 01.09.2014 10:05:34 |     3 | 
...
          300 | 03.09.2014 09:00:23 |     4 |

TransactionID est unique. Pour chaque TransactionID, je voudrais calculer la moyenne de la valeur de la colonne, sur les précédentes 250 dossiers. Donc, pour TransactionID 300, de recueillir toutes les valeurs précédentes de 250 lignes (affichage est trié en ordre décroissant par TransactionID), puis dans la colonne MovAvg écrire le résultat de la moyenne de ces valeurs. Je suis à la recherche pour recueillir des données à l'intérieur d'une plage d'enregistrements.

Regarder dans PARTITION BY et ROW_NUMBER
Merci. À tout hasard vous avez une idée de comment le faire?
ne même identifiant de transaction se répète avec une valeur différente, est-il une colonne de type timestamp ou de la colonne d'identité en dehors de l'identifiant de transaction?
pouvez-vous fournir des exemples de données et si l'identifiant de transaction est unique , comment allez-vous disposer de 250 enregistrements précédents pour le même id?
Si vous modifiez la question et inclure les données d'exemple, vous serez en mesure de faire plus de mise en forme. Aussi, tout le monde ne lit les commentaires, il va aider les autres lors de la lecture de la question et de décider si elles veulent répondre.

OriginalL'auteur RunW | 2014-10-28

  1. 20

    Les fonctions de la fenêtre dans SQL 2008 sont assez limitées par rapport aux versions ultérieures et si mes souvenirs sont corrects, vous pouvez seulement de la partition et vous ne pouvez pas utiliser toutes les lignes/intervalle d'image de limite mais je pense que cela pourrait être ce que vous voulez:

    ;WITH cte (rn, transactionid, value) AS (
    SELECT 
       rn = ROW_NUMBER() OVER (ORDER BY transactionid),
       transactionid,
       value
    FROM your_table
)

SELECT 
    transactionid, 
    value, 
    movagv = (
        SELECT AVG(value) 
        FROM cte AS inner_ref
        -- average is calculated for 250 previous to current row inclusive
        -- I might have set the limit one row to large, maybe it should be 249
        WHERE inner_ref.rn BETWEEN outer_ref.rn-250 AND outer_ref.rn
        ) 
FROM cte AS outer_ref

    Note qu'il s'applique une sous-requête corrélée à chaque ligne et de la performance peut-être pas idéal.

    Avec les versions ultérieures, vous aurait pu se servir de cadre de fenêtre de fonctions et de faire quelque chose comme ceci:

    SELECT 
    transactionid, 
    value,
    -- avg over the 250 rows counting from the previous row
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 251 PRECEDING AND 1 PRECEDING),
    -- or 250 rows counting from current
    AVG(value) OVER (ORDER BY transactionid  
                     ROWS BETWEEN 250 PRECEDING AND CURRENT ROW)
FROM your_table
    Je vous remercie beaucoup. C'est certainement le moyen de le faire, mais comme vous le dites, la performance est assez mauvais. Reconnaissant de votre aide.
    Est-il un moyen plus efficace de le faire en SQL 2008 qui n'est pas une sous-requête corrélée sur chaque ligne? J'ai cassé mon cerveau à essayer et revenir avec une solution de gouttes de mon temps d'exécution, mais j'ai été à venir à court.
    Il existe peut-être, mais je ne suis pas au courant de tout - je n'ai pas beaucoup réfléchi à ce sujet même si, comme il ya de meilleures options avec les dernières versions du serveur.

    OriginalL'auteur

  2. 5

    Utiliser un Expression de Table commune (CTE) d'inclure le numéro de rangée pour chaque transaction, puis rejoindre la CTE contre elle-même sur le numéro de ligne de sorte que vous pouvez obtenir les valeurs précédentes pour calculer la moyenne.

    CREATE TABLE MyTable (TransactionId INT, Value INT)

;with Data as
(
  SELECT TransactionId, 
         Value, 
         ROW_NUMBER() OVER (ORDER BY TransactionId ASC) as rownum
  FROM MyTable
)
SELECT d.TransactionId , Avg(h.Value) as MovingAverage
FROM Data d
JOIN Data h on h.rownum between d.rownum-250 and d.rownum-1
GROUP BY d.TransactionId

    OriginalL'auteur