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 passer d'une vision flux dans le contenu d'une table à une vision stock ?


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 passer d'une vision flux dans le contenu d'une table à une vision stock ?
    Bonjour,
    J'ai une table avec chaque ligne qui représente un flux pour la date donnée et je dois obtenir une représentation de stock pour chaque date.
    Ca fonctionne bien avec ce jeu de données et ce code :
    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
    drop table if exists columnsFlux;
    CREATE TABLE columnsFlux (
        ViewDate DATE,
        silo varchar(10),
        GAR varchar(10),
        lob varchar(10),
        recours int
    );
     
    INSERT INTO columnsFlux VALUES
    ('2023-01-31', 'EDI', 'GAR1', 'lob1', 10),
    ('2023-02-28', 'EDI', 'GAR1', 'lob1', 5),
     
    ('2023-01-31', 'Completude', 'GAR1', 'lob1', 100),
    ('2023-02-28', 'Completude', 'GAR1', 'lob1', 50);
     
    select i1.ViewDate, i1.silo, i1.GAR, i1.Lob,
                SUM(coalesce(i2.recours, 0)) as recours
            FROM 
                columnsFlux i1
            JOIN 
                columnsFlux i2 ON i1.Silo = i2.Silo
                				AND i1.GAR = i2.GAR
                				AND i1.Lob = i2.Lob
                				AND i1.ViewDate >= i2.ViewDate
            GROUP BY 
                i1.Silo,
                i1.ViewDate, 
                i1.GAR,
                i1.Lob
    order by i1.ViewDate, i1.silo, i1.GAR, i1.Lob;
    Par contre ça ne fonctionne plus avec ce jeu de données (lorsqu'une ligne n'est plus présente à une certaine date D, mais qu'il y a un stock antérieur existant, je dois quand même retourner une ligne avec le stock pour la date D) :
    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
    drop table if exists columnsFlux;
    CREATE TABLE columnsFlux (
        ViewDate DATE,
        silo varchar(10),
        GAR varchar(10),
        lob varchar(10),
        recours int
    );
     
    INSERT INTO columnsFlux VALUES
    ('2023-01-31', 'EDI', 'GAR1', 'lob1', 10),
    ('2023-02-28', 'EDI', 'GAR1', 'lob1', 5),
    ('2023-05-31', 'EDI', 'GAR1', 'lob1', 6),
     
    ('2023-01-31', 'Completude', 'GAR1', 'lob1', 100),
    ('2023-02-28', 'Completude', 'GAR1', 'lob1', 50),
     
    ('2023-04-30', 'Completude', 'GAR1', 'lob1', 60),
    ('2023-05-31', 'Completude', 'GAR1', 'lob1', 70);
     
    select i1.ViewDate, i1.silo, i1.GAR, i1.Lob,
                SUM(coalesce(i2.recours, 0)) as recours
            FROM 
                columnsFlux i1
            JOIN 
                columnsFlux i2 ON i1.Silo = i2.Silo
                				AND i1.GAR = i2.GAR
                				AND i1.Lob = i2.Lob
                				AND i1.ViewDate >= i2.ViewDate
            GROUP BY 
                i1.Silo,
                i1.ViewDate, 
                i1.GAR,
                i1.Lob
    order by i1.ViewDate, i1.silo, i1.GAR, i1.Lob;
    Ici, il manque dans le résultat deux lignes au '2023-03-31' qui doivent reprendre le stock tel qu'il était au '2023-02-28' pour le tuple 'EDI' / 'GAR1' / 'lob1' et pour le tuple 'Completude', 'GAR1', 'lob1', car même s'il n'y a pas eu de nouveau flux au mois de mars, le stock, lui, doit bien ressortir. De la même manière, il manque une ligne au '2023-04-30' pour le stock du tuple 'EDI' / 'GAR1' / 'lob1'.
    Au '2023-05-31', le montant de recours est bien correcte dans le résultat de ma requête. En effet, pour chaque date où il existe un stock du tuple à la date précédente, c'est comme s'il fallait simuler l'ajout d'une ligne avec ce tuple et un flux de valeur 0 pour la colonne recours.
    Pour info, je suis sur Databricks avec du spark sql.

  2. #2
    Expert éminent sénior
    Homme Profil pro
    Responsable Données
    Inscrit en
    Janvier 2009
    Messages
    5 197
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Hérault (Languedoc Roussillon)

    Informations professionnelles :
    Activité : Responsable Données

    Informations forums :
    Inscription : Janvier 2009
    Messages : 5 197
    Points : 12 772
    Points
    12 772
    Par défaut
    Bonjour,
    Pour moi le plus simple ici est de partir d'une table calendrier, qui permet d'avoir "toutes les dates", même pour celles pour lesquelles il n'y a pas de ligne dans la table des flux.
    SqlPro nous a fait tout un article sur ce genre de table: c'est .

    Tatayo.

  3. #3
    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 539
    Points
    38 539
    Billets dans le blog
    9
    Par défaut
    Bonjour,

    Demande très semblable à ce sujet-ci :
    https://www.developpez.net/forums/d2.../#post12003238

  4. #4
    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
    J'ai trouvé la solution suivante :
    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
    drop table if exists ColumnsFlux;
    CREATE TABLE ColumnsFlux (
        ViewDate DATE,
        silo varchar(10),
        GAR varchar(10),
        lob varchar(10),
        recours int
    );
     
    INSERT INTO ColumnsFlux VALUES
    ('2023-01-31', 'EDI', 'GAR1', 'lob1', 10),
    ('2023-02-28', 'EDI', 'GAR1', 'lob1', 5),
    		('2023-05-31', 'EDI', 'GAR1', 'lob1', 6),
     
    ('2023-01-31', 'Completude', 'GAR1', 'lob1', 100),
    ('2023-02-28', 'Completude', 'GAR1', 'lob1', 50)
     
    		,('2023-04-30', 'Completude', 'GAR1', 'lob1', 60),
    		 ('2023-05-31', 'Completude', 'GAR1', 'lob1', 70)
    ;
     
    with 
    min_max_dates AS (
      SELECT MIN(ViewDate) as min_date, MAX(ViewDate) as max_date
      FROM ColumnsFlux
    ),
    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
    )
    select * from columnsFluxToStock
    order by ViewDate, Silo, GAR, Lob

  5. #5
    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
    Je n'ai pas encore de databricks sous la main, mais j'aurai écrit quelque chose comme ceci :
    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
    with cte_stock (ViewDate, NextViewDate, silo, GAR, Lob, recours_stock) as
    (
    select ViewDate + 1
         , case
             when months_between(lead(ViewDate + 1, 1, ViewDate + 1) over (partition by silo, GAR, Lob order by ViewDate asc), ViewDate + 1) > 1
             then add_months(lead(ViewDate + 1, 1, ViewDate + 1) over (partition by silo, GAR, Lob order by ViewDate asc), -1)
             else ViewDate + 1
           end
         , silo, GAR, Lob
         , sum(recours) over (partition by silo, GAR, Lob order by ViewDate asc rows unbounded preceding)
      from columnsFlux
    )
      select dt.col - 1 as ViewDate
           , t.silo, t.GAR, t.Lob
           , t.recours_stock - lag(t.recours_stock, 1, 0) over (partition by silo, GAR, Lob order by dt.col asc) as recours
           , t.recours_stock
        from cte_stock as t
           , lateral explode(sequence(t.ViewDate, t.NextViewDate, interval 1 month)) as dt
    order by t.silo, t.GAR, t.Lob, ViewDate asc;
     
     
    ViewDate    silo        GAR   Lob   recours  recours_stock
    ----------  ----------  ----  ----  -------  -------------
    2023-01-31  Completude  GAR1  lob1      100            100
    2023-02-28  Completude  GAR1  lob1       50            150
    2023-03-31  Completude  GAR1  lob1        0            150
    2023-04-30  Completude  GAR1  lob1       60            210
    2023-05-31  Completude  GAR1  lob1       70            280
    2023-01-31  EDI         GAR1  lob1       10             10
    2023-02-28  EDI         GAR1  lob1        5             15
    2023-03-31  EDI         GAR1  lob1        0             15
    2023-04-30  EDI         GAR1  lob1        0             15
    2023-05-31  EDI         GAR1  lob1        6             21
    Edit: après avoir activé mon databricks community j'ai corrigé la requête ci-dessus.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 12
    Dernier message: 11/03/2016, 18h05
  2. Réponses: 1
    Dernier message: 09/04/2013, 10h46
  3. Réponses: 1
    Dernier message: 10/01/2012, 15h35
  4. Réponses: 9
    Dernier message: 12/08/2006, 13h01
  5. Réponses: 7
    Dernier message: 13/12/2005, 16h22

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