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

[Waldar]

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

Là pas le choix, il vous faut un calendrier dans le modèle.
C'est simple à écrire (surtout sous postgresql) et c'est toujours pratique.
Je n'en ai pas un tout fait sous la main, donc rapidement :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create table calendrier
as
select dt                         as cal_jour
     , to_char(dt, 'd')           as cal_position_semaine
     , to_char(dt, 'dd')          as cal_position_mois
     , to_char(dt, 'ddd')         as cal_position_annee
     , to_char(dt, 'iyyy"W"iw')   as cal_semaine_iso
     , to_char(dt, 'yyyy-mm')     as cal_mois
     , to_char(dt, 'yyyy"Q"qq')   as cal_trimestre
     , to_char(dt, 'yyyy')        as cal_annee
  from (select date '1900-01-01' + generate_series(0,100000)) as sr (dt)
 where dt < date '2100-01-01';

Dès lors, une simple jointure externe répondra à votre besoin.
Pour avoir les trois derniers jours, vous pouvez utiliser simplement l'expression CURRENT_DATE - 3.

[Bakura]

J'avance, calmement...

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
16
17
18
19
20
 
WITH cal AS (
	SELECT dt AS cal_jour
	FROM (SELECT date '2014-06-01' + generate_series(0,60)) AS sr (dt)
	WHERE dt < date '2015-01-01'
)
SELECT
	*
FROM
	(
		SELECT
			cal_jour,
			SUM(amount) as daily_amount,
			SUM(SUM(amount)) OVER(PARTITION BY currency ORDER BY cal_jour ROWS 1 PRECEDING) rolling
		FROM foo
		RIGHT JOIN cal ON cal_jour = date::date
		GROUP BY cal_jour
	) AS sq
WHERE cal_jour BETWEEN '2014-06-01' AND '2014-07-07'
ORDER BY cal_jour;

J'ai généré les valeurs via WITH, du coup j'obtiens bien chaque ligne pour toutes les dates, par contre les sommes sont toujours fausses car, apparemment, le ROWS 1 PRECEDING ignore les valeurs nulles, du coup ça ne marche pas comme prévu.

Donc si ma table contient :

Code :

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

1
2
3
4
5
 
Date                  Montant
 
2014-06-28       10
2014-06-30       20

Résultat souhaité :

Code :

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

1
2
3
4
5
6
 
Date                  Montant        Rolling
 
2014-06-28       10                 10
2014-06-29       0                   10
2014-06-30       20                 20

Résultat obtenu :

Code :

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

1
2
3
4
5
6
 
Date                  Montant        Rolling
 
2014-06-28       10                 10
2014-06-29       NULL             NULL
2014-06-30       20                 30

Merci en tout cas, je pense que je m'approche

[Waldar]

Étrange, je ne reproduis pas le même comportement :

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
16
17
18
19
20
21
22
23
24
25
WITH foo (date, amount) AS
(
select date '2014-06-28', 10 union all
select date '2014-06-30', 20
)
  ,  cal (cal_jour) AS
(
SELECT dt 
  FROM (SELECT date '2014-06-01' + generate_series(0,60)) AS sr (dt)
 WHERE dt < date '2015-01-01'
)
    SELECT cal_jour
         , SUM(coalesce(amount, 0)) AS daily_amount
         , SUM(SUM(coalesce(amount, 0))) OVER(ORDER BY cal_jour ROWS 1 PRECEDING) rolling
      FROM foo
RIGHT JOIN cal ON cal_jour = date
     WHERE cal_jour BETWEEN '2014-06-28' AND '2014-06-30'
  GROUP BY cal_jour
  ORDER BY cal_jour ASC;
 
cal_jour   daily_amount rolling
---------- ------------ -------
2014-06-28           10      10
2014-06-29            0      10
2014-06-30           20      20

Et même sans les coalesce, seul le montant quotidien se retrouve à null, le montant rolling est correct.

[aieeeuuuuu]

Bonjour,

Je pense que votre problème vient de votre PARTITION BY currency. Quel est son but ?

Car en effet, ROWS 1 PRECEDING se cantonne à la fenêtre définie.

[Bakura]

Bonjour,

Je pense que votre problème vient de votre PARTITION BY currency. Quel est son but ?

Car en effet, ROWS 1 PRECEDING se cantonne à la fenêtre définie.

Je suis allé un peu plus vite en besogne, j'essaye de faire ça par étape pour comprendre. Mais effectivement dans mon cas réel, j'ai une autre variable qui intervient qui est currency. Je souhaite grosso modo faire ce rolling par devise.

J'ai donc repris la requête de Waldar directement et, après avoir lu la documentation, il semble qu'il faille utiliser le mot clé "PARTITION BY" pour pouvoir partitioner suivant la devise :

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
16
17
18
19
20
21
22
23
24
 
WITH foo (date, amount, devise) AS
(
SELECT date '2014-06-26', 50, 'eur' union ALL
SELECT date '2014-06-27', 10, 'usd' union ALL
SELECT date '2014-06-28', 20, 'eur' union ALL
SELECT date '2014-06-29', 30, 'usd' union ALL
SELECT date '2014-06-30', 40, 'usd'
)
  ,  cal (cal_jour) AS
(
SELECT dt 
  FROM (SELECT date '2014-06-01' + generate_series(0,60)) AS sr (dt)
 WHERE dt < date '2015-01-01'
)
    SELECT cal_jour
         , devise
         , SUM(coalesce(amount, 0)) AS daily_amount
         , SUM(SUM(coalesce(amount, 0))) OVER(PARTITION BY devise ORDER BY cal_jour ROWS 1 PRECEDING) rolling
      FROM foo
RIGHT JOIN cal ON cal_jour = date
     WHERE cal_jour BETWEEN '2014-06-26' AND '2014-06-30'
  GROUP BY cal_jour, devise
  ORDER BY cal_jour ASC;

Résultat obtenu :

Code :

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

1
2
3
4
5
6
7
8
 
date                  dev  amo  rolling
 
2014-06-26	eur	50	50
2014-06-27	usd	10	10
2014-06-28	eur	20	70
2014-06-29	usd	30	40
2014-06-30	usd	40	70

Or je souhaite obtenir :

Code :

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

1
2
3
4
5
6
7
8
date                  dev  amo  rolling

2014-06-26	eur	50	50
2014-06-27	usd	10	10
2014-06-28	eur	20	20
2014-06-29	usd	30	30
2014-06-30	usd	40	70

Il semblerait que le PARTITION BY ne fonctionne pas comme je m'y attends.

Logiquement, après c'est bon, j'ai tout ce qu'il faut. Et en parallèle, quelles sont les bonnes pratiques en terme de performance concernant les fenêtres ? Sur la requête que vous avez trouvé, y a t-il des choses sur lesquelles faire attention ?

Entre autre, je suis obligé de stocker les dates sous forme de datetime (dans de très très très rares cas j'ai besoin de lire la date avec une précision à la minute). Toutefois, tous les calculs de recherche et de somme se feront uniquement avec une granularité au jour (au maximum). Si je mets un index sur un datetime et que la fenêtre effectue ses calculs sur un date (je suis obligé de faire un cast dans ma requête), mieux vaut-il que je créé un index "avec expression" de PostgreSQL qui fera une troncature sur jusqu'à la précision journalière ? Ou ce n'est pas la peine ?

Merci en tout cas !

[Waldar]

Comme pour le calendrier, il faut faire exister toutes les devises.
En gros, il faut créer la matrice complète avant de faire les jointures.

Le résultat que vous attendez me surprend un peu, néanmoins cette requête donne satisfaction :

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WITH foo (date, amount, devise) AS
(
SELECT date '2014-06-26', 50, 'eur' union ALL
SELECT date '2014-06-27', 10, 'usd' union ALL
SELECT date '2014-06-28', 20, 'eur' union ALL
SELECT date '2014-06-29', 30, 'usd' union ALL
SELECT date '2014-06-30', 40, 'usd'
)
  ,  cal (cal_jour) AS
(
SELECT dt 
  FROM (SELECT date '2014-06-01' + generate_series(0,60)) AS sr (dt)
 WHERE dt < date '2015-01-01'
)
  , sr AS
(
    SELECT cal.cal_jour
         , dvs.devise
         , foo.devise as devise_foo
         , SUM(coalesce(foo.amount, 0)) AS daily_amount
         , SUM(SUM(coalesce(foo.amount, 0))) OVER(PARTITION BY dvs.devise ORDER BY cal_jour ROWS 1 PRECEDING) rolling
      FROM cal 
CROSS JOIN (select distinct devise from foo) as dvs
 LEFT JOIN foo
        ON foo.date   = cal.cal_jour
       AND foo.devise = dvs.devise
     WHERE cal.cal_jour BETWEEN date '2014-06-26' AND date '2014-06-30'
  GROUP BY cal.cal_jour
         , dvs.devise
         , foo.devise
)
  select cal_jour, devise, daily_amount, rolling
    from SR
   where devise_foo is not null
ORDER BY cal_jour ASC;

[Bakura]

Mmhh ça ça m'arrange un peu moins car je ne peux pas savoir à l'avance les devises existantes. (Édit : autant pour moi, je n'avais pas vu le cross join. Faut que je me renseigne un peu sur ça :p)

Pourquoi est-ce que le résultat attendu vous surprend ?

Concrètement, je souhaite afficher sur un graph, par jour, la somme des trente derniers jours. Seulement les montants peuvent être dans différentes devises, et je dois les afficher dans une devise définie par l'utilisateur. Donc je suis obligé de grouper par devise et de récupérer les cours monétaires pour homogénéiser tout ça. Puis côté applicative, une fois que tout est homogène je me contente juste de sommer pour avoir le résultat final en dollars, euros...

Le cas vous paraît plus clair ?