IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Langage SQL Discussion :

Comment unifier 2 tables qui ont la même origine ?


Sujet :

Langage SQL

  1. #1
    Membre habitué
    Inscrit en
    Novembre 2004
    Messages
    415
    Détails du profil
    Informations forums :
    Inscription : Novembre 2004
    Messages : 415
    Points : 138
    Points
    138
    Par défaut Comment unifier 2 tables qui ont la même origine ?
    Bonjour,
    J'ai une table avec des valeurs qui ont une vision flux (recours) et d'autres avec une vision stock (PSAP). Je sépare cette table en deux pour pouvoir transformer mes champs qui sont en vision flux à une vision stock, ça marche bien mais ensuite je bloque sur comment réunifier mes deux tables en une.
    J'ai essayé plusieurs approches que vous pouvez voir dans le code ci-dessous.
    Soit je génère une nouvelle CTE columnsStockFinal pour qu'elle ait le même nombre de ligne que columnsFluxToStock et que je puisse faire un inner join entre columnsFluxToStock et columnsStockFinal.
    Soit je garde columnsStock et je fais un full outer join avec columnsFluxToStock.
    Pour chaque date manquante, une ligne doit apparaître avec la valeur PSAP qui correspond au dernier stock connu à date du tuple/clef : Silo / GAR / Lob.
    Ce qui fait que :
    Pour 2023-03-31 / Completude / GAR1 / lob1, je devrais avoir la dernière valeur de PSAP disponible directement antérieurement, donc au 2023-02-28, c'est-à-dire 2100.
    Pour 2023-03-31 / EDI / GAR1 / lob1, je devrais avoir la dernière valeur de PSAP disponible directement antérieurement, donc au 2023-02-28, c'est-à-dire 1100.
    Pour 2023-04-30 / EDI / GAR1 / lob1, je devrais avoir la dernière valeur de PSAP disponible directement antérieurement, donc au 2023-02-28 ou au 2023-03-31, c'est-à-dire 1100.
    Bien sûr, les valeurs ne doivent pas être mises en dur.

    Il faut donc que j'obtienne le résultat suivant :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    ViewDate	Silo	GAR	Lob	recours	PSAP
    2023-01-31	Completude	GAR1	lob1	100	2000
    2023-01-31	EDI	GAR1	lob1	10	1000
    2023-02-28	Completude	GAR1	lob1	150	2100
    2023-02-28	EDI	GAR1	lob1	15	1100
    2023-03-31	Completude	GAR1	lob1	150	2100
    2023-03-31	EDI	GAR1	lob1	15	1100
    2023-04-30	Completude	GAR1	lob1	210	2200
    2023-04-30	EDI	GAR1	lob1	15	1100
    2023-05-31	Completude	GAR1	lob1	280	2300
    2023-05-31	EDI	GAR1	lob1	21	1200
    Voici le code que j'ai fait jusqu'à maintenant avec d'autres de mes essais en commentaire :
    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
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    drop table if exists ColumnsMixedFluxStock;
    CREATE TABLE ColumnsMixedFluxStock (
        ViewDate DATE,
        silo varchar(10),
        GAR varchar(10),
        lob varchar(10),
        recours int,
        PSAP int
    );
     
    INSERT INTO ColumnsMixedFluxStock VALUES
    ('2023-01-31', 'EDI', 'GAR1', 'lob1', 10, 1000),
    ('2023-02-28', 'EDI', 'GAR1', 'lob1', 5, 1100),
    		('2023-05-31', 'EDI', 'GAR1', 'lob1', 6, 1200),
     
    ('2023-01-31', 'Completude', 'GAR1', 'lob1', 100, 2000),
    ('2023-02-28', 'Completude', 'GAR1', 'lob1', 50, 2100)
     
    		,('2023-04-30', 'Completude', 'GAR1', 'lob1', 60, 2200),
    		 ('2023-05-31', 'Completude', 'GAR1', 'lob1', 70, 2300)
    ;
     
    with ColumnsFlux as (
    select Silo, ViewDate, GAR, Lob, recours
    from ColumnsMixedFluxStock
    ),
    ColumnsStock as (
    select Silo, ViewDate, GAR, Lob, PSAP
    from ColumnsMixedFluxStock
    ),
    min_max_dates AS (
      SELECT MIN(ViewDate) as min_date, MAX(ViewDate) as max_date
      FROM ColumnsMixedFluxStock
    ),
    date_range AS (
      SELECT explode(sequence(to_date(min_date), to_date(max_date), interval 1 month)) as ViewDate
      FROM min_max_dates
    ),
    columnsFluxToStock AS (
    SELECT i1.ViewDate, i2.silo, i2.GAR, i2.Lob,
           SUM(coalesce(i2.recours, 0)) as recours
    FROM  ColumnsFlux i2
    cross join date_range i1 on 
                       i1.ViewDate >= i2.ViewDate
    GROUP BY i1.ViewDate, i2.silo, i2.GAR, i2.Lob
    )
     
    /*columnsStockFinal AS (
    SELECT i1.ViewDate, i2.silo, i2.GAR, i2.Lob,
           coalesce(last_value(i2.PSAP) OVER (PARTITION BY /*i2.ViewDate,*/    i2.silo, i2.GAR, i2.Lob ORDER BY i2.ViewDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) as PSAP
    FROM date_range i1
    LEFT JOIN ColumnsStock i2 on 
                       i1.ViewDate >= i2.ViewDate
    ) select * from columnsStockFinal order by ViewDate, Silo, GAR, Lob*/
     
    /*all_combinations AS (
      SELECT dr.ViewDate, cs.silo, cs.GAR, cs.Lob
      FROM date_range dr
      CROSS JOIN (SELECT DISTINCT silo, GAR, Lob FROM ColumnsStock) cs
    ),
    columnsStockFinal AS (
      SELECT ac.ViewDate, ac.silo, ac.GAR, ac.Lob,
             COALESCE(LAST_VALUE(cs.PSAP) OVER (PARTITION BY cs.silo, cs.GAR, cs.Lob ORDER BY ac.ViewDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS PSAP
      FROM all_combinations ac
      LEFT JOIN ColumnsStock cs ON ac.silo = cs.silo AND ac.GAR = cs.GAR AND ac.Lob = cs.Lob AND ac.ViewDate = cs.ViewDate
    ) select * from columnsStockFinal order by ViewDate, Silo, GAR, Lob*/
     
    /*
    columnsStockFinal AS (
    SELECT i1.ViewDate, i2.silo, i2.GAR, i2.Lob,
           coalesce(i2.PSAP, 0) as PSAP
    FROM  ColumnsStock i2
    cross join date_range i1 on 
                       i1.ViewDate = i2.ViewDate
    GROUP BY i1.ViewDate, i2.silo, i2.GAR, i2.Lob
    ) --select * from columnsStockFinal order by ViewDate, Silo, GAR, Lob
    */
     
     
     
    /*select cs.Silo, cs.ViewDate, cs.GAR, cs.Lob,
      cfts.recours,
      cs.PSAP
    from columnsStockFinal cs  --columnsStock cs 
    inner join
    --full outer join
    --inner join après avoir fait une autre CTE columnsStockFinal avec un cross join de ColumnsStock et date_range
    columnsFluxToStock cfts on 
                            cs.Silo = cfts.Silo
                        AND cs.ViewDate = cfts.ViewDate
                				AND cs.GAR = cfts.GAR
                				AND cs.Lob = cfts.Lob
    --where recours != 0 or PSAP != 0
    order by ViewDate, Silo, GAR, Lob*/
     
    select coalesce(cs.Silo, cfts.Silo) as Silo, coalesce(cs.ViewDate, cfts.ViewDate) ViewDate, coalesce(cs.GAR, cfts.GAR) as GAR, coalesce(cs.Lob, cfts.Lob) as Lob,
      cfts.recours,
      cs.PSAP
    from columnsStock cs  --columnsStock cs
    full outer join
    columnsFluxToStock cfts on
                            cs.Silo = cfts.Silo
                        AND cs.ViewDate = cfts.ViewDate
                        AND cs.GAR = cfts.GAR
                        AND cs.Lob = cfts.Lob
    --where recours != 0 or PSAP != 0
    order by ViewDate, Silo, GAR, Lob
    Merci pour votre aide
    Pour info, je suis sur Databricks avec du spark sql

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 129
    Points : 38 527
    Points
    38 527
    Billets dans le blog
    9
    Par défaut
    @jmclej : quelle est la différence avec ce sujet-ci ?

    https://www.developpez.net/forums/d2...-vision-stock/

  3. #3
    Membre habitué
    Inscrit en
    Novembre 2004
    Messages
    415
    Détails du profil
    Informations forums :
    Inscription : Novembre 2004
    Messages : 415
    Points : 138
    Points
    138
    Par défaut
    Bonjour,
    Il s'agit de 2 problematiques distinctes qui se basent sur un jeu de données proche.
    Le 1er avait trait à comment gérer des dates manquantes et transformer en même temps des flux en stock : ce problème est résolu.
    Ce topic-ci intègre la résolution précédente mais je bloque sur comment accomplir la problématique de dates manquantes avec des données qui sont en vision stock, afin de pouvoir relier avec mes données que j'ai transformé de flux en stock.

  4. #4
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 129
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 129
    Points : 38 527
    Points
    38 527
    Billets dans le blog
    9
    Par défaut
    Je n'ai pas compris l'intérêt de charger une table à partir des deux tables initiales pour finalement essayer de distinguer dans cette table consolidée ce qui est un flux de ce qui est un stock...

    Quoi qu'il en soit, les lignes marquées "EDI" sont les lignes flux et celles marquées "COMPLETUDE" sont les lignes stock ?

    EDIT : et "recours", c'est quoi ?

  5. #5
    Membre habitué
    Inscrit en
    Novembre 2004
    Messages
    415
    Détails du profil
    Informations forums :
    Inscription : Novembre 2004
    Messages : 415
    Points : 138
    Points
    138
    Par défaut
    Bonjour,
    Pour "recours", j'aurais pu mettre toto titi tata, peu importe, c'est juste un montant.
    Ce qui est en visions stock, c'est la colonne PSAP (qui est un autre type de montant) et ce qui est en vision flux à la base c'est la colonne recours, peu importe le Silo.

    Pour clarifier la vision stock/flux, voici un exemple :
    Imaginez la tirelire d'un enfant. En janvier, ses parents lui donnent 100€. Il ne dépense rien, donc à la fin de janvier, il y a une vision stock de 100€ et une vision flux de 100€ également. En février, il obtient 200€ et ne dépense rien, donc à la fin février, il y a 200€ en vision flux mais 300€ en vision stock. En mars, il n'a rien, donc la vision stock est encore de 300€ et tandis que la vision flux devrait être de 0€, dans mon jeu de données, il n'y a pas dedonnées du tout (mais ce qui est égale à une vision flux de 0€).

    A la base, je n'ai qu'une table qui a des colonnes de montants qui sont en vision flux et d'autres en vision stock. Je scinde en deux tables pour effectuer mon traitement de transformation des montants qui sont en flux vision à des montants en vision stock .Je veux ensuite réunir de nouveau les tables pour n'en présenter qu'une vu que tous les montants sont désormais en vision stock. Mais je ne dois pas perdre de lignes dans cette réunification et c'est là où je bloque car mes 2 tables n'ont plus le même nombre de lignes vu que les trous des dates manquantes ont été comblés dans ma transformation vision flux-->vision stock, mais que ces trous n'ont pas été comblés dans la table de vision stock.
    Je cherche donc à savoir comment rajouter les lignes avec les dates manquantes pour la table ColumnsStock mais avec une logique différente que pour columnsFluxToStock car ici je dois mettre dans PSAP des nouvelles lignes créés la dernière valeur connue à date.

  6. #6
    Expert éminent
    Avatar de Séb.
    Profil pro
    Inscrit en
    Mars 2005
    Messages
    5 089
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations professionnelles :
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2005
    Messages : 5 089
    Points : 8 187
    Points
    8 187
    Billets dans le blog
    17
    Par défaut
    A la base, je n'ai qu'une table qui a des colonnes de montants qui sont en vision flux et d'autres en vision stock. Je scinde en deux tables pour effectuer mon traitement de transformation des montants qui sont en flux vision à des montants en vision stock.
    C'est pas très clair tout ça.

    La table contient naturellement les mouvements (le flux), le reste (le stock) ce n'est que du calcul à placer dans une vue ou une table analytique de rapport. Il n'y a rien à "reconstituer".

    Pour les trous dans les dates il faut faire un calendrier sur lequel te baser, soit avec une CTE récursive, soit avec une table spécialisée.

    À mon sens on mélange ici 2 problèmes distincts avec trop d'informations, ce qui brouille l'ensemble.
    Un problème exposé clairement est déjà à moitié résolu
    Keep It Smart and Simple

  7. #7
    Membre habitué
    Inscrit en
    Novembre 2004
    Messages
    415
    Détails du profil
    Informations forums :
    Inscription : Novembre 2004
    Messages : 415
    Points : 138
    Points
    138
    Par défaut
    Voici les mêmes données présentés avec des titres que j'espère plus parlant et ce qui est attendu en photo (vous pouvez faire un select * de chacune des CTE de mon code pour voir le contenu):

    Nom : vision_flux_stock.PNG
Affichages : 61
Taille : 72,1 Ko

  8. #8
    Expert éminent
    Avatar de Séb.
    Profil pro
    Inscrit en
    Mars 2005
    Messages
    5 089
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France

    Informations professionnelles :
    Secteur : High Tech - Opérateur de télécommunications

    Informations forums :
    Inscription : Mars 2005
    Messages : 5 089
    Points : 8 187
    Points
    8 187
    Billets dans le blog
    17
    Par défaut
    Bref, tu veux boucher les trous pour les périodes où il n'y aurait pas de mouvement/flux, c'est ça ?

    Ton exemple est bon ou j'ai raté une subtilité ?

    Prenons Alexandre
    Le stock de la ligne c'est avant ou après application du flux de la ligne ?
    Je trouve plus naturel après application du flux de la ligne

    Ici, si je reprends ton exemple d'entrée, je ne comprends pas la logique :

    Période		Cible		Flux		Stock
    2023-01-31	Alexandre	100		2000
    2023-02-28	Alexandre	50		2100
    2023-04-30	Alexandre	60		2200
    2023-05-31	Alexandre	70		2300
    Je comprends le résultat attendu comme étant :

    Période		Cible		Flux		Stock
    2023-01-31	Alexandre	100		2000		# Stock initial 1900
    2023-02-28	Alexandre	50		2050
    2023-03-31	Alexandre	0		2050
    2023-04-30	Alexandre	60		2110
    2023-05-31	Alexandre	70		2180
    vous pouvez faire un select * de chacune des CTE de mon code pour voir le contenu
    explode() et sequence() ne sont pas supportés par MySQL.
    Un problème exposé clairement est déjà à moitié résolu
    Keep It Smart and Simple

  9. #9
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Customer Success Manager @Vertica
    Inscrit en
    Septembre 2008
    Messages
    8 452
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Customer Success Manager @Vertica
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 452
    Points : 17 820
    Points
    17 820
    Par défaut
    Oui c'est étrange ce que vous faites. Si le stock ne bouge pas le flux devrait être à 0 non ?
    Vérifiez votre exemple.

Discussions similaires

  1. Réponses: 3
    Dernier message: 08/03/2010, 13h36
  2. Rechercher BD, nom des tables qui ont une colonne "location"
    Par arnaudperfect dans le forum Requêtes
    Réponses: 5
    Dernier message: 22/10/2008, 11h33
  3. comment exclure les champs qui ont pour valeur NULL
    Par agur29 dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 05/10/2007, 19h23
  4. comment relier une table qui a deux maîtres
    Par tchimou dans le forum Bases de données
    Réponses: 7
    Dernier message: 07/05/2007, 20h15
  5. Réponses: 3
    Dernier message: 21/11/2006, 18h26

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo