Jointure complexe / intervalle de date

[Isildur]

bonjour à tous,
je me permet de vous soumettre un cas de jointure qui se révèle être un vrai casse tête.

j'ai une table de fait (des facturations) qui de manière simplifiée se présente ainsi :

sociéte / devise / client / chiffres_d_affaires / date_de_facture

je cherche à faire une jointure sur une table devise :

sociéte/ devise/date_de_debut/taux_de_change

cette table contient les différents taux de change valable pour un intervalle de temps imprévisible (puisqu'il est possible à tous moment d'ajouter un taux de change pour une date donnée, ce taux sera valable à partir de la date en question, jusqu'à la date correspondant à l'enregistrement suivant)

les enregistrements ont donc cet aspect :

sociéte/ devise/date_de_debut/taux_de_change

001/USD/01-05-10/1.28
001/USD/05-06-10/1.27
001/USD/10-08-10/1.31
etc...

mon objectif étant bien entendu de pouvoir rapprocher les factures des taux de change valables pour la date de facturation.

merci de votre aide.

[asmduty]

Dans une sous requête cherches les dates inférieures à la date de la facture.

001/USD/01-05-10/1.28
001/USD/05-06-10/1.27
001/USD/10-08-10/1.31

facture du 08-06-10 tu élimines donc la dernière ligne

Et pour avoir seulement le taux de change qui t'interesse, utilise la fonction MAX() dans ton select, tu élimines donc la première ligne et trouve la ligne qui t'intéresse

[lola06]

Bonjour,

Quel est ton SGBD ?

Code :

1
2
3
4
5
6
7
SELECT societe, devise, client, chiffres_d_affaires, date_de_facture, max(date_de_debut), taux_de_change
FROM facturation f
     JOIN devise d
     ON d.societe = f.societe
     AND d.devise = f.devise
WHERE date_debut < date_de_facture
GROUP BY societe, devise, client, chiffres_d_affaires, date_de_facture, taux_de_change

J'ai supposé que tes clés primaires étaient societe et client...

[fsmrel]

Bonjour,

@ Lola

Il y a quelque chose qui cloche : pour la paire {société, devise} votre requête produit nécessairement une ligne pour chaque chaque taux dont la date d'effet est < à la date de facture...

[lola06]

Effectivement, je me suis plantée (fin de semaine, boulot, toussa...)

Je ne peux pas tester par contre, donc...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT t.societe, t.devise, f.client, f.chiffres_d_affaires, f.date_de_facture, f.taux_de_change
FROM (
     SELECT societe, devise, max(date_de_debut) date_debut
     FROM facturation f
          JOIN devise d
            ON d.societe = f.societe
            AND d.devise = f.devise
     WHERE date_debut < date_de_facture
     GROUP BY societe, devise) t
JOIN facturation f
  ON f.societe = t.societe
  AND f.devise = t.devise
JOIN devise d
  ON t.societe = d.societe
  AND t.devise = d.devise
  AND d.date_de_debut = t.date_debut

[Waldar]

Évitez les recalculs à la volée.
D'un point de vue modélisation, la table devise est très bien comme cela.

Il suffit de calculer la date de fin pour créer une période qui vous permettront une jointure avec un between.

Si votre SBGD supporte les fonctions de fenêtrage, utilisez la fonction LEAD.
Sinon, il faudra passer par le MIN des dates supérieures avec une autojointure.

[SQLpro]

Le mieux serait de créer une vue. C'est fait pour cela !

A +

[fsmrel]

Bonsoir Isildur,


Sur la base de votre exemple, effectuons un test en tenant compte des préconisations de Waldar. J'utilse ici SQL Server (voyez la fonction DATEADD qui sert ici à calculer J-1), adaptez s'il le faut en fonction de votre SGBD.

Création des tables :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE FACTURATION 
(
        Societe       CHAR(10)   NOT NULL
      , Devise        CHAR(10)   NOT NULL
      , Client        CHAR(10)   NOT NULL
      , DateFacture   DATE       NOT NULL
) ;
 
CREATE TABLE DEVISE 
(
        Societe       CHAR(10)       NOT NULL
      , Devise        CHAR(10)       NOT NULL
      , DateDebutTaux DATE           NOT NULL
      , TauxChange    DECIMAL(3,2)   NOT NULL
) ;

Un jeu d’essai :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c10', '2010-05-15') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c25', '2010-07-13') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c26', '2010-07-14') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c27', '2010-07-15') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c30', '2010-09-09') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c31', '2010-09-10') ;
INSERT INTO FACTURATION VALUES ('s1', 'usd', 'c32', '2010-09-11') ;
INSERT INTO FACTURATION VALUES ('s1', 'eur', 'c03', '2011-12-16') ;
 
INSERT INTO FACTURATION VALUES ('s2', 'usd', 'c1', '2010-05-20') ;
INSERT INTO FACTURATION VALUES ('s2', 'eur', 'c1', '2010-07-20') ;

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-01-01', 1.28) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-05-02', 1.40) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-06-05', 1.27) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-07-14', 1.29) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-08-01', 1.31) ;
INSERT INTO DEVISE VALUES ('s1', 'usd', '2010-09-10', 1.34) ;
INSERT INTO DEVISE VALUES ('s1', 'eur', '2010-01-01', 1.00) ;
 
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-02-10', 1.30) ;
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-07-10', 1.34) ;
INSERT INTO DEVISE VALUES ('s2', 'usd', '2010-10-10', 1.38) ;

Afin que les requêtes ne soient pas trop hermétiques, on peut utiliser une vue pour représenter les dates d’effet des devises sous la forme intervallaire (date de début, date de fin). Comme dit Waldar, on passe par une auto-jointure (1er bloc de l’UNION), avec en plus la mise en œuvre d’un intervalle dont la fin soit suffisamment éloignée dans le temps pour prendre en compte le taux du jour (2e bloc de l’UNION) :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE VIEW INTERVALLE  (Societe, Devise, DateDebutTaux, DateFinTaux, TauxChange) AS
    SELECT  x.societe, x.devise, x.DateDebutTaux, DATEADD (DAY, -1, MIN(y.DateDebutTaux)) AS DateFinTaux, x.TauxChange
    FROM   DEVISE AS x JOIN DEVISE AS y 
              ON x.Societe = y.Societe, AND x.Devise = y.Devise AND x.DateDebutTaux < y.DateDebutTaux
    GROUP BY x.societe, x.devise, x.DateDebutTaux, x.TauxChange
 
    UNION ALL
 
    SELECT x.Societe, x.Devise, y.DateDebutTaux, y.DateFinTaux, x.TauxChange 
    FROM   DEVISE AS x JOIN  
                           (
                            SELECT  societe, devise, MAX(DateDebutTaux)AS DateDebutTaux, '9999-12-31' AS DateFinTaux
                            FROM    DEVISE
                            GROUP BY societe, devise
                           ) AS y
                        ON  x.Societe = y.Societe AND x.Devise = y.Devise AND x.DateDebutTaux = y.DateDebutTaux
;

Recherche des taux pour les lignes de facturation, au moyen du BETWEEN préconisé par Waldar :

Code SQL :

1
2
3
4
5
6
SELECT x.Societe, x.Devise, x.Client, x.DateFacture, y.TauxChange
FROM   FACTURATION AS x JOIN INTERVALLE AS y
                ON  x.Societe = y.Societe
                AND x.Devise = y.Devise 
                AND x.DateFacture BETWEEN y.DateDebutTaux AND y.DateFinTaux 
;

Au résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
Societe    Devise    Client    DateFacture    TauxChange
-------    ------    ------    -----------    ----------
s1         eur       c03       2011-12-16     1.00
s1         usd       c10       2010-05-15     1.40
s1         usd       c25       2010-07-13     1.27
s1         usd       c26       2010-07-14     1.29
s1         usd       c27       2010-07-15     1.29
s1         usd       c30       2010-09-09     1.31
s1         usd       c31       2010-09-10     1.34
s1         usd       c32       2010-09-11     1.34
s2         usd       c1        2010-05-20     1.30

[fsmrel]

Hello Fred,

Je n'avais pas vu votre message, mais je constate que nous avons eu le même réflexe

[pacmann]

Salut,

On est obligé de construire des intervalles ?

Pour moi j'aurais dit que c'est la dernière date de cours inférieure ou égale à la date de facture...

Sous Oracle, avec keep :

Code :

1
2
3
4
5
6
7
 
SELECT a.*, (SELECT max(tauxchange) keep(dense_rank last ORDER BY datedebuttaux)
             FROM devise b
             WHERE a.Societe = b.Societe
                AND a.Devise = b.Devise
                AND b.datedebuttaux <= datefacture) tx
FROM facturation a

Sous Oracle, mais adaptable aux autres SGBD (en adaptant le to_number, to_char et substr) :

Code :

1
2
3
4
5
6
SELECT a.*, (SELECT to_number(substr(max(to_char(datedebuttaux, 'yyyymmdd')||tauxchange), 9)) 
             FROM devise b
             WHERE a.Societe = b.Societe
                AND a.Devise = b.Devise
                AND b.datedebuttaux <= datefacture) tx
FROM facturation a

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SOCIETE    DEVISE     CLIENT     DATEFACT         TX
---------- ---------- ---------- -------- ----------
s1         usd        c10        15/05/10        1.4
s1         usd        c25        13/07/10       1.27
s1         usd        c26        14/07/10       1.29
s1         usd        c27        15/07/10       1.29
s1         usd        c30        09/09/10       1.31
s1         usd        c31        10/09/10       1.34
s1         usd        c32        11/09/10       1.34
s1         eur        c03        16/12/11          1
s2         usd        c1         20/05/10        1.3
s2         eur        c1         20/07/10           
 
 
10 rows selected.

NB :
- comme c'est une sous-requête scalaire, ça se comporte comme une jointure ouverte : j'ai un null qui ressort pour une facture antérieure au premier cours de sa devise
- en général je suis pas très sous-requête scalaire parce que ça force le nl join ou filter... mais dans ce cas de toutes façons pas moyen de faire du hash avec des critères d'intervalle à mon avis.

[punkoff]

Envoyé par pacmann

-requête scalaire parce que ça force le nl join ou filter... mais dans ce cas de toutes façons pas moyen de faire du hash avec des critères d'intervalle à mon avis.

Salut,

Pourquoi donc ? (edit: en effet, il ne veut pas inclure les critère d'intervale dans le hash)

Je ne peux pas tester sous Oracle, mais sous postegreSql par exemple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
"Hash Join  (cost=1.79..2.98 rows=1 width=73)"
"  Output: a.societe, a.devise, a.client, a.datefacture, devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange, (COALESCE(lead((devise.datedebuttaux - 1)) OVER (?), to_date('29999-01-01'::text, 'yyyy-mm-dd'::text)))"
"  Hash Cond: ((a.societe = devise.societe) AND (a.devise = devise.devise))"
"  Join Filter: ((a.datefacture >= devise.datedebuttaux) AND (a.datefacture <= (COALESCE(lead((devise.datedebuttaux - 1)) OVER (?), to_date('29999-01-01'::text, 'yyyy-mm-dd'::text)))))"
"  ->  Seq Scan on public.facturation a  (cost=0.00..1.10 rows=10 width=37)"
"        Output: a.societe, a.devise, a.client, a.datefacture"
"  ->  Hash  (cost=1.64..1.64 rows=10 width=36)"
"        Output: devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange, (COALESCE(lead((devise.datedebuttaux - 1)) OVER (?), to_date('29999-01-01'::text, 'yyyy-mm-dd'::text)))"
"        ->  WindowAgg  (cost=1.27..1.54 rows=10 width=32)"
"              Output: devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange, COALESCE(lead((devise.datedebuttaux - 1)) OVER (?), to_date('29999-01-01'::text, 'yyyy-mm-dd'::text))"
"              ->  Sort  (cost=1.27..1.29 rows=10 width=32)"
"                    Output: devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange"
"                    Sort Key: devise.societe, devise.devise, devise.datedebuttaux"
"                    ->  Seq Scan on public.devise  (cost=0.00..1.10 rows=10 width=32)"
"                          Output: devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange"

Code :

1
2
3
4
5
 
CREATE OR REPLACE VIEW v_interval AS 
 SELECT devise.societe, devise.devise, devise.datedebuttaux, devise.tauxchange, 
coalesce(lead(devise.datedebuttaux - 1) OVER (PARTITION BY devise.societe, devise.devise ORDER BY datedebuttaux), to_date('2999-01-01', 'yyyy-mm-dd')) AS datefintaux
   FROM devise;

Code :

1
2
3
4
 
SELECT *
FROM facturation a
INNER JOIN v_interval b ON a.societe = b.societe AND a.devise = b.devise AND a.datefacture BETWEEN b.datedebuttaux AND b.datefintaux;

[Waldar]

fsmrel, je pensais plutôt à ces vues qui sont équivalentes.

Fenêtrage

Code :

1
2
3
4
5
6
7
8
CREATE VIEW v_devise (societe, devise, DateDebutTaux, DateFinTaux, TauxChange)
AS
SELECT societe
     , devise
     , DateDebutTaux
     , lead(DateDebutTaux - 1, 1, '9999-12-31') over(partition BY societe, devise ORDER BY DateDebutTaux)
     , TauxChange
  FROM devise;

Autojointure

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE VIEW v_devise (societe, devise, DateDebutTaux, DateFinTaux, TauxChange)
AS
  SELECT d1.societe
       , d1.devise
       , d1.DateDebutTaux
       , coalesce(min(d2.DateDebutTaux)-1, '9999-12-31')
       , d1.TauxChange
    FROM devise d1
         LEFT OUTER JOIN devise d2
           ON d2.societe       = d1.societe
          AND d2.devise        = d1.devise
          AND d2.DateDebutTaux > d1.DateDebutTaux
GROUP BY d1.societe
       , d1.devise
       , d1.DateDebutTaux
       , d1.TauxChange;

[pacmann]

Bah en fait effectivement, la partie "=" des conditions peut être hashée, mais je pensais à la partie comparaison... en me disant qu'il y avait sûrement plein de cours pour une devise donnée. C'est ce qui me fait penser qu'un accès index sur (société, devise, datetaux) n'est pas une mauvaise solution...

[SQLpro]

Envoyé par fsmrel

Hello Fred,

Je n'avais pas vu votre message, mais je constate que nous avons eu le même réflexe

C'est l'exemple classique de chez basic que je donne pour faire comprendre à des benêts l'intérêt des vues...

En gros je leur met une table des taux de TVA avec les dates des différents changements (luxe, normale, réduit...) et je leur demande de calculer le total d'une facture avec des tables entête et détail facture... En bref ils n'y arrivent jamais !

A +

[fsmrel]

En tout cas, Isildur n'a plus que l'embarras du choix...