Souci pour le calcul de la moyenne dans une table

Version imprimable

17/07/2021, 15h35
bestorchidee

Souci pour le calcul de la moyenne dans une table
bonjour,

j'ai une table de solde avec cette structure :
Code:

1 2 3 4 5 CREATE TABLE "COMPTES" ("ID_COMPTE" NUMBER not null, "DATE_JOUR" DATE not null, "SOLDE" NUMBER(18,5), CONSTRAINT pk_cpt PRIMARY KEY(ID_COMPTE,DATE_JOUR));
je dois calculer la moyenne des soldes pour chaque mois (somme des soldes/nombre total des jours du mois en question), mais je sèche car il n'y a pas de lignes qui correspondent aux week-ends et aux jours fériés, et dans ce cas là, la valeur du solde = valeur du solde de j-1 (exemple : vendredi le solde=100 , samedi =100,dimanche=solde ).
je ne sais pas comment m'y prendre pour le faire avec une requête sql
si quelqu'un peut m'orienter
je vous remercie beaucoup

Bonjour,

Il faut générer une table des jours pour "combler" les jours manquants, puis utiliser la fonction analytique LAST_VALUE pour aller récupérer le solde du jour précédent le plus proche. Voici la requête:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
with fourchette_date
as (select min(date_jour) min_date, max(date_jour) max_date
    from comptes
   ),
calendrier as (select min_date + level date_cal
               from fourchette_date
               connect by level <= max_date - min_date + 1
              ),
soldes as (              
select ca.date_cal, 
       nvl(co.id_compte, last_value(co.solde ignore nulls) over (order by ca.date_cal rows between unbounded preceding and 1 preceding)) solde
from calendrier ca
left join comptes co
on (ca.date_cal = co.date_jour)
)
select to_char(date_cal, 'YYYY-MM') mois,
       avg(solde) moyenne
from soldes
group by to_char(date_cal, 'YYYY-MM')
order by 1;

Si tu dois le faire par ID_COMPTE c'est un peu plus compliqué.

17/07/2021, 21h11
bestorchidee

Bonjour,
merci beaucoup d'avoir répondu.
j'ai créé une table calendrier, en suivant ce topic , afin de récupérer les jours manquants
https://www.developpez.net/forums/d1...-jours-feries/

là je travaille avec ta requête, merci de m'avoir orienté.

Bonsoir vanagreg
effectivement j'ai besoin de calculer la moyenne pour chaque mois et pour chaque compte
j'ai modifié ta requête de la sorte :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with fourchette_date
as (select min(date_jour) min_date, max(date_jour) max_date
    from comptes
    group by (to_char(date_jour,'mm/yyyy'))
   ),
calendrier as (select min_date + level date_cal
               from fourchette_date
               connect by level <= max_date - min_date + 1
              ),
soldes as (              
select ca.date_cal,co.id_compte,
       nvl(co.id_compte, last_value(co.solde ignore nulls) over (partition by co.id_compte order by ca.date_cal rows between unbounded preceding and 1 preceding)) solde
from calendrier ca
left join comptes co
on (ca.date_cal = co.date_jour)
)
select id_compte,to_char(date_cal, 'YYYY-MM') mois,
       avg(solde) moyenne
from soldes
group by to_char(date_cal, 'YYYY-MM'),id_compte
order by 1;

mais je ne suis pas sure de moi car je n'ai jamais utilisé les requête de type over et partition , est ce que ça te parait correct ce que j'ai fait ?
merci beaucoup

Bonjour,

Ce n'est pas si simple. Il faut faire un PARTITION OUTER, c'est-à-dire une jointure externe partitionnée. Oracle possède cette fonctionnalité que d'autres SGBDR n'ont pas. En gros, pour chaque id_compte, il faut que tu puisses combler tous les jours manquants. Si tu fais une simple jointure externe sur la date, alors les jours manquants seront présents mais indépendamment du id_compte. Il faut donc "partitionner" ces jours manquants pour chaque id_compte, et c'est là que le PARTITION OUTER entre en scène.

Voici la requête:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 with fourchette_date as (select min(date_jour) min_date, max(date_jour) max_date from comptes ), calendrier as (select min_date + level - 1 date_cal from fourchette_date connect by level <= max_date - min_date + 1 ), soldes as ( select co.id_compte, ca.date_cal, coalesce(co.solde, last_value(co.solde ignore nulls) over (partition by co.id_compte order by ca.date_cal rows between unbounded preceding and 1 preceding), 0) solde from comptes co partition by (co.id_compte) right join calendrier ca on (ca.date_cal = co.date_jour) ) select id_compte, to_char(date_cal, 'YYYY-MM') mois, avg(solde) moyenne from soldes group by id_compte, to_char(date_cal, 'YYYY-MM') order by 1, 2;
Tu observes que le PARTITION OUTER se traduit ainsi dans le bloc "soldes":
Code:

1 2 3 4 from comptes co partition by (co.id_compte) right join calendrier ca on (ca.date_cal = co.date_jour)
Je partitionne donc ma jointure sur la date par id_compte, ce qui ajoutera les jours manquants de chaque mois pour chaque id_compte. Ensuite oui, il faut ajouter partition by co.id_compte dans la clause OVER de la fonction LAST_VALUE pour que la valeur soit récupérée par id_compte.
Je n'ai pas tes données donc je ne peux pas tester cette requête.

22/07/2021, 23h12
bestorchidee

Bonjour vanagreg,

merci pour ton aide. alors comme j'étais vraiment perdue,j'ai opté pour une solution longue mais simple, en s'inspirant de ta première solution.
1. j'ai crée une table de calendrier
2. j'ai crée une vue basée sur les tables compte et calendrier ,qui ajoute les dates manquante
3. une requête sur cette vue qui utilise last_value

ça m'a aidé, mais comme ta dernière requête est plus propre, je vais travailler dessus.

En tout cas , c'est grâce à l'efficacité de tes réponses que j'ai pu sortir avec un résultat, que j'espère optimiser dans les prochains jours.

Merci!
25/07/2021, 02h25
bestorchidee

Bonjour,
en fait j'ai rien modifié à la requête , elle fonctionne. GENIAL!
merci encore pour ton aide précieuse.