sql—生成5个随机dna序列的查询，每个序列大约有20个碱基，

5cg8jx4n 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(252)

这个问题在这里已经有答案了：

postgresql:generate sequence （2个答案）
10个月前关门了。
我得到了前20行要解决的查询，但我不知道如何将其扩展到5行

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 ;

在此处输入图像描述

sql postgresql recursive-query Random dna-sequence

来源：https://stackoverflow.com/questions/62584420/a-query-to-generate-5-random-dna-sequences-that-are-each-about-20-bases

1条答案

按热度按时间

ycggw6v21#

这对你有用吗？你可以把1美元放回去换掉20美元 t3 如果是的话。

with t1 as (
    select 1 as rn, 'A' as s
    union select 2, 'C' 
    union select 3, 'T' 
    union select 4, 'G' 
), t2 as (
    select generate_series(1, 5) as sample
), t3 as ( 
    select t2.sample, generate_series(1,20) as i,
           round(random() * 4 + 0.5) as rn 
      from t2
), t4 as (
    select t3.sample, t3.i, t3.rn, t1.s
      from t3 
      join t1 on t1.rn = t3.rn
) 
select sample, string_agg(s, '' order by i) 
  from t4
 group by sample
 order by sample;

 sample |      string_agg      
--------+----------------------
      1 | AGCATCAAGCGGTAAAAAAG
      2 | ATATCGCGCCGAGGGAAGAC
      3 | GAAACCCCATCTTAACTGGA
      4 | AGTGAGGCCGCCACTCTACC
      5 | AGTCCCCACAACGATTAGAA
(5 rows)

赞(0）回复(0）举报 2021-07-24

我来回答

sql—生成5个随机dna序列的查询，每个序列大约有20个碱基，

1条答案

相关问题

热门标签

最新问答