PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 09/01/2017 15:02:06

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 !

Hors ligne

#2 09/01/2017 15:11:11

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 smile

Hors ligne

#3 09/01/2017 15:25:45

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

Hors ligne

#4 09/01/2017 15:38:40

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

Hors ligne

#5 09/01/2017 16:12:27

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?

Hors ligne

#6 09/01/2017 16:29:17

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.

Hors ligne

#7 10/01/2017 09:52:22

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

Hors ligne

#8 10/01/2017 11:20:01

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.

Hors ligne

#9 10/01/2017 14:58:41

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"

Hors ligne

#10 10/01/2017 15:05:37

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.

Hors ligne

Pied de page des forums