Comment Demander une ligne aléatoire en SQL?

Pour SQL Server 2005 et 2008, si nous voulons un échantillon aléatoire de lignes individuelles (à partir de documentation en Ligne ):

SELECT * FROM Sales.SalesOrderDetail
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)
/ CAST (0x7fffffff AS int)

Place de à l'aide de RAND(), comme il n'est pas encouragée , vous pouvez simplement obtenir un max ID=Max):

SELECT MAX(ID) FROM TABLE;

obtenir un aléatoire entre 1..Max (=My_Generated_Random)

My_Generated_Random = rand_in_your_programming_lang_function(1..Max);

et ensuite exécuter ce SQL:

SELECT ID FROM TABLE WHERE ID >= My_Generated_Random ORDER BY ID LIMIT 1

notez qu'il vérifiera les lignes dont les ID sont égaux ou supérieurs à la valeur choisie. Il est également possible de chasser la ligne vers le bas dans la table, et d'obtenir un ID égal ou inférieur puis modifier la requête comme ceci:

SELECT ID FROM TABLE WHERE ID <= My_Generated_Random ORDER BY ID DESC LIMIT 1

comme indiqué dans le commentaire de @BillKarwin sur la réponse de @cnu...

en combinant avec une limite, j'ai trouvé qu'il fonctionne beaucoup mieux (au moins avec PostgreSQL 9.1) Pour joindre avec un ordre aléatoire plutôt que pour commander directement les lignes réelles: par exemple "151900920"

assurez-vous que le 'r' génère 'rand' valeur pour chaque valeur de clé dans la requête complexe qui est joint avec elle, mais encore limiter le nombre de lignes de 'r' lorsque cela est possible.

le plâtre en tant Qu'entier est particulièrement utile pour PostgreSQL 9.2 qui a une optimisation de tri spécifique pour les types entiers et les types flottants de précision simple.

la Plupart des solutions ici visent à éviter le tri, mais ils doivent encore faire un balayage séquentiel sur une table.

il y a aussi un moyen d'éviter le scan séquentiel en passant au scan index. Si vous connaissez la valeur d'index de votre ligne aléatoire, vous pouvez obtenir le résultat presque instantanément. Le problème est de deviner une valeur d'indice.

la solution suivante fonctionne sur PostgreSQL 8.4:

explain analyze select * from cms_refs where rec_id in 
  (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
   from generate_series(1,10))
  limit 1;

I ci-dessus solution vous devinez 10 différentes valeurs de l'indice aléatoire de l'intervalle 0 .. [dernière valeur de la pièce d'identité].

le nombre 10 est arbitraire - vous pouvez utiliser 100 ou 1000 car il (étonnamment) n'a pas un grand impact sur le temps de réponse.

il y a aussi un problème - si vous avez des ids épars vous pourriez manquer . La solution est de avoir un plan de sauvegarde :) dans ce cas un pur ordre ancien par requête aléatoire (). Lorsque l'id combiné ressemble à ceci:

explain analyze select * from cms_refs where rec_id in 
    (select (random()*(select last_value from cms_refs_rec_id_seq))::bigint 
     from generate_series(1,10))
    union all (select * from cms_refs order by random() limit 1)
    limit 1;

, Pas la union TOUS clause. Dans ce cas, si la première partie renvoie des données, la seconde ne sera jamais exécutée!

en retard, mais arrivé ici via Google, donc pour la postérité, je vais ajouter une solution alternative.

une autre approche consiste à utiliser TOP deux fois, avec des commandes alternées. Je ne sais pas si C'est" pure SQL", parce qu'il utilise une variable dans le haut, mais il fonctionne dans SQL Server 2008. Voici un exemple que j'utilise contre une table de mots de dictionnaire, si je veux un mot aléatoire.

SELECT TOP 1
  word
FROM (
  SELECT TOP(@idx)
    word 
  FROM
    dbo.DictionaryAbridged WITH(NOLOCK)
  ORDER BY
    word DESC
) AS D
ORDER BY
  word ASC

bien sûr, @idx est un entier généré au hasard qui varie de 1 à compter(*) sur la table cible, inclusivement. Si votre colonne est indexée, vous en bénéficierez aussi. Un autre avantage est que vous pouvez l'utiliser dans une fonction, puisque NEWID() est refusé.

enfin, la requête ci-dessus tourne dans environ 1/10 du temps exec d'un NEWID () - type de requête sur la même table. YYMV.

vous pouvez également essayer d'utiliser la fonction new id() .

il suffit d'écrire une requête et d'utiliser l'ordre par la fonction new id() . C'est assez aléatoire.

pour MySQL pour obtenir un enregistrement aléatoire

 SELECT name
  FROM random AS r1 JOIN
       (SELECT (RAND() *
                     (SELECT MAX(id)
                        FROM random)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1

plus de détails http://jan.kneschke.de/projects/mysql/order-by-rand /

N'a pas encore vu cette variation dans les réponses. J'avais une contrainte supplémentaire où j'ai besoin, étant donné une graine initiale, pour sélectionner le même ensemble de lignes à chaque fois.

pour MS SQL:

exemple minimal:

select top 10 percent *
from table_name
order by rand(checksum(*))

temps D'exécution normalisé: 1,00

NewId() exemple:

select top 10 percent *
from table_name
order by newid()

temps D'exécution normalisé: 1,02

NewId() est légèrement plus lent que rand(checksum(*)) , donc vous ne voudrez peut-être pas l'utiliser contre de grands ensembles de disques.

sélection avec semence initiale:

declare @seed int
set @seed = Year(getdate()) * month(getdate()) /* any other initial seed here */

select top 10 percent *
from table_name
order by rand(checksum(*) % seed) /* any other math function here */

Si vous devez sélectionner le même ensemble donné une graine, cela semble fonctionner.

Dans MSSQL (testé sur 11.0.5569) à l'aide de

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

est significativement plus rapide que

SELECT TOP 100 * FROM employee ORDER BY NEWID()

dans SQL Server vous pouvez combiner TABLESAMPLE avec NEWID() pour obtenir un assez bon aléatoire et avoir encore de la vitesse. Ceci est particulièrement utile si vous ne voulez 1, ou un petit nombre de lignes.

SELECT TOP 1 * FROM [table] 
TABLESAMPLE (500 ROWS) 
ORDER BY NEWID()

 SELECT * FROM table ORDER BY RAND() LIMIT 1

je dois être d'accord avec CD-MaN: utiliser" ORDER BY RAND () " fonctionnera bien pour les petites tables ou quand vous faites votre SELECT seulement quelques fois.

j'utilise aussi le "num_value >= RAND() * ..."technique, et si je veux vraiment avoir des résultats aléatoires, j'ai une colonne "aléatoire" spéciale dans le tableau que je mets à jour une fois par jour ou plus. Cette mise à jour simple prendra du temps (surtout parce que vous devrez avoir un index sur cette colonne), mais c'est beaucoup plus rapide que de créer nombres aléatoires pour chaque ligne chaque fois que le select est exécuté.

soyez prudent car TableSample ne renvoie pas un échantillon aléatoire de rangées. Il dirige votre requête pour regarder un échantillon aléatoire des 8KB pages qui composent votre rangée. Ensuite, votre requête est exécutée sur les données contenues dans ces pages. En raison de la façon dont les données peuvent être regroupées sur ces pages (ordre d'insertion, etc), cela pourrait conduire à des données qui ne sont pas en fait un échantillon aléatoire.

voir: http://www.mssqltips.com/tip.asp?tip=1308

cette page MSDN pour TableSample inclut un exemple de comment générer un échantillon réellement aléatoire de données.

http://msdn.microsoft.com/en-us/library/ms189108.aspx

il semble que beaucoup des idées énumérées utilisent encore la commande

cependant, si vous utilisez une table temporaire, vous êtes en mesure d'assigner un index aléatoire (comme beaucoup de solutions ont suggéré), et puis saisir le premier qui est plus grand qu'un nombre arbitraire entre 0 et 1.

par exemple (pour DB2):

WITH TEMP AS (
SELECT COMLUMN, RAND() AS IDX FROM TABLE)
SELECT COLUMN FROM TABLE WHERE IDX > .5
FETCH FIRST 1 ROW ONLY

un moyen simple et efficace de http://akinas.com/pages/en/blog/mysql_random_row /

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

il y a une meilleure solution pour Oracle au lieu d'utiliser dbms_random.valeur, alors qu'il nécessite un balayage complet pour ordonner les lignes par dbms_random.valeur et il est assez lent pour les grandes tables.

utilisez ceci à la place:

SELECT *
FROM employee sample(1)
WHERE rownum=1

Pour Firebird:

Select FIRST 1 column from table ORDER BY RAND()

avec SQL Server 2012+ vous pouvez utiliser le OFFSET FETCH query pour faire ceci pour une seule ligne aléatoire

select  * from MyTable ORDER BY id OFFSET n ROW FETCH NEXT 1 ROWS ONLY

où id est une colonne d'identité, et n est la ligne que vous voulez-calculé comme un nombre aléatoire entre 0 et le compte () -1 de la table (offset 0 est la première ligne après tout)

cela fonctionne avec des trous dans les données de table, aussi longtemps que vous avez un index à travailler avec pour L'ordre Par clause. Son aussi très bon pour le hasard - que vous travaillez que vous-même de passer dans les niggles, mais dans d'autres méthodes ne sont pas présents. En outre la performance est assez bonne, sur un plus petit ensemble de données il tient bien, bien que je n'ai pas essayé des tests de performance sérieux contre plusieurs millions de lignes.

pour SQL Server 2005 et au-dessus, prolongeant la réponse de @GreyPanther pour les cas où num_value n'a pas de valeurs continues. Cela fonctionne aussi pour les cas où nous n'avons pas distribué uniformément les ensembles de données et où num_value n'est pas un numéro mais un identifiant unique.

WITH CTE_Table (SelRow, num_value) 
AS 
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SelRow, num_value FROM table
) 

SELECT * FROM table Where num_value = ( 
    SELECT TOP 1 num_value FROM CTE_Table  WHERE SelRow >= RAND() * (SELECT MAX(SelRow) FROM CTE_Table)
)

fonction aléatoire de la sql pourrait aider. Aussi, si vous souhaitez limiter à une seule ligne, il suffit d'ajouter qu'à la fin.

SELECT column FROM table
ORDER BY RAND()
LIMIT 1