Vous n'êtes pas identifié(e).
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.000je 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.000je pensais passer par une window function mais je n'arrive pas à voir comment !
Hors ligne
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/
Hors ligne
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_dateSi on peut le faire autrement, je suis intéressé également
Hors ligne
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
) latJulien.
https://rjuju.github.io/
Hors ligne
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
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/
Hors ligne
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_dateHors ligne
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/
Hors ligne
ç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_datele "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
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/
Hors ligne