Discussion :

[pdelorme]

Bonjour,

J'ai la requette suivante qui me retourne les dernière visite (par date) lié à un couple intervention/equipement :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT 
                       LAST(x.intervention_id)    AS intervention_id, 
                       LAST(x.equip_vess_id)      AS equip_vess_id, 
                       LAST(x.visit_id)           AS visit_id, 
                       LAST(x.finish_dttm)        AS finish_dttm,
                    FROM (SELECT i1.intervention_id, v.visit_id, v.equip_vess_id, v.finish_dttm, v.finish_meter_value
                            FROM interventions i1, interventions i2, visits v
                           WHERE i1.intervention_log_group_id = i2.intervention_log_group_id AND i2.intervention_id = v.intervention_id
                           ORDER BY v.finish_dttm) x
                   GROUP BY x.equip_vess_id

Cette requette ne retourne pas la derniere ligne en date du groupe, mais uen autre.
en creusant nous avons finalement trouvé qu'il fallais qye l'order by soit sur

Code :

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

 i1.intervention_id,v.equip_vess_id,v.finish_dttm

ce qui ne parait pas logique.
En effet il semble que l'order by regroupe le résultat de la sous requette sans tenir compte du tri.
Mon problème est résolu, mais j'aimerias bien avoir comprendre ou se trouve l'erreur (dans ma logique ou dans le moteur postgres)

Merci,

P

[punkoff]

Bonjour,

Quelle version de postgresql utilisez vous ?

LAST ne semble pas exister en v9.0+

[pdelorme]

effectivement, LAST() est une fonction maison (super pratique)... je suis sur postgres 8.3

La fonction last :

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 OR REPLACE FUNCTION last_agg(anyelement, anyelement)
  RETURNS anyelement AS
$BODY$
        SELECT $2;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION last_agg(anyelement, anyelement) OWNER TO postgres;
 
CREATE AGGREGATE "last"(anyelement) (
  SFUNC=last_agg,
  STYPE=anyelement
);
ALTER AGGREGATE "last"(anyelement) OWNER TO postgres;

P.

[pdelorme]

en cadeau la fonction FIRST :

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 OR REPLACE FUNCTION first_agg(anyelement, anyelement)
  RETURNS anyelement AS
$BODY$
        SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$BODY$
  LANGUAGE sql STABLE
  COST 100;
ALTER FUNCTION first_agg(anyelement, anyelement) OWNER TO postgres;
 
CREATE AGGREGATE "first"(anyelement) (
  SFUNC=first_agg,
  STYPE=anyelement
);
ALTER AGGREGATE "first"(anyelement) OWNER TO postgres;

P.

[SQLpro]

Votre erreur vient du fait que la clause ORDER BY est normalement interdite dans les sous requêtes. Même si vous la mettez elle n'a en général aucun effet.
En effet, la clause ORDER BY n'est, par nature, par relationnelle. Elle ne sert qu'a présenter un résultat (et non pas une table dérivée, c'est à dire une table créée à la volée dans une sous requête) et c'est que que l'on appelle une clause COSMÉTIQUE. À me lire : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L9

Pour réaliser des opérations ordonnées, il existe toute une classe de fonction appelées fonction de fenêtrage, comme RANK(), ROW_NUMBER(), LEAD(), LAG().
Apprenez le langage SQL. Notamment : http://sqlpro.developpez.com/article...clause-window/

A +

[punkoff]

Votre erreur vient du fait que la clause ORDER BY est normalement interdite dans les sous requêtes. Même si vous la mettez elle n'a en général aucun effet.
A +

Bonjour,


Dans le cas de PostGresql ca me semble un peu différent. (je ne remet en cause le fait que cela soit interdit ou non au vu de la norme).

Code :

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

1
2
3
4
5
6
7
8
 
with tmp(id) as (
select 2 union all
select 5 union all
select 1)
 
select min(id)
from (select id from tmp order by id) as a

Explain :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
"Aggregate  (cost=0.19..0.20 rows=1 width=4)"
"  CTE tmp"
"    ->  Result  (cost=0.00..0.06 rows=3 width=4)"
"          ->  Append  (cost=0.00..0.06 rows=3 width=4)"
"                ->  Result  (cost=0.00..0.01 rows=1 width=0)"
"                ->  Result  (cost=0.00..0.01 rows=1 width=0)"
"                ->  Result  (cost=0.00..0.01 rows=1 width=0)"
"  ->  Sort  (cost=0.08..0.09 rows=3 width=4)"
"        Sort Key: tmp.id"
"        ->  CTE Scan on tmp  (cost=0.00..0.06 rows=3 width=4)"

On voit bien ici que PG réalise le sort de la sous-requête avant de le passer dans la fonction d’agrégation

[punkoff]

Donc vous utilisez des fonctions "maisons" dont vous ne comprenez pas le méchanisme ? :p

un test tout simple qui vous permettra de comprendre son fonctionnement :

Code :

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

1
2
3
4
5
6
7
8
 
with tmp (id, val) as (
select 1, 3 union all 
select 1, 2 union all
select 2, 4)
 
select *
from tmp

Resultat :

Code :

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

1
2
3
4
5
6
 
id         val
-------------------
1          3
1          2
2          4

Avec votre fonction :

Code :

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

1
2
3
4
5
6
7
8
9
 
with tmp (id, val) as (
select 1, 3 union all 
select 1, 2 union all
select 2, 4)
 
select last(val)
from tmp
group by id

Résultat :

Code :

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

1
2
3
4
5
 
id            val
------------------
1             2
2             4

[pdelorme]

Avec votre fonction :

Code :

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

1
2
3
4
5
6
7
8
9
 
with tmp (id, val) as (
select 1, 3 union all 
select 1, 2 union all
select 2, 4)
 
select last(val)
from tmp
group by id

Résultat :

Code :

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

1
2
3
4
5
 
id            val
------------------
1             2
2             4

d'ou la nécessité du ORDER BY val dans l'innée sélection qui présente les données dans le bon ordre au GROUP BY et retourne le résultats attendu.

[estofilo]

Mon problème est résolu, mais j'aimerias bien avoir comprendre ou se trouve l'erreur (dans ma logique ou dans le moteur postgres)

Le problème est que manifestement la requête est fausse, c.a.d qu'elle ne donnera pas les résultats voulus dans 100% des cas.
Ca ne devrait pas être trop compliqué de la réécrire dans une version juste.

Sachant qu'en 8.3 il n'y pas de fonction de fenêtrage, il faut procéder en extrayant d'abord la date max avec GROUP BY qui va bien et ensuite en joignant avec le reste pour récupérer les valeurs des autres colonnes associées à cette date.

[pdelorme]

Le problème est que manifestement la requête est fausse, c.a.d qu'elle ne donnera pas les résultats voulus dans 100% des cas.
Ca ne devrait pas être trop compliqué de la réécrire dans une version juste.

Sachant qu'en 8.3 il n'y pas de fonction de fenêtrage, il faut procéder en extrayant d'abord la date max avec GROUP BY qui va bien et ensuite en joignant avec le reste pour récupérer les valeurs des autres colonnes associées à cette date.

Le problème de cette approche est qu'elle me parait très consommatrice car plus complexe... Il me semble plus optimal (et élégant) de faire un tri puis un LAST (pour peu que cela fonctionne) que de faire un group by puis une jointure sur une date ce qui nécessite de faire 2 inner selects qui peuvent être relativement complexe...