Discussion :

[jmacrez]

Bonsoir,

A partir d'un résultat de requête aléatoire, est-il possible d'obtenir n lignes aléatoirement.

Je m'explique :

j'ai une requête qui me renvoie 100 lignes. j'en veux 40 parmi les 100.
Le rownum = 40 me renvoie les 40 premieres
Existe t il une syntaxe me renvoyant 40 lignes au hasard ?

Merci par avance de vos réponses.

Jérôme.

[Invité]

Existe t il une syntaxe me renvoyant 40 lignes au hasard ?

Oui, je me souviens avoir déjà vu ça, mais désolé, je ne l'ai pas en mémoire...
Désolé pour cette moitié de réponse...

PS : La version de bd serait bienvenue...

[salim11]

Salut,

Il suffit de rajouter à ta requête ORDER BY dbms_random.value

Code :

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

1
2
3
4
5
 
 SELECT    col1,col2..............coln 
    FROM    nom_table
    WHERE  rownum <= 40
    ORDER BY dbms_random.value

http://www.petefreitag.com/item/466.cfm

[jmacrez]

Bonjour,

je crois que l'on y est presque au résultat attendu.

La réponse apportée me ramene les 40 1eres lignes avec un ordre aléatoire.

En fait ce que je veux c'est si la requete me renvoie 100 lignes j'aimerais qu'elle me renvoie 40 lignes des 100 lignes au hasard.

Merci de vos réponses.

Jérôme.

[Pomalaix]

Ah tiens, super intéressante cette astuce ! (On la trouve aussi citée chez Tom Kyte).

Par contre son fonctionnement m'échappe complètement (je viens de tester un peu sur une 10g).
En effet :
- ORDER BY n est censé trier selon la colonne n, mais on constate que ORDER BY DBMS_RANDOM.VALUE ne trie sur aucune colonne

- je n'aurais pas imaginé que n puisse ne pas être entier. On se rend compte cependant que si on utilise un décimal, celui ci est simplement tronqué, Oracle ne retenant que la partie entière.
Mais justement, DBMS_RANDOM.VALUE produit une valeur décimale dans l'intervalle [0, 1[ donc si celle-ci est tronquée, on tombe à zéro qui n'est pas une valeur admissible pour un ORDER BY.

- si on essaye de remplacer DBMS_RANDOM.VALUE par la valeur qu'il a générée, la requête échoue :
select * from scott.emp order by dbms_random.value ; --> OK

Code :

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

1
2
3
4
5
SQL> select * from scott.emp order by 0.8324578 ;
select * from scott.emp order by 0.8324578
                                 *
ERREUR à la ligne 1 :
ORA-01785: l'élément ORDER BY doit être le numéro d'une expression de la liste SELECT

Bref, cette syntaxe reste pleine de mystères...

[McM]

Je peux apporter une aide à la compréhension :
Le ORDER BY n'utilise pas que des NUMBER

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH t AS (SELECT TRUNC(SYSDATE) -2 dte FROM dual
		UNION SELECT TRUNC(SYSDATE) FROM dual
		UNION SELECT TRUNC(SYSDATE) + 2FROM dual
		UNION SELECT TRUNC(SYSDATE) + 3 FROM dual)
SELECT  dte, DECODE(SIGN(dte - TRUNC(SYSDATE)), -1, 0, 1) AS avant
FROM  t
ORDER BY DECODE(SIGN(dte - TRUNC(SYSDATE)), -1, 0, 1), dte DESC
 
DTE	AVANT
01/10/2008	0
06/10/2008	1
05/10/2008	1
03/10/2008	1

J'aurais pu remplacer le

Code :

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

1
2
ORDER BY DECODE(SIGN(dte - TRUNC(SYSDATE)), -1, 0, 1), dte DESC
par ORDER BY 2, 1 DESC

Imaginons maintenant avec le DBMS_RANDOM
Il faut savoir que chaque appel à DBMS_RANDOM génère une nouvelle valeur

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH t AS (SELECT TRUNC(SYSDATE) -2 dte FROM dual
		UNION SELECT TRUNC(SYSDATE) FROM dual
		UNION SELECT TRUNC(SYSDATE) + 2 FROM dual
		UNION SELECT TRUNC(SYSDATE) + 3 FROM dual)
SELECT  dte, TRUNC(dbms_random.VALUE(1,10)) rand1, TRUNC(dbms_random.VALUE(1,10)) rand2
FROM  t
ORDER BY 3
 
DTE	RAND1	RAND2
06/10/2008	4	1
03/10/2008	6	3
01/10/2008	6	4
05/10/2008	9	9

En fait le ORDER BY, si tu mets autre chose qu'un nombre,
par exemple une fonction, Oracle doit rajouter cette fonction à la partie SELECT et trier dessus.

Donc la requête précédente est équivalente (Tri sur rand2)

Code :

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

1
2
3
4
5
6
7
WITH t AS (SELECT TRUNC(SYSDATE) -2 dte FROM dual
		UNION SELECT TRUNC(SYSDATE) FROM dual
		UNION SELECT TRUNC(SYSDATE) + 2 FROM dual
		UNION SELECT TRUNC(SYSDATE) + 3 FROM dual)
SELECT  dte, TRUNC(dbms_random.VALUE(1,10)) rand1
FROM  t
ORDER BY TRUNC(dbms_random.VALUE(1,10)) -- Tri sur un autre random. EDIT C'est FAUX ! voir post suivant

[McM]

Je continue pour prouver que ce que j'ai dit est vrai pour le début et faux pour la dernière requête :
La requête précédente trie par rand1 !

Confirmation (requête lancée plusieurs fois)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY TRUNC(dbms_random.VALUE(1,100)) 
 
RAND1
2
10
28
31
33
70
80
83
87

Alors que

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY TRUNC(dbms_random.VALUE(1,10))
 
RAND1
4
92
74
29
99
8
69
12
79

Donc Oracle modifie la requête pour l'order by :

Donnée Order by équivalente à une donnée ramenée dans le SELECT

Code :

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

1
2
3
4
5
6
7
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY TRUNC(dbms_random.VALUE(1,100)) 
=>
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY 1

Donnée Order by n'existant pas dans les données ramenées dans le SELECT

Code :

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

1
2
3
4
5
6
7
8
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY TRUNC(dbms_random.VALUE(1,10)) 
=>
SELECT  TRUNC(dbms_random.VALUE(1,100)) rand1,
  TRUNC(dbms_random.VALUE(1,10)) nonaffichée
FROM  (SELECT 1 FROM all_objects WHERE ROWNUM < 10)
ORDER BY 2