Quel est le meilleur moyen d’obtenir un ordre aléatoire ?

La première suggestion de Pradeep Adiga, ORDER BY NEWID(), est bien et quelque chose que j’ai utilisé dans le passé pour cette raison.

Soyez prudent avec l’utilisation de RAND() – dans de nombreux contextes, il n’est exécuté qu’une fois par déclaration, donc ORDER BY RAND() n’aura aucun effet (puisque vous obtenez le même résultat de RAND() pour chaque ligne).

Par exemple :

SELECT display_name, RAND() FROM tr_person

renvoie chaque nom de notre table de personnes et un nombre « aléatoire », qui est le même pour chaque ligne. Le nombre varie effectivement chaque fois que vous exécutez la requête, mais il est le même pour chaque ligne à chaque fois.

Pour montrer qu’il en est de même avec RAND() utilisé dans une clause ORDER BY, j’essaie:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

Les résultats sont toujours ordonnés par le nom, ce qui indique que le champ de tri antérieur (celui qui est censé être aléatoire) n’a pas d’effet et a donc vraisemblablement toujours la même valeur.

Le tri par NEWID() fonctionne cependant, car si NEWID() n’était pas toujours réévalué, l’objectif des UUID serait brisé lors de l’insertion de nombreuses nouvelles lignes dans un statemnt avec des identifiants uniques comme clé, donc:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordonne les noms « au hasard ».

Autres SGBD

Ce qui précède est vrai pour MSSQL (2005 et 2008 au moins, et si je me souviens bien 2000 aussi). Une fonction retournant un nouvel UUID devrait être évaluée à chaque fois dans tous les SGBDs NEWID() est sous MSSQL mais cela vaut la peine de le vérifier dans la documentation et/ou par vos propres tests. Le comportement d’autres fonctions à résultat arbitraire, comme RAND(), est plus susceptible de varier entre les SGBD, donc encore une fois vérifiez la documentation.

En outre, j’ai vu l’ordonnancement par les valeurs UUID être ignoré dans certains contextes, car la DB suppose que le type n’a pas d’ordonnancement significatif. Si vous trouvez que c’est ce cas explicitement cast l’UUID à un type de chaîne de caractères dans la clause d’ordonnancement, ou envelopper une autre fonction autour d’elle comme CHECKSUM() dans SQL Server (il peut y avoir une petite différence de performance de cela aussi car l’ordonnancement sera fait sur une valeurs 32 bits et non 128 bits, bien que si l’avantage de cela l’emporte sur le coût de l’exécution de CHECKSUM() par valeur d’abord je vous laisse tester).

Note annexe

Si vous voulez un ordre arbitraire mais quelque peu répétable, ordonnez par un certain sous-ensemble relativement incontrôlé des données dans les lignes elles-mêmes. Par exemple, l’un ou l’autre de ces éléments renverra les noms dans un ordre arbitraire mais répétable :

Les ordonnancements arbitraires mais répétables ne sont pas souvent utiles dans les applications, bien qu’ils puissent être utiles dans les tests si vous voulez tester un certain code sur des résultats dans une variété d’ordres, mais que vous voulez pouvoir répéter chaque exécution de la même manière plusieurs fois (pour obtenir des résultats de chronométrage moyen sur plusieurs exécutions, ou pour tester qu’une correction que vous avez apportée au code supprime bien un problème ou une inefficacité précédemment mis en évidence par un ensemble de résultats d’entrée particulier, ou simplement pour tester que votre code est « stable » en ce sens qu’il renvoie le même résultat à chaque fois si on lui envoie les mêmes données dans un ordre donné).

Cette astuce peut également être utilisée pour obtenir des résultats plus arbitraires à partir de fonctions, qui ne permettent pas d’appels non déterministes comme NEWID() dans leur corps. Encore une fois, ce n’est pas quelque chose qui est susceptible d’être souvent utile dans le monde réel, mais pourrait être utile si vous voulez qu’une fonction renvoie quelque chose d’aléatoire et « aléatoire » est assez bon (mais faites attention à vous rappeler les règles qui déterminent quand les fonctions définies par l’utilisateur sont évaluées, c’est-à-dire généralement une seule fois par ligne, ou vos résultats peuvent ne pas être ce que vous attendez/exigez).

Performance

Comme EBarr le souligne, il peut y avoir des problèmes de performance avec tout ce qui précède. Pour plus de quelques lignes, vous êtes presque garanti de voir la sortie spoilée vers tempdb avant que le nombre demandé de lignes ne soit relu dans le bon ordre, ce qui signifie que même si vous recherchez le top 10, vous pourriez trouver un balayage d’index complet (ou pire, un balayage de table) se produit en même temps qu’un énorme bloc d’écriture vers tempdb. C’est pourquoi il peut être d’une importance vitale, comme pour la plupart des choses, de faire un benchmark avec des données réalistes avant d’utiliser ceci en production.