Discussion :

[StringBuilder]

Bonjour,

J'ai un besoin relativement classique :

Sur un jeu de données, j'ai une clé (partition) et une colonne dont la valeur évolue en fonction d'une autre colonne d'horodatage.

Je souhaite, pour chaque clé, obtenir la dernière valeur chronologique.

Exemple :

Code :

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

1
2
3
4
5
6
7
8
 
ID  Valeur Date
1   A    2021-05-01
1   B    2021-05-02
1   C    2021-05-03
1   B    2021-05-04
2   D    2021-05-01
2   A    2021-05-02

Je souhaite avoir :

Code :

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

1
2
3
4
 
ID  Valeur
1   B
2   A

Je pensais bêtement faire :

Code sql :

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

1
2
3
 
select distinct id, last_value(valeur) over (partition by id order by date)
from matable

Mais last_value() ne fait pas du tout ça visiblement...

Code :

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

1
2
3
4
5
6
7
 
ID      Valeur
1	A
1	B
1	C
2	A
2	D

[sevyc64]

si tu veux partitionner par id, je pense qu'il faut aussi d'abords trier par id. Je ne pense pas que la partition provoque automatiquement le tri.

donc à mon avis il faudrait mettre order by id, date

[ectap]

Bonjour,

Plutôt que last_value(), j'utiliserai plutôt row_number() pour ne garder que la première ligne de chaque partition triée par date décroissante

Code :

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

1
2
3
4
5
select id, valeur
from
   (select  id, valeur, row_number() over (partition by id order by date desc) as num_ordre
    from matable)
where num_ordre = 1

Remarque: row_number() ne sectionne qu'une seule ligne en cas d'ex aequo, si l'on veut garder les ex aequo on peur utiliser rank()

Bonne soirée

[escartefigue]

Bonjour,

je propose différentes solutions pour ce besoin dans mon blog ICI

[skuatamad]

Avec LAST_VALUE Il faut étendre la sélection du fenêtrage jusqu'à UNBOUNDED FOLLOWING, par défaut le fenêtrage s'arrête à CURRENT ROW :

Code :

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

last_value(valeur) over (partition by id order by date rows between unbounded preceding and unbounded following)

[Waldar]

Ou encore, un first_value avec un tri descendant sur les dates :

Code :

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

first_value(valeur) over (partition by id order by date desc)

[Waldar]

Je ne pense pas que la partition provoque automatiquement le tri.

Si heureusement, les tris sont propres à chaque partition.

[escartefigue]

PARTITION BY, GROUP BY, DISTINCT et UNION sans ALL impliquent un tri (et ORDER BY bien sûr )

[SQLpro]

PARTITION BY, GROUP BY, DISTINCT et UNION sans ALL impliquent un tri (et ORDER BY bien sûr )

NON.... PARTITION BY, GROUP BY, DISTINCT et UNION sont généralement fait par hachage et pas par un tri trop couteux, sauf dans les mauvais SGBDR, ou sauf s'il existe un index qui le permet !

A +

[escartefigue]

NON.... PARTITION BY, GROUP BY, DISTINCT et UNION sont généralement fait par hachage et pas par un tri trop couteux, sauf dans les mauvais SGBDR, ou sauf s'il existe un index qui le permet !

A +

Seulement sur les tous petits volumes non ?

[SQLpro]

Seulement sur les tous petits volumes non ?

Au contraire !!!!! Si petit volume => tri. Sur les grands volume le hachage est bien moins couteux !!!

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
CREATE TABLE T_AGG (ID INT IDENTITY, VAL VARCHAR(16));
GO
 
TRUNCATE TABLE T_AGG
 
INSERT INTO T_AGG VALUES ('ABCDEF')
GO 1000
 
INSERT INTO T_AGG VALUES ('GHIJKL')
GO 1000
 
INSERT INTO T_AGG
SELECT RIGHT(T1.VAL, FLOOR(7 * RAND(CAST(GETDATE() AS REAL)))) + LEFT(T2.VAL, FLOOR(7 * RAND(CAST(GETDATE() AS REAL))))
FROM   T_AGG AS T1
       CROSS JOIN T_AGG AS T2

Cet agrégat :

Code :

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

1
2
3
SELECT COUNT(*), VAL
FROM   T_AGG
GROUP  BY VAL

Conduit à un hachage

En le forçant à faire un tri, c'est catastrophique en temps de réponse :

Code :

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

1
2
3
4
SELECT COUNT(*), VAL
FROM   T_AGG
GROUP  BY VAL
OPTION (ORDER GROUP)

20 fois plus couteux....

A +

[StringBuilder]

Tiens, question qui n'a rien à voir, les GO 1000 fonctionnent dans quel éditeur ?

Dans SQL Server Management Studio (le dernier) il me fait une erreur sur le 1000.

Sinon, merci pour vos réponses.

Je suis parti sur une solution à base de sous-requête (dans le premières réponses) mais j'ai oublié de marquer comme résolu.

Il faudra que je reteste la solution proposé par waldar, même si je ne vois pas trop ce que va changer un first_value order by desc par rapport à un last_value order by asc.

[StringBuilder]

En relisant la réponse de skuatamad, je percute et me dit "ah mais oui mais c'est biensûr ! j'avais déjà eu le même souci et la même solution par le passé !"

Je regrette juste que la documentation MSDN soit merdique et ne mentionne pas le bounding et propose des exemples aussi peu probants...

[Waldar]

Il faudra que je reteste la solution proposé par waldar, même si je ne vois pas trop ce que va changer un first_value order by desc par rapport à un last_value order by asc.

Rien du tout si ce n'est éviter de mettre la spécification du fenêtrage, mais oui c'est conceptuellement exactement la même chose.