Forums PostgreSQL.fr

damalaan

Membre

Retourner les n dernières valeurs pour chaque catégorie

Bonjour,

Voici un exemple de données dont je voudrais récupérer les 5 valeurs les plus récentes pour chaque catégorie (sans se baser sur la date, car je n'ai pas de valeur systématiquement pour chaque catégorie et chaque jour)

catégorie;date;valeur
1;"2016-12-26";0.000
1;"2016-12-27";1.000
1;"2016-12-28";23.000
1;"2016-12-29";11.000
1;"2016-12-30";2.000
1;"2017-01-03";824.000
1;"2017-01-04";803.000
1;"2017-01-05";669.000
1;"2017-01-07";204.000
2;"2016-12-27";25.000
2;"2016-12-28";30.000
2;"2016-12-29";29.000
2;"2016-12-30";18.000
2;"2017-01-03";21.000
2;"2017-01-04";26.000
2;"2017-01-05";20.000
2;"2017-01-06";20.000
3;"2016-12-27";25.000
3;"2016-12-28";14.000
3;"2016-12-29";18.000
3;"2016-12-30";18.000
3;"2017-01-03";11.000
3;"2017-01-04";11.000
3;"2017-01-05";10.000
3;"2017-01-06";9.000
3;"2017-01-07";10.000

je veux obtenir ça

1;"2016-12-30";2.000
1;"2017-01-03";824.000
1;"2017-01-04";803.000
1;"2017-01-05";669.000
1;"2017-01-07";204.000

2;"2016-12-30";18.000
2;"2017-01-03";21.000
2;"2017-01-04";26.000
2;"2017-01-05";20.000
2;"2017-01-06";20.000

3;"2017-01-03";11.000
3;"2017-01-04";11.000
3;"2017-01-05";10.000
3;"2017-01-06";9.000
3;"2017-01-07";10.000

je pensais passer par une window function mais je n'arrive pas à voir comment !

rjuju

Administrateur

Re : Retourner les n dernières valeurs pour chaque catégorie

Si vous êtes en 9.3 ou plus, regardez du côté de LATERAL : http://docs.postgresql.fr/9.6/sql-select.html . Sinon, il faudra mettre à jour bientôt de toutes façons

---

Julien.
https://rjuju.github.io/

damalaan

Membre

Re : Retourner les n dernières valeurs pour chaque catégorie

Je suis en 9.5.

Je ne connais pas LATERAL, et la doc ne m'a pas inspiré.....:-(

J'ai persisté avec une fonction de fenêtrage, et ça a l'air d'être concluant :

WITH a as (
select  
categorie, 
val_date,
valeur, 
rank () OVER (PARTITION BY categorie order by val_date desc) as rang
from tbl_valeur_val where  val_date >'01/01/2016')

select * from a where rang <=5 order by a.categorie, a.val_date

Si on peut le faire autrement, je suis intéressé également

rjuju

Administrateur

Re : Retourner les n dernières valeurs pour chaque catégorie

On le fait généralement ainsi :

SELECT ...
FROM categorie c,
LATERAL (
    SELECT ...
    FROM categorie_valeur cv
    WHERE cv.id_categorie = c.id
    ORDER BY val_date DESC
    LIMIT 5
) lat

---

Julien.
https://rjuju.github.io/

damalaan

Membre

Re : Retourner les n dernières valeurs pour chaque catégorie

Votre solution m’intéresse également mais je n'arrive pas à reproduire la requête avec la fonction LATERAL
Est-ce que vous pourriez me montrer la requête complète?

rjuju

Administrateur

Re : Retourner les n dernières valeurs pour chaque catégorie

Je n'ai pas la définition de votre schéma, j'ai supposé que vous aviez une table de référence des catégories (table «categorie» dans mon exemple, et que votre table de valeur avait une clé étrangère pointant dessus (champ «id_categorie» de la table «categorie_valeur» dans mon exemple). Remplacez le nom des tables et colonnes utilisées, et au pire utiliser un simple "SELECT *".

De plus LATERAL n'est pas une fonction mais un mot clé pour un type de jointure.

---

Julien.
https://rjuju.github.io/

damalaan

Membre

Re : Retourner les n dernières valeurs pour chaque catégorie

La table qui m’intéresse a la structure suivante :

CREATE TABLE public.tbl_valeur_val
(
  val_id bigint NOT NULL DEFAULT nextval('tbl_valeur_val_val_id_seq'::regclass),
  sta_id integer NOT NULL,
  val_date date NOT NULL,
  val_esp integer NOT NULL,
  val_valeur numeric(9,3),
  CONSTRAINT tbl_valeur_val_pkey PRIMARY KEY (val_id),
  CONSTRAINT tbl_valeur_val_sta_id_fkey FOREIGN KEY (sta_id)
      REFERENCES public.tbl_stat_sta (sta_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT contrainte_unique UNIQUE (sta_id, val_date, val_esp)
)

sta_id étant le champ categorie de l'exemple

J'ai essayé avec cette requete mais j'ai un retour de 382000 lignes au lieu de 380 !

select  a.sta_id, a.val_date, a.val_valeur
from tbl_valeur_val a,
LATERAL (
	select  
	b.sta_id	
	from tbl_valeur_val b
	where b.sta_id=a.sta_id
	and b.val_date >'01/01/2016'
	and val_esp = 1
	order by b.val_date desc
	limit 5) lat
order by a.sta_id, a.val_date

rjuju

Administrateur

Re : Retourner les n dernières valeurs pour chaque catégorie

Parce que vous joignez la table « tbl_valeur_val » avec elle même. Votre alias a devrait être la table de référence des catégories comme indiqué dans mon message précédent, donc à priori « tbl_stat_sta ».

De plus, la clause « b.val_date >'01/01/2016' » me semble inutile, à moins que vous ne vouliez les 5 derniers depuis le début de l'année 2016.  Je ne sais pas non plus pourquoi vous utilisez un prédicat sur « val_esp = 1 », qui n'apparaissait pas avant.

---

Julien.
https://rjuju.github.io/

damalaan

Membre

Re : Retourner les n dernières valeurs pour chaque catégorie

ça y est  !
j'ai repris le select * proposé plus haut !
J'avoue que j'ai beaucoup de mal à comprendre la logique d'écriture de la requête

select  *
from tbl_stat_sta a,
LATERAL (
	select  
	*	
	from tbl_valeur_val b
	where b.sta_id=a.sta_id
	and b.val_date >'01/01/2016'
	and val_esp = 1
	order by b.val_date desc
	limit 5) lat

order by a.sta_id, val_date

le "val_esp" est un tri pour avoir les 5 dernières valeurs de val_esp=1.

J'ai fait un explain analyze pour chaque méthode, je suis convaincu que LATERAL est plus efficace !! (même si je n'ai pas tout compris)
Avec LATERAL

"Sort  (cost=2354.07..2355.60 rows=610 width=121) (actual time=2.722..2.734 rows=293 loops=1)"
"  Sort Key: a.sta_id, b.val_date"
"  Sort Method: quicksort  Memory: 82kB"
"  ->  Nested Loop  (cost=0.42..2325.85 rows=610 width=121) (actual time=0.074..2.125 rows=293 loops=1)"
"        ->  Seq Scan on tbl_stat_sta a  (cost=0.00..3.22 rows=122 width=96) (actual time=0.016..0.036 rows=76 loops=1)"
"        ->  Limit  (cost=0.42..18.94 rows=5 width=25) (actual time=0.022..0.025 rows=4 loops=76)"
"              ->  Index Scan Backward using contrainte_unique on tbl_valeur_val b  (cost=0.42..633.82 rows=171 width=25) (actual time=0.022..0.024 rows=4 loops=76)"
"                    Index Cond: ((sta_id = a.sta_id) AND (val_date > '2016-01-01'::date) AND (val_esp = 1))"
"Planning time: 0.619 ms"
"Execution time: 2.935 ms"

avec RANK() OVER (PARTITION BY.....)

"Sort  (cost=3727.75..3738.60 rows=4339 width=30) (actual time=43.054..43.062 rows=293 loops=1)"
"  Sort Key: a.sta_id, a.val_date"
"  Sort Method: quicksort  Memory: 47kB"
"  CTE a"
"    ->  WindowAgg  (cost=2912.34..3172.70 rows=13018 width=13) (actual time=30.148..37.962 rows=13641 loops=1)"
"          ->  Sort  (cost=2912.34..2944.89 rows=13018 width=13) (actual time=30.140..30.749 rows=13641 loops=1)"
"                Sort Key: tbl_valeur_val.sta_id, tbl_valeur_val.val_date DESC"
"                Sort Method: quicksort  Memory: 1024kB"
"                ->  Seq Scan on tbl_valeur_val  (cost=0.00..2022.68 rows=13018 width=13) (actual time=0.026..20.530 rows=13641 loops=1)"
"                      Filter: ((val_date > '2016-01-01'::date) AND (val_esp = 1))"
"                      Rows Removed by Filter: 71781"
"  ->  CTE Scan on a  (cost=0.00..292.90 rows=4339 width=30) (actual time=30.155..42.950 rows=293 loops=1)"
"        Filter: (rang <= 5)"
"        Rows Removed by Filter: 13348"
"Planning time: 0.433 ms"
"Execution time: 43.467 ms"

rjuju

Administrateur

Re : Retourner les n dernières valeurs pour chaque catégorie

Il faut voir le LATERAL comme une boucle imbriquée.  On demande ici pour chaque catégorie (enregistrement présent dans la table tbl_valeur_val) les 5 derniers enregistrements de la table tbl_valeur_val. Le mot clé LATERAL permet de pouvoir effectuer la jointure entre ces deux tables (la partie b.sta_id=a.sta_id) à cet endroit de la requête.  Ce mode de fonctionnement est du coup beaucoup plus efficace que de calculer le rang de chaque enregistrement pour ensuite supprimer ceux dont le rang est supérieur à 5.

---

Julien.
https://rjuju.github.io/