Discussion :

[Bakura]

Bonsoir,

Désolé pour le titre pas très explicite. Imaginons une table "Facture" avec un simple champ "Montant" et un champ "Date". Je souhaiterais, dans un but d'analytics, écrire une requête qui me calculerait la somme/moyenne/whatever sur les n derniers jours, pour les n derniers jours.

Par exemple, avec les données suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Date             Montant
06-07-2014    10
05-07-2014    5
04-07-2014    15
03-07-2014    10
02-07-2014    10
01-07-2014    15

Disons que, pour les 4 derniers jours, je souhaite calculer la somme des 3 derniers jours, je souhaite donc obtenir le résultat suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
Date             Résultat
06-07-2014    10 + 5 + 15 = 30
05-07-2014    5 + 15 + 10 = 30
04-07-2014    15 + 10 + 10 = 35 
03-07-2014    10 + 10 + 15 = 35

Calculer ce résultat pour une seule date est triviale, mais je me demande comment réaliser ça de manière efficace sans avoir à faire autant de requêtes que je souhaites de data point (ni même si c'est possible).

Merci !

[ced]

Bonjour,

Avec des fonctions de fenêtrage, c'est possible. Encore faut-il que votre SQBD les implémente, mais vous ne le précisez pas...
Quel SGBD utilisez-vous ?

[Bakura]

Bonjour,

J'utilise actuellement MySQL. J'ai la possibilité d'utiliser PostgreSQL également, mais je préférerais rester sur MySQL pour certaines raisons spécifiques au projet .

[ced]

Si vous restez sous MySQL, pas de fonctions de fenêtrage... Alors que c'est possible avec PostgreSQL.

[Bakura]

Merci. Je vais essayer de voir du côté de cette fonctionnalité sur postgre alors .

[Bakura]

Ca semble très intéressant en tout cas ! J'ai fait un petit test avec une table contenant une dizaine de valeurs, par jour, et je souhaite calculer pour les trois derniers jours, la somme des trois jours précédents en chaque point. J'ai écrit la requête suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT date, SUM(amount) OVER (ORDER BY date) AS amount FROM foo WHERE date >= '2014-07-05';

Ca marche bien pour la date 2014-07-07 (qui fait la somme des trois précédents), par contre 2014-07-06 ne fait la somme que des montants du 6 et du 5, et le 5 ne fait que la somme que de lui même, en ignorant les valeurs avant.

De la même manière, d'un point de vue performance comment ce genre de requêtes se comportent ? J'essaye d'évaluer plusieurs solutions, car grosso modo, je souhaite calculer des analytics, pour la plupart en utilisant ce modèle de "rolling periods", et souvent sur des périodes mensuelles. Certains mois, je peux avoir avoir jusqu'à 40 000 entrées à additionner (soit pas mal d'entrées sur 3 ou 4 ans, et la somme est de ce fait trop longue). Est-ce que le fait d'utiliser ces partitions permet d'améliorer les performances ?

EDIT : ça semble fonctionner ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
select
    date,
    amount,
	rolling
from
	(
		SELECT 
			date, 
			amount,
			sum(amount) over(partition by null order by date rows 2 preceding) rolling
		FROM foo
	) AS sq
where date >= '2014-07-05'
order by date;

Est-ce la manière la plus efficace ?

EDIT 2: réarrangé pour tenir compte du fait que, par jour, il puisse y avoir plusieurs valeurs :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
SELECT
	date,
	daily_amount,
	rolling
FROM
	(
		SELECT
			date,
			SUM(amount) as daily_amount,
			SUM(SUM(amount)) OVER(ORDER BY date ROWS 2 PRECEDING) rolling
		FROM foo
		GROUP BY date
	) AS sq
WHERE date >= '2014-07-05';

EDIT 3 : maintenant le problème consiste à modifier cette requête pour avoir des valeurs pour les jours ou il n'y a rien. Par exemple, si je n'ai pas de ligne le 2014-06-30, j'aimerais quand même avoir la somme pour les 30 jours précédents (en considérant que, ce jour-ci, amount est équivalent de 0). De la même manière, si je dispose d'une ligne pour 2014-07-05 d'un montant 50 et une pour 2014-07-08 d'un montant 10, si je souhaite calculer sur les 3 derniers jours, je souhaite avoir pour 2014-07-08 le résultat 10, et non 50 (ce qui est le cas avec la requête actuelle qui sélectionne les ROWS 3 PRECEDING