Lier deux tables indépendantes par dates

Version imprimable

18/07/2012, 11h32
fprovot

Lier deux tables indépendantes par dates

Bonjour,
je cherche depuis un bon moment en étudiant les fonctions analytiques et en cherchant des exemples, mais je n'aboutis pas.

Sur Oracle 11g, j'ai besoin de lier deux tables sans lien (oui ça part mal), pour lister disons toutes les ventes réalisées (table ventes) entre chaque collecte d'argent (table transfert). Bien sûr rien ne lie les deux tables, et les dates de l'une ne sont pas identiques aux dates de l'autre.

Table Ventes
| id_vente1 | id_produit1 | montant_vente1 | date_vente1 |
| id_vente2 | id_produit2 | montant_vente2 | date_vente2 |
| id_vente3 | id_produit3 | montant_vente3 | date_vente3 |
...

Table Transferts
| id_transfert1 | montant_transfert1 | date_transfert1 |
| id_transfert2 | montant_transfert2 | date_transfert2 |
| id_transfert3 | montant_transfert3 | date_transfert3 |
| id_transfert4 | montant_transfert4 | date_transfert4 |

Résultat souhaité, trié dans l'ordre des dates, chaque ligne contient en plus la date du transfert suivant(!):

| date_transfert1 | date_transfert2 | montant_transfert1 | date_vente1 | id_produit1 | montant_vente1 |
| date_transfert1 | date_transfert2 | montant_transfert1 | date_vente2 | id_produit2 | montant_vente2 |
| date_transfert1 | date_transfert2 | montant_transfert1 | date_vente3 | id_produit3 | montant_vente3 |
| date_transfert1 | date_transfert2 | montant_transfert1 | date_vente4 | id_produit4 | montant_vente4 |
| date_transfert2 | date_transfert3 | montant_transfert2 | date_vente5 | id_produit5 | montant_vente5 |
| date_transfert2 | date_transfert3 | montant_transfert2 | date_vente6 | id_produit6 | montant_vente6 |
| date_transfert2 | date_transfert3 | montant_transfert2 | date_vente7 | id_produit7 | montant_vente7 |
| date_transfert3 | date_transfert4 | montant_transfert3 | date_vente8 | id_produit8 | montant_vente8 |
| date_transfert3 | date_transfert4 | montant_transfert3 | date_vente9 | id_produit9 | montant_vente9 |
etc...

(ici 4 ventes ont eu lieues entre les deux premières collectes)

A l'aide de la fonction lead, j'obtiens bien la partie gauche, mais la totalité des ventes sont répétées par groupe de transfert (de date_transfertN à date_transfertN+1, et c'est bien normal car je n'ai pas de clause reliant les transferts et les ventes).
Vers quelle technique devrais-je m'orienter pour limiter les ventes affichées selon les bornes de dates de transfert?
Merci
18/07/2012, 11h37
Waldar
Par rapport à votre résultat souhaité, on est bien d'accord que :
1. date_transfert1 <= date_vente1 <= date_vente2 <= date_vente3 <= date_vente4 <= date_transfert2
2. date_transfert2 <= date_vente5 <= date_vente6 <= date_vente7 <= date_transfert3
3. date_transfert3 <= date_vente8 <= date_vente9 <= date_transfert4
18/07/2012, 11h41
fprovot

Exactement; tout est ordonné "logiquement". (j'avais peur que ce ne soit pas très compréhensible en effet)

Bonjour,

Voici une proposition de solution, mais sans les fonctions analytiques :

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 vente as (
select 1 idVente, 1 idProduit, 10 MntVente, to_date('01/01/2012', 'dd/mm/yyyy') DateVente from dual union all
select 2 idVente, 2 idProduit, 20 MntVente, to_date('02/01/2012', 'dd/mm/yyyy') DateVente from dual union all
select 3 idVente, 1 idProduit, 30 MntVente, to_date('03/01/2012', 'dd/mm/yyyy') DateVente from dual union all
select 4 idVente, 2 idProduit, 40 MntVente, to_date('04/01/2012', 'dd/mm/yyyy') DateVente from dual union all
select 5 idVente, 1 idProduit, 50 MntVente, to_date('05/01/2012', 'dd/mm/yyyy') DateVente from dual 
),
transfert as (
Select 1 idTransfert, 0 MntTransfert, to_date('31/12/2011', 'dd/mm/yyyy') DateTransfert From dual union all
Select 2 idTransfert, 30 MntTransfert, to_date('02/01/2012', 'dd/mm/yyyy') DateTransfert From dual union all
Select 3 idTransfert, 120 MntTransfert, to_date('06/01/2012', 'dd/mm/yyyy') DateTransfert From dual 
)
Select T1.DATETRANSFERT, T2.DATETRANSFERT, T2.MNTTRANSFERT, V.DATEVENTE, V.IDPRODUIT, V.MNTVENTE
  From TRANSFERT T1
  Join TRANSFERT T2 On T1.DATETRANSFERT < T2.DATETRANSFERT
                   And Not Exists (Select 1
                          From TRANSFERT T
                         Where T.DATETRANSFERT > T1.DATETRANSFERT
                           And T.DATETRANSFERT < T2.DATETRANSFERT)
  Join VENTE V On V.DATEVENTE > T1.DATETRANSFERT
              And V.DATEVENTE <= T2.DATETRANSFERT
 Order By T1.DATETRANSFERT, T2.DATETRANSFERT, V.DATEVENTE

Créez une vue v_Transferts :

Code:

1
2
3
4
5
CREATE VIEW v_Transferts AS
select id_transfert, montant_transfert
     , date_transfert as date_transfert_deb
     , lead(date_transfert, 1, date '9999-12-31') over(order by id_transfert asc) as date_transfert_fin
  from Transferts;

Après il ne reste plus qu'à écrire une requête BETWEEN :

Code:

1
2
3
4
select ...
  from v_Transferts tr
       inner join Ventes vt
         on vt.date_vente between tr.date_transfert_deb and tr.date_transfert_fin;

Woahou, c'est beau.

Et ça marche effectivement! Enfin j'ai eu des problème avec mes données de tests car je me suis aperçu que j'avais parfois plusieurs enregistrement de transfert à la même date, car il peut y avoir plusieurs containers collectés/remplis en même temps. Il faudra que j'ajoute un distinct.
Autre soucis, si je n'ai pas de vente entre deux transferts, je perd le transfert de base. Une jointure ouverte devrait résoudre ça je pense.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH vente AS (
SELECT 1 idVente, 1 idProduit, 10 MntVente, to_date('01/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 2 idVente, 2 idProduit, 20 MntVente, to_date('02/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 3 idVente, 3 idProduit, 30 MntVente, to_date('03/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 4 idVente, 4 idProduit, 40 MntVente, to_date('04/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 5 idVente, 5 idProduit, 50 MntVente, to_date('05/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 6 idVente, 6 idProduit, 60 MntVente, to_date('05/01/2012', 'dd/mm/yyyy') DateVente FROM dual union ALL
SELECT 7 idVente, 7 idProduit, 70 MntVente, to_date('08/01/2012', 'dd/mm/yyyy') DateVente FROM dual 
),
transfert AS (
SELECT 1 idTransfert, 0 MntTransfert, to_date('31/12/2011', 'dd/mm/yyyy') DateTransfert FROM dual union ALL
SELECT 2 idTransfert, 30 MntTransfert, to_date('02/01/2012', 'dd/mm/yyyy') DateTransfert FROM dual union ALL
SELECT 3 idTransfert, 120 MntTransfert, to_date('06/01/2012', 'dd/mm/yyyy') DateTransfert FROM dual union ALL
SELECT 4 idTransfert, 130 MntTransfert, to_date('07/01/2012', 'dd/mm/yyyy') DateTransfert FROM dual  union ALL
SELECT 5 idTransfert, 140 MntTransfert, to_date('09/01/2012', 'dd/mm/yyyy') DateTransfert FROM dual 
)

Encore merci, j'étais surpris de ne pas avoir besoin de fonction analytique, et je vais tâcher d'appliquer ça.
Et passer (très bientôt) le thread en résolu...

18/07/2012, 12h34
fprovot

Ah ben je vais tester aussi la solution de Waldar... Deux solution à mon problème alors que je n'avais pas grand espoir, voilà une bonne journée! ;)

Je retombe sur mon thread, du coup j'en profite pour le compléter avec la solution finale, un an après ! :ccool: ...

Code:

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
36
37
 
WITH 
LISTE_TRANSFERTS AS ( 
        SELECT
            ...
            lead( SESSION_FINANCE.SES_DATE_OUVERTURE,1,sysdate) over (order by  SESSION_FINANCE.SES_DATE_OUVERTURE) as TRA_DATE_SUIVANTE,
            lag( SESSION_FINANCE.SES_DATE_OUVERTURE,1,TO_DATE('01/01/2001','dd/mm/yyyy')) over (order by  SESSION_FINANCE.SES_DATE_OUVERTURE) as TRA_DATE_PRECEDENTE,
            sum(DWF_TRANSFERT.TRA_MONT_COLLECTE) as MONT_TRANSFERE
        FROM
            ...
        WHERE
            ...
        GROUP BY SESSION_FINANCE.SES_ID_EQUIPEMENT, 
            ...
    ) ,         
LISTE_VENTES AS (
        SELECT
            ...
            DWF_VENTE.VEN_DATE_VENTE,
            ...
        FROM
            ...
        WHERE
            ...
        GROUP BY
            ...
    )
SELECT
            ...
FROM
  LISTE_TRANSFERTS,
  LISTE_VENTES
WHERE
    ( LISTE_VENTES.EQU_ID_EQUIPEMENT(+)=LISTE_TRANSFERTS.SES_ID_EQUIPEMENT )
  AND  ( LISTE_VENTES.VEN_DATE_VENTE(+) > LISTE_TRANSFERTS.TRA_DATE_PRECEDENTE )
  AND  ( LISTE_VENTES.VEN_DATE_VENTE(+) <= LISTE_TRANSFERTS.TRA_DATE )
ORDER BY LISTE_TRANSFERTS.TRA_DATE , LISTE_VENTES.VEN_DATE_VENTE