Qual é a melhor maneira de obter um pedido aleatório?

ORDER BY NEWID() classificará os registros aleatoriamente. Um exemplo aqui

SELECT *
FROM Northwind..Orders 
ORDER BY NEWID()

Esta é uma pergunta antiga, mas falta um aspecto da discussão, na minha opinião -- PERFORMANCE. ORDER BY NewId()é a resposta geral. Quando alguém fica chique, eles acrescentam que você deve realmente envolver NewID(), CheckSum()você sabe, para o desempenho!

O problema com esse método é que você ainda garante uma verificação completa do índice e, em seguida, uma classificação completa dos dados. Se você trabalhou com qualquer volume de dados sério, isso pode se tornar caro rapidamente. Veja este plano de execução típico e observe como a classificação leva 96% do seu tempo...

Para dar uma ideia de como isso é dimensionado, darei dois exemplos de um banco de dados com o qual trabalho.

• TableA - tem 50.000 linhas em 2.500 páginas de dados. A consulta aleatória gera 145 leituras em 42ms.
• Tabela B - tem 1,2 milhão de linhas em 114.000 páginas de dados. A execução Order By newid()nessa tabela gera 53.700 leituras e leva 16 segundos.

A moral da história é que, se você tiver tabelas grandes (pense em bilhões de linhas) ou precisar executar essa consulta com frequência, o newid()método será interrompido. Então, o que um garoto pode fazer?

Conheça TABLESAMPLE()

No SQL 2005 foi criado um novo recurso chamado TABLESAMPLE. Eu só vi um artigo discutindo seu uso ... deveria haver mais. Documentos do MSDN aqui . Primeiro um exemplo:

SELECT Top (20) *
FROM Northwind..Orders TABLESAMPLE(20 PERCENT)
ORDER BY NEWID()

A ideia por trás da amostra da tabela é fornecer aproximadamente o tamanho do subconjunto que você solicita. O SQL numera cada página de dados e seleciona X por cento dessas páginas. O número real de linhas que você recebe de volta pode variar com base no que existe nas páginas selecionadas.

Então, como eu uso? Selecione um tamanho de subconjunto que cubra mais do que o número de linhas de que você precisa e adicione um arquivo Top(). A idéia é que você pode fazer sua mesa gigantesca parecer menor antes do tipo caro.

Pessoalmente, tenho usado isso para limitar o tamanho da minha mesa. Então, nessa tabela de milhões de linhas, top(20)...TABLESAMPLE(20 PERCENT)a consulta cai para 5.600 leituras em 1.600 ms. Há também uma REPEATABLE()opção onde você pode passar um "Seed" para seleção de página. Isso deve resultar em uma seleção de amostra estável.

De qualquer forma, apenas pensei que isso deveria ser adicionado à discussão. Espero que ajude alguém.

A primeira sugestão de Pradeep Adiga, ORDER BY NEWID(), é boa e algo que usei no passado por esse motivo.

Tenha cuidado ao usar RAND()- em muitos contextos, ele é executado apenas uma vez por instrução, portanto ORDER BY RAND(), não terá efeito (já que você está obtendo o mesmo resultado de RAND() para cada linha).

Por exemplo:

SELECT display_name, RAND() FROM tr_person

retorna cada nome da nossa tabela de pessoas e um número "aleatório", que é o mesmo para cada linha. O número varia cada vez que você executa a consulta, mas é sempre o mesmo para cada linha.

Para mostrar que o mesmo é o caso de RAND()usado em uma ORDER BYcláusula, tento:

SELECT display_name FROM tr_person ORDER BY RAND(), display_name

Os resultados ainda são ordenados pelo nome, indicando que o campo de classificação anterior (o que se espera que seja aleatório) não tem efeito, portanto, presumivelmente, sempre tem o mesmo valor.

Ordenar por NEWID()funciona, porque se NEWID() nem sempre fosse reavaliado, o propósito dos UUIDs seria quebrado ao inserir muitas novas linhas em uma instrução com identificadores exclusivos à medida que eles digitam, então:

SELECT display_name FROM tr_person ORDER BY NEWID()

ordena os nomes "aleatoriamente".

Outros SGBD

O acima é verdadeiro para MSSQL (2005 e 2008 pelo menos, e se bem me lembro de 2000 também). Uma função que retorna um novo UUID deve ser avaliada toda vez que em todos os SGBDs NEWID() estiver sob MSSQL mas vale a pena verificar isso na documentação e/ou pelos seus próprios testes. O comportamento de outras funções de resultados arbitrários, como RAND(), é mais provável que varie entre DBMSs, portanto, verifique novamente a documentação.

Também vi a ordenação por valores UUID sendo ignorada em alguns contextos, pois o banco de dados pressupõe que o tipo não tem ordenação significativa. Se você achar que esse é o caso, converta explicitamente o UUID para um tipo de string na cláusula de ordenação ou envolva alguma outra função como CHECKSUM()no SQL Server (pode haver uma pequena diferença de desempenho disso também, pois a ordenação será feita em um valor de 32 bits não um de 128 bits, embora se o benefício disso supere o custo de execução CHECKSUM()por valor primeiro, deixarei você testar).

Nota

Se você deseja uma ordenação arbitrária, mas um tanto repetível, ordene por algum subconjunto relativamente não controlado dos dados nas próprias linhas. Por exemplo, um ou estes retornarão os nomes em uma ordem arbitrária, mas repetível:

SELECT display_name FROM tr_person ORDER BY CHECKSUM(display_name), display_name -- order by the checksum of some of the row's data
SELECT display_name FROM tr_person ORDER BY SUBSTRING(display_name, LEN(display_name)/2, 128) -- order by part of the name field, but not in any an obviously recognisable order)

Ordens arbitrárias mas repetíveis não costumam ser úteis em aplicativos, embora possam ser úteis em testes se você quiser testar algum código em resultados em uma variedade de ordens, mas quiser repetir cada execução da mesma maneira várias vezes (para obter o tempo médio resultados em várias execuções, ou testar se uma correção que você fez no código remove um problema ou ineficiência anteriormente destacado por um conjunto de resultados de entrada específico, ou apenas para testar se seu código é "estável" e retorna o mesmo resultado todas as vezes se enviou os mesmos dados em uma determinada ordem).

Esse truque também pode ser usado para obter resultados mais arbitrários de funções, que não permitem chamadas não determinísticas como NEWID() dentro de seu corpo. Novamente, isso não é algo que provavelmente será útil no mundo real, mas pode ser útil se você quiser que uma função retorne algo aleatório e "random-ish" é bom o suficiente (mas tenha cuidado para lembrar as regras que determinam quando funções definidas pelo usuário são avaliadas, ou seja, geralmente apenas uma vez por linha, ou seus resultados podem não ser o que você espera/exige).

atuação

Como EBarr aponta, pode haver problemas de desempenho com qualquer um dos itens acima. Para mais do que algumas linhas, você tem quase a garantia de ver a saída em spool para tempdb antes que o número solicitado de linhas seja lido na ordem correta, o que significa que, mesmo se você estiver procurando os 10 principais, poderá encontrar um índice completo scan (ou pior, scan de tabela) acontece junto com um enorme bloco de escrita no tempdb. Portanto, pode ser de vital importância, como a maioria das coisas, comparar com dados realistas antes de usá-los na produção.

Muitas tabelas têm uma coluna de ID numérica indexada relativamente densa (poucos valores ausentes).

Isso nos permite determinar o intervalo de valores existentes e escolher linhas usando valores de ID gerados aleatoriamente nesse intervalo. Isso funciona melhor quando o número de linhas a serem retornadas é relativamente pequeno e o intervalo de valores de ID é densamente preenchido (portanto, a chance de gerar um valor ausente é pequena o suficiente).

Para ilustrar, o código a seguir escolhe 100 usuários aleatórios distintos da tabela de usuários do Stack Overflow, que tem 8.123.937 linhas.

O primeiro passo é determinar o intervalo de valores de ID, uma operação eficiente devido ao índice:

DECLARE 
    @MinID integer,
    @Range integer,
    @Rows bigint = 100;

--- Find the range of values
SELECT
    @MinID = MIN(U.Id),
    @Range = 1 + MAX(U.Id) - MIN(U.Id)
FROM dbo.Users AS U;

O plano lê uma linha de cada extremidade do índice.

Agora geramos 100 IDs aleatórios distintos no intervalo (com linhas correspondentes na tabela de usuários) e retornamos essas linhas:

WITH Random (ID) AS
(
    -- Find @Rows distinct random user IDs that exist
    SELECT DISTINCT TOP (@Rows)
        Random.ID
    FROM dbo.Users AS U
    CROSS APPLY
    (
        -- Random ID
        VALUES (@MinID + (CONVERT(integer, CRYPT_GEN_RANDOM(4)) % @Range))
    ) AS Random (ID)
    WHERE EXISTS
    (
        SELECT 1
        FROM dbo.Users AS U2
            -- Ensure the row continues to exist
            WITH (REPEATABLEREAD)
        WHERE U2.Id = Random.ID
    )
)
SELECT
    U3.Id,
    U3.DisplayName,
    U3.CreationDate
FROM Random AS R
JOIN dbo.Users AS U3
    ON U3.Id = R.ID
-- QO model hint required to get a non-blocking flow distinct
OPTION (MAXDOP 1, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

O plano mostra que, neste caso, foram necessários 601 números aleatórios para encontrar 100 linhas correspondentes. É bem rápido:

Tabela 'Usuários'. Contagem de varredura 1, leituras lógicas 1937, leituras físicas 2, leituras antecipadas 408
Tabela 'Mesa de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0
Tabela 'Arquivo de trabalho'. Contagem de varredura 0, leituras lógicas 0, leituras físicas 0, leituras antecipadas 0

 Tempos de execução do SQL Server:
   Tempo de CPU = 0 ms, tempo decorrido = 9 ms.

Experimente no Stack Exchange Data Explorer.

Como expliquei neste artigo , para embaralhar o conjunto de resultados SQL, você precisa usar uma chamada de função específica do banco de dados.

Observe que classificar um conjunto de resultados grande usando uma função RANDOM pode ser muito lento, portanto, certifique-se de fazer isso em conjuntos de resultados pequenos.

Se você precisar embaralhar um grande conjunto de resultados e limitá-lo posteriormente, é melhor usar o SQL Server TABLESAMPLEno SQL Server em vez de uma função aleatória na cláusula ORDER BY.

Então, supondo que tenhamos a seguinte tabela de banco de dados:

E as seguintes linhas na songtabela:

| id | artist                          | title                              |
|----|---------------------------------|------------------------------------|
| 1  | Miyagi & Эндшпиль ft. Рем Дигга | I Got Love                         |
| 2  | HAIM                            | Don't Save Me (Cyril Hahn Remix)   |
| 3  | 2Pac ft. DMX                    | Rise Of A Champion (GalilHD Remix) |
| 4  | Ed Sheeran & Passenger          | No Diggity (Kygo Remix)            |
| 5  | JP Cooper ft. Mali-Koa          | All This Love                      |

No SQL Server, você precisa usar a NEWIDfunção, conforme ilustrado pelo exemplo a seguir:

SELECT
    CONCAT(CONCAT(artist, ' - '), title) AS song
FROM song
ORDER BY NEWID()

Ao executar a consulta SQL mencionada no SQL Server, obteremos o seguinte conjunto de resultados:

| song                                              |
|---------------------------------------------------|
| Miyagi & Эндшпиль ft. Рем Дигга - I Got Love      |
| JP Cooper ft. Mali-Koa - All This Love            |
| HAIM - Don't Save Me (Cyril Hahn Remix)           |
| Ed Sheeran & Passenger - No Diggity (Kygo Remix)  |
| 2Pac ft. DMX - Rise Of A Champion (GalilHD Remix) |

Observe que as músicas estão sendo listadas em ordem aleatória, graças à NEWIDchamada de função usada pela cláusula ORDER BY.

Este é um tópico antigo, mas me deparei com isso recentemente; então atualizando um método que funcionou para mim e dá um bom desempenho. Isso pressupõe que sua tabela tenha uma coluna IDENTITY ou semelhante:

DECLARE @r decimal(8,6) = rand()
SELECT @r

SELECT  TOP 100 *
FROM    TableA
ORDER BY ID % @r