LAST_VALUE() ne fait pas ce que je veux, je suis perdu

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:

---

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:

---

1 2 3 4

ID Valeur 1 B 2 A

---

Je pensais bêtement faire :

Code:

---

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:

---

1 2 3 4 5 6 7

ID Valeur 1 A 1 B 1 C 2 A 2 D

---

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

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:

---

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

Bonjour,

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

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:

---

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

---

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

Code:

---

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

---

Citation:

---

Envoyé par sevyc64

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

---

Si heureusement, les tris sont propres à chaque partition.

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

Citation:

---

Envoyé par escartefigue

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 +

Citation:

---

Envoyé par SQLpro

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 ?

Citation:

---

Envoyé par escartefigue

Seulement sur les tous petits volumes non ?

---

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

Code:

---

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:

---

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:

---

1 2 3 4

SELECT COUNT(*), VAL FROM T_AGG GROUP BY VAL OPTION (ORDER GROUP)

---

20 fois plus couteux....

A +

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.

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

Citation:

---

Envoyé par StringBuilder

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.