PostgreSQL：生成序列

Question

下面的查询生成一行DNA序列

prepare dna_length(int) as
with t1 as (select chr(65) as s union select chr(67) union select chr(71) union select chr(84) ),

 t2 as ( select s, row_number() over() as rn from t1),

 t3 as ( select generate_series(1,$1) as i,round(random() * 4 + 0.5) as rn ),

 t4 as ( select t2.s from t2 join t3 on (t2.rn=t3.rn))

select array_to_string(array(select s from t4),'') as dna ;

execute dna_length(20);

结果-dna CCCGGTTCTCCCCATGCAGG

我想生成10个随机DNA序列

Answer 1

像这样吗？

select x, string_agg((array['A', 'C', 'G', 'T'])[1 + floor(random() * 4)], '')
from generate_series(1, 20, 1) gsn(n) cross join
     generate_series(1, 10, 1) gsx(x)
group by x