[ORDER BY] Tri personnalisé

Version imprimable

25/10/2010, 10h05
gui80

[ORDER BY] Tri personnalisé

Bonjour à tous,

j'aimerai savoir comment vous procédez quand vous voulez faire un tri personnalisé.

J'ai bien vu qu'il y avait possibilité de faire un CASE dans un ORDER BY, mais j'aimerais savoir s'il existe une fonction comme en MySQL :
FIELD (p1,p2,p3,...,pn)

Le CASE est assez lourdingue si on a plus de 5 valeurs et je n'ai aucun intérêt vraiment à le stocker dans une base de données.
25/10/2010, 12h08
ced

Salut,

J'ai trouvé la réponse sur ce site : http://stackoverflow.com/questions/1...-in-postgresql.
Il y a notamment un code permettant de simuler sous PostgreSQL la fonction FIELD, donné pour la version 8.4 et même pour les versions précédentes.

ced

Merci ced.

Oui, cela correspond.
Pour avoir regardé l'exemple par contre, je ne comprend pas trop la logique d'écriture dans le ORDER BY j'avoue.
Après, je peux toujours l'appliquer bêtement sans comprendre, mais bon

Reprise du code du site web stack overflow :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 select * from test order by field!='GBP', field!='EUR', field!='BBD', field!='AUD', field!='CAD', field!='USD'; id | field ----+------- 1 | GBP 7 | GBP 2 | EUR 8 | EUR 3 | BBD 9 | BBD 4 | AUD 10 | AUD 5 | CAD 11 | CAD 6 | USD 12 | USD (12 rows)

Il y a plus rapide et plus simple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
CREATE TABLE test (name TEXT, value FLOAT); 
INSERT INTO test VALUES ('a',random()),('b',random()),('c',random());
 
SELECT t.* FROM test t JOIN (VALUES ('b',1),('c',2),('a',3)) o ON (o.column1=t.name) ORDER BY o.column2;
 name |       value       
------+-------------------
 b    | 0.313314548693597
 c    | 0.676097862888128
 a    | 0.604159130714834
WITH o AS (VALUES ('b',1),('c',2),('a',3)) SELECT t.* FROM test t JOIN o ON (o.column1=t.name) ORDER BY o.column2;
 name |       value       
------+-------------------
 b    | 0.313314548693597
 c    | 0.676097862888128
 a    | 0.604159130714834
 
EXPLAIN ANALYZE SELECT t.* FROM test t JOIN (VALUES ('b',1),('c',2),('a',3)) o ON (o.column1=t.name) ORDER BY o.column2;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=26.54..26.58 rows=17 width=44) (actual time=0.043..0.044 rows=3 loops=1)
   Sort Key: "*VALUES*".column2
   Sort Method:  quicksort  Memory: 25kB
   ->  Hash Join  (cost=0.08..26.20 rows=17 width=44) (actual time=0.031..0.032 rows=3 loops=1)
         Hash Cond: (t.name = "*VALUES*".column1)
         ->  Seq Scan on test t  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.006..0.006 rows=3 loops=1)
         ->  Hash  (cost=0.04..0.04 rows=3 width=36) (actual time=0.009..0.009 rows=3 loops=1)
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=36) (actual time=0.004..0.006 rows=3 loops=1)
 Total runtime: 0.083 ms

Moi, je pensais plutôt, dans le lien que je te donnais, à la fonction field réécrite, plutôt qu'à l'exemple du ORDER BY :
Code:

1 2 3 4 5 6 7 8 -- SELECT FIELD(varnames, 'foo', 'bar', 'baz') CREATE FUNCTION field(anyelement, VARIADIC anyarray) RETURNS numeric AS $$ SELECT COALESCE( ( SELECT i FROM generate_subscripts($2, 1) gs(i) WHERE $2[i] = $1 ), 0); $$ LANGUAGE SQL STABLE
Cette version ne fonctionne qu'à partir de PostgreSQL 8.4.
Sinon, il y a une version qui fonctionne pour les versions antérieures.

Pour la requête de peufeu, même remarque : il faut être en PostgreSQL 8.4 minimum pour que ça fonctionne (les requêtes WITH ne sont apparues que dans la version 8.4).

ced

Bonjour,

oui désolé, je n'avais pas été jusqu'à regarder la solution sous forme de fonction.
C'est une version juste en dessous de la version 8.4.
Donc je ne peux pas utiliser les "WITH".

J'ai crée la fonction conseillée pour les versions inférieures à 8.4.

Cependant, je n'arrive pas à l'exploiter, j'ai le message suivant (la fonction field est nommé a dans mon exemple) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 ERREUR: la fonction a(character varying, text[]) n'existe pas LINE 4: ORDER BY a(nom_du_produit, ARRAY['foo'::text,'bar'::text,'ba... ^ HINT: Aucune fonction ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. ********** Erreur ********** ERREUR: la fonction a(character varying, text[]) n'existe pas État SQL :42883 Astuce : Aucune fonction ne correspond au nom donné et aux types d'arguments. Vous devez ajouter des conversions explicites de type. Caractère : 89
Je ne suis pas habitué aux fonctions PostgreSQL donc j'avoue avoir du mal à m'en sortir seul. Avez-vous une idée ? Ou des tutoriaux adaptés ?

Merci d'avance.

Voilà le code de la fonction pour une version antérieure à la 8.4 :
Code:

1 2 3 4 5 6 7 8 -- SELECT FIELD(varname, ARRAY['foo', 'bar', 'baz']) CREATE OR REPLACE FUNCTION field(anyelement, anyarray) RETURNS numeric AS $$ SELECT COALESCE((SELECT i FROM generate_series(1, array_upper($2, 1)) gs(i) WHERE $2[i] = $1), 0); $$ LANGUAGE SQL STABLE
Une fois la fonction créée, le commentaire explique comment l'utiliser. Tu remplaces varname par la colonne sur laquelle faire le tri et dans ARRAY[...] tu mets l'ordre dans lequel tu veux trier.
Ça donne quelque chose comme :
Code:

1 2 3 4 SELECT ... FROM ... WHERE ... ORDER BY FIELD(ton_champ, ARRAY['p1', 'p2', 'p3', ...])
ced

Oui Ced,

j'ai bien écouté tes conseils. J'ai juste renommé la fonction "field", j'ai mis "a" comme nom de fonction.

Déclaration de ma fonction :

Code:

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION a(anyelement, anyarray) RETURNS numeric AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL STABLE

Appel de ma fonction :

Code:

1
2
3
SELECT * 
FROM produitscommerciaux
ORDER BY a(nom_produit, ARRAY['foo','bar','baz'])

Malgré tout, j'ai un message d'erreur. Ma version de PostgreSQL est 8.3.*

Message d'erreur généré :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
ERREUR:  la fonction a(character varying, text[]) n'existe pas
LINE 4: ORDER BY a(nom_du_produit, ARRAY['foo'::text,'bar'::text,'ba...
                 ^
HINT:  Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
 
********** Erreur **********
 
ERREUR: la fonction a(character varying, text[]) n'existe pas
État SQL :42883
Astuce : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
Caractère : 89

Avez-vous une idée ? Cela fonctionne bien chez vous ?

J'avoue ne pas maîtriser les types et fonction polymorphes.
Essaie en modifiant le type du premier paramètre :

Code:

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION metaifn.FIELD(character, anyarray) RETURNS integer AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL STABLE

ced

Citation:

Envoyé par ced

Pour la requête de peufeu, même remarque : il faut être en PostgreSQL 8.4 minimum pour que ça fonctionne (les requêtes WITH ne sont apparues que dans la version 8.4).ced

C'est pourquoi je mets la même sans WITH (en utilisant directement VALUES() dans la jointure, ce qui fonctionne à partir de la 8.2).

Etant donné que postgres matérialise la clause VALUES() sous forme d'un hash (hash join en fait) cette solution est de loin la plus rapide, bien plus rapide par exemple que toute solution utilisant une fonction.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
CREATE TABLE test (category INTEGER, value FLOAT);
INSERT INTO test (category,value) SELECT random()*5, random() FROM generate_series(1,2000) AS n;
SELECT category, count(*) FROM test GROUP BY category ORDER BY category;
 category | count 
----------+-------
        0 |   211
        1 |   376
        2 |   382
        3 |   424
        4 |   408
        5 |   199
 
EXPLAIN ANALYZE SELECT t.* FROM test t 
JOIN (VALUES (0,3),(1,2),(2,1),(3,0),(4,4),(5,5)) o 
ON (o.column1=t.category) 
ORDER BY o.column2, t.value;
 
 Sort  (cost=168.31..173.31 rows=2000 width=16) (actual time=3.233..3.418 rows=2000 loops=1)
   Sort Key: "*VALUES*".column2, t.value
   Sort Method:  quicksort  Memory: 205kB
   ->  Hash Join  (cost=0.15..58.65 rows=2000 width=16) (actual time=0.028..1.170 rows=2000 loops=1)
         Hash Cond: (t.category = "*VALUES*".column1)
         ->  Seq Scan on test t  (cost=0.00..31.00 rows=2000 width=12) (actual time=0.009..0.260 rows=2000 loops=1)
         ->  Hash  (cost=0.08..0.08 rows=6 width=8) (actual time=0.008..0.008 rows=6 loops=1)
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.08 rows=6 width=8) (actual time=0.001..0.005 rows=6 loops=1)
 Total runtime: 3.693 ms

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE OR REPLACE FUNCTION FIELD( INTEGER, INTEGER[] ) RETURNS INTEGER AS $$
  SELECT
    COALESCE((SELECT i
              FROM generate_series(1, array_upper($2, 1)) gs(i)
              WHERE $2[i] = $1),
             0);
$$ LANGUAGE SQL IMMUTABLE;
 
EXPLAIN ANALYZE SELECT * FROM test ORDER BY FIELD( category, ARRAY[3,2,1] ), value;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Sort  (cost=640.66..645.66 rows=2000 width=12) (actual time=33.215..33.399 rows=2000 loops=1)
   Sort Key: (field(category, '{3,2,1}'::integer[])), value
   Sort Method:  quicksort  Memory: 205kB
   ->  Seq Scan on test  (cost=0.00..531.00 rows=2000 width=12) (actual time=0.122..30.535 rows=2000 loops=1)
 Total runtime: 33.660 ms

La version avec la fonction est 10x plus lente (et en O(n) sur la taille de la liste dans le FIELD), ce qui n'est pas surprenant...

Autre façon de procéder en utilisant un type des contrib (hstore), pas d'avantage décisif par rapport au VALUES() :

Code:

1
2
3
4
5
6
7
8
 
EXPLAIN ANALYZE SELECT * FROM test ORDER BY ('3=>0, 2=>1, 1=>2, 0=>3, 4=>4, 5=>5'::hstore -> category::TEXT)::INTEGER;
 
 Sort  (cost=165.66..170.66 rows=2000 width=12) (actual time=2.781..2.955 rows=2000 loops=1)
   Sort Key: ((('"0"=>"3", "1"=>"2", "2"=>"1", "3"=>"0", "4"=>"4", "5"=>"5"'::hstore -> (category)::text))::integer)
   Sort Method:  quicksort  Memory: 205kB
   ->  Seq Scan on test  (cost=0.00..56.00 rows=2000 width=12) (actual time=0.020..1.930 rows=2000 loops=1)
 Total runtime: 3.229 ms

26/10/2010, 15h40
ced

:hola:
Merci pour la clause VALUES(), que je découvre.

ced
26/10/2010, 15h48
gui80

Effectivement, en remplaçant le type du 1er paramètre (de anyelement à character varying), cela fonctionne.

J'avais essayé de caster dans la fonction d'appel, le 1er élément, mais cela ne fonctionnait pas.
Il faudrait juste faire une fonction par type de données.
Par contre, le 2ème paramètre du COALESCE, à voir selon les cas, quelle valeur à affecter.
J'essairai de jeter un oeil sur http://www.postgresql.org/docs/9.0/static/plpgsql.html.
Si vous connaissez des tutoriaux complets là-dessus, dîtes-moi.

Alors, ensuite, ce que montre peufeu est très intéressant également.
On voit la rapidité d'exécution, c'est vrai.
Cependant, quand un élément soumis au tri, n'est pas présent dans la table temporaire permettant de faire le tri (via une jointure), il ne sera pas affiché.
C'est bien là, sa seule limitation non ? Dîtes-moi si vous n'êtes pas d'accord.
26/10/2010, 16h17
ced

Citation:

Envoyé par gui80

Cependant, quand un élément soumis au tri, n'est pas présent dans la table temporaire permettant de faire le tri (via une jointure), il ne sera pas affiché.
C'est bien là, sa seule limitation non ? Dîtes-moi si vous n'êtes pas d'accord.

Tu peux toujours faire une jointure externe à gauche (LEFT JOIN) et trier selon un COALESCE(o.column2, 99).

ced
26/10/2010, 19h02
peufeu
Citation:

Envoyé par gui80

Cependant, quand un élément soumis au tri, n'est pas présent dans la table temporaire permettant de faire le tri (via une jointure), il ne sera pas affiché.
C'est bien là, sa seule limitation non ? Dîtes-moi si vous n'êtes pas d'accord.

Oui, tu peux tout à fait faire un LEFT JOIN VALUES(...) puis soit laisser les NULLs (qui arriveront en dernier) soit mettre un COALESCE si tu les veux ailleurs.

Le tri des NULLs :
Code:

1 2 3 4 5 6 7 > values (1),(null),(2) order by column1; column1 --------- 1 2 (NULL)
VALUES() est aussi extrêmement utile quand tu as un tableau de valeurs (au hasard des clés primaires) dans ton appli et que tu veux choper les lignes correspondantes, ou bien quand tu as un tableau éventuellement sur plusieurs colonnes et que tu veux l'envoyer à la BDD pour qu'elle bosse dessus, par exemple faire un gros tas UPDATE d'un coup (UPDATE + JOIN avec VALUES combiné avec UPDATE RETURNING c'est bon) ou même des aggrégats, etc.
27/10/2010, 10h36
gui80

Bonjour et merci.

oui cette clause VALUES est vraiment top pour faire des tris sur des entiers.
- Gain de temps énorme
- pas besoin de créer de fonction.

Par contre, pour faire des tris sur des éléments de type chaîne, il faudrait passer par des fonctions.

On est d'accord ?

Citation:

Envoyé par gui80

Par contre, pour faire des tris sur des éléments de type chaîne, il faudrait passer par des fonctions.

Hm pourquoi donc ?

Dans le VALUES tu créé une fausse table avec 2 colonnes, l'une contient la valeur (du type que tu veux) et l'autre (un entier c'est préférable) la position que tu veux lui donner dans le tri. La flemme de créer une table de test, donc :
Code:

1 2 3 4 5 6 7 8 9 10 SELECT a.* FROM (VALUES ('rouge'),('vert'),('bleu')) AS a LEFT JOIN (VALUES ('vert',1),('bleu',2),('rouge',3)) AS tri ON (a.column1=tri.column1) ORDER BY tri.column2; column1 --------- vert bleu rouge

28/10/2010, 10h51
gui80

Bonjour,

oui, grosse fatigue de ma part.
J'ai refait des tests avec mes tables, effectivement, ca fonctionne bien.

Merci encore.

Tous ces astuces comme ce tri avec VALUES, tu l'as vu dans un tutorial, dans des exemples très avancés ?

Si oui, j'aimerai bien y jeter un œil car là, j'ai été bluffé par ta solution qui en plus est hyper performante.
28/10/2010, 23h02
peufeu

Citation:

Envoyé par gui80

Tous ces astuces comme ce tri avec VALUES, tu l'as vu dans un tutorial, dans des exemples très avancés ?

Euh, je sais pas où j'ai trouvé ça, ça m'a semblé logique en lisant ton post.

SQL = un peu mcgyver des fois !

Il faut examiner les features de ton SGBDR et voir ce qu'il est possible d'en tirer... VALUES est un peu le couteau suisse, genre tu veux faire 50 UPDATEs d'un coup avec des valeurs différentes provenant de l'appli, tu peux.

Dans le même style, il y a INSERT/UPDATE/DELETE RETURNING qui permettent des schémas particulièrement élaborés... ou l'utilisation des array() dans les procédures stockées pour mémoriser un résultat de requête et rebalancer une liste d'ids dans la requête suivante.