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

[Pomalaix]

Eh oui effectivement, aveuglé par le caractère niladique de DBMS_RANDOM.VALUE et le type numérique de ses valeurs, j'ai voulu à tout prix faire le rapprochement avec la forme ORDER BY n, où n est une constante, calculée une fois pour toutes, et désignant un numéro de colonne.

Alors qu'il suffit de considérer le cas normal, où on trie non selon un numéro de colonne, mais selon un nom de colonne, ou une expression calculée à partir de la colonne.
Cette expression est bien entendu calculée pour chaque ligne (par exemple ORDER BY UPPER(ename)).

Dans le cas du DBMS_RANDOM.VALUE, il se trouve que la colonne de tri cumule diverses caractéristiques
- c'est une colonne calculée résultant d'une fonction niladique (sans paramètres)
- elle n'apparaît pas forcément dans les colonnes du SELECT

Si on introduit le DBMS_RANDOM.VALUE dans le SELECT, cela clarifie tout de suite le mécanisme, et on se rend bien compte que le random est calculé pour chaque ligne, et que le tri classe bien les lignes selon cette valeur aléatoire.

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
SQL> select dbms_random.value, scott.emp.* from scott.emp 
order by dbms_random.value;
 
     VALUE      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------
,135357648       1201                                 01/04/08
,201378601       1000                                 12/11/87
,324887118       7934 MILLER     CLERK           7782 23/01/82       1300                    10
 ,33533404       7900 JAMES      CLERK           7698 03/12/81        950                    30
,356827314       7521 WARD       SALESMAN        7698 22/02/81       1250        500         30
 ,46777383       7839 KING       PRESIDENT            17/11/81       5000                    10
,484999319       7788 SCOTT      ANALYST         7566 19/04/87       3000                    20
,501806207       7782 CLARK      MANAGER         7839 09/06/81       2450                    10
,515659071       1001                                 18/05/87
,519276032       7876 ADAMS      CLERK           7788 23/05/87       1100                    20
,529799207       7499 ALLEN      SALESMAN        7698 20/02/81       1600        300         30
,648186635       7369 SMITH      CLERK           7902 17/12/80        800                    20
,670264702       7654 MARTIN     SALESMAN        7698 28/09/81       1250       1400         30
,684613243       7844 TURNER     SALESMAN        7698 08/09/81       1500          0         30
,710392294       7566 JONES      MANAGER         7839 02/04/81       2975                    20
,807643374       1200                                 01/08/08
,874462506       7902 FORD       ANALYST         7566 03/12/81       3000                    20
,891921585       7698 BLAKE      MANAGER         7839 01/05/81       2850                    30

Merci McM de m'avoir remis les trous en face des yeux !