[ORDER BY] Tri personnalisé

**gui80** · 25/10/2010, 10h05

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.

**ced** · 25/10/2010, 12h08

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

**gui80** · 25/10/2010, 17h16

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 :

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

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)

**peufeu** · 26/10/2010, 00h10

Il y a plus rapide et plus simple :

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

**ced** · 26/10/2010, 09h09

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 :

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

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

**gui80** · 26/10/2010, 10h25

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 :

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

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.

**ced** · 26/10/2010, 10h44

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

Code :

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

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 :

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

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

ced

**gui80** · 26/10/2010, 10h59

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 :

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

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 :

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

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 :

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

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 ?

**ced** · 26/10/2010, 14h45

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

Code :

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

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

**peufeu** · 26/10/2010, 15h22

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 :

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

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 :

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

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 :

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

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

**ced** · 26/10/2010, 15h40

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

ced

**gui80** · 26/10/2010, 15h48

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.

**ced** · 26/10/2010, 16h17

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

**peufeu** · 26/10/2010, 19h02

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 :

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

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.

**gui80** · 27/10/2010, 10h36

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 ?

**peufeu** · 27/10/2010, 18h49

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 :

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

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

**gui80** · 28/10/2010, 10h51

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.

**peufeu** · 28/10/2010, 23h02

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.

[ORDER BY] Tri personnalisé

PostgreSQL

Discussions similaires

Partager

Partager