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

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

[ORDER BY] Tri personnalisé

PostgreSQL

Vue hybride

Discussions similaires

Partager

Partager