Вернуть пять рядов случайной ДНК вместо одного

Это код, который мне нужен для создания строки ДНК:

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);

Я пытаюсь понять, как переписать это, чтобы получить таблицу из 5 строк строк ДНК длиной 20 каждая вместо одной строки. Это для PostgreSQL.

Я старался:

CREATE TABLE dna_table(g int, dna text);
INSERT INTO dna_table (1, execute dna_length(20));

Но это, похоже, не работает. Я абсолютный новичок. Как это сделать правильно?


postgresql sql random prepared-statement set-returning-functions
person Sharquinn    schedule 30.04.2020    source источник

Ответы (2)


arrow_upward
0
arrow_downward

PREPARE создает подготовленный оператор, который можно использовать «как есть». Если ваш подготовленный оператор возвращает одну строку, вы можете получить только одну строку. Вы не можете использовать его в других операциях, таких как вставка, например.

В вашем случае вы можете создать функцию:

create or replace function dna_length(int) returns text 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
$$ language sql;

И используйте его следующим образом:

insert into dna_table(g, dna) select generate_series(1,5), dna_length(20)

Из официального документа:

PREPARE создает подготовленный оператор. Подготовленный оператор — это серверный объект, который можно использовать для оптимизации производительности. Когда оператор PREPARE выполняется, указанный оператор анализируется, анализируется и перезаписывается. Когда впоследствии выдается команда EXECUTE, подготовленный оператор планируется и выполняется. Такое разделение труда позволяет избежать повторяющейся работы по анализу синтаксического анализа, в то же время позволяя плану выполнения зависеть от конкретных предоставленных значений параметров.

О функциях.

person Nikolai    schedule 30.04.2020

arrow_upward
0
arrow_downward

Это может быть намного проще и быстрее:

SELECT string_agg(CASE ceil(random() * 4)
                   WHEN 1 THEN 'A'
                   WHEN 2 THEN 'C'
                   WHEN 3 THEN 'T'
                   WHEN 4 THEN 'G'
                  END, '') AS dna
FROM   generate_series(1,100) g  -- 100 = 5 rows * 20 nucleotides
GROUP  BY g%5;

random() выдает random value in the range 0.0 <= x < 1.0. Умножьте на 4 и возьмите математический потолок с ceil () (дешевле, чем round()), и вы получаете случайное распределение чисел 1-4. Преобразование в ACTG и объединение с GROUP BY g%5 — < strong>% — оператор по модулю.

О string_agg():

Как подготовленный оператор, принимая
$1 ... количество строк
$2 ... количество нуклеотидов в строке

PREPARE dna_length(int, int) AS
SELECT string_agg(CASE ceil(random() * 4)
                   WHEN 1 THEN 'A'
                   WHEN 2 THEN 'C'
                   WHEN 3 THEN 'T'
                   WHEN 4 THEN 'G'
                  END, '') AS dna
FROM   generate_series(1, $1 * $2) g
GROUP  BY g%$1;

Вызов:

EXECUTE dna_length(5,20);

Результат:

| dna                  |
| :------------------- |
| ATCTTCGACACGTCGGTACC |
| GTGGCTGCAGATGAACAGAG |
| ACAGCTTAAAACACTAAGCA |
| TCCGGACCTCTCGACCTTGA |
| CGTGCGGAGTACCCTAATTA |

db‹>скрипка здесь

Если вам это очень нужно, рассмотрите вместо этого функцию. Видеть:

person Erwin Brandstetter    schedule 30.04.2020