Somme efficace de fenêtres glissantes sur une table de base de données

Question

Une base de données a un transactions tableau avec des colonnes : account_id , date , transaction_value (nombre entier signé). Un autre tableau ( account_value ) stocke la valeur totale actuelle de chaque compte, qui est la somme de tous les comptes de l transaction_value par compte. Il est mis à jour avec un déclencheur sur le transactions (c'est-à-dire les INSERTS, les MISES À JOUR et les SUPPRESSIONS de la table de données). transactions déclencher la gâchette pour changer le account_value .)

Une nouvelle exigence consiste à calculer la valeur totale des transactions du compte. uniquement au cours des 365 derniers jours . Seul le total courant est requis, pas les totaux précédents. Cette valeur sera demandée souvent, presque aussi souvent que l'option account_value .

Comment mettre en œuvre efficacement cette "somme par fenêtre glissante" ? Un nouveau tableau est acceptable. Y a-t-il un moyen d'éviter de faire la somme sur une année à chaque fois ?

Answer 1

Cela peut être fait avec les fonctions de fenêtrage standard :

SELECT account_id,
       sum(transaction_value) over (partition by account_id order by date)
FROM transactions

Le site order by à l'intérieur de la over() claues fait de la somme une "somme glissante".

Pour le "seulement les 356 derniers jours", vous avez besoin d'une deuxième requête qui limitera les lignes dans la clause WHERE.

Ce qui précède fonctionne dans PostgreSQL, Oracle, DB2 et (je pense) Teradata. SQL Server ne supporte pas l'ordre par dans la définition de la fenêtre (la prochaine version Denali le fera, je crois).

Answer 2

Aussi simple que ça ?

SELECT
   SUM(transaction_value), account_id
FROM
   transactions t
WHERE
   -- SQL Server, Sybase       t.DATE >= DATEADD(year, -1, GETDATE())
   -- MySQL            t.DATE >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY
   account_id;

Vous pouvez supprimer la composante temporelle des expressions de date en utilisant DATE (MySQL) ou de cette façon dans SQL Server

Answer 3

Si les requêtes de la table des transactions sont plus fréquentes que les insertions dans la table des transactions, alors peut-être qu'une vue est la meilleure solution ?

Answer 4

Vous allez avoir besoin d'un script unique pour remplir la table existante avec les valeurs de l'année précédente pour chaque enregistrement existant - qui devra être exécuté pour l'ensemble de l'année précédente pour chaque enregistrement généré.

Une fois que la colonne de l'année mobile est remplie, une alternative à l'addition de l'année précédente serait de calculer la valeur de chaque nouvel enregistrement comme étant la valeur de l'année mobile de l'enregistrement précédent, plus la ou les valeurs de transaction depuis la dernière mise à jour, moins les valeurs de transaction entre un an avant la dernière mise à jour et un an avant aujourd'hui.

Je suggère d'essayer les deux approches avec des données de test réalistes pour voir laquelle sera la plus performante - je m'attends à ce que l'addition de l'année entière soit au moins aussi performante lorsque les données sont relativement rares, tandis que la méthode des différences peut fonctionner mieux si les données sont fréquemment mises à jour sur chaque compte.

Answer 5

J'éviterai d'utiliser le langage SQL ici, car il varie beaucoup en fonction de la variété de SQL que vous utilisez.

Vous dites que vous avez un déclencheur pour maintenir le total courant existant.

Je suppose qu'il crée également (ou peut-être un processus nocturne) de nouveaux enregistrements quotidiens dans le fichier account_value table. Ensuite, les INSERTs, UPDATEs et DELETEs déclenchent le trigger pour ajouter ou soustraire du total courant existant ?

Les seuls changements que vous devez faire sont :
- ajouter un nouveau champ, "valeur_annuelle" ou quelque chose du genre
- que le déclencheur existant mette à jour ce champ de la même manière que le champ existant.
- utiliser le type de réponse de gbn pour créer les enregistrements d'aujourd'hui (ou jusqu'à quel point vous pouvez antidater).
- mais initialiser chaque nouvel enregistrement quotidien d'une manière légèrement différente...

Lorsque vous insérez une nouvelle ligne pour un nouveau jour, elle doit être initialisée à yesterday's value - the value 365 days ago . Après cela, le comportement devrait être identique à celui auquel vous êtes déjà habitué.