Início / dba / Perguntas / 302614
Accepted
Nathan Goings
Asked: 2021-11-15 14:19:51 +0800 CST

Valor aleatório duplicado, não é possível selecionar N pares aleatórios, subconsulta não atualizando - sqlite

  • 772

O problema

Usando SQLite v3.35.4 e v3.36.0 eu tenho uma first_nametabela e uma surnametabela que tem uma lista de nomes comuns. Eu quero produzir N número de pares em uma nova tabela.

Eu escrevi esta consulta recursiva:

WITH RECURSIVE
cte(first_name, surname) AS (
    SELECT first_name, surname from ( -- always returns the same value
        select first_name, surname from (select first_name from first_name order by random() limit 1)
        join (select surname from surname order by random() limit 1)
    )
    UNION ALL
    SELECT first_name, surname
    FROM cte
    LIMIT 2000
)
SELECT first_name, surname FROM cte;

Infelizmente a saída fica assim:

+------------+---------+
| first_name | surname |
+------------+---------+
| james      | smith   |
| james      | smith   |
| james      | smith   |
| ---------- | ------- |
| ...        | ...     |
+------------+---------+

O que eu tentei

Depois de revisar a documentação do SQLite, experimentei NOT MATERIALIZEDo CTE recursivo e várias condições descritas na seção Subquery Flattening . Eu coloquei a seleção de nome aleatório em uma visão. No entanto, nada disso afetou positivamente os resultados.

Existe uma maneira de executar o que estou tentando fazer?

* Editar

Eu tentei uma função de janela e selecionando os nomes aleatoriamente de uma cláusula where sem sucesso: (onde 1998 é o tamanho da tabela)

with recursive
r_first_name as (
    select first_name, ROW_NUMBER() over(order by random()) as rn from first_name
),
r_surname as (
    select surname, ROW_NUMBER() over(order by random()) as rn from surname
),
rcte(first_name, surname) as (
    select first_name, surname from r_first_name rf
    join r_surname rs on rs.rn = (select abs(random() % 1998))
    where rf.rn = (select abs(random() % 1998))
    union all
    select first_name, surname from rcte
    limit 3000
)
select * from rcte

!!! Solução!!!

Depois de revisar esta resposta em um problema semelhante.

Descobri que no lado de recursão dos CTEs, um random()será atualizado com sucesso. Embora, infelizmente, ele não seja atualizado quando aninhado em uma subconsulta, se estiver na "raiz" da recursão CTE, posso utilizá-lo para obter um número aleatório.

Abaixo está a solução que desenvolvi. Ele atende ao meu caso de uso específico e é relativamente eficiente em comparação com uma junção cruzada:

WITH RECURSIVE
cte AS (
       select abs(random()) % (select count(*) from first_name) as first_name_num, abs(random()) % (select count(*) from surname) as surname_num
       union all
       select abs(random()) % (select count(*) from first_name) as first_name_num, abs(random()) % (select count(*) from surname) as surname_num from cte
        LIMIT 6000  
),
result as (
    select * from cte
    join (select first_name, ROW_NUMBER() over (order by random()) as rn from first_name) fn -- this is always the same result
    on cte.first_name_num = fn.rn
    join (select surname, ROW_NUMBER() over (order by random()) as rn from surname) sn -- this updates every loop around except subqueries are compiled/cached or something so they are unusable here if you want updated values
    on cte.surname_num = sn.rn
)
select first_name, surname from result
sqlite sqlite3

1 respostas

  1. Best Answer

    " Eu quero produzir um número N de pares em uma nova tabela. " Por que não simplesmente fazer a CROSS JOINcom uma LIMITcláusula assim?

    SELECT fn.first_name, sn.surname
FROM first_name fn
CROSS JOIN surname sn
LIMIT 2000;

    Isso deve produzir uma lista distinta de 2.000 linhas first_namee surnamecombinações (supondo que você não tenha duplicatas em cada tabela). É apenas pseudo-aleatório, pois os resultados não são determinísticos, pois não há ORDER BYcláusula. Mas é muito mais simples de uma consulta, especialmente se você só precisa fazer isso para carregar uma tabela uma vez.

    Você também pode adicionar uma ORDER BYcláusula com a RANDOM()função para melhorar a aleatoriedade da consulta acima, assim:

    SELECT fn.first_name, sn.surname
FROM first_name fn
CROSS JOIN surname sn
ORDER BY RANDOM()
LIMIT 2000;

    Minha intuição me diz que isso também não é perfeitamente aleatório , mas deve ser muito melhor do que minha primeira consulta, se você precisar de alguma aleatoriedade.

    Observe que também recomendo não nomear as colunas exatamente com o mesmo nome das tabelas às quais elas pertencem. Isso pode ser sintaticamente confuso e tornar a legibilidade mais difícil. Pessoalmente, eu teria uma única namestabela com um campo para denotar o tipo de nome que é (primeiro nome vs sobrenome etc). Mas se você quiser duas tabelas explícitas, eu apenas nomearia a coluna genericamente, como namejá que é autoexplicativa o suficiente quando você seleciona first_name.name.

    • 2

relate perguntas