Comment passer d'une vision flux dans le contenu d'une table à une vision stock ?

**jmclej** · 13/02/2024, 21h20

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.

**tatayo** · 14/02/2024, 07h15

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 là.

Tatayo.

**escartefigue** · 14/02/2024, 14h32

Bonjour,

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

**jmclej** · 14/02/2024, 18h25

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

**Waldar** · 15/02/2024, 16h18

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.

Comment passer d'une vision flux dans le contenu d'une table à une vision stock ?

Langage SQL

Discussions similaires

Partager

Partager