O problema
Usando SQLite v3.35.4 e v3.36.0 eu tenho uma first_name
tabela e uma surname
tabela 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 MATERIALIZED
o 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