Fonctionnement ORDER BY dans un GROUP BY

[pdelorme]

Bonjour,

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

Code :

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 :

 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 :

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 :

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]

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 :

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 :

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

Avec votre fonction :

Code :

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 :

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

[punkoff]

Envoyé par 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.
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 :

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 :

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

[SQLpro]

Envoyé par punkoff

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

NON !!!!

Votre position est parfaitement idiote, car il n'y a aucune garantie de reproduction du résultat, le SGBDR étant libre de lire les données dans l'ordre qu'il souhaite et non dans celle que vous lui indiquez (gestion ensembliste).
Malheureusement mettre cela en évidence, nécessite des jeux de données importants (dépassant le volume du cache) et diffère suivant l'indexation.

A +

[estofilo]

Envoyé par pdelorme

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.

[punkoff]

Envoyé par SQLpro

NON !!!!

Votre position est parfaitement idiote, car il n'y a aucune garantie de reproduction du résultat, le SGBDR étant libre de lire les données dans l'ordre qu'il souhaite et non dans celle que vous lui indiquez (gestion ensembliste).
Malheureusement mettre cela en évidence, nécessite des jeux de données importants (dépassant le volume du cache) et diffère suivant l'indexation.

A +

Bonjour,


Nous parlons de postgresql ici.

Je suis bien conscient de votre argumentation mais si vous faites des recherches ça ne semble pas si blanc / noir au niveau de se fonctionnement :

http://postgresql.1045698.n5.nabble....td5527695.html

En particulier les derniers message de la discussion.

Si j’interprète bien ces messages (en particulier ceux de Tom Lane) :
- La clause order by n'est pas légal dans une sous requete (sans utilisation d'un top / offset ?)
- Si un utilisateur en à mis une c'est pour une raison bien précise.
- A partir de ce moment là l'order by est honnoré car l'utilisateur l'a mit


edit : non valide

nb: ce message n'est pas fait pour encourager notre posteur à continuer d'utiliser sa méthode mais de mieux comprendre certaine spécificité de l'optimiseur postgres

[pdelorme]

Envoyé par punkoff

Avec votre fonction :

Code :

Résultat :

Code :

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.

[pdelorme]

Envoyé par SQLpro

NON !!!!

Votre position est parfaitement idiote, car il n'y a aucune garantie de reproduction du résultat, le SGBDR étant libre de lire les données dans l'ordre qu'il souhaite et non dans celle que vous lui indiquez (gestion ensembliste).
Malheureusement mettre cela en évidence, nécessite des jeux de données importants (dépassant le volume du cache) et diffère suivant l'indexation.

A +

Le problème que vous citez à effectivement été constaté (c'est même l'origine du bug)
Selon la machine, notre requête initial ne retourne pas les mêmes résultats bien que les version postgres et Linux soient identiques... Peut être que la taille des cache diffère, à vérifier. Cependant, l'ajout d'un ORDER BY plus complet à permis de corriger le résultat... Il est clair que l'optimiseur postgres y est pour quelque chose...

[pdelorme]

Envoyé par estofilo

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

[punkoff]

Envoyé par pdelorme

Le problème que vous citez à effectivement été constaté (c'est même l'origine du bug)
Selon la machine, notre requête initial ne retourne pas les mêmes résultats bien que les version postgres et Linux soient identiques... Peut être que la taille des cache diffère, à vérifier. Cependant, l'ajout d'un ORDER BY plus complet à permis de corriger le résultat... Il est clair que l'optimiseur postgres y est pour quelque chose...

Bah dans ce cas, vous ne pouvez pas garantir que ceci ne se reproduise pas donc cette solution est a jeter.

[pdelorme]

Envoyé par punkoff

Bah dans ce cas, vous ne pouvez pas garantir que ceci ne se reproduise pas donc cette solution est a jeter.

Le ORDER BY plus complet à effectivement résolu mon problème, l'ORDER BY n'est donc pas qu'une fonction uniquement "cosmétique" et semble réellement pouvoir être utilisé comme je l'utilise (avec LAST() dans une inner query)...

Mise à part une présomption de culpabilité, j'ai du mal à comprendre ce qui "concrètement" en interdit l'usage.

[estofilo]

Envoyé par pdelorme

Le ORDER BY plus complet à effectivement résolu mon problème, l'ORDER BY n'est donc pas qu'une fonction uniquement "cosmétique" et semble réellement pouvoir être utilisé comme je l'utilise (avec LAST() dans une inner query)...

Mise à part une présomption de culpabilité, j'ai du mal à comprendre ce qui "concrètement" en interdit l'usage.

C'est vraiment simple: le GROUP BY qui est fait sur la base des résultats de la sous-requête n'a pas à conserver le tri de la sous-requête, il est libre de réordonner les lignes comme il l'entend.

L'ironie de cette discussion est que tu as toi-même constaté le problème dès le 1er message:

En effet il semble que l'order by regroupe le résultat de la sous requette sans tenir compte du tri.

mais que malgré tout, tu n'as pas l'air de vouloir y croire, alors que tu as la preuve sous les yeux.

[SQLpro]

Mais rien n'empêche les gens de se suicider !

Actuellement vous ne voyez pas les effets de bord, car votre jeu de données est biaisé et comme vous pensez que vous avez raison, vous allez continuer stupidement à mettre en œuvre cela jusqu'au jour ou vos utilisateurs découvrirons sans doute trop tard les bogues que vous avez sciemment introduit en toute connaissance de cause dans le logiciel.

Errare humanum est, perseverare diabolicum !

A +

[pdelorme]

Envoyé par estofilo

mais que malgré tout, tu n'as pas l'air de vouloir y croire, alors que tu as la preuve sous les yeux.

Il n'y a donc pas de place pour des fonctions d’agrégation LAST et FIRST et la seule solution est donc de faire un group by et une jointure sur la date max comme suggéré par estofilo ?

J'ai effectivement du mal à croire que postgres n'ait pas la gentillesse de conserver mes Order by.

Merci en tous cas pour ces explications qui m’ont remis dans le droit chemin!

Amen !

[SQLpro]

Il serait peut être temps de comprendre ce qu'est un SGBD Relationnel !

La théorie de l'Algèbre Relationnelle a été bâtie sur la théorie des ensembles qui ne possède aucune relation d'ordre de manière naturelle.

La clause ORDER BY de SQL ne fait d'ailleurs pas partie de l'algèbre relationnelle.
C'est pourquoi les SGBDR l'ignorent en général, ou bien signalent l'erreur, lorsqu'elle figure à l'intérieur des requêtes.
En fait il s'agit d'une clause COSMÉTIQUE c'est à dire appliqué au rendu du résultat et c'est pourquoi elle ne doit figurer qu'une seule fois et à la toute fin de la requête.
On a jugé plus pratique de rajouter cette clause au langage, parce qu'il est généralement moins couteux de demander au SGBDR de trier les données que de le faire sur le poste client. En effet, au niveau physique, il est parfois possible d'éviter le coût du traitement du tri par le simple fait de l'existence d'un index adéquat, puisqu'un index est généralement une forme de tri...

Si vous regardez comment est conçu l'intérieur d'un SGBDR, vous remarquerez que la partie qui assure le tri n'est pas du tout située dans le moteur relationnel. En effet les requêtes sont de l'algèbre relationnelle, donc relève du moteur relationnel, c'est à dire de la partie LOGIQUE du SGBDR. En revanche le tri est une opération physique (le résultat d'une requête est le même que les données soient triées ou non) et relève donc du moteur de stockage !

La partie logique (requêtes relationnelle) est assurée par le moteur relationnel, tandis que la partie physique est assurée par le moteur de stockage. Et c'est bien le moteur de stockage qui doit effectuer la lecture de l'index ou s'il n'y a pas d'index adéquat, effectuer l'opération physique des tri des lignes résultant de la requête relationnelle.

Pour vous en convaincre, voici l'architecture interne de MS SQL Server...
http://www.developpez.net/forums/att...1&d=1351259693

Tous les SGBD relationnel sont bâtis sur les mêmes principes de séparation :
Physique : stockage, indexation, lectures, écritures, verrouillage, gestion des transactions, chargement de données via des fichiers externe, sauvegarde, restauration...
Logique : analyse grammaticale, vérification des privilèges, transformation diverses (XML, Full-Text, SIG...), algébrisation, optimisation, exécution...

A +

[SQLpro]

Envoyé par pdelorme

Il n'y a donc pas de place pour des fonctions d’agrégation LAST et FIRST et la seule solution est donc de faire un group by et une jointure sur la date max comme suggéré par estofilo ?

J'ai effectivement du mal à croire que postgres n'ait pas la gentillesse de conserver mes Order by.

Merci en tous cas pour ces explications qui m’ont remis dans le droit chemin!

Amen !

Vous pouvez quand même utiliser les fonctions de fenêtrage LEAD et LAG. Elles sont implémentées dans PG depuis la version 9.

À me lire : http://sqlpro.developpez.com/article...clause-window/

A +