Discussion :

[Sparky95]

Bonjour,
Voila je suis un peu embêté car je ne suis pas un AS en requêtes SQL et j'aurais du coup besoin de votre aide.

Je possède 2 tables
-GiftCard (id, amount)
-Transaction(id, amount, GiftCard, created_at)

Ce que je tente de générer comme tableau

GiftCard id	Trasaction id	Transaction amount	Amount	Date
1	8	-3,20 €	8,43 €	10/9/2020
1	7	-33,37 €	11,63 €	25/7/2020
1	6	45,00 €	45,00 €	30/12/2019
1	5	-28,79 €	0,00 €	16/5/2018
1	4	-21,21 €	28,79 €	12/7/2017
1	3	50,00 €	50,00 €	8/5/2017
1	2	-10 €	0,00 €	20/9/2016
1	1	10 €	10 €	19/9/2016

Je possède donc GiftCard id, Transaction id, Transaction amount et Date.
En ce qui concerne Amount je ne possède que la valeur final, à savoir 8,43€ dans l'exemple.
Comment puis-je faire une requête se basant sur la valeur précédente pour calculer la valeur suivant?
Est-il possible de le faire directement avec une requête SQL ou dois-je passer par du PL/SQL?

Merci d'avance,
Sparky

[alassanediakite]

Salut
A vérifier...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
select c.id as giftcard_id, t.id as transaction_id, t.amount as transaction_amount, 
--la somme des transactions antérieures plus le montant de la transaction en cours
(select sum(t2.amount) from trasaction as t2 where t.giftcard=t2.giftcard and t2.id<=t.id) as amount_ligne
date
from giftcard as c inner join trasaction as t on c.id=t.giftcard

NB: ici j'utilise t2.id<=t.id mais il est mieux de faire t2.date<=t.date si les transaction ont des dates!
@+

[escartefigue]

NB: ici j'utilise t2.id<=t.id mais il est mieux de faire t2.date<=t.date si les transaction ont des dates!
@+

Non seulement il vaut mieux, mais surtout il ne faut jamais utiliser des id de type serial (attribués par le SGGBD) comme critère chronologique.
Il n'y a aucune garantie que les n° soient attribués dans l'ordre chronologique.
Il faut donc s'appuyer sur la colonne date en utilisant par exemple la fonction LAG() pour rechercher la valeur de la ligne précédente.
cf. https://www.postgresql.org/docs/8.4/...ns-window.html

[Sparky95]

Nickel cela fonctionnes
Merci beaucoup


Ajout:
@escartefigue J'ai lu la documentation concernant la fonction LAG qui permets donc de ce déplacer d'x étape(s) cependant, je n'ai pas réussi à générer la requête qui utiliserait la méthode

Code sql :

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
 
DROP TABLE IF EXISTS gift_cards CASCADE;
DROP TABLE IF EXISTS transactions;
 
 
CREATE TABLE gift_cards(
    id INT PRIMARY KEY,
    amount NUMERIC(15, 5)
);
 
 
CREATE TABLE transactions(
    id SERIAL PRIMARY KEY,
    payment NUMERIC(15, 5),
    gift_card_id INT,
    created_at TIMESTAMP,
    FOREIGN KEY (gift_card_id) REFERENCES gift_cards(id)
);
 
 
INSERT INTO gift_cards(id, amount) VALUES (1, 8.43);
 
 
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (10, 1, '19/09/2016');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (-10, 1, '20/09/2016');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (50, 1, '08/05/2017');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (-21.21, 1, '12/07/2017');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (-28.79, 1, '16/05/2018');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (45, 1, '30/12/2019');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (-33.37, 1, '27/07/2020');
INSERT INTO transactions(payment, gift_card_id, created_at) VALUES (-3.20, 1, '10/09/2020');
 
 
 
 
SELECT c.id AS gift_card_id, t.id AS transaction_id, t.payment AS transaction_payment, 
--la somme des transactions antérieures plus le montant de la transaction en cours,
(SELECT SUM(t2.payment) FROM transactions AS t2 WHERE t.gift_card_id = t2.gift_card_id AND t2.created_at <= t.created_at) AS amount_ligne,
LAG(t.payment, 1) OVER ( ORDER BY created_at) --si j'ai bien suivi le but est de remplacer le select du dessus par le LAG ?
created_at
FROM gift_cards AS c INNER JOIN transactions AS t ON c.id = t.gift_card_id;

Voici une copie de mon code de test.


Merci d'avance pour votre aide,
Sparky

[escartefigue]

bonjour Sparky95,


À cette question

Comment puis-je faire une requête se basant sur la valeur précédente pour calculer la valeur suivant(e) ?
Est-il possible de le faire directement avec une requête SQL ou dois-je passer par du PL/SQL?

J'avais proposé la fonction LAG() qui permet de récupérer les n valeurs précédentes, n valant 1 par défaut.
Mais, si la solution proposée par alassanediakite convient, c'est que l'expression de besoin n'est pas bien formulée : c'est le cumul de toutes les valeurs précédentes qui est recherché et non pas la valeur précédente seulement.
En ce cas, LAG() ne conviendra pas.

Mais, comme expliqué précédemment, la solution est applicable sous réserve de remplacer la restriction sur identifiants inférieurs par une restriction sur dates inférieures.

[Waldar]

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
  select gift_card_id
       , id             as transaction_id
       , payment        as transaction_amount
       , sum(payment) over(partition by gift_card_id order by created_at asc) as amount
       , created_at
    from transactions
order by created_at desc;
 
gift_card_id  transaction_id  transaction_amount  amount    created_at
------------  --------------  ------------------  --------  ----------
           1               8           - 3.20000   8.43000  2020-09-10
           1               7           -33.37000  11.63000  2020-07-27
           1               6            45.00000  45.00000  2019-12-30
           1               5           -28.79000   0.00000  2018-05-16
           1               4           -21.21000  28.79000  2017-07-12
           1               3            50.00000  50.00000  2017-05-08
           1               2           -10.00000   0.00000  2016-09-20
           1               1            10.00000  10.00000  2016-09-16

[Sparky95]

Merci Waldar